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 Articles
- Why can't I use a range value's property as a parameter when calling a method in a LINQ query?
- How do you give the resulting set a name when calling Linqpad's .Dump() method on a List<>?
- InvalidArgument error when calling LINQ First() method
- Difference of LINQ .Aggregate with result selector parameter or directly calling method
- C# - Code supposed to be unreachable when calculate value in LINQ sum method
- 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
- How to create a new list from property values when grouping items with GroupBy?
- Custom LINQ provider calling an asynchronous, generic method with reflection fails when getting the Task.Result object
- Calling a shared code from Page method and WebApi Controller with Razor Pages
- Is it possible to convince the Entity Framework to make an expression when calling a method from a select?
- How to return two values from single methods and set two parent class property calling function 1 time?
- Parameter of OfType<????> when used in a method with C#
- What is the typeArguments parameter of the Expression.Call Method when making a Expression Tree?
- Remove empty string values and ignore casing in C# List when using Distinct() method
- LINQ calling a method inside Select passing as parameter the selected item
- How to handle System.ArgumentOutOfRangeException error when getting values from JSON by Property Name
- Simplify a method with LINQ to update a series of nested property values
- Put LINQ Query in Method with Property as Parameter
- c# calling method and then property (design class structure)
- Executing linq lamda expression as parameter in System.Reflection Invoke Method calling
- Pass property as parameter to create a filtering method with LINQ/C#?
- Pass multiple values of the same parameter for search method
- Code equivalent to the 'let' keyword in chained LINQ extension method calls
- Value cannot be null. Parameter name: source
- Calling generic method with a type argument known only at execution time
- C# Pass Lambda Expression as Method Parameter
- C# - code to order by a property using the property name as a string
- How to select values within a provided index range from a List using LINQ
- Hitting the 2100 parameter limit (SQL Server) when using Contains()
- Entity-framework code is slow when using Include() many times
- Linq filtering causes property to not update view
- How to cast a nested LINQ query into a rectangular array?
- How can you drop items in a `ICollection` without knowing its `TSource`?
- Getting average of sums
- Load data from database to richtextbox in c# windows form using Linq
- Amazon Marketplace XML parsing with LINQ
- Converting string array to double array
- Get The Full List Of Objects In A Dictionary Values
- convert EnumerableRowCollection<string> to List<string>
- Why does this CollectionAssert.AreEquivalent() test fail?
- Access child token in JSON
- Advanced LINQ Update Statement
- datatable to datatable linq filter
- linq: from object in an object to just an object
- IEnumerable performs differently on Array vs List
- How to combine rows in SQL Server if Identical values exist, such as a given product name?
- Optional join with Entity Framework
- How do I resolve multiple linq queries in one operation without using a base query?
- LINQ: left join instead of inner join
- How to get unique entries of products in a List<Order> with LINQ