How would you go about performing a MultiMap using EF/Linq?

Question

I'm trying to take the return from a SQL statement with a one-to-many relationship and map it out to a model with Header information and a subset of objects from the "many" table.

I'm currently using Entity Framework to generate the query:

var result = from PI in PersonId
             join ab in Breakfast on PI.BreakfastId equals ab.Id into abTemp
             from AB in abTemp
             join al in Lunch on PI.LunchId equals al.Id into alTemp
             from AL in alTemp
             join ad in Dinner on PI.DinnerId equals ad.Id into adTemp
             from AD in adTemp
             select new Person {
                ...?
             }

The return from SQL looks something like this:

Id	Name	AteForBreakfast	AteForLunch	AteForDinner
123	Tim	Eggs	Ham Sandwich	Steak
123	Tim	Bacon	Chips	Potatoes
456	Sharon	Cereal	Chicken Wrap	Salad
456	Sharon	null	null	Grilled Chicken
789	Ralph	Cereal	null	Lasagna

I want to put this into a list of the following model:

public class Person
{
   public decimal Id {get; set;}
   public string Name {get; set;}
   public IEnumerable<string> AteForBreakfast {get; set;}
   public IEnumerable<string> AteForLunch {get; set;}
   public IEnumerable<string> AteForDinner {get; set;}
}

I've seen this done before using Dapper with QueryAsync and MultiMap, but can't seem to find an equivalent using Entity Framework and Linq.

I've tried a couple things from doing a "group by" to trying to pull from the grouped "temp"s the left joins are dumping into, but the group by killed performance (There's the possibility of having 100,000+ results before the pagination) and kept getting errors that the temp groups couldn't be evaluated.

I'm not sure if I'm missing something or approaching this mapping incorrectly. Any help would be greatly appreciated, thank you in advance!

Answer 1

I'm trying to take the return from a SQL statement with a one-to-many relationship and map it out to a model with Header information and a subset of objects from the "many" table.

Well accordingly to your scenario, while Entity Framework doesn't have a direct equivalent to Dapper's QueryAsync and MultiMap, you can achieve a similar multi-mapping result using a combination of SelectMany and anonymous types in your Linq query.

Let's consider, you have following class:

Models:

public class PersonId
{
    public decimal Id { get; set; }
    public string Name { get; set; }
    public int? BreakfastId { get; set; }
    public int? LunchId { get; set; }
    public int? DinnerId { get; set; }
}

public class Breakfast
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Lunch
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Dinner
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Person
{
    public decimal Id { get; set; }
    public string Name { get; set; }
    public List<string> AteForBreakfast { get; set; }
    public List<string> AteForLunch { get; set; }
    public List<string> AteForDinner { get; set; }
}

Now In order to achieve, your requirement, Instead of joining with each meal table, we could use SelectMany on each navigation property. SelectMany flattens a collection of collections into a single collection. Let have a look how we could implement that:

Refactored query:

var result = from PI in personIds
             join ab in breakfasts on PI.BreakfastId equals ab.Id into abTemp
             from AB in abTemp.DefaultIfEmpty()
             join al in lunches on PI.LunchId equals al.Id into alTemp
             from AL in alTemp.DefaultIfEmpty()
             join ad in dinners on PI.DinnerId equals ad.Id into adTemp
             from AD in adTemp.DefaultIfEmpty()
             group new { AB, AL, AD } by new { PI.Id, PI.Name } into g
             select new Person
             {
                 Id = g.Key.Id,
                 Name = g.Key.Name,
                 AteForBreakfast = g.Select(x => x.AB != null ? x.AB.Name : null).Where(x => x != null).ToList(),
                 AteForLunch = g.Select(x => x.AL != null ? x.AL.Name : null).Where(x => x != null).ToList(),
                 AteForDinner = g.Select(x => x.AD != null ? x.AD.Name : null).Where(x => x != null).ToList()
             };

Output:

Note: I just tried with the sample dummy data so few column has to data which I allowed nullable, you could test with your genuine data and scenario and could reconfigure according to your requirement. In addition, please refer to this official document, if you want to learn more.