If you take a quick detour over to StackOverflow, you’ll realize very, very quickly that, when it comes to wholly and full-heartedly screwing up your Linq to SQL statements, the screw-up almost always involves a misunderstanding of the purpose and the correct usage of Linq to SQL’s IQueryable interface.
If we screw up our usage of IQueryable, we’re pissing super-hard in a very, very long MVC journey downstream:
... Whoops.
I’ve spent the past couple of days perusing through blogs about Linq, and I’ve tried to gather together a handful of IQueryable Maxims. If you have any maxims you’d like to add, please feel free to chime in. The only rule is: the maxim must contain less than 30 letters. If the maxim contains more than 30 letters, you suck at games, and your willingness to defy my rules upsets me.
—
The first Maxim from the first paragraph in a post by Fredrik Normen
IQueryable Doesn’t Get
IQueryable<Customer> GetCustomers() doesn’t actually “get” anything. And that’s at the heart of any IQueryable result: until you iterate over the items in the IQueryable list, your IQueryable list is nothing more than an expression of the records that you want. When you filter the IQueryable list down, e.g.:
IQueryable<Customer> youngCustomers = repo.GetCustomers().Where(m => m.Age < 15);
you still haven't gotten anything. All you've done is modify the expression of the records that, at some point, you might want to retrieve! That lazy ol' IQueryable...
If it's easier, think of IQueryable as an a*hole (figurative). Your college roommate, maybe. You ask him to clean the dishes. He says, "did it." You ask him to get groceries. He says, "did it." Then you say, "No you didn't." And then he does all of it all at once, and says, "Yeah I did. Right now."
The point: IQueryable is first and foremost an expression builder, not a list. When you finally iterate over the "records" in the IQueryable list, IQueryable converts its expression into a SQL statement, fires the SQL statement to retrieve records from the database, and populates (finally) the IQueryable list with the Linq-mapped objects that you've asked for.
Quite unexpectedly, this point leads me right into the second maxim.
---
The Second Maxim Stems From a Near-Coma-Inspiring Fear Harnessed By Mike Hadlow:
Drop It Like It's Hot
When an IQueryable list has returned results from the database, get rid of it. In fact, for quite a few Linq-weary developers, the IQueryable "spout" should be "shut off" at the Repository layer. The methods in the repository, when called, do the heavy lifting work with IQueryable, and return an IList, an Array, an IDictionary, a new object, a single record, etc. This significantly reduces the possibility of an error occurring downstream because a developer's ill-conceived linq query gunks up the creation of the SQL statement. It also reduces the likeliness that a stupid programmer like myself might unknowingly grab an entire table, or multiple tables, from the database, thus slowing the entire website down to a sad, sad crawl...
For more: Justin Etheredge has an older but excellent post that explains why exposing IQueryable past your repository might be a bad idea.
So, when possible--if at all possible--try using this:
public class CustomerRepo : IRepo
{
private DataContext ct;
public Customer GetCustomerById(int id) { ... }
public Customer[] GetCustomersInAlaskaWithChildren() { ... }
}
Instead of this:
public class CustomerRepo : IRepo
{
private DataContext ct;
public IQueryable<Customer> Customers { get { return ctx.Customers.AsQueryable(); } }
}
---
This leads to Self-Explanatory Maximish Thing Two-Point-Five:
IQueryable Should NOT Be Found Hiding In Your MVC Controllers Or Your Views. That's Like Letting Your Best Friend Sleep In A Bed With Your Current Wife
...
---
The Third Maxim Stems From a Confused Fellow at Stack Overflow
It's For ITable, Not You
If you have a static list of records in your project, or if you have a method that's returning a list of objects, never return an IQueryable list unless that list comes from an ITable in your datacontext. And even then, you should probably still try to follow the 2nd maxim, and drop it like it's hot. To put this another way: don't create the batter; just make the pancakes.
If you do create unnecessary IQueryable lists, you'll get all kinds of confusing errors when you try to retrieve records from the database.
---
This leads to Maximish Thing Three-Point-Five
If You Don't Understand WHY You're IQueryable Is Returning The Records (Or Non-Records) It's Returning, You've Probably Got a Bigger Problem
Linq's syntax is great, but it also allows for some seriously difficult code to read. A few times, I've caught myself nearing the deep, dark edge of not understanding what code I'm writing while I'm writing it. This is a bad sign. When that happens, try converting your Linq-syntaxed code into oldschool C# syntax. This will allow you to parse the query out into its logical parts.
This "cool" query:
IQueryable<string> name = from m in context.Monkeys where m.MonkeyCollar.Id == 1 select m.MonkeyName;
Is the same as this "remember-the-days" query:
IQueryable<Monkey> allMonkeys = context.Monkeys; //query hasn't "gotten anything" yet
IQueryable<Monkey> oneMonkey = allMonkeys.Where(m => m.MonkeyCollar.Id == 1); //query hasn't "gotten anything" yet
IQueryable<string> monkeyName = oneMonkey.Select(m => m.MonkeyName); //query grabs the monkey names
---
The Fourth Maxim comes from a weird Robert Downey Jr. Poster at Mindscape
Confused? Use LINQPad.
I've seen unnecessary joins. I've watched men sob like babies because, after hours of angry typing, they couldn't figure out exactly why their query was returning 100 (nay, 1000) duplicate results (hyperbolic) instead of the 1 or 2 results that they so desired.
LINQPad, my friends, is your friend. From the website, and from my heart:
[LINQPad is] an ergonomic C#/VB scratchpad that instantly executes any C#/VB expression, statement block or program with rich output formatting – the ultimate in dynamic development. Put an end to those hundreds of Visual Studio Console projects cluttering your source folder!
Need I say more?
Ultimately, LINQPad is especially helpful for those developers who have a really strong grasp of SQL and want to convert that strong grasp into an equally strong grasp of Linq. And, for those developers just starting out, it's also a great tool if you want to do some visual and contextual Linq fiddling.
---
The last maxim is a simple one. Unfortunately, it stems from no outside source, and it defies the maxim rule I set forth and the beginning of this post. But, alas:
IQueryable Cannot Care For Your Babies. It Will Not Learn How To Eat. It Does Not Love You. Do Not Love It Back.
Linq (and, by way of Linq, IQueryable) has a prescribed and particular purpose. When a developer loses track of what exactly that purpose is, he or she will likely apply the technology in ways it wasn't intended to be applied. This may lead to captivating inventions or ingenious workarounds. It may also lead to a heap-pile of human bewilderment, human confusion, and uncontrollable human sobbing.
All I'm asking is: think about Linq. And think long and hard about IQueryable. Then, read all the opinions and all the tutorials you can. That includes the links I've included in this blog. They'll help you get your bearings a bit, come to terms with your lambda-based existence, and help you face the hardships that you'll surely encounter when you finally stumble out into (or, I should say, from) that wilderness.
Read More
You can leave a response, or trackback from your own site.
17 Responses to “IQueryable Can Kill Your Dog, Steal Your Wife, Kill Your Will To Live, etc.”
Leave a Reply
point 3.5 has a bug, but nice article otherwise.
Hah. I deserved that mistake, I suppose.
Your tabloid-like heading and weird use of really weird-rendering fonts really screw up your blog. Get it fixed.
Thanks, John. I’m just glad you can’t see the type of atrociously unfashionable, poorly designed man-attire that I generally wear.
I tend to agree with rude John, although I would have made my point in a less rude way. Great article – thanks! And isn’t the 3.5 bug still there?
Good article.
On a related subject, how do you feel about returning IEnumerable:
public IEnumerable GetCustomersInAlaskaWithChildren() { … }
Would you still convert to an array first in the repository?
IQueryable Can Kill Your Dog, Steal Your Wife, Kill Your Will To Live, etc. – weirdlover – I make love to asp.net mvc, c#, vb, legos, ladies, etc….
Thank you for submitting this cool story – Trackback from DotNetShoutout…
Hey Neil–
I think that’s a great question. I guess my half-witted answer would be: though it’s a simple point, I agree with Joshua here.
Also, a quick stack overflow review of the difference between IEnumerable and IQueryable can be found here.
Ultimately, if all you need to do is cycle through the records in the list, or if the result of the repository’s list may need to be converted to any number of different list types, then IEnumerable is the way to go.
I think you came to the right conclusion (The repository should not expose IQueryables), but for the wrong reason.
Simply put: The Repository should expose data; IQueryables are not data. (More specifically, IQueryable is the question; data is the answer).
So I do agree that it’s important to understand what’s going on with IQueryable before throwing it around. But, if I were an uninitiated reader, I would think you are trashing LINQ in general, which to a point I think you are.
One of the primary benefits of IQueryable is the delayed execution. Building expressions that can vary permutationally based on runtime data is a HUGE plus compared to writing a bunch of SQL generating cases in stored procs, for example, or willy nilly constructing textual SQL Queries.
Here’s a little pattern that I like to use in repositories so that I don’t get bitten by the IQueryable bug when I least expect it:
note: before my show and tell, I’ll point out that this may be obviated somewhat by the POCO integration features of the 4.0 version, but this is how I have done it and had no trouble. (I always return an IEnumerable of my POCO type so that I cannot possibly be confused by when it was executed, the resulting objects are serializable, and I don’t have to worry about stomping on the context before I mess with them.)
public class EmployeeSource : IDisposable {
DbEntities1 ctx = null;
public EmployeeSource() {
ctx = Config.CreateContext();
}
public IQueryable<ENTITY_TS_EMPL> BeginQuery() {
return ctx.ENTITY_TS_EMPL;
}
public IEnumerable EndQuery( IQueryable entities ) {
return POCOEmployee.CreateFromEntities( entities );
}
#region IDisposable Members
public void Dispose() {
if (ctx != null)
ctx.Dispose();
}
#endregion
// an example of how to use this class, normally would be in another class not this one, but I’m lazy.
public IEnumerable<POCOEmployee> GetByLastName( string lastName ) {
using( var source = new EmployeeSource() ) {
var people = source.BeginQuery();
people = people.Where( a => a.LAST_NAME.StartsWith( lastName ) );
// do a bunch of other possibly fancy stuff with the IQueryable here
return source.EndQuery( people );
}
}
}
Fun post, Evan, and I think pretty close to on the money. The only part that I disagree with a little is that you seem to conflate “linq” (the syntax) with “Linq to Sql” (the lite ORM). At least, somewhat.
I would also go a step further on your Repository example and suggest that the GetCustomersInAlaskaWithoutChildren query should return IEnumerable rather than Customer[] in order to emphasize the fact that it’s supposed to be returning a read-only enumeration as a query result, rather than an array which is potentially mutable.
Hey Dave,
Thanks for your truly thoughtful response.
Let me start by saying (or, I should’ve started this whole silly post by saying): I really love Linq. I use it everyday. I brush my teeth. I use Linq. I shower. I use Linq. Link Linq to anything, I’m all about it.
I think Linq (and Linq To SQL) is the most beautiful addition to my life since I started dating my girlfriend. Maybe even more beautiful. And I should say: no uninitiated developer should take this post to be a big red GET OUT; LINQ TO SQL SUCKS sign. Instead, it’s a GET IN sign, but the subtitle reads: THIS A GOOD THING THAT’S HAPPENING TO YOU; BE CAREFUL NOT TO MESS IT UP.
I like the way your Source class works quite a bit–I was hoping to follow this post up with a series of posts about how to build a decent Repository around Linq To SQL. Any way you might have a more in-depth demo up on the interwebs somewhere? Or, you can contact me via e-mail at evan.nagle(a)gmail(d)com.
Cheers,
E
Paul–
Another good point.
I think this might be a problem with the Linq moniker? Because the “Linq To…” title makes for awkward sentence syntax, it lends itself to lazy shortening–e.g. I’ll say “Linq” when I mean “Linq To …”. Although, I guess, when it comes to Linq To SQL, perhaps I should say L2S next time?
Yes, I just answered my own question; I should say L2S.
C,e
Interesting post.
I just don’t agree returning arrays or enumerable from a repository. What is coming out of the repository should be paginable and sortable, because otherwise, you are good to add page number and sort expression parameters to every method in your repository.
So I would wrap an IQueryable into a IPaginableSortable or something like that instead.
Julien,
IEnumberable has extension methods in the Linq namespace that lets you do that stuff client side (Sort, and Skip/Take). If you want to do server paging and sorting, do it on the IQueryable before you convert to IEnumerable… I’m not sure what the problem is…
This seems to be a reoccuring theme. As i have over the last month shown friends at work this more than once.
I don’t understand where the idea comes from? Its a query object. where you build your query, add to your query etc etc.
Why do so many people not seem to see that? (not that i don’t make stupid idiot mistakes myself now and then, but this seemed pretty obvious from the word go)
[...] Evan Nagle: IQueryable Can Kill Your Dog, Steal Your Wife, and Kill Your Will To Live [...]