How to build RSS feed using LINQ to SQL

I was watching PDC 2008 and Chris Anderson and Don Box mentioned their presentation on PDC 2006 about LINQ and RSS feeds. They were using SyndicationFeed class, which is new in .NET 3.5. Currently my own RSS feed was constructed manually using old XmlDocument class. LINQ to SQL and Syndication class aproach is more elegant and easy.

Here is my database structure:

Article Schema

Let's load articles first. First we need to disable deferred loading (DeferredLoadingEnabled = false), then we need to make sure that Category would be populated: opt.LoadWith<Article>(a => a.Category).

public static List<Article> GetArticles(int count)

{

    using (SQLDataContext db = new SQLDataContext())

    {

        db.DeferredLoadingEnabled = false;

        System.Data.Linq.DataLoadOptions opt = new System.Data.Linq.DataLoadOptions();

        opt.LoadWith<Article>(a => a.Category);

        db.LoadOptions = opt;

        return db.Articles.Take(count).OrderByDescending(a => a.CreationDate).ToList();

    }

}

Execution of this method is going to produce following SQL:

SELECT [t2].[Id], [t2].[FileName], [t2].[Title], [t2].[ShortDescription], [t2].[Description], [t2].[CreationDate], [t2].[CategoryId], [t2].[Keywords], [t2].[Active], [t2].[CategoryId2], [t2].[Name], [t2].[Description2], [t2].[MetaDescription], [t2].[MetaKeywords]

FROM (

    SELECT TOP (15) [t0].[Id], [t0].[FileName], [t0].[Title], [t0].[ShortDescription], [t0].[Description], [t0].[CreationDate], [t0].[CategoryId], [t0].[Keywords], [t0].[Active], [t1].[CategoryId] AS [CategoryId2], [t1].[Name], [t1].[Description] AS [Description2], [t1].[MetaDescription], [t1].[MetaKeywords]

    FROM [dbo].[Article] AS [t0]

    INNER JOIN [dbo].[Category] AS [t1] ON [t1].[CategoryId] = [t0].[CategoryId]

    ) AS [t2]

ORDER BY [t2].[CreationDate] DESC

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

 

Not bad, just one sql statement.

Now lets write a core of RSS feed. Let's create simple asp.net page. Just delete everything from aspx page.

<%@ Page Language="C#" AutoEventWireup="true" Inherits="RSS_Recent_Articles" Codebehind="RSS-Recent-Articles.aspx.cs" %>

 

Then codebehind file would look like this:

 

#region UI Handlers

 

    protected void Page_Load(object sender, EventArgs e)

    {

        SyndicationFeed feed = new SyndicationFeed()

        {

            Title = new TextSyndicationContent("Viktar Karpach Web Developer Blog about ASP.NET,C#,T-SQL,Javascript,CSS,Silverlight"),

            Language = "en-us",

            Description = new TextSyndicationContent("Viktar Karpach Web Developer Blog about ASP.NET,C#,T-SQL,Javascript,CSS,Silverlight. New technics and tehnlogies reviews."),

            Generator = "Karpach Web Developer Blog"

        };               

        List<SyndicationItem> items = new List<SyndicationItem>();

        foreach (Article a in Db.GetArticles(15))

        {

            SyndicationItem item = new SyndicationItem();

            item.AddPermalink(new Uri(Utils.GetAbsolutePath(a.FileName)));

            item.Title = new TextSyndicationContent(a.Title);                                   

            item.Summary = new TextSyndicationContent(a.Description,TextSyndicationContentKind.Html);

            item.PublishDate = a.CreationDate;

            item.LastUpdatedTime = a.CreationDate;

            item.Copyright = new TextSyndicationContent("Copyright (c) 2007 Viktar Karpach");

            item.Categories.Add(new SyndicationCategory(a.Category.Name));           

            items.Add(item);

        }

        feed.Items = items;

 

        Rss20FeedFormatter formater = new Rss20FeedFormatter(feed);

        XmlWriter xml = XmlWriter.Create(Response.OutputStream);

        formater.WriteTo(xml);

        xml.Flush();

        Response.Cache.SetExpires(DateTime.Now.AddHours(12));

        Response.Cache.SetCacheability(HttpCacheability.Public);

        Response.Cache.SetValidUntilExpires(false);

 

    }   

 

    #endregion  

 

kick it on DotNetKicks.com


Posted on Sunday, November 09, 2008 by | Comments (1) | Add Comment

Comments

Gravatar

Re:How to build RSS feed using LINQ to SQL

You are a great man, tnx for all

Posted on 7/3/2011 1:52:49 AM by aa #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Valid HTML5