CODEWITHSUNDEEP
linqvisual-studio-lightswitch
Ranko
Ranko

Reputation: 31

SQL 'IN' Operator with LINQ in Visual Studio Lightswitch

Very often I have need for queries similar to this:

SELECT * FROM Customers  
WHERE City IN ('Paris','London');

I have some list (most often strings). When list is small (like this in example), it is not such a problem, I can write something like this (C#): 

Customers custs = this.DataWorkspace.ApplicationData.Customers;
var filteredcustomers = custs.Where(c=>c.City == "Paris" || c=>c.City == "London");

but if I have bigger list, it's a little clumsy. I've tried this (from some forum): 

List<string> months = new List<string>() {"Jan", "Feb", "Mar"......"Dec"};
Customers custs = this.DataWorkspace.ApplicationData.Customers; 
var filteredcustomers = custs.Where(c => months.Contains(c.City));

but I get run-time error:
"{System.NotSupportedException: The expression value(System.Collections.Generic.List`1[System.String]).Contains([10007].City) is not supported. ..."

Upvotes: 2

Views: 588

Answers (3)

Ranko
Ranko

Reputation: 31

First, thanks both @danielrozo and @sh1ng for answering the question. The thing is, both suggestions don't work at run-time, when at some point in code I try to .Execute() query or do foreach loop (when query actually executes), and I think that I finally figure out why: 

this.DataWorkspace.ApplicationData.Customers;

this is EntitySet and I found out that it doesn't support full set of LINQ operators (it doesn't support 'Contains'). It supports only restricted se of LINQ operators. If list is for example:

List<string> cities = new List<string>() {"Paris", "London", "Berlin", "Moscow",.....};

and I modify 'custs' like this:

var custs = this.DataWorkspace.ApplicationData.Customers.GetQuery().Execute();

it returns IEnumerable object and we can use it to filter locally. IEnumerable supports full set of LINQ operators and now both suggestions works. E.g. from @danielrozo

var fi = from fcusts in custs where custs.Any(x => cities.Contains(x.City)) select fcusts;

Now, this also works: 

var filteredcustomers = custs.Where(c => cities.Contains(c.City));

Also I can do sums (when needed):

decimal total = custs.Where(c => cities.Contains(c.City)).Sum(c => c.Points);

We need to be careful here, because .GetQuery().Execute() returns all records from server (in this case Customers), which can has impact on performance if we have a lot of records.

I hope this will help somebody.

Upvotes: 1

sh1ng
sh1ng

Reputation: 2973

Use array instead of List<>

var filteredcustomers = custs.Where(c => months.ToArray().Contains(c.City));

Upvotes: 0

danielrozo
danielrozo

Reputation: 1442

Try this:

var filteredCustomers = from fcusts in custs where custs.Any(x=>x.Contains<string>(x.City)) select fcusts;

This way you'll be calling Enumerable.Contains and not List.Contains.

Upvotes: 0

Related Questions