Query execution
I’ve said that a query is kind of like a delegate, in that it is only executed when it’s triggered. Well, the detail is a little more complicated. (Isn’t it always?) Here are the rules:
Queries that return a singleton value are executed immediately. Examples of this kind of query are those that include the Average, Count or First expressions:
Queries that return multiple values are executed when they’re iterated over. You’ll usually do this with a foreach statement in C# or a For Each statement in Visual Basic.
Conversion methods will force immediate execution. In addition to iterating over a query, you can call one of the conversion functions: ToList(), ToArray(), or ToDictionary().
Put On Your Thinking Hat
Ready for some problem solving? Here are a couple of code snippets that don’t match the examples you’ve seen. Can you answer some questions about them?
Supplier firstS = context.Suppliers.First();
Will this code work? Why or why not? Does it matter which API is used to define the context?
var q = from r in (context.Recipes
orderby r.RecipeName
select r).Take(5);
foreach (Recipe r in q)
{
String str;
str = r.RecipeName + " has " + r.Ingredients.Count() + " ingredients";
Console.Writeline(str);
}
Dim q = (From r In context.Recipes
Order By r.RecipeName
Select r).Take(5);
For Each (r As Recipe in q)
Dim str As String
str = r.RecipeName + " has " + r.Ingredients.Count() + " ingredients"
Console.Writeline(str)
Next r
The Take() method will return the first five recipes alphabetically. Assuming that each recipe has 10 ingredients, how many trips to the database will this snippet make?
Put On Your Thinking Hat
Ready for some problem solving? Here are a couple of code snippets that don’t match the examples you’ve seen. Can you answer some questions about them?
Supplier firstS = context.Suppliers.First();
Will this code work? Why or why not? Does it matter which API is used to define the context?
This code will work just fine, no matter which API you use, because both DbSet<> and ObjectSet<> implement the IQueryable interface.
var q = from r in (context.Recipes
orderby r.RecipeName
select r).Take(5);
foreach (Recipe r in q)
{
String str;
str = r.RecipeName + " has " + r.Ingredients.Count() + " ingredients";
Console.Writeline(str);
}
Dim q = (From r In context.Recipes
Order By r.RecipeName
Select r).Take(5);
For Each (r As Recipe in q)
Dim str As String
str = r.RecipeName + " has " + r.Ingredients.Count() + " ingredients"
Console.Writeline(str)
Next r
The Take() method will return the first five recipes alphabetically. Assuming that each recipe has 10 ingredients, how many trips to the database will this snippet make?
Trick question. The snippet will trigger 6 trips to the database: once to retrieve the recipes, and once to retrieve each of the ingredients for the current recipe inside the for loop. Probably not what you’d want, is it? Keep reading...