Re: Mixing linq to sql and linq to xml (in linqpad)
- From: Jeroen Mostert <jmostert@xxxxxxxxx>
- Date: Fri, 11 Jul 2008 13:05:14 +0200
Jeroen Mostert wrote:
timor.super@xxxxxxxxx wrote:....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:Hi group,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).
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)
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()
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.
.
- References:
- Mixing linq to sql and linq to xml (in linqpad)
- From: timor . super
- Re: Mixing linq to sql and linq to xml (in linqpad)
- From: Jeroen Mostert
- Mixing linq to sql and linq to xml (in linqpad)
- Prev by Date: Re: DLinq dynamic order by child entity property
- Next by Date: Re: RegExp
- Previous by thread: Re: Mixing linq to sql and linq to xml (in linqpad)
- Next by thread: Using stringbuilder with javascript alert
- Index(es):
Relevant Pages
|
