c# - NHibernate QueryOver .Left.JoinAlias具有附加的连接条件

我有以下方法：

    public IEnumerable<MyRosterDTO> MyRosterGetCustomers(
        IEnumerable<Guid> SalesRepIds,
        IEnumerable<Guid> escrowOfficerIds,
        IEnumerable<int> targetTypes,
        IEnumerable<int> tagIds,
        IEnumerable<Guid> custTypes,
        IEnumerable<int> distListIds,
        bool myExceptions)
    {
        customerStatusLog cslAlias = null;
        customer custAlias = null;
        customerOptions coAlias = null;
        employee salesRepAlias = null;
        Office officeAlias = null;
        ListTags tagsAlias = null;
        MyRosterDTO dto = null;
        contactInformation contactInfo = null;

        var myRosterQuery = _sms.CurrentSession.QueryOver<customer>(() => custAlias)
                                .JoinAlias(c => c.CustomerOptions, () => coAlias)
                                .Left.JoinAlias(c => c.SalesRep, () => salesRepAlias)
                                .JoinAlias(c => coAlias.Company, () => officeAlias)
                                .Left.JoinAlias(c => c.ContactInfo, () => contactInfo)
                                .Where(x => contactInfo.ContactTypeID == 8);


        #region Where Clauses for parameters
        if (myExceptions)
        {
            myRosterQuery.Where(c => salesRepAlias.Id == _ctx.User.Id && _ctx.User.Id != officeAlias.RepId);
        }
        else
        {
            if (SalesRepIds != null)
            {
                if (SalesRepIds.Contains(Guid.Empty))
                {
                    if (SalesRepIds.Count() > 1) { myRosterQuery.Where(c => salesRepAlias.Id.IsIn(SalesRepIds.ToArray()) || salesRepAlias.Id == null); }
                    else
                    { myRosterQuery.Where(c => salesRepAlias.Id == null); }
                }
                else
                { myRosterQuery.Where(c => salesRepAlias.Id.IsIn(SalesRepIds.ToArray())); }
            }
        }

        if (escrowOfficerIds != null
            && escrowOfficerIds.Any())
        {
            myRosterQuery.Where(c => coAlias.PreferredEscrowOfficer.IsIn(escrowOfficerIds.ToArray()));
        }

        if (targetTypes != null
            && targetTypes.Any())
        {
            myRosterQuery.JoinAlias(c => c.CustomerStatusLog, () => cslAlias)
                         .Where(() => cslAlias.StatusId.IsIn(targetTypes.ToArray()));
        }

        if (tagIds != null
            && tagIds.Any())
        {
            myRosterQuery.JoinAlias(c => c.Tags, () => tagsAlias)
                         .Where(() => tagsAlias.Id.IsIn(tagIds.ToArray()));
        }

        if (custTypes != null
            && custTypes.Any())
        {
            myRosterQuery.Where(c => coAlias.cusTypeID.IsIn(custTypes.ToArray()));
        }

        if (distListIds != null
            && distListIds.Any())
        {
            var distCustIds = _sms.CurrentSession.Query<ListofAgents>()
                              .Where(loa => distListIds.Contains(loa.ListId))
                              .Select(loa => loa.AgentId)
                              .Distinct();

            myRosterQuery.Where(c => c.Id.IsIn(distCustIds.ToArray()));
        }
        #endregion

        return myRosterQuery.SelectList(list => list
            .SelectGroup(c => c.Id).WithAlias(() => dto.Id)
            .SelectGroup(c => c.FirstName).WithAlias(() => dto.FirstName)
            .SelectGroup(c => c.LastName).WithAlias(() => dto.LastName)
            .SelectGroup(() => officeAlias.Name).WithAlias(() => dto.CompanyName)
            .SelectGroup(() => officeAlias.Address1).WithAlias(() => dto.Address1)
            .SelectGroup(() => officeAlias.Address2).WithAlias(() => dto.Address2)
            .SelectGroup(() => officeAlias.City).WithAlias(() => dto.City)
            .SelectGroup(() => officeAlias.State).WithAlias(() => dto.State)
            .SelectGroup(() => officeAlias.Zip).WithAlias(() => dto.Zip)
            .SelectGroup(() => contactInfo.ContactData).WithAlias(() => dto.Phone)
            .SelectGroup(() => salesRepAlias.FirstName).WithAlias(() => dto.SalesRepFirstName)
            .SelectGroup(() => salesRepAlias.LastName).WithAlias(() => dto.SalesRepLastName)
            )
            .TransformUsing(Transformers.AliasToBean<MyRosterDTO>())
            .List<MyRosterDTO>();
    }

该查询很好且快速，但问题出在以下地方：如果记录中没有具有ContactTypeID为8的contactInfo，则该记录会从最终结果中丢失。 （8等于电话号码，仅供参考。）

我需要做的就是获取客户记录，显示所有客户，但在可用时提供电话号码，在不可用时不提供任何电话号码。

诀窍是，contactInfo包含多种类型的联系信息（电子邮件，电话，传真等），并且没有上面的.Where子句，输出数据会爆炸，在屏幕上为用户拥有的每个contactInfo记录显示一条记录-因此数据库中具有3个contactInfo类型的用户显示为3行输出数据。

此方法用于根据输入参数获取客户列表，但与其显示没有电话号码的客户，还不显示那些没有电话号码的客户，而是仅显示拥有电话号码的客户。

如果这是SQL，我会很高兴

LEFT JOIN ContactInfo as CI on customer.UserId = CI.UserId AND CI.ContactTypeID = 8

我的结果查询将显示有和没有电话号码的客户。

最佳答案

您需要“ with子句”，它来自HQL，并已集成到以下查询中：

.Left.JoinAlias(c => c.ContactInfo, () => contactInfo, () => contactInfo.ContactTypeID == 8)

这将产生以下SQL：

SELECT ...
FROM
   ...
  LEFT OUTER JOIN ContactInfo as CI on (customer.UserId = CI.UserId
    AND CI.ContactTypeID = 8)

与之相反

.Left.JoinAlias(c => c.ContactInfo, () => contactInfo)
.Where(x => contactInfo.ContactTypeID == 8)

哪个创造

SELECT ...
FROM
   ...
   LEFT OUTER JOIN ContactInfo as CI on (customer.UserId = CI.UserId)
WHERE CI.ContactTypeID = 8

HQL中的WITH子句如下所示：

FROM
  ...
  left join ContactInfo ci WITH ci.ContactTypeID = 8

参见此blog post by Fabio。