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.
Source: stackoverflow.com
Related Articles
- LINQ ToList().Take(10) vs Take(10).ToList() which one generates more efficient query
- LINQ Source Code Available
- Is there a more efficient LINQ statement to reverse-search for a condition in a List<T>?
- How can I write the following code more elegantly using LINQ query syntax?
- Make file IO with LINQ more efficient with large numbers of small XML files?
- More efficient Linq to SQL
- creating Linq to sqlite dbml from DbLinq source code
- Making a UNION query more efficient in LINQ
- Is there a better or more efficient way to filter with linq
- Is there a more efficient way to convert LINQ result to string[] or List<string>?
- How can I make a more efficient Entity Framework LINQ call than a nested loop
- How to make LINQ to EF query more efficient
- More Efficient LINQ Query
- replace 3 levels of nested for loops by efficient code possibly linq
- source code for LINQ 101 samples
- Refactoring C# code - doing more within Linq
- Linq join two lists: is it more efficient to use Dictionary?
- Which is more efficient in this case? LINQ Query or FOREACH loop?
- More efficient Linq expression
- Implicit operators, Linq and Lambda expressions can make code less readable. But what is more readable?
- How to make LINQ query with Unions more efficient
- c# Linq or code to extract groups from a single list of source data
- Making a Linq query more efficient (.Net Core)
- Entity Framework Code Most First Efficient Linq Query
- Convert string[] to int[] in one line of code using LINQ
- Code equivalent to the 'let' keyword in chained LINQ extension method calls
- Linq code to select one item
- LINQ OrderBy with more than one field
- How are people unit testing code that uses Linq to SQL
- Efficient Linq Enumerable's 'Count() == 1' test
- get first three elements of jagged array
- how to get max id from a table using linq
- Get specific data from a JSON
- How to get n records of each group in mongodb using C#, LINQ?
- What is the best way to sort a List of objects with a start value?
- How to prevent a condition from being repeated in several navigation properties in lambda expressions or linq
- Linq to entities projection: is this projection inefficient?
- Using .Where() on a List
- LINQ query translate from SQL which uses subquery containing GROUP BY and COUNT
- How do I create a class from XML elements?
- Linq c# use between on time span
- LINQ error type DBNull column
- Sort List based on object properties in another List
- XML with sub nodes to csv format using linq
- Group and Sum With Select Many C#?
- Can't "Enable Editing" on my gridview
- Get average of sum of grouped values with LINQ
- Can't get the children of root element in xml file
- Converting linq to non linq
- Linq Should I Return List<T> Or IEnumerable<T> When I Still May Do More Later
