I have the following code:
public void SalesCount(string customerId)
{
..
..
return ...;
}
var resultQuery = dataContext.Customers
.Where (c => c.Name == "Alugili")
.Where (c => SalesCount(c.CustomerId) < 100);
When I execute resultQuery I get a translation to SQL exception.
I need to call SalesCount in the Where can I do that is there any workaround for this problem!
Simply you can't. You can't execute C# code on the SQL server directly, you can use only Expressions
and some special recognized functions...
Unless you transform your query (at least partially) in a LINQ-to-Objects...
var resultQuery = dataContext.Customers
.Where (c => c.Name == "Alugili")
.AsEnumerable()
.Where (c => SalesCount(c.CustomerId) < 100);
Be aware that the last Where
will be executed on the client side, so many useless rows will be fetched from the DB.
Try
var resultQuery = dataContext.Customers
.Where (c => c.Name == "Alugili")
.ToArray()
.Where (c => SalesCount(c.CustomerId) < 100);
But then the second Where
is not run as SQL but locally - all customers with the name "Alugili" are pulled from the DB...
Otherwise you have to write out your method directly in the where method as lambda expression.