CODEWITHSUNDEEP
c#linq-to-sql
dan
dan

Reputation: 5784

How can I optimise this Linq query to remove the unnecessary SELECT Count(*)

I have three tables, Entity, Period and Result. There is a 1:1 mapping between Entity and Period and a 1:Many between Period and Result.

This is the linq query:

int id = 100;
DateTime start = DateTime.Now;

from p in db.Periods
where p.Entity.ObjectId == id && p.Start == start
select new { Period = p, Results = p.Results })

This is relevant parts of the generated SQL:

SELECT [t0].[EntityId], [t2].[PeriodId], [t2].[Value], (
    SELECT COUNT(*)
    FROM [dbo].[Result] AS [t3]
    WHERE [t3].[PeriodId] = [t0].[Id]
    ) AS [value2]

FROM [dbo].[Period] AS [t0]
INNER JOIN [dbo].[Entity] AS [t1] ON [t1].[Id] = [t0].[EntityId]
LEFT OUTER JOIN [dbo].[Result] AS [t2] ON [t2].[PeriodId] = [t0].[Id]
WHERE ([t1].[ObjectId] = 100) AND ([t0].[Start] = '2010-02-01 00:00:00')

Where is the SELECT Count(*) coming from and how can I get rid of it? I don't need a count of the "Results" for each "Period" and it slows the query down by an order of magnitude.

Upvotes: 5

Views: 329

Answers (1)

Jim Wooley
Jim Wooley

Reputation: 10398

Consider using the Context.LoadOptions and specifying for Period to LoadWith(p => p.Results) to eager load the period with results without needing to project into an anonymous type.

Upvotes: 2

Related Questions