Querying Entity Model. Part 7 – Left Join With First Row

Let’s imagine the situation when a person could have a few phone numbers, for example, home, work, mobile for home country, mobile for traveling, etc. I want to make a list of clients with just one phone number. This phone number can be chosen arbitrary or the first from an ordered list. This task can be done in Entity Framework Code First model by using a LINQ query.

I have Clients and Phones entities, and I’ll create a query in lambda syntax to select client names with a phone number with a first country code. I’ve found three ways to do this.

1. Join with first row using subquery

2. Join with first row using Navigation properties

3. Join with first row using GroupJoin

Entity Framework generates the same T-SQL query in all these cases.

From the SQL Server perspective, using APPLY operator is not a good solution. APPLY executes a subquery for each client row. You can read more about APPLY operator in MSDN article Using APPLY.

The most efficient way for SQL Server will be a query with Window function such as ROW_NUMBER().

To check the efficiency you need to insert a lot of records to your database, copy both scripts to SQL Server Management Studio and look at the Estimated or Actual Execution Plan. In a test with 4 client rows I have 74% and 26% relative to a batch. The percentages of the query cost could differ due to number of rows/data pages/clustered indexes to be processed, but the result is the query with APPLY is much heavier than that with a ROW_NUMBER() function.

I think you should avoid queries with APPLY to get many records. In these cases it would better to invoke a T-SQL queries or stored procedures from Entity Framework.

Leave a Reply

Your email address will not be published. Required fields are marked *