More efficient Linq to SQL

Question

I played with Linq in SQL to help me find a simple database. The database consists of two tables: “Player” and “Team”, and each player record has a team identifier for joining two tables (Player.TeamId → Team.Id).

To add a little complexity, the Team table contains historical data for the last 10 seasons. This means that each team can have up to 10 different entries in the team table pertaining to the 10 seasons presented.

I want my query to search for a player who returns a list of players matching the search criteria and a list of teammates for each of the returned players for that team this season.

Search criteria include first name, last name, (list) of seasons and team name.

My query looks like this:

using (var context = DataContextFactory.Context) { var playerList = context.GetTable<Player>(t => searchRequest.Seasons.Contains((int) t.Team.Season)) .Where(p => string.Equals(p.Surname, (searchRequest.Surname ?? p.Surname), StringComparison.OrdinalIgnoreCase) && string.Equals(p.Forename, (searchRequest.Forename ?? p.Forename), StringComparison.OrdinalIgnoreCase) && string.Equals(p.Team.Name, (searchRequest.TeamName ?? p.Team.Name), StringComparison.OrdinalIgnoreCase) )).ToList(); var teamMateList = new List<Player>(); foreach (var Player in playerList.Select(p => context.GetTable<Player>( tm => tm.Team.Id == p.Team.Id && tm.Id.CompareTo(p.Id) != 0))) { otherPeopleList.AddRange(people); } } 

This works and returns a list of players (playerList) that match the search criteria, and for each of these players I can match my teammates from the second query results (teamMateList).

My problem is that Linq to SQL translates this into quite inefficient SQL. The first problem is that it selects the entire Player table from the database - I assume that this is due to the fact that Linq to SQL cannot translate Where clauses to standard SQL and therefore returns the entire table and does part of the query in the code

The second problem is that when executing the second query, Linq to SQL generates separate database queries for each playerList member. When reading the code, this probably makes sense, but I would have thought that Linq would be smart enough to translate it into a single query, which would lead to a more efficient search.

Any thoughts / suggestions on optimizing my query?

Answer 1

I assume that this is due to the fact that Linq to SQL cannot translate Where clauses to standard SQL and so returns the entire table and does any part of the query execute in the code?

Or, in other words, because you write the condition, ignoring any reasonable approach that LINQ can convert.

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 that you must define the entire LINQ part in one pass. It was terrible at writing manual SQL, and it is terrible with LINQ. A LINQ expression in which the result is again IQueryable, and this type of binding is explicitly supported. We make high-performance LINQ for over a hundred million lines, and it looks great - but only because we don't write code as bad as you do.

Your second problem is the same: you are attacking the problem from the wrong side by forcing LINQ to use an inefficient search pattern. Make a choice with the group, and then join the client side with a different table.

Answer 2

LINQPad Can help you debug your LINQ statements locally.

You can also use SQL Server Profiler when debugging LINQ to SQL queries, it will not only show you that .NET translates the query, but also everything else that rushes into your database. It is also very useful when trying to increase performance issues with LINQ Queries, translating ridiculously long equivalent SQL queries.

Hope this helps.