Getting record counts with Entity Framework results in high number of reads

Question

SQL Server 2016, Entity Framework 6.4 on .NET 4.6.2

Given these two tables:

• ParentTable (int masterId, int id)
• ChildTable (int id, bit isActive)

There's a one-to-many relationship between the ParentTable and the ChildTable on the id column.

I'd like to get a count of how many "active" child rows there are, with a masterId of 1. Simple enough in T-SQL

select count(1) 
from parentTable 
join childTable on parentTable.id = childTable.id
where masterId = 1 and isActive = 0

Now I'd like to do that in Entity Framework. Again, the syntax is simple enough:

 var activeCount= db.childTable.Count(d => d.parentTable.masterId == 1 && !d.isActive);

Both return the same count, however.... this Entity Framework call makes some ugly SQL which is REALLY inefficient:

exec sp_executesql N'SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM  [dbo].[childTable] AS [Extent1]
        INNER JOIN [dbo].[parentTable] AS [Extent2] ON [Extent1].[id] = [Extent2].[id]
        WHERE ([Extent2].[masterId] = @p__linq__0) AND ([Extent1].[isActive] <> 1)
    )  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=1

The difference is 10 logical reads for the simple count(1) statement, and 9200 logical reads for the Entity Framework statement. I've messed around with different syntaxes for the EF and at this point they have all created the same T-SQL.

Am I missing something in EF to do this simple count efficiently?

Answer 1

All three queries are asking for different things.

The first query doesn't include the condition that childTable.isActive = 1.

The LINQ expression does include that condition. This is the only query that seems to match the intent you stated, "get a count of how many "active" child rows there are, with a masterId of 1".

The generated SQL has the opposite condition for isActive: [Extent1].[isActive] <> 1. It also seems to be looking for a different masterid.

While Linq to SQL can definitely produce some terrible SQL, that's not the problem here. The generated sql is only ugly in the sense that it uses verbose aliases and ugly parameter names. But the inequality along with the different masterid is the issue.

Double check your code against your intent and make sure you're comparing apples to apples.