• I've got Movements and Equipment...
  • but not all Movements have Equipment.
• For example, a "Back Squat" requires a Barbell, Plates, etc.. but a "Pushup" doesn't require any equipment.
• So, I've got a bridge table linking the Movements that do have Equipment.
• I need an OUTER JOIN so I can access all of the Movements even if they do not have any Equipment.
• ...I hope that makes sense.
• My code below only results in Movements that have matching Equipment records in the bridge table.

Movements = (
    from mvmt   in applicationDbContext.Movements
    join mvmteq in applicationDbContext.MovementEquips on mvmt  .MvmtId  equals mvmteq.MvmtId
    join equip  in applicationDbContext.Equipment      on mvmteq.EquipId equals equip .EquipId
    orderby mvmt.MvmtId
    select new Movement
    {
        MvmtId = mvmt.MvmtId,
        MvmtDescr = mvmt.MvmtDescr
    })
    .ToList();

CodePudding user response：

        var query =  
        from mvmt in applicationDbContext.Movements
        join mvmteq in applicationDbContext.MovementEquips on mvmt.MvmtId  
        equals mvmteq.MvmtId
        join equip  in applicationDbContext.Equipment on mvmteq.EquipId equals 
        equip.EquipId into g
        from mvmteq in g.DefaultIfEmpty()
        where mvmt.MvmtId != null
        select new
        {     
          MvmtId = mvmt.MvmtId,
          MvmtDescr = mvmt.MvmtDescr
         }).ToList();

Please refer to https://docs.microsoft.com/en-gb/dotnet/csharp/linq/perform-left-outer-joins

and

http://msdn.microsoft.com/en-us/library/bb397895.aspx

CodePudding user response：

• You really don't want an OUTER JOIN query.
• If you want to load all Movement and all Equipment data then all you need is this:

static async Task<( IReadOnlyList<Movement> allMovements, IReadOnlyList<Equipment> allEquipment )> LoadEverythingAsync( this ApplicationDbContext db, CancellationToken cancellationToken )
{
    IReadOnlyList<Movement>  allMovements;
    IReadOnlyList<Equipment> allEquipment;
    {
        List<Movement>  mov = await db.Movements.ToListAsync( cancellationToken ).ConfigureAwait(false);
        List<Equipment> eqp = await db.Equipment.ToListAsync( cancellationToken ).ConfigureAwait(false);

        await db.MovementEquips.LoadAsync( cancellationToken ).ConfigureAwait(false);

        allMovements = mov;
        allEquipment = eqp;
    }

    return ( allMovements, allEquipment );
}

The code above "works" because the Entity Framework DbContext performs magic whenever you load data into the DbContext (from both ToListAsync and LoadAsync) such that all attached and loaded entities will have their reference and navigation properties' setters invoked.

CodePudding user response：

Movements = (
            from mvmt in applicationDbContext.Movements
            join mvmteq in applicationDbContext.MovementEquips on mvmt.MvmtId equals mvmteq.MvmtId into join_1
            from mvmteq in join_1.DefaultIfEmpty()
            join equip in applicationDbContext.Equipment on mvmteq.EquipId equals equip.EquipId into join_2
            from equip in join_2.DefaultIfEmpty()
            select new GetMovementRequest
            {
                MvmtId = mvmt.MvmtId,
                MvmtDescr = mvmt.MvmtDescr,
                MvmtMetrics = mvmt.MvmtMetrics,
                MvmtStandard = mvmt.MvmtStandard,
                EquipId = equip.EquipId,
                EquipDescr = equip.EquipDescr
            }
        ).ToList();