Presumably the data provider looks at each sub-expression and if it can be computed it just does a compile and execute while it's constructing the query. This would be no different than saying:
where c.ContactID == (4 + 5)
That's because numbers
in your example is an int[]
type.
Interestingly this is only evaluated once before the query is generated, so it's only calculated once. If you did something like this:
where c.ContactID == myObject.NextID()
...then it evaluates myObject.NextID()
during the construction of the query, so it only calls it once. I have witnessed, in Linq2NHibernate, that not only does it only call it once during the creating of the query, but the data provider has built-in query caching, and it won't re-evaluate it the next time you execute the same query. YMMV.
Also remember it's lazy evaluated, so if you never execute the query, the subexpression is never compiled and executed. Also, if you modify numbers
after your statement but before executing the foreach
then the query will be built using your modified value.
Edit:
Here's an article called Walkthrough: Creating an IQueryable LINQ Provider. Check the section on "Adding the Expression Evaluator". Specifically the Nominator
class:
/// <summary>
/// Performs bottom-up analysis to determine which nodes can possibly
/// be part of an evaluated sub-tree.
/// </summary>
Basically it's searching the expression tree for anything that can be evaluated, and compiling and executing it, then replacing that sub-expression with a constant expression.
Here's the function that determines if an expression can be evaluated:
private static bool CanBeEvaluatedLocally(Expression expression)
{
return expression.NodeType != ExpressionType.Parameter;
}
So as long as no sub-expression contains a parameter of the expression, then it can be evaluated.
Edit 2:
Based on your edit, it does look like it's putting the values from the array into a table in SQL and selecting MAX
from that. I honestly have no idea why it would bother doing that when none of the elements of the sub-expression numbers.Max()
are parameters. I certainly know that you can write an expression like numbers.Contains(c.ID)
and that will actually create an IN
clause such as c.ID IN (@p0, @p1, @p2, @p3, @p4)
where @p0 = 1
, @p1 = 2
, etc.
So, to answer your question: "is it possible that Linq2Entities provider indeed doesn't execute non-Linq and Linq-to-Object methods, but instead creates equivalent SQL statements for some of them"
Yes, it's possible.
for
vs. foreach
There is a common confusion that those two constructs are very similar and that both are interchangeable like this:
foreach (var c in collection)
{
DoSomething(c);
}
and:
for (var i = 0; i < collection.Count; i++)
{
DoSomething(collection[i]);
}
The fact that both keywords start by the same three letters doesn't mean that semantically, they are similar. This confusion is extremely error-prone, especially for beginners. Iterating through a collection and doing something with the elements is done with foreach
; for
doesn't have to and shouldn't be used for this purpose, unless you really know what you're doing.
Let's see what's wrong with it with an example. At the end, you'll find the full code of a demo application used to gather the results.
In the example, we are loading some data from the database, more precisely the cities from Adventure Works, ordered by name, before encountering "Boston". The following SQL query is used:
select distinct [City] from [Person].[Address] order by [City]
The data is loaded by ListCities()
method which returns an IEnumerable<string>
. Here is what foreach
looks like:
foreach (var city in Program.ListCities())
{
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
Let's rewrite it with a for
, assuming that both are interchangeable:
var cities = Program.ListCities();
for (var i = 0; i < cities.Count(); i++)
{
var city = cities.ElementAt(i);
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
Both return the same cities, but there is a huge difference.
- When using
foreach
, ListCities()
is called one time and yields 47 items.
- When using
for
, ListCities()
is called 94 times and yields 28153 items overall.
What happened?
IEnumerable
is lazy. It means that it will do the work only at the moment when the result is needed. Lazy evaluation is a very useful concept, but has some caveats, including the fact that it's easy to miss the moment(s) where the result will be needed, especially in the cases where the result is used multiple times.
In a case of a foreach
, the result is requested only once. In a case of a for
as implemented in the incorrectly written code above, the result is requested 94 times, i.e. 47 × 2:
Querying a database 94 times instead of one is terrible, but not the worse thing which may happen. Imagine, for example, what would happen if the select
query would be preceded by a query which also inserts a row in the table. Right, we would have for
which will call the database 2,147,483,647 times, unless it hopefully crashes before.
Of course, my code is biased. I deliberately used the laziness of IEnumerable
and wrote it in a way to repeatedly call ListCities()
. One can note that a beginner will never do that, because:
The IEnumerable<T>
doesn't have the property Count
, but only the method Count()
. Calling a method is scary, and one can expect its result to not be cached, and not suitable in a for (; ...; )
block.
The indexing is unavailable for IEnumerable<T>
and it's not obvious to find the ElementAt
LINQ extension method.
Probably most beginners would just convert the result of ListCities()
to something they are familiar with, like a List<T>
.
var cities = Program.ListCities();
var flushedCities = cities.ToList();
for (var i = 0; i < flushedCities.Count; i++)
{
var city = flushedCities[i];
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
Still, this code is very different from the foreach
alternative. Again, it gives the same results, and this time the ListCities()
method is called only once, but yields 575 items, while with foreach
, it yielded only 47 items.
The difference comes from the fact that ToList()
causes all data to be loaded from the database. While foreach
requested only the cities before "Boston", the new for
requires all cities to be retrieved and stored in memory. With 575 short strings, it probably doesn't make much difference, but what if we were retrieving only few rows from a table containing billions of records?
So what is foreach
, really?
foreach
is closer to a while loop. The code I previously used:
foreach (var city in Program.ListCities())
{
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
can be simply replaced by:
using (var enumerator = Program.ListCities().GetEnumerator())
{
while (enumerator.MoveNext())
{
var city = enumerator.Current;
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
}
Both produce the same IL. Both have the same result. Both have the same side effects. Of course, this while
can be rewritten in a similar infinite for
, but it would be even longer and error-prone. You're free to choose the one you find more readable.
Want to test it yourself? Here's the full code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
public class Program
{
private static int countCalls;
private static int countYieldReturns;
public static void Main()
{
Program.DisplayStatistics("for", Program.UseFor);
Program.DisplayStatistics("for with list", Program.UseForWithList);
Program.DisplayStatistics("while", Program.UseWhile);
Program.DisplayStatistics("foreach", Program.UseForEach);
Console.WriteLine("Press any key to continue...");
Console.ReadKey(true);
}
private static void DisplayStatistics(string name, Action action)
{
Console.WriteLine("--- " + name + " ---");
Program.countCalls = 0;
Program.countYieldReturns = 0;
var measureTime = Stopwatch.StartNew();
action();
measureTime.Stop();
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("The data was called {0} time(s) and yielded {1} item(s) in {2} ms.", Program.countCalls, Program.countYieldReturns, measureTime.ElapsedMilliseconds);
Console.WriteLine();
}
private static void UseFor()
{
var cities = Program.ListCities();
for (var i = 0; i < cities.Count(); i++)
{
var city = cities.ElementAt(i);
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
}
private static void UseForWithList()
{
var cities = Program.ListCities();
var flushedCities = cities.ToList();
for (var i = 0; i < flushedCities.Count; i++)
{
var city = flushedCities[i];
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
}
private static void UseForEach()
{
foreach (var city in Program.ListCities())
{
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
}
private static void UseWhile()
{
using (var enumerator = Program.ListCities().GetEnumerator())
{
while (enumerator.MoveNext())
{
var city = enumerator.Current;
Console.Write(city + " ");
if (city == "Boston")
{
break;
}
}
}
}
private static IEnumerable<string> ListCities()
{
Program.countCalls++;
using (var connection = new SqlConnection("Data Source=mframe;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
connection.Open();
using (var command = new SqlCommand("select distinct [City] from [Person].[Address] order by [City]", connection))
{
using (var reader = command.ExecuteReader(CommandBehavior.SingleResult))
{
while (reader.Read())
{
Program.countYieldReturns++;
yield return reader["City"].ToString();
}
}
}
}
}
}
And the results:
--- for ---
Abingdon Albany Alexandria Alhambra [...] Bonn Bordeaux Boston
The data was called 94 time(s) and yielded 28153 item(s).
--- for with list ---
Abingdon Albany Alexandria Alhambra [...] Bonn Bordeaux Boston
The data was called 1 time(s) and yielded 575 item(s).
--- while ---
Abingdon Albany Alexandria Alhambra [...] Bonn Bordeaux Boston
The data was called 1 time(s) and yielded 47 item(s).
--- foreach ---
Abingdon Albany Alexandria Alhambra [...] Bonn Bordeaux Boston
The data was called 1 time(s) and yielded 47 item(s).
LINQ vs. traditional way
As for LINQ, you may want to learn functional programming (FP) - not C# FP stuff, but real FP language like Haskell. Functional languages have a specific way to express and present the code. In some situations, it is superior to non-functional paradigms.
FP is known being much superior when it comes to manipulating lists (list as a generic term, unrelated to List<T>
). Given this fact, the ability to express C# code in a more functional way when it comes to lists is rather a good thing.
If you're not convinced, compare the readability of code written in both functional and non-functional ways in my previous answer on the subject.
Best Answer
You can trivially define an extension method for
ToHashSet
if that's what you're looking for.You should now be able to use it in your original example: