How can I LINQ select from two EF DbSets and group join - but using AutoMapper

Question

Note: These classes are related, but not part of the same Aggregate (like PurchaseOrder and OrderLine) - so I do not have a navigation property from "One" to "Many".

=== Entities ===

public class One
{
  public Guid Id { get; set; }
  public string Name { get; set; }
}
 
public class Many
{
  public Guid Id { get; set; }
  public string Name { get; set; }
  public Guid One { get; set; }
}

=== Contracts ===

public class OneWithMany
{
  public Guid Id { get; set; }
  public string Name { get; set; }
  public IEnumerable<Many>? ManyRelatedObjects { get; set; }
}

I want to select all One objects and any related Many objects from DbSet/DbSet into OneWithMany.

To ensure I don't miss properties added in future I am using ProjectTo in AutoMapper - but I can't work out how to fit it into the equation.

The `Parent` class should have a `Children` collection property with `Child` objects. The `Child` class should have a `Parent` with the actual `Parent` object. There's no reason to use JOINs when using EF, NHibernate or any other full-featured ORM. The SQL queries are generated by the ORM based on the entities and configured relations between entities. AutoMapper isn't involved at all — Panagiotis Kanavos, Jul 25 '22 at 11:20
`To ensure I don't miss properties added in future I am using AutoMapper` that's not what AutoMapper is used for. It's *only* used to simplify conversions from EF entities to DTOs, by automagically generating the `.Select` clauses that map entity properties to DTO properties. Instead of writing `.Select(entity=>new Dto{ Prop1=entity.Prop1;....})` you use AutoMapper. — Panagiotis Kanavos, Jul 25 '22 at 11:22
If you use conventions, in EF Core 6 you won't even have to create the bridge class `ParentAndChildren` and its table. Check the [Relationships](https://learn.microsoft.com/en-us/ef/core/modeling/relationships) article in the docs. No AutoMapper, no joins and when conventions are used, not even a `HasMany` call — Panagiotis Kanavos, Jul 25 '22 at 11:26
@PanagiotisKanavos I have a unit test that checks my schemas. So if I add a new property to my DTO I can be sure it is mapped everywhere. — Peter Morris, Jul 25 '22 at 11:31
@PanagiotisKanavos They are named Parent and Child in this question for simplicity, the real model is more like "Relative - Related" and aren't part of the same aggregate so I am not using a navigation property. — Peter Morris, Jul 25 '22 at 11:32
That doesn't affect what I wrote. You don't need JOINS and AutoMapper doesn't affect entities and their relations. It's an external library as far as EF is concerned — Panagiotis Kanavos, Jul 25 '22 at 11:33
@PanagiotisKanavos If you'd like to argue whether or not we should have navigation properties between objects then feel free to @ me on Twitter (MrPeterLMorris) - but that's not what this question is about - your comment isn't a suitable solution to my specific problem, I'm afraid. — Peter Morris, Jul 25 '22 at 11:36
As for `They are named Parent and Child in this question for simplicity,` and `aren't part of the same aggregate so I am not using a navigation property.` post the *actual* classes. `aggregate` isn't an ORM term, although EF Core is actually used to load root aggregates in a DDD context. Nothing prevents you from having *multiple* DbContexts either. A DbContext is a Unit-of-Work and multi-entity repository. It needs to contain the entities used in a bounded context but that doesn't mean you need to use the same DbContext in all bounded contexts — Panagiotis Kanavos, Jul 25 '22 at 11:36
`if you would like to argue`. It's not a matter of opinion. That's how ORMs work. The very fact you have such trouble using libraries that are meant to *reduce* code is a very strong indication that something is wrong. — Panagiotis Kanavos, Jul 25 '22 at 11:38
Besides, if you want to use JOINS in LINQ, do that in the LINQ query, not the mapping of the results. Using query instead of fluent form will make writing the joins easier too. AutoMapper only works in the `Select` clause. You're trying to modify the `from` and `join` clauses. — Panagiotis Kanavos, Jul 25 '22 at 11:39
@PanagiotisKanavos I am using the Domain Driven Design approach, where objects outside of aggregates only reference by ID and not object. If you think that approach is wrong or not isn't the issue, I need to solve the problem. I've changed the class names as you suggested, thanks. — Peter Morris, Jul 25 '22 at 11:40
Yes, I guessed that from the start. That's why I keep mentioning bounded contexts and aggregates. What you try is a typical problem when ORMs and DDD technologies are mixed the wrong way. — Panagiotis Kanavos, Jul 25 '22 at 11:41
@PanagiotisKanavos Are you saying that you have navigation properties between all of your classes, no matter if they are part of the same aggregate or not? — Peter Morris, Jul 25 '22 at 11:43
I have multiple DbContexts, because I have multiple bounded contexts. I use a different DbContext for transactional and reporting parts of my web site. A `Sale` requires very different data and behavior in order fulfillment and financial reporting. — Panagiotis Kanavos, Jul 25 '22 at 11:50
@PeterMorris Applying DDD rules to EF Core entities which by definition represent **data** model is just wrong. Yes, data model need navigation properties because they represent the relationships and allow querying from both sides of the relationship. LINQ Joins are for LINQ to Objects. — Ivan Stoev, Jul 25 '22 at 11:52
@PanagiotisKanavos We are talking about Aggregates, not Bounded Contexts. Do you have navigation properties across Aggregates? — Peter Morris, Jul 25 '22 at 11:53
OK, you win. What is the problem here? You deleted all relevant information. What made you you post a question and why did you delete the `GroupJoin` code? If you don't want to use relations you have to create the join condition manually. That's what relations are for, to avoid writing such code. You can't avoid the mapping code though because [you're mapping multiple sources](https://stackoverflow.com/questions/21413273/automapper-convert-from-multiple-sources) in the result selector. — Panagiotis Kanavos, Jul 25 '22 at 12:08
You'll find a similar question: [AutoMapper in LINQ to Entities GroupJoin](https://stackoverflow.com/questions/25219099/automapper-in-linq-to-entities-groupjoin) - that's where you are already. You have two sources and want to map to `ParentAndChildren`. But that's not possible without the multiple mappings shown in the [mapping multiple sources](https://stackoverflow.com/questions/21413273/automapper-convert-from-multiple-sources) question. The problem isn't manual construction, it's loading the entire objects when only a few properties would do — Panagiotis Kanavos, Jul 25 '22 at 12:11
To avoid this *maybe* you could use `ProjectTo` inside the `resultselector` of `GroupJoin`? Perhaps an indirect mapping through the mapping from `Child` to `ParentAndChild`? That splits the *EF* query across multiple locations — Panagiotis Kanavos, Jul 25 '22 at 12:18
The GroupJoin gives IEnumerable, so ProjectTo doesn't work on it. — Peter Morris, Jul 25 '22 at 13:39
This is the case when AM will not help. Also note that EF Core has limited support of GroupJoin. — Svyatoslav Danyliv, Jul 26 '22 at 06:49

score 0 · Accepted Answer · answered Jul 26 '22 at 08:07

Unfortunately, it seems Entity Framework does not support GroupJoin.

The solution is to do the projection and as much filtering as possible as two separate queries, and then combine them into a result in memory.

If you find EF related answers on the web related to GroupJoin make sure you check the example code to see if they are actually showing code working on arrays instead of DbSet.

How can I LINQ select from two EF DbSets and group join - but using AutoMapper

1 Answers1