CODEWITHSUNDEEP
entity-frameworkautomapper
Stephen
Stephen

Reputation: 171

Automapper projectTo generated SQL has no where clause

Using Entity Framework Core I'm trying to map an entity containing a child collection. Without projectTo the generated SQL includes a where clause and only fetches the entities that belong in the child collection. When I add a projectTo, the result is correct but looking at the SQL I see no where clause. It appears to load all the entities of the child type and then do a where in memory. I've tried all sorts of mappings on the collection but nothing seems to change this behavior. Is there a way to improve this query?

The code in question is:

var parent = _context
                .Parents
                .Where(a => a.FamilyId == familyId && a.Id == id)
                .Include(r => r.Children)
                //.ProjectTo<ParentDetailViewModel>()
                .AsNoTracking() // In case projection contains sub entities
                .SingleOrDefault();

Without the project to I see the following SQL in the profiler

exec sp_executesql N'SELECT [r].[Id], [r].[FieldOne], [r].[Cid], [r].[FieldTwo], [r].[ParentId]
FROM [Child] AS [r]
WHERE EXISTS (
    SELECT TOP(2) 1
    FROM [Parent] AS [a]
    WHERE (([a].[FamilyId] = @__familyId_0) AND ([a].[Id] = @__id_1)) AND ([r].[ParentId] = [a].[Id]))
ORDER BY [r].[ParentId]',N'@__familyId_0 int,@__id_1 int',@__familyId_0=1,@__id_1=1

With project I see this. No where clause included.

SELECT [r].[ParentId], [r].[Id]
FROM [Child] AS [r]

Upvotes: 0

Views: 985

Answers (2)

Stephen
Stephen

Reputation: 171

Apparently caused by an Entity Framework Core bug. Fixed in 1.1.0 preview

Upvotes: 0

Jimmy Bogard
Jimmy Bogard

Reputation: 26785

As a general rule of thumb, I put the ProjectTo as the last thing before your query materialization. Put ProjectTo after AsNoTracking - HOWEVER, the AsNoTracking is pointless. With ProjectTo, you're skipping entities entirely, going straight from SQL to DTO, no entities involved at all.

Also, the Include is pointless. With ProjectTo/Select, EF knows exactly what entities it needs to join, because they're in the Select projection!

Upvotes: 4

Related Questions