c# - 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 查询一样。

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

标签： c#performanceentity-frameworklinq

Answer 1

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

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;
}