ASP.NET核心5.0 Linq外部联接

ogsagwnx  于 2022-12-06  发布在  .NET
关注(0)|答案(3)|浏览(150)
  • 我得到了MovementsEquipment ...
  • 但不是所有Movements都具有Equipment
  • 例如,“后蹲”需要杠铃、平板等,但“俯卧撑”不需要任何设备。
  • 我有一个桥表,它链接了有EquipmentMovements.
  • 我需要一个OUTER JOIN,这样我就可以访问所有的Movements,即使他们没有任何Equipment
  • ......我希望这是有意义的。
  • 我下面的代码只产生在桥表中具有匹配的Equipment记录的Movements
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();
rhfm7lfc

rhfm7lfc1#

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();

请参阅https://learn.microsoft.com/en-gb/dotnet/csharp/linq/perform-left-outer-joins

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

bxgwgixi

bxgwgixi2#

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();
fkvaft9z

fkvaft9z3#

  • 您确实不需要OUTER JOIN查询。
  • 如果要加载所有Movement和所有Equipment数据,则需要执行以下操作:
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 );
}

上面的代码“工作”是因为实体框架DbContext执行 * 魔术 * 每当您加载数据到DbContext(从ToListAsyncLoadAsync),使所有附加和加载的实体将有他们的引用和导航属性的setter调用。

相关问题