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

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.

Answer 2

You would return the XML from LINQ to SQL and then parse with LINQ to XML based on what I know, or perform the LINQ to XML parsing over the resulting object from LINQ to SQL. I parse XML using LINQ to XML in my Extended Event Manager application like this:

    private List<Bucket> ProcessXML(XDocument targetdata)
    {
 return new List<Bucket>(
                 from e in targetdata.Elements()
                 from d in e.Elements()
                 select new Bucket
                 {
                     count = (int)d.Attribute("count"),
                     truncated = (int)d.Attribute("trunc"),
                     bucket = (string)d.Element("value").Value
                 });
    }


    /*
    <BucketizerTarget truncated="0" buckets="256">
      <Slot count="2715303" trunc="0">
        <value>14</value>
      </Slot>
    </BucketizerTarget>
    */

public class Bucket
{
    internal string bucket;
    internal int count;
    internal int truncated;

    public string BucketSlot
    {
        get { return bucket; }
    }

    public int Count
    {
        get { return count; }
    }

    public int Truncated
    {
        get { return truncated; }
    }
}

there are a tone of other examples available in the source code for the Targets. It is opensource on codeplex so feel free to look all you want.

Answer 3

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 4

Here is the final solution to my problem. Credit to @pwzeus for his observations. This is an exact copy-and-paste of my test code from LINQPad:

var articles = 
    (from ch in ContentHistories
        .Where(ch=> ch.CompareTag == new Guid("D3C38885-58AB-45CB-A19C-8EF48360F29D")
            && ch.AgainstTag == new Guid("5832933B-9AF9-4DEC-9D8D-DA5F211A5B53")
            & ch.Created > DateTime.Now.AddDays(-3)) // Initial record filtering
    select ch.Content) // Only return the XML Content column
        .Elements("Article") // Get <Article> child elements
        .Select(article => new {
            Id = Convert.ToInt32(article.Element("Id").Value),
            AcessionNumber = (string)article.Element("AcessionNumber").Value,
            Headline = (string)article.Element("Headline").Value,
            PublicationDate = Convert.ToDateTime(article.Element("PublicationDate").Value),
            ArrivalDate = Convert.ToDateTime(article.Element("ArrivalDate").Value),
            Source = (string)article.Element("Source").Value,
            CopyRight = (string)article.Element("CopyRight").Value,
            Language = (string)article.Element("Language").Value,
            WordCount = String.IsNullOrEmpty(article.Element("WordCount").Value) ? 0 : Convert.ToInt32(article.Element("WordCount").Value),
            Snippet = (string)article.Element("Headline").Value,
            LeadParagraph = (string)article.Element("Headline").Value,
            ContentGuid = new Guid(article.Element("ContentGuid").Value)
        }) // Select and coerce data into new object
        .Skip(5) // Skip records for paging in web UI
        .Take(5) // Take only 1 page of records for display;

articles.Dump();

For the curious here is the T-SQL generated:

-- Region Parameters
DECLARE @p0 UniqueIdentifier = 'd3c38885-58ab-45cb-a19c-8ef48360f29d'
DECLARE @p1 UniqueIdentifier = '5832933b-9af9-4dec-9d8d-da5f211a5b53'
DECLARE @p2 DateTime = '2009-09-27 12:43:20.386'
-- EndRegion
SELECT [t0].[Content]
FROM [ContentHistory] AS [t0]
WHERE ([t0].[CompareTag] = @p0) AND ([t0].[AgainstTag] = @p1)
    AND ([t0].[Created] > @p2)

It would have been nice too only retrieve as many rows as needed on the database side but for each row in ContentHistory the Content field contains an XML document with a variable number of <Article> nodes. For example, if I .Skip(5).Take(5) on the SQL-side then I may have just skipped over 50 articles yet only return 5 if the rows contained the following article counts:

Row    ArticleCount
===    ============
1      10
2      5
3      20
4      10
5      5
6      1
7      1
8      1
9      1
10     1

Answer 5

I use Linqer. It costs money, but does a GREAT job of converting from TSQL to Linq-To-SQL queries.