Deep Dive into Many-to-Many: A Tour of EF Core 5.0 pt. 2

The Discussion

• 

  Hi,
  What do you think about 3 party library MutableIdeas.Web.Linq.Query.Service and
  Z.EntityFramework.Plus.EFCore for writing efficient code using EF core 5. Should I use them for Fiter Expression and Filters. For example:
  
  return await GetAllQueryable()
  .Where(m => !m.IsDeleted)
  .IncludeFilter(m => m.Roles.Where(s => !s.IsDeleted && !s.Role.IsDeleted).Select(s => s.Role))
  .IncludeFilter(m => m.Roles.Where(s => !s.IsDeleted && !s.Role.IsDeleted).SelectMany(s => s.SkillSets.Where(s => !s.IsDeleted && !s.SkillSet.IsDeleted).Select(s => s.SkillSet)))
  .IncludeFilter(m => m.EquipmentTypes.Where(s => !s.IsDeleted && !s.EquipmentType.IsDeleted).Select(s => s.EquipmentType))
  .IncludeFilter(m => m.EquipmentTypes.Where(s => !s.IsDeleted && !s.EquipmentType.IsDeleted).SelectMany(s => s.SkillSets.Where(s => !s.IsDeleted && !s.SkillSet.IsDeleted).Select(s => s.SkillSet)))
  .IncludeFilter(m => m.SkillSets.Where(s => !s.IsDeleted && !s.SkillSet.IsDeleted).Select(s => s.SkillSet))
  .FilterExpression(filterExpression)
  .SortExpression(sortExpression)
  .ToPagedResultAsync(page, pageSize);

• 

  Great talk, very informative and I really like how Arthur explains everything in a very understandable manner what is happening.
  
  With Visual Studio (Preview) it's nowadays so easy to create a GitHub Repo, (it's literally done in seconds) so I created one with your example code. Couldn't see all of the code, but I think I came close.
  
  GitHub Repo ON.NET SHOW: EF Core 5.x Tour Many To Many Part 2 => https://github.com/JeepNL/EFCore-5.0-Tour-Many-To-Many-Pt2

• 

  @Jaap: You get a gold star for this. This is very helpful.

• 

  The N:M join table has indexers of Dictionary<int,object> which sounds inefficient
  surely the 2 indexers could be Dictionary<int,Group> and Dictionary<int,User>
  this would be efficient (native int instead of inevitable parse/tostring transforms everywhere)
  and would be typesafe instead of the dreaded object which presumably needs lotsa typecasting
  
  sounds like this EF5 is a primitive [first-cut] implementation, but perhaps the EF6 will improve perf?

• 

  When using selection/projection like this:
  ``` c#
  var query = context.Users.Include(e => e.Groups);
  
  var x = query.Select(x => new { UserName = x.Name, GroupNames = x.Groups.Select(g => g.Id) }).ToList();
  ```
  
  It still generates this sql?
  ``` sql
  SELECT "u"."Name", "u"."Id", "t"."Name", "t"."GroupId", "t"."UserId", "t"."Id"
  FROM "Users" AS "u"
  LEFT JOIN (
  SELECT "g"."Name", "m"."GroupId", "m"."UserId", "g"."Id"
  FROM "Memberships" AS "m"
  INNER JOIN "Groups" AS "g" ON "m"."GroupId" = "g"."Id"
  ) AS "t" ON "u"."Id" = "t"."UserId"
  ORDER BY "u"."Id", "t"."GroupId", "t"."UserId", "t"."Id"
  ```