score:1

I assume this is because Linq to SQL can't translate my Where clauses to standard SQL and so returns the entire table and does the Where part of the query in code?

Or, in other words - because you write the condition ignoring any sensible approach that LINQ could translate.

string.Equals(p.Surname, (searchRequest.Surname ?? p.Surname

If searchRequest.Surname is null, then DO NOT SELECT.

var query = context.GetTable<Player>(*first condition);

if (!string.IsNullOrEmpty(searchRequest.Surname) {
query = query.Where (x=> x.surname.StartsWIth (searchRequest.Surname);
}

No one says you have to define the whole LINQ part in one run. This was terrible when writing manual SQL, and it is terrible with LINQ. A LINQ expression where the result is an IQueryable again and chaining like this is explicitly supported. We do high efficient LINQ over hundreds of millions rows and it looks great - but only because we do not write the code in that bad a way you do.

Your second issue is the same - you attack the problem from the wrong side by forcing LINQ to use an inefficient search pattern. Make a select with a group by and then join client side with the other table.

score:0

LINQPad Can help you with debugging your LINQ Statements locally.

You can also use SQL Server Profiler when debugging LINQ to SQL Queries, it will not only show you what .NET is translating the query into but everything else being thrown at your DB. This is also very useful when trying to increase performance issues with LINQ Queries translating ridiculously long equivalent SQL Queries.

Hope this helps.


Related Articles