在muilti join LINQ表达式后添加条件

Question

我得到了一个带有5个连接/表的Linq查询，带有预定义的条件。因为我需要多次查询，所以我创建了一个函数，它将默认的LINQ查询作为IQueryable返回。

public static IQueryable<MroomLinqModel> GetDefaultQuery(CustomerContext CustomerCtx)
{
        var Mrooms = (from mr in CustomerCtx.Mrooms
                      join m in CustomerCtx.Moves on mr.MoveId equals m.MoveId
                      join mg in CustomerCtx.mgroup on m.MgroupId equals mg.MgroupId
                      join s in CustomerCtx.Status on m.StatusId equals s.StatusId
                      join rt in CustomerCtx.Roomtypes on mr.RoomtypeId equals rt.Key
                      join g in CustomerCtx.Guests on m.Mgroup.GuestId equals g.GuestId

                      where
                      Math.Abs(mg.Status) != (int)IResStatus.InComplete &&
                      s.Visible

                      select new MroomLinqModel
                      {
                          OpenDepositPayments = mg.DepositPayments.Any(dp => !dp.Paid),
                          RoomHidden = (mr.RoomId == null ? true : mr.Room.Hidden),
                          StatusVisible = s.Visible,

                          MroomId = mr.MroomId,
                          MoveId = m.MoveId,
                          MgroupId = mg.MgroupId,
                          StatusId = s.StatusId,
                          StatusFlags = s.Flags,
                          BackgroundColor = s.Background_Argb,
                          TextColor = s.Foreground_Argb,
                          PersonCount = m.Movegroups.Sum(m => m.PersonCount),
                          MoveCount = mg.Moves.Count(),

                          RoomId = mr.RoomId,
                          PMSMroomId = mr.PMS_Id,
                          PMSMoveId = m.PMS_Id,
                          PMSMgroupId = mg.MgroupId_Casablanca,

                          From = mr.From,
                          Until = mr.Until,

                          EditableState = m.EditableState,
                          MroomStatus = mr.Status,
                          RoomtypeUsage = mr.Roomtype.Usage,

                          BookingReference = mg.ReferenceNumber,

                          Guest = g
                      });

        return Mrooms;
}

现在我想在后面添加一些条件，比如：

Query = GetDefaultQuery.Where(q => !q.RoomHidden && q.From <= dtLoadEnd && dtLoadStart <= q.Until);
Query = Query.Where(q => q.RoomtypeUsage == RoomtypeUsageType.Roomplan);

这可以很好地工作，但需要更多的时间来执行，就像我直接将所有条件添加到第一个LINQ查询中一样。

如何形成查询以访问原始表并生成快速查询？

Answer 1

由于您的所有条件都与您要连接其他表的表相匹配，因此您可以在多连接之前添加条件，我给您提供了两个建议，要么创建另一个过滤条件的预定义方法，要么将可选过滤器添加到方法中，如下所示：

