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 the old XmlDocument class. LINQ to SQL and Syndication class approach is more elegant and easy.
Here is my database structure:
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 the 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 let’s write a core of RSS feed. Let’s create a simple ASP.NET page. Just delete everything from the aspx page.
html
<%@ Page Language="C#" AutoEventWireup="true" Inherits="RSS_Recent_Articles" Codebehind="RSS-Recent-Articles.aspx.cs" %>
Then the code behind 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
