I've got a Linq query setup where I'd like to filter based on a field matching any entry in a list. If I do something like this:

var filter = new List<int>; filter.Add(3); ... q = q.Where(d => filter.Contains(d.ContractId)); var result = q.ToList();

A SQL query is generated with no WHERE clause and that when executed doesn't filter at all.

I guess the Contains method needs to operate in C# so the database returns everything for a record by record evaluation. Can anyone explain why that evaluation never occurs?

Altering the query to:

q = q.Where(d => d.ContractId == 3);

the generated query filters properly in the SQL WHERE clause.

How can I construct a Linq statement that will cause filtering in SQL based on a field matching any member of a list stored in a C# variable?

var q = from c in metaData.Customer where filter.Contains(c.Country) select c;

?

The point is that the 'Contains' call on the Filter is an in-memory method call on the List (I think it's seen that way), but I'm not sure. If you use it the way I described above, it does filter? (it does here)

Thanks for the reply. I understood the Contains() does an in-memory filter (as the generated SQL has no where clause) although it doesn't actually filter the list in the ToList() output. My first question is why not.

However, since I don't want to filter in memory as this causes a serious performance penalty, the more critical question is how to construct a linq query that will filter database side. I've got a series of filter values retrieved from the user interface in List<int> filter. What I'm trying to achieve is a linq query that will generate a query like: select * from table where field in (3,4,5,6,7) or something equivalent to this.

Any ideas?

List<int> numbers = new List<int>() { 1, 2, 3, 4}; var q = from o in metaData.SomeEntity where numbers.Contains(o.SomeField) select o;

Is there any way to achieve the same functionality without using Contains()? I want the filter to execute in SQL rather than in-memory functions on the whole table.

RichardRoz wrote:

Is there any way to achieve the same functionality without using Contains()? I want the filter to execute in SQL rather than in-memory functions on the whole table.

This is converted into an IN query. Try it It's different from your query as the expression tree is different.

Thanks Otis and Walaa for sticking with me on this. I finally figured out what the problem was. I was simplifying the problem out of my example.

If I query against a non-nullable field in my database, everything works just as you described. However, in my case, the field was nullable. This means the generated entity code defines a Nullable<System.Int32> type for the field. The compiler doesn't allow you to simply provide the nullable field as the Contains() parameter: where numbers.Contains(o.SomeField) You have to resolve it to an int first. The solution that works with LLBLGen Pro 2.6 is to simply cast the value: where numbers.Contains((int)o.SomeField) I didn't come to this solution quickly since I know that casting a null value in C# will throw an exception. I forgot that the Contains() is being translated (hopefully) to SQL code where comparison between a null value and an int simply results in non-equality.

Instead, I tried handling the null values by converting them to a number that I'm sure won't match any element in my numbers list like: where numbers.Contains(o.SomeField ?? -1) and where numbers.Contains(o.SomeField.HasValue ? (int)o.SomeField : -1) and where numbers.Contains(o.SomeField == null ? -1 : (int)o.SomeField) These queries cause the behavior I've been describing i.e. the query has no where clause returning the entire table AND the list is not filtered in memory either!

I'm taking a guess that if the destination data provider isn't SQL Server and can't handle evaluation of null values, it will mean a crash.

Will you be adding support for this case in the near future?

All the best,

Richard.

RichardRoz wrote:

Thanks Otis and Walaa for sticking with me on this. I finally figured out what the problem was. I was simplifying the problem out of my example.

If I query against a non-nullable field in my database, everything works just as you described. However, in my case, the field was nullable. This means the generated entity code defines a Nullable<System.Int32> type for the field. The compiler doesn't allow you to simply provide the nullable field as the Contains() parameter: where numbers.Contains(o.SomeField) You have to resolve it to an int first. The solution that works with LLBLGen Pro 2.6 is to simply cast the value: where numbers.Contains((int)o.SomeField) I didn't come to this solution quickly since I know that casting a null value in C# will throw an exception. I forgot that the Contains() is being translated (hopefully) to SQL code where comparison between a null value and an int simply results in non-equality.

That's indeed better info . o.SomeField, nullable or not, should have been handled properly and you should have seen a list of elements. You shouldn't have to cast it, so it could be a bug, however we have added better checkcode for nullable fields to convert them more properly in all possible cases, so also this case. I'll see if I can repro it with a nullable field. I couldn't find a buildnumber of the runtime you're using, so in any case, try with the latest build.

Instead, I tried handling the null values by converting them to a number that I'm sure won't match any element in my numbers list like: where numbers.Contains(o.SomeField ?? -1) and where numbers.Contains(o.SomeField.HasValue ? (int)o.SomeField : -1) and where numbers.Contains(o.SomeField == null ? -1 : (int)o.SomeField) These queries cause the behavior I've been describing i.e. the query has no where clause returning the entire table AND the list is not filtered in memory either!

I'm taking a guess that if the destination data provider isn't SQL Server and can't handle evaluation of null values, it will mean a crash. Will you be adding support for this case in the near future? All the best, Richard.

These all result in a CASE statement, or better: should result in a CASE statement with the construct of the contains call, i.e. an IN query. I'm not sure if the where is simply ignored because the coalesce call or the IIF call wasn't expected, but I'll try.

Anyway, for now, cast it as the query won't care about the nullability anyway: the list of numbers doesn't contain null.

When I do:

List<int> employees = new List<int>() { 2, 3 };
var q = from o in metaData.Order
        where employees.Contains(o.EmployeeId)
        select o;

I get a compile error, as it can't convert int? to int

So casting indeed solves it.

When I do:

List<int> employees = new List<int>() { 2, 3 };
var q = from o in metaData.Order
        where employees.Contains(o.EmployeeId ?? -1)
        select o;

it indeed skips the where clause. Looking into why this happens.

(edit) the expression isn't expected. I'll add support for it. This also fails: var q = from o in metaData.Order where employees.Contains(((int)o.EmployeeId +1)) select o;

(edit). I've fixed it, both in queries with constants, like the examples above, and also in contains calls on queries: var q = from o in metaData.Order where (from e in metaData.Employee where e.FirstName.StartsWith("L") select e.EmployeeId).Contains(o.EmployeeId ?? -1) select o;

Fixed in next build.

Thanks for the quick attention to this. Given the options, I think cast remains best given that I don't want to add generation of unnecessary SQL code. The build for our current version of LinqSupportClasses is 2.6.8.616 and for SqlServer 2.6.8.612. We'll be upgrading to your newest build soon even though cast is supported in our current build.

However, I still find it counter intuitive i.e. cast an int? to int will generate an exception in .net when a null is encountered (which indicates that casting is a real bad solution) but not in most SQL languages. I just don't have any suggestion of how to make the different null handling rules between .net and SQL Server cleaner. This unfortunately means the destination database with it's subtle processing rules needs to be considered when constructing linq queries.

All the best,

Richard.

RichardRoz wrote:

Thanks for the quick attention to this. Given the options, I think cast remains best given that I don't want to add generation of unnecessary SQL code. The build for our current version of LinqSupportClasses is 2.6.8.616 and for SqlServer 2.6.8.612. We'll be upgrading to your newest build soon even though cast is supported in our current build.

I expect to have a new build up in the coming days (probably tuesday).

However, I still find it counter intuitive i.e. cast an int? to int will generate an exception in .net when a null is encountered (which indicates that casting is a real bad solution) but not in most SQL languages. I just don't have any suggestion of how to make the different null handling rules between .net and SQL Server cleaner. This unfortunately means the destination database with it's subtle processing rules needs to be considered when constructing linq queries.

That's indeed a thing which is unfortunately unavoidable due to how Linq is designed. To me it's clear that Linq was a good idea but it's unavoidable to have it leak details of the layer it abstracts into the code, which is something you'd want to avoid IF you strive for a higher level of abstraction, like MS tried with Linq.