473,237 Members | 1,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Mixing linq to sql and linq to xml (in linqpad)

Hi group,

In my database, I have a table with fields like this :

id | title | xml
------------------------------------
1 | title1 | <datas><data><item name="item1">value1</
item><item name="item2">value2</item></data></datas>
......

I would like to filter my xml data, when it contains a item attribute
named item1

I'm using Linqpad with a request like that :

from i in myTable
where
i.xml.Descendants("data").Element("item").Attribut e("name").Value ==
"item1"
select i

but, linqpad tells me that :

'System.Collections.Generic.IEnumerable<System.Xml .Linq.XElement>'
does not contain a definition for 'Element' and no extension method
'Element' accepting a first argument of type
'System.Collections.Generic.IEnumerable<System.Xml .Linq.XElement>'
could be found (press F4 to add a using directive or assembly
reference)

Is this possible ? How to correct it ?

Thanks in advance for any help
Jul 11 '08 #1
2 4443
ti*********@gmail.com wrote:
Hi group,

In my database, I have a table with fields like this :

id | title | xml
------------------------------------
1 | title1 | <datas><data><item name="item1">value1</
item><item name="item2">value2</item></data></datas>
.....

I would like to filter my xml data, when it contains a item attribute
named item1

I'm using Linqpad with a request like that :

from i in myTable
where
i.xml.Descendants("data").Element("item").Attribut e("name").Value ==
"item1"
select i

but, linqpad tells me that :

'System.Collections.Generic.IEnumerable<System.Xml .Linq.XElement>'
does not contain a definition for 'Element' and no extension method
'Element' accepting a first argument of type
'System.Collections.Generic.IEnumerable<System.Xml .Linq.XElement>'
could be found (press F4 to add a using directive or assembly
reference)
It's telling you that "Descendants" will give you a collection of elements,
and you're trying to call "Element" on this collection (which only applies
to single elements).

This would work:

where i.xml.Descendants("data").Descendants("item").Any( item =(string)
item.Attribute("name") == "item1")

So would this (using XPath):

where i.xml.XPathSelectElements("/data/item[@name=\"item1\"]").Any()

--
J.
Jul 11 '08 #2
Jeroen Mostert wrote:
ti*********@gmail.com wrote:
>Hi group,

In my database, I have a table with fields like this :

id | title | xml
------------------------------------
1 | title1 | <datas><data><item name="item1">value1</
item><item name="item2">value2</item></data></datas>
.....

I would like to filter my xml data, when it contains a item attribute
named item1

I'm using Linqpad with a request like that :

from i in myTable
where
i.xml.Descendants("data").Element("item").Attribu te("name").Value ==
"item1"
select i

but, linqpad tells me that :

'System.Collections.Generic.IEnumerable<System.Xm l.Linq.XElement>'
does not contain a definition for 'Element' and no extension method
'Element' accepting a first argument of type
'System.Collections.Generic.IEnumerable<System.Xm l.Linq.XElement>'
could be found (press F4 to add a using directive or assembly
reference)
It's telling you that "Descendants" will give you a collection of
elements, and you're trying to call "Element" on this collection (which
only applies to single elements).

This would work:

where i.xml.Descendants("data").Descendants("item").Any( item =>
(string) item.Attribute("name") == "item1")

So would this (using XPath):

where i.xml.XPathSelectElements("/data/item[@name=\"item1\"]").Any()
....except that neither of these work if "myTable" is an actual SQL table,
because LINQ to SQL can't handle XML methods. This is quite unfortunate,
because SQL Server 2005 does have support for querying XML. You can get
around it by materializing the query results and working with that, of course:

from i in myTable.ToArray()
where i.xml.XPathSelectElements("/data/item[@name=\"item2\"]").Any()
select i

But this has the huge drawback of pulling in all the records for selecting
on the client side, which rather defeats the point of querying.

If your table will never become big enough for this to matter, it might be
acceptable. Otherwise, you could factor out the XML selection logic to the
database. For example:

CREATE FUNCTION dbo.SelectByItemName(@name AS NVARCHAR(MAX))
RETURNS TABLE
RETURN
SELECT id, title, xml FROM myTable
WHERE xml.exist('/datas/data/item[@name=sql:variable("@name")]') = 1;

You can then use LINQ to XML in the designer to create a mapping to this
function.

The obvious drawback to this method is that you need to carefully work out
your data needs in advance as far as the SQL selection is concerned. And if
you can do that, you may want to consider avoiding XML altogether and using
a pure relational database, which cooperates much more nicely with most data
access technologies.

--
J.
Jul 11 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: =?Utf-8?B?cmF1bGF2aQ==?= | last post by:
Hi all: after reading different places/sites about linq... I ran into these questions: 1. What framework do we need to run linq ? (does it depend on what version of visual studio we have?) how...
4
by: Jacek Jurkowski | last post by:
Why is it so slow? I really like that queries but using DataReader i have done my task's much more faster than ising LINQ...
0
by: =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post by:
I'm encountering some strange behavior after deploying a ASP.net 3.5 website to production, i'm unable to reproduce these in my dev environment. This error seems to occur very randomly but it's...
4
by: =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post by:
We’re storing our main entity in an insert only table which stores the history of past revisions, but we’re facing problems with storing this history as LINQ will only update the entity, and...
14
by: thj | last post by:
Hi, I was wondering what you guys are using and why? LINQ to SQL or NHibernate? Thanks in advance, Tommy
7
by: shapper | last post by:
Hello, Is it possible to multiply all Prices in a List<Productby 1.1 using Linq? Product has a property named Price. Thanks, Miguel
12
by: Eps | last post by:
Hi there, I am doing the following, this is a List of audio files. this.Where(p =p.Album == AnAudioFileObject.Album).Select(s => s.Artist).Distinct().Count() 1; The aim is to determine...
3
by: =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post by:
I'm attempting to use LINQ to insert a record into a child table and I'm receiving a "Specified cast is not valid" error that has something to do w/ the keys involved. The stack trace is: ...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.