Convert T-SQL to LINQ-to-SQL

Question

I have a table ContentHistory in a SQL Server 2008 database with a column Content of data type xml, NOT NULL. This column stores complete XML documents (an Intersection root node containing one or more Article nodes:

 <InterSection>
    <Article>
        <ID>1</<ID>
        ...other nodes/data
    </Article>
    <Article>
        <ID>2</<ID>
        ...other nodes/data
    </Article>
    <Article>
        <ID>3</<ID>
        ...other nodes/data
    </Article>
 </InterSection>

I have written the T-SQL that takes the XML data and shreds it so that I get a result row for each Article node for whichever rows I select from the ContentHistory table:

SELECT      T2.Articles.query('.')
FROM        ContentHistory
CROSS APPLY Content.nodes('/InterSection/Article') AS T2(Articles)
WHERE ... /* Filter records based on other columns in ContentHistory */

This works great but I don't want to call this as a stored procedure from my ASP.NET MVC application as all other database requests have so far been achieved using LINQ-to-SQL.

So the question is to how achieve this in LINQ (examples in C# please)?

Answer 1

staticboy,

Use Linq to SQL and Linq to XML in conjunction.

First Get XML Column from Data context. Assuming you have added .dbml file for LINQ to XML.

//Lets say you have database called TestDB which has your ContentHistory table
TestDBContext db = new TestDBContext();


//This will return as IQueryable. 
var result = db.ContentHistory.Select(p=>p.Content).Where(p=>p.Content == <your filter>);

You can perform foreach loop on var "result" and achieve desired result.

Peace

Answer 2

I know this is not exactly what you're looking for, but Linq to SQL has full support for stored procedures. When you're in the DBML designer, you can drag your sproc in and have LTS create strongly-typed methods for you.