为什么 相同 where 子句需要在 Linq 查询中多次写入以下 SQL

Question

下面的SQL查询的正确linq语法是什么？

select a.id, a.AppointmentStatusID, ad.ID as DetailID
from [dbo].[Appointment] a, [dbo].[AppointmentDetail] ad
where a.[ID] = ad.[AppointmentID]
and a.CompanyID = 'a3dea87a-804e-4115-98cf-472988cf1678'
and a.LocationID = '3165caca-2a48-46f0-bbed-578cff29167t'
and ad.AppDateFrom <= {ts '2017-11-14 23:59:31'}
and ad.AppDateTo >= {ts '2017-11-14 00:00:00'}
and ad.[ApprovalStatusID] = 2

我面临的问题：

我需要在连接内第一次过滤 Where 条件两次，在对象期间第二次过滤 Where 条件。 选择表达式，请检查以下

var results = (from a in appointments
                                   join ad in _appointmentDetailRepository.GetAll() on a.ID equals ad.AppointmentID
                                   where ad.ApprovalStatusID == 2
                                   && DbFunctions.TruncateTime(ad.AppDateFrom) <= DbFunctions.TruncateTime(viewmodel.AppointmentDate)
                                   && DbFunctions.TruncateTime(ad.AppDateTo) >= DbFunctions.TruncateTime(viewmodel.AppointmentDate)
                                   orderby a.ID
                                   select new Appointment
                                   {
                                       ID = a.ID,
                                       CompanyID = a.CompanyID,
                                       LocationID = a.LocationID,
                                       AppointmentDetail = a.AppointmentDetail.Select(ad => new AppointmentDetail
                                       {
                                           ID = ad.ID,
                                           AppDateFrom = ad.AppDateFrom,
                                           AppDateTo = ad.AppDateTo,
                                           AppointmentStatusID = ad.AppointmentStatusID,
}).Where(ad=> ad.ApprovalStatusID == 2
                                        && DbFunctions.TruncateTime(ad.AppDateFrom) <= DbFunctions.TruncateTime(viewmodel.AppointmentDate)
                                        && DbFunctions.TruncateTime(ad.AppDateTo) >= DbFunctions.TruncateTime(viewmodel.AppointmentDate)).ToList()
 }).GroupBy(x => x.ID).Select(x => x.DefaultIfEmpty().FirstOrDefault());

查询：为什么我需要写2次where条款？

所需结果

一个约会对象 --> 包含 ICollection<AppoinmentDetails> if 详细信息。其中条件 == true

Answer 1

从我所看到的(不知道您拥有的模型(来看，您似乎应该使用已经连接和过滤的详细信息ad而不是从属性a.AppointmentDetail再次查找它......

未经测试：

select new Appointment
{
    ID = a.ID,
    CompanyID = a.CompanyID,
    LocationID = a.LocationID,
    AppointmentDetail = ad.ToList(), // <-- don't you think?
    ...
}

Answer 2

对于给定的 SQL 查询

select a.id, a.AppointmentStatusID, ad.ID as DetailID
from [dbo].[Appointment] a, [dbo].[AppointmentDetail] ad
where a.[ID] = ad.[AppointmentID]
and a.CompanyID = 'a3dea87a-804e-4115-98cf-472988cf1678'
and a.LocationID = '3165caca-2a48-46f0-bbed-578cff29167t'
and ad.AppDateFrom <= {ts '2017-11-14 23:59:31'}
and ad.AppDateTo >= {ts '2017-11-14 00:00:00'}
and ad.[ApprovalStatusID] = 2

LINQ 查询可以编写为

var results = (from a in appointments
                       join ad in appointmentDetails on a.ID equals ad.AppointmentID
                       where ad.ApprovalStatusID == 2
                           && a.CompanyID == "a3dea87a-804e-4115-98cf-472988cf1678"
                           && a.LocationID == "3165caca-2a48-46f0-bbed-578cff29167t"
                           && ad.AppDateFrom.Date <= viewmodel.AppointmentDate.Date
                           && ad.AppDateTo.Date >= viewmodel.AppointmentDate.Date
                       select new
                       {
                           ID = a.ID,
                           AppointmentStatusID = a.AppointmentStatusID,
                           DetailID = ad.ID
                       }).ToList();

你也可以这样写

var results = appointmentDetails
                        .Where(ad => ad.AppDateFrom.Date <= viewmodel.AppointmentDate.Date
                                && ad.AppDateTo.Date >= viewmodel.AppointmentDate.Date
                                && ad.ApprovalStatusID == 2
                                && ad.Appointment.CompanyID == "a3dea87a-804e-4115-98cf-472988cf1678"
                                && ad.Appointment.LocationID == "3165caca-2a48-46f0-bbed-578cff29167t")
                        .Select(ad =>
                        new
                        {
                            ID = ad.Appointment.ID,
                            AppointmentStatusID = ad.Appointment.AppointmentStatusID,
                            DetailID = ad.ID
                        })
                        .ToList()

根据更新的问题，要获取具有约会详细信息集合的约会对象，请尝试此查询

var results = appointmentDetails
                        .Where(ad => ad.AppDateFrom.Date <= viewmodel.AppointmentDate.Date
                                && ad.AppDateTo.Date >= viewmodel.AppointmentDate.Date
                                && ad.ApprovalStatusID == 2
                                && ad.Appointment.CompanyID == "a3dea87a-804e-4115-98cf-472988cf1678"
                                && ad.Appointment.LocationID == "3165caca-2a48-46f0-bbed-578cff29167t")
                        .Select(ad =>
                        new
                        {
                            ID = ad.Appointment.ID,
                            AppointmentStatusID = ad.Appointment.AppointmentStatusID,
                            Detail = ad
                        })
                        .AsEnumerable()
                        .GroupBy(a => new { a.ID, a.AppointmentStatusID })
                        .Select(a => new Appointment
                        {
                            ID = a.Key.ID,
                            AppointmentStatusID = a.Key.AppointmentStatusID,
                            AppointmentDetails = a.Select(d => d.Detail).ToList()
                        })
                        .ToList();