Filtering database records by xml column content using linq-to-sql and xlinq

Question

Hi

I need to select rows from database table using filtering by xml-type column.

table looks like (short version)

id
dbfield int
xmlfield xml

and i'm filtering it in this way

IQueryable<Data.entity> q = from u in datacontex.entities
select u;

if (val1.HasValue)
  q = q.Where( x => x.dbfield > val1.value)

if (val2.HasValue)
  q = q.Where( x=> x.dbfield < val2.value)

if (!string.IsNullOrEmpty(searchString))
 q = q.Where ( x=> x.xmlfield contains values from searchString)

XML in xmlfield is very simple it looks like

<doc>
  <item id="no">test/WZ/2009/04/02</item>
  <item id="title">blabla</item>
...

The question is how to add WHERE condition in linq and preferably this contition should translate to ms-sql query, without processing dataset on webservice application.

Thanks.

Answer 1

LINQ-to-SQL does not AFAIK support the xml extensions in TSQL. Two choices that I see:

• write a SPROC/udf for your entire query that uses the sql/xml syntax, and map that to your data-context
• write a UDF that does the test for a single row (returning a bool), map that to the data-context, and use ctx.SomeUdf(row) in the where clause of the LINQ

Answer 2

You could also create computed columns on your SQL Server table which extract those bits and pieces from the XML and store them as if they were "normal" fields on the table. I'm using that technique in various places all the time in production systems - works just fine.

After you do this, then you can use those like normal table fields and you can use them to filter in Linq-to-SQL - no problem.