Convert T-SQL to LINQ-to-SQL

Question

I have a table ContentHistoryin a SQL Server 2008 database with a Contentdata type column xml, NOT NULL. This column stores complete XML documents ( Intersectionroot node containing one or more nodes Article:

 <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 wrote T-SQL that takes XML data and splits it so that I get a row of results for each Articlenode for any rows that I select from the table ContentHistory:

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 fine, but I don't want to call it as a stored procedure from my ASP.NET MVC application, since all other database queries have so far been reached using LINQ-to-SQL.

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

Answer 1

staticboy,

Linq SQL Linq XML .

XML- . , .dbml 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>);

foreach var "result" .

Answer 2

, , , Linq to SQL . DBML-, sproc LTS .

Answer 3

. @pwzeus . 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();

T-SQL:

-- 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)

, , ContentHistory Content XML- <Article>. , .Skip(5). (5) SQL, , , 50 , 5, :

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

Answer 4

XML LINQ SQL, LINQ to XML , , LINQ to XML LINQ to SQL. XML, LINQ to XML Extended Event Manager :

    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; }
    }
}

, . open source codeplex, , .

Answer 5

Linqer. , TSQL Linq-To-SQL.