score:0
It turns out in the example I was looking at the Where
method wasn't being run on the database itself, but a list already pulled from the database.
ie. this doesn't work:
var airplanes = _dataContext.Airplanes.Where(p => Test(p.Status.ToString(), "a");
but this does:
var airplanes = _dataContext.Airplanes.ToList().Where(p => Test(p.Status.ToString(), "a");
Although in my case that sort of defeats the purpose of doing LINQ instead of iteration.
score:-1
LINQ to Entities has to know how to translate your code into SQL query it can run against database.
When you use (I removed ToString
call from the code you posted, because ToString
doesn't work with LINQ to Entities):
var airplanes = _dataContext.Airplanes.Where(p => p.Status.Contains("a"));
compiler translates your lambda into Expression Tree that LINQ provider can traverse and generate proper SQL:
You can have the C# or Visual Basic compiler create an expression tree for you based on an anonymous lambda expression, or you can create expression trees manually by using the
System.Linq.Expressions
namespace.quote from Expression Trees (C# and Visual Basic)
It knows how to translate call to IEnumerable.Contains
because it's one of the methods that was added to it as 'known' stuff - it knows it has to generate IN
statement the same way it knows that !=
should be translated into <>
in SQL.
When you use
var airplanes = _dataContext.Airplanes.Where(p => Test(p.Active, "a");
all the Expression Tree has is Test
methods call, which LINQ provider knows nothing about. It also can't inspect it's content to find that it's actually just wrapping around Contains
call, because the methods get's compiled to IL, not to Expression Tree.
Appendix
As of why Where(p => Test("abc", "a"))
word and Where(p => Test(s, "a"))
doesn't I'm not 100% sure, but my guess is LINQ provider is smart enough to see that you're calling it with two constant values, so it just tries to execute it and see if it can get a value back, that could be treated as constant in the SQL query.
score:1
In the first two cases, the call to Test doesn't relate to the parameter in the lambda, so they both reduce to p => true
.
In the third this similarly happens, though it'll reduce to p => true
sometimes, and p => false
sometimes, but either way when it comes to creating the expression, the result of calling Test
is found and then feed into the expression as a constant.
In the fourth the expression includes sub-expressions accessing a property of an entity and calling Contains
, both of which EF understands and can turn into SQL.
In the fifth the expression contains sub-expressions accessing a property and calling Test
. EF doesn't understand how to translate the call to Test
so you need to either associate it with a SQL function, or rewrite Test
so it creates an expression rather than calculating the result directly.
More on expressions as promised:
Let's start with two things that you may already know all about, but if you don't then the rest will be harder to understand.
The first is what p => p.Status.Contains("a")
actually means.
Which is, on its own, absolutely nothing. Unlike most expressions in C# lambdas can't have a type without a context.
1 + 3
has a type, which is int
and hence in var x = 1 + 3
the compiler gives x
the type int
. Even long x = 1 + 3
starts with the int
expression 1 + 3
and then casts it to long
after that.
p => p.Status.Contains("a")
doesn't have a type. Even (Airplane p) => p.Status.Contains("a")
doesn't have a type, and so var λ = (Airplane p) => p.Status.Contains("a");
isn't allowed.
Instead the type of a lambda expression will be either a delegate type or an Expression
strongly typed to a delegate. So all of these are allowed (and mean something):
Func<Airplane, bool> funcλ = p => p.Status.Contains("a");
Expression<Func<Airplane, bool>> expFuncλ = p => p.Status.Contains("a");
delegate bool AirplanePredicate(Airplane plane);
AirplanePredicate delλ = p => p.Status.Contains("a");
Expression<AirplanePredicate> expDelλ = p => p.Status.Contains("a");
Okay. Maybe you knew that, if not you do now.
The second thing is what Where
actually does in Linq.
The Queryable
form of Where
(we'll ignore the Enumerable
form for now, and come back to it) is defined thus:
public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
IQueryable<T>
represents something that can obtain 0 or more items. It can do four things, through four methods:
- Give you an enumerator to enumerate through those items (inherited from
IEnumerable<T>
). - Tell you what type of item it has (which will be
typeof(T)
but it's inherited fromIQueryable
where its not as obvious). - Tell you what its query provider is.
- Tell you what its expression is.
Now, those last two are the important bits here.
If you start with new List<Airplane>().AsQueryable
then the query provider will be a EnumerableQuery<Airplane>
which is a class that handles dealing with querys about in-memory enumerations of Airplane
, and its expression will represent returning that list.
If you start with _dataContext.Airplanes
the provider will be a System.Data.Entity.Internal.Linq.DbQueryProvider
which is a class that handles dealing with EF queries about databases, and its expression will represent running SELECT * FROM Airplanes
on a database and then creating an object for each row returned.
Now, the job of Where
is to get the provider to create a new IQueryable
that represents filtering the results of the expression we start with according to the Expression<Func<Airplane, bool>>
passed to it.
The fun bit is that this is wonderfully self-referencial: The expression returned by Where
when you call it with arguments of IQueryable<Airplane>
and Expression<Func<Airplane, bool>>
in fact represents calling Where
with arguments of IQueryable<Airplane>
and Expression<Func<Airplane, bool>>
! It's like calling Where
results in Where
saying "hey, you should call Where
here".
So, what happens next?
Well, sooner or later we do some operation that results in an IQueryable
not being used to return another IQueryable
but some other object representing the results of a query. For the sake of simplicity let's say we just start enumerating through the results of our single Where
.
If it were Linq-to-objects then what we'd have is a queryable with an expression that means:
Take the
Expression<Func<Airplane, bool>>
and compile it so you have aFunc<Airplane, bool>
delegate. Cycle through every element in the list, calling that delegate with it. If the delegate returnstrue
thenyield
that element, otherwise don't.
(This incidentally is what the Enumerable
version of Where
does directly with a Func<Airplane, bool>
instead of an Expression<Func<Airplane, bool>>
. Remember when I said that the result of Where
was an expression saying "hey, you should call Where
here"? That's pretty much what it does, but because the provider now picks the Enumerable
form of Where
and uses the Func<Airplane, bool>
rather than the Expression<Func<Airplane, bool>>
, we get the results we want. This also means that as long as the operations provided on IQueryable<T>
have an equivalent provided on IEnumerable<T>
linq-to-objects can cater for everything linq generally caters for).
But this is not linq-to-objects, it's EF so what we have is an expression that means:
Take the
Expression<Func<Airplane, bool>>
and turn it into a SQL boolean expression such as can be used in a SQLWHERE
clause. Then add that as aWHERE
clause to the earlier expression (which translates toSELECT * FROM Airplanes
).
The tricky bit here is "and turn it into a SQL boolean expression".
When your lambda was p => p.Status.Contains("a")
then the SQL could be produced of (depending on SQL version) CONTAINS (Status, 'a')
or Status LIKE '%a%'
or something else for a different type of database. The end result is hence SELECT * FROM Airplanes WHERE Status LIKE '%a%'
or so on. EF knows how to break down that expression into component expressions, and how to turn .Status
into a column access and how to turn string
's Contains(string value)
into SQL where clauses.
When your lambda was p => Test(p.Status.ToString(), "a")
the result was a NotSupportedException
because EF does not know how to turn your Test
method into SQL.
Okay. That's the meat, lets get to the pudding.
Can you elaborate on what you mean by "rewrite Test so it creates an expression rather than calculating the result directly".
A problem here is that I don't know quite what your end goal was, as in just where you wanted to be flexible. So I'm going to do something that does the equivalent of .Where(p => Test(p.Status.ToString(), someStringParameter))
in three ways; an easy way, a pretty easy way, and a hard way, where they can be made more flexible in a variety of ways.
First the easiest way:
public static class AirplaneQueryExtensions
{
public static IQueryable<Airplane> FilterByStatus(this IQueryable<Airplane> source, string statusMatch)
{
return source.Where(p => p.Status.Contains(statusMatch));
}
}
Here you can use _dataContext.Airplanes.FilterByStatus("a")
and it is as if you'd used your working Where()
. Because that's precisely what it is doing. We haven't really done a lot here, though there certainly is scope for DRY on more complicated Where()
calls.
Roughly equally easy:
public static Expression<Func<Airplane, bool>> StatusFilter(string sought)
{
return p => p.Status.Contains(sought);
}
Here you can use _dataContext.Airplanes.Where(StatusFilter("a"))
and againt it's pretty much the same as if you'd used your working Where()
. Again, we haven't done a lot here, but there's scope for DRY if the filter were more complicated.
Now for the fun version:
public static Expression<Func<Airplane, bool>> StatusFilter(string sought)
{
var param = Expression.Parameter(typeof(Airplane), "p"); // p
var property = typeof(Airplane).GetProperty("Status"); // .Status
var propExp = Expression.Property(param, property); // p.Status
var soughtExp = Expression.Constant(sought); // sought
var contains = typeof(string).GetMethod("Contains", new[]{ typeof(string) }); // .Contains(string)
var callExp = Expression.Call(propExp, contains, soughtExp); // p.Status.Contains(sought)
var lambda = Expression.Lambda<Func<Airplane, bool>>(callExp, param); // p => p.Status.Contains(sought);
return lambda;
}
This does pretty much exactly the same as the previous version of StatusFilter
does behind the scenes, except that identifies types, methods and properties using .NET metadata tokens and we use typeof()
and names.
As the comments in each line show, the first line obtains an expression representing a property. We don't really need to give it a name since we won't be using it directly in source-code, but we call it "p"
anyway.
The next line gets the PropertyInfo
for Status
and the subsequent creates an expression representing getting that for p
, hence p.Status
.
The next line creates an expression representing the constant value of sought
. While sought
isn't a constant generally, it is in terms of the overall expression we are creating (this is why EF was able to treat Test("abc", "a")
as a constant true
rather than having to translate it).
The next line gets the MethodInfo
for Contains
and in the next line we create an expression representing calling that on the result of p.Status
with sought
as an argument.
And finally we create an expression that ties them all together into the equivalent of p => p.Status.Contains(sought)
, and return it.
This is clearly a lot more work than just doing p => p.Status.Contains(sought)
. And well, that's sort of the point of having lambdas for expressions in C#, so that we normally don't have to do that work.
Indeed, to have a true expression-based equivalent of your Test
we find ourselves doing:
public static MethodCallExpression Test(Expression a, string b)
{
return Expression.Call(a, typeof(string).GetMethod("Contains", new[]{ typeof(string) }), Expression.Constant(b));
}
But then to use it we need to do more expression based work, because we can't just p => Test(p.Status, "a")
because p.Status
isn't an expression in that context. We have to do:
public static Expression<Func<Airplane, bool>> UseTest(string b)
{
var param = Expression.Parameter(typeof(Airplane));
return Expression.Lambda<Func<Airplane, bool>>(Test(Expression.Property(param, typeof(Airplane).GetProperty("Status")), b), param);
}
And now finally we can use _dataContext.Airplanes.UseTest("a")
. Phew!
The expression-based approach has two advantages though.
- We can use it if we want to do some manipulation with the expressions beyond want lambdas allow direction, such as in https://stackoverflow.com/a/30795217/400547 where they're use alongside reflection to be able to specify the property to access as a string.
- You hopefully know enough about how linq works behind the scenes to know all you need to know to understand fully why some of the queries in your question were working and some where not.
Source: stackoverflow.com
Related Query
- Why can't I use a range value's property as a parameter when calling a method in a LINQ query?
- Why Enumerable.OrderBy<TSource, TKey> Method works faster when it doesn't use Comparer
- Can someone tell me why this simple bit of c# code won't work, involves recursively calling method from within itself to get the root category ID
- Why is there no Linq method to return distinct values by a predicate?
- Why does C# compiler create private DisplayClass when using LINQ method Any() and how can I avoid it?
- When to use an extension method with lambda over LINQtoObjects to filter a collection?
- Why and When to use LINQ?
- Why should I use Any method instead of Count?
- Why can't I use LINQ's First/FirstOrDefault method in OData?
- Use Linq to filter Hashtable based on a property of Values custom object
- LINQ to Entities: Why can't I use Split method as condition?
- Why does Class Coupling jump when I use LINQ?
- How do you give the resulting set a name when calling Linqpad's .Dump() method on a List<>?
- Trying to use parent property as parameter in child collection expression; LinqKit throws "Unable to cast MethodCallExpressionN to LambdaExpression"
- Why does Enumerable<T>.ToArray() use an intermediary Buffer when it can just call Count() first?
- Why do linq search has a huge difference in efficiency when I use string and array especially for large data?
- Converting a C# method to use generics referencing a property inside
- InvalidArgument error when calling LINQ First() method
- Difference of LINQ .Aggregate with result selector parameter or directly calling method
- Why doesn't Visual Studio 2010 allow the use of "is null" in a linq query when VS2017 does?
- c# use string parameter to define what property to filter by in List of objects
- C# - Code supposed to be unreachable when calculate value in LINQ sum method
- How is ParallelEnumerable.Zip() inteded to work, and why does the behavior change when calling AsEnumerable()?
- why null values not return in when apply != in linq
- Why I cannot use the Linq Where clause in a method of a public abstract class?
- How to use OrderBy Linq when object is null and property is integer
- why does this linq code get exponentially slower when applying First() to projection?
- Why is Entity Framework ignoring the Take() method when generating SQL?
- .NET LINQ Call Method with Out Parameters Within Query and use Out Values
- Why did my code work when I changed it from IEnumerable to List?
More Query from same tag
- Linq Expression Slow - Optimization needed if possible
- Linq iterate a control collection
- Use dynamic datatype in LINQ query
- linq select items from child collection
- How to speed up the comparison of many fields using IEqualityComparer?
- LINQ InheritanceMappingAttribute Code property
- How to do a double left outer join in Linq query syntax(or fluent)
- LINQ: Why does Expression.Call() reference the Db when CreateQuery() does as well?
- Which is faster: Single(predicate) or Where(predicate).Single()
- Divide Db Query Result into as many tasks as I want
- ASP.NET Core - Get tuple with two values
- Get items from list where inner list contains set of enum-values
- Better way to remove characters that aren't ASCII 32 to 175 C#
- Why does using Random in Sort causing [Unable to sort IComparer.Compare error]
- Compare complex subquery between two values
- f#: Only parameterless constructors and initializers are supported in LINQ to Entities
- Linq For Each - Need Advice
- Adding a range of values in to a List using LinQ
- Convert SQL (Left Outer Join's) to LINQ
- count of last item of the day
- linq query methods all throwing NotSupportedException
- Set PredicateBuilder also on child collection
- EF Linq to Entities calling ToList() on entity set generates SQL command containing multiple left outer join
- Using .ToString inside a LINQ query select statement
- Optimize IEnumerable to HashSet conversion in LINQ
- C# Linq help improve performance?
- In Entity Framework 5 CodeFirst, how to create a query that will return when any pair of values are found?
- Convert.Int64 Is Not Reconized LINQ To Entities
- Why cannot convert from EntityLayer.SomeClass to DataLayer.SomeTable?
- How to use Where with IEnumerable assignment?