CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sql
Kjensen
Kjensen

Reputation: 12374

How to query SQL Server table with XML column (as string) using Linq to SQL?

How can I use LINQ to SQL to query a table with an XML datatype column as an IQueryable?

SQL Server table: 

Id [int]
SomeData [xml]

An example of SomeData could be:

<container><Person>Joe Smith</person></container>

In T-SQL, I can query the table like this to find employees with a specific nam,e :

SELECT 
    *
FROM
    [MyTable]
WHERE 
    SomeData.value('(//*[local-name()="Person"])[1]', 'varchar(max)') = 'Joe Smith'

Or to treat the xml as a string, to search for a string regardless of xml schema:

SELECT 
    *
FROM
    [MyTable]
WHERE 
    CONVERT(varchar(max), SomeData) like '%Joe Smith%'

Now I turn to LINQ to SQL, and want to perform the second query:

var myTable = db.MyTable.Where(x => x.SomeData.Value.Contains("Joe Smith"));

I get the error:

"The member 'System.Xml.Linq.XElement.Value' has no supported translation to SQL.

I understand why I get the error, but is there a way to work around it?

Being able to query the column as a string (with no concept of xml-structure) is completely fine in this case.

Upvotes: 0

Views: 2555

Answers (3)

Ethan Drotning
Ethan Drotning

Reputation: 11

Instead of Contains you can use System.Data.Linq.SqlClient.SqlMethods.Like().

var myTable = db.MyTable.Where(x => SqlMethods.Like(x.SomeData, "%Joe Smith%"));

Upvotes: 1

Leventis1984
Leventis1984

Reputation: 82

Import xml file into a temporary table with a xml column and THE try to parse the file and put it into a relational table.

Upvotes: 0

Phil
Phil

Reputation: 42991

Create a view that exposes the Xml column as varchar(...) and query that.

Upvotes: 1

Related Questions