Automapper - projection with EF generates ugly sql

Question

I'm using the Project() on my IQueryable of "EF Object" to map it to IQueryable of "POCO Model" in the data layer.

The generated SQL statement has 20 LEFT JOINs to the same table. Has anyone seen this behavior?

The object that is being queried has a nested complex objects.

Answer 1

I have the same problem. I don't know what to do about it, but I think I understand what the problem is. In short, Entity Framework duplicates joins in certain query structures, and AutoMapper uses those query structures.

When Entity Framework sees entity.Relationship.Field1 and entity.Relationship.Field2 in a linq query, it generates a separate join for each field. for example:

from entity in dbSet
select new { entity.Relationship.Field1, entity.Relationship.Field2 }

might generate the following sql:

SELECT [Extent2].[Field1], [Extent3].[Field2]
FROM EntityTable AS [Extent1]
JOIN RelatedTable AS [Extent2] ON [Extent1].[RelationshipID] = [Extent2].[ID]
JOIN RelatedTable AS [Extent3] ON [Extent1].[RelationshipID] = [Extent3].[ID]

Normally, this double-join can be avoided by writing linq like this:

from entity in dbSet
let relatedValue = entity.Relationship
select new { relatedValue.Field1, relatedValue.Field2 }

Producing:

SELECT [Extent2].[Field1], [Extent2].[Field2]
FROM EntityTable AS [Extent1]
JOIN RelatedTable AS [Extent2] ON [Extent1].[RelationshipID] = [Extent2].[ID]

Unfortunately, you don't have this control in AutoMapper. When using .Project().To(), they presumably generate each "select" mapping separately and completely, like in the first example. It's probably possible for them to see which relationships/joins they would want to reuse, but depending on how they build the query up, the let syntax might be unavailable to them.