In Working with Ids in Entity Framework I discussed my foray into using the ADO.NET Entity Framework (EF) to power thejoyofcode.com's blog engine. Here's another learning from that experience.

This isn't a workaround or a problem with EF but it does lead to one in the next post. It actually represents more of a Wow moment I had with EF; I wanted to select the next and previous post's title and link url to support the Next and Previous links on my blog. In T-SQL, I might do this with a sub-select. But what about a Sproc-free world with only LINQ queries? Exactly the same...

from p in be.Posts.Include("Posts").Include("Comments")
select new PostData
{
    Post = p,
    NextLinkData = (from n in be.Posts where n.PostedDate > p.PostedDate orderby n.PostedDate ascending select new PostLinkData { Title = n.Title, LinkTitle = n.LinkTitle }).Take(1).FirstOrDefault(),
    PreviousLinkData = (from prev in be.Posts where prev.PostedDate < p.PostedDate orderby prev.PostedDate descending select new PostLinkData { Title = prev.Title, LinkTitle = prev.LinkTitle }).Take(1).FirstOrDefault(),
};

And it generates good T-SQL. We make heavy use of caching on thejoyofcode.com so I'm not too worried if this adds a little work to the select query. NOTE - be sure to read the next post before running off and using this... Projection blows includes in Entity Framework