LINQ to SQL is My Hero!
(
Mar 05 2008 - 04:26:41 PM by
Timothy Khouri) - [
print blog
post]
I've been using LINQ for a while now (LINQ to Objects, and LINQ to SQL mainly) and I just saw something absolutely beautiful that I've never seen before. First, though, I'll give you a little history on the problem that I had (and everyone else in the world who uses Microsoft SQL Server will at some point run into).
Now, don't get me wrong, I love Microsoft SQL Server (2005 in particular), and I know that it is absolutely the best database out there... but there is one task in particular that comes up often, but is difficult to do.
The Scenario
Imagine if you had a website dedicated to top quality development articles (much like this one), and you wanted to display a "related blogs" section on your site. This would allow users to see blog posts that are similar to the article they are reading. Sounds simple enough, and it is... the only problem is that your articles can belong to multiple categories!
If you don't see the problem yet, then it's probably because you haven't run into it. So I'll show you by an example here why my simple desire won't work. If articles could only belong to one category, then your "get related blog posts" SQL code would look something like this:
SELECT
*
FROM
dbo.BlogPosts
WHERE
Category = @SelectedCategory
But, we want to be able to search multiple categories... So, we need to do something like this:
SELECT
*
FROM
dbo.BlogPosts
WHERE
Category = @SelectedCategoryA
OR Category = @SelectedCategoryB
The problem here is that there can be any number of selected categories... so unless you want to dynamically build a SQL string, your out of luck. Also, if you made a stored procedure to house your code, there's little you can do (that isn't a nasty hack) to get the job done.
LINQ to SQL Builds Dynamic Queries
So, to get to the good stuff, what I realized is that LINQ to SQL does the nasty deed of building a SQL string for you. So, here is my LINQ code (in C#):
string[] selectedCategories = new string[] { "ASP.NET", "LINQ", "WCF" }
var blogPosts = from blogPost in context.BlogPosts
where selectedCategories.Contains(blogPost.CategoryName)
select blogPost;
And here is the SQL code that will be built on the fly by LINQ:
SELECT * FROM dbo.BlogPosts WHERE CategoryName IN (@p0, @p1, @p2)
You guessed it; LINQ to SQL is parameterizing the categories and using an "IN" statement... beautiful!
Conclusion
If you have no idea what I'm talking about above, it's probably because you're not familiar with LINQ or SQL... but all I can say is, you should really start getting into .NET 3.5 and LINQ. Hopefully I can finish up this re-write soon and start getting back to development articles.