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

[timor.super]

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").Attribute("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

 

[Jeroen Mostert]

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").Attribute("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()

 

[Jeroen Mostert]

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").Attribute("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()

....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.