public static IQueryable<MroomLinqModel> GetDefaultQuery(CustomerContext CustomerCtx, bool? roomHidden, DateTime? dtLoadEnd 
 /* you can add more parameters but for demonstrations purposes i'm only describing this 2*/)
{
        var query = CustomerCtx.Mrooms;

        if(roomHidden.HasValue)
        {
           query = query.Where( q=>q.From == roomHidden.Value)
        }

        if(dtLoadEnd  .HasValue)
        {
           query = query.Where( q=>q.RoomHidden <= dtLoadEnd.Value)
        }
        // you can add more conditions 

        var Mrooms = (from query 
                      join m in CustomerCtx.Moves on mr.MoveId equals m.MoveId
                      join mg in CustomerCtx.mgroup on m.MgroupId equals mg.MgroupId
                      join s in CustomerCtx.Status on m.StatusId equals s.StatusId
                      join rt in CustomerCtx.Roomtypes on mr.RoomtypeId equals rt.Key
                      join g in CustomerCtx.Guests on m.Mgroup.GuestId equals g.GuestId

                      where
                      Math.Abs(mg.Status) != (int)IResStatus.InComplete &&
                      s.Visible

                      select new MroomLinqModel
                      {
                          OpenDepositPayments = mg.DepositPayments.Any(dp => !dp.Paid),
                          RoomHidden = (mr.RoomId == null ? true : mr.Room.Hidden),
                          StatusVisible = s.Visible,

                          MroomId = mr.MroomId,
                          MoveId = m.MoveId,
                          MgroupId = mg.MgroupId,
                          StatusId = s.StatusId,
                          StatusFlags = s.Flags,
                          BackgroundColor = s.Background_Argb,
                          TextColor = s.Foreground_Argb,
                          PersonCount = m.Movegroups.Sum(m => m.PersonCount),
                          MoveCount = mg.Moves.Count(),

                          RoomId = mr.RoomId,
                          PMSMroomId = mr.PMS_Id,
                          PMSMoveId = m.PMS_Id,
                          PMSMgroupId = mg.MgroupId_Casablanca,

                          From = mr.From,
                          Until = mr.Until,

                          EditableState = m.EditableState,
                          MroomStatus = mr.Status,
                          RoomtypeUsage = mr.Roomtype.Usage,

                          BookingReference = mg.ReferenceNumber,

                          Guest = g
                      });

        return Mrooms;
}

Answer 2

期望CustomerCtx.Mrooms是一个DbSet<Mroom>，Mroom看起来像这样：

public class Mroom {
    public int MroomId {get; set;}

    [ForeignKey("Move")]
    public int MoveId {get; set;}         //FK
    public virtual Move Move {get; set;}  //Navigation property 
                                          //configuration may be needed cf annotation
    [ForeignKey("Status")]        
    public int StatusId {get; set;}
    public virtual Status Status {get; set;}

    //... Mgroup, Guest, ...
}

如果不是这样，我建议你重构你的代码来使用导航属性。

然后您可以使用linqkit的PredicateBuilder，如下所示：

public static IQueryable<MroomLinqModel> GetDefaultQuery(CustomerContext CustomerCtx, Expression<Function<Mroom, bool>> q)
{
    Expression<Function<Mroom, bool>> w = PredicateBuilder.New<Mroom>
       (s => Math.Abs(s.Mgroup.Status) != (int)IResStatus.InComplete &&
       s.Visible);
    w = w.And(q);

    return 
        CustomerCtx.Mrooms.
        Where(w.Expand()).
        Select( x => new MroomLinqModel
        {
            OpenDepositPayments = x.Mgroup.DepositPayments.Any(dp => !dp.Paid),
            RoomHidden = (x.RoomId == null ? true : x.Room.Hidden),
            StatusVisible = x.Status.Visible,
            MroomId = x.MroomId,
            MoveId = x.MoveId,
            MgroupId = x.MgroupId,
            StatusId = x.Status.StatusId,
            StatusFlags = x.Status.Flags,
            BackgroundColor = x.Status.Background_Argb,
            TextColor = x.Status.Foreground_Argb,
            PersonCount = x.Move.Movegroups.Sum(m => m.PersonCount),
            MoveCount = x.Mgroup.Moves.Count(),
            RoomId = x.RoomId,
            PMSMroomId = x.PMS_Id,
            PMSMoveId = x.Move.PMS_Id,
            PMSMgroupId = x.Mgroup.MgroupId_Casablanca,

            From = x.From,
            Until = x.Until,

            EditableState = x.Move.EditableState,
            MroomStatus = x.Move.Status,
            RoomtypeUsage = mr.Roomtype.Usage,

            BookingReference = x.Mgroup.ReferenceNumber,
            Guest = x.Guest
        }
    );
}

并将其用作：

GetDefaultQuery(ctx, q => !q.RoomHidden && q.From <= dtLoadEnd && dtLoadStart <= q.Until);

和或

Expression<Function<Mroom, bool>> w = PredicateBuilder.New<Mroom>(q =>
    !q.RoomHidden && q.From <= dtLoadEnd && dtLoadStart <= q.Until);
//some logic
w = w.And(q => q.RoomtypeUsage == RoomtypeUsageType.Roomplan);
GetDefaultQuery(ctx, w);