NHibernate左连接查询抛出System.NotImplementedException异常求助

问题描述 #

尝试在TaxInformation表和ThirdParty表之间执行左连接查询，普通连接可正常运行，但左连接时抛出异常，异常信息：

System.NotImplementedException: 'The method or operation is not implemented.'

相关查询代码 #

var TaxInformations = session.Query<TaxInformation>();

var query = session.Query<ThirdParty>()
                   .Where(tpa => tpa.ThirdPartySourceId == 1);

var resultList = (from tpn in query
                  join tti in TaxInformations on tpn.Id equals tti.ThirdPartyId into ttiLeft
                  from ttiL in ttiLeft.DefaultIfEmpty()
                  select new ThirdParty()
                  {
                      Id = tpn.Id,
                      LastName = tpn.LastName,
                      FirstName = tpn.FirstName,
                      DateOfBirth = tpn.DateOfBirth,
                      LegalContactPostalAddressId = tpn.LegalContactPostalAddressId,
                      BirthCityId = tpn.BirthCityId,
                      BirthCityName = tpn.BirthCityName,
                      Code = tpn.Code,
                      ContactPostalAdress = tpn.ContactPostalAdress,
                      TaxInformation = ttiL ?? null
                  }).ToList();
return resultList;

实体映射代码 #

TaxInformation映射 #

public ThirdPartyTaxInformationMap()
{
    this.Table("TaxInformation");
    this.Schema("dbo");
    this.Lazy(false);
    this.DynamicUpdate(true);
    this.DynamicInsert(true);           
    
    this.Id(x => x.Id, map => map.Generator(Generators.Identity));
    this.Property(x => x.ThirdPartyId);
    

    this.ManyToOne(
        x => x.Country,
        map =>
        {
            map.Column("CountryId");
            map.Cascade(Cascade.None);
            map.Lazy(LazyRelation.NoLazy);
            map.Fetch(FetchKind.Join);
            map.Update(false);
            map.Insert(false);
        }
        );
}

ThirdParty映射 #

public ThirdPartyMap()
{
    this.Table("ThirdParty");
    this.Lazy(false);
    this.DynamicUpdate(true);
    this.DynamicInsert(true);

    this.Id(x => x.Id, map => map.Generator(Generators.Identity));
    this.Property(x => x.Code, map => map.Column("Code"));
    this.Property(x => x.ThirdPartySourceId);
    

    this.ManyToOne(
        x => x.ContactPostalAdress,
        map =>
        {
            map.Column("LegalContactPostalAddressId");
            map.Cascade(Cascade.None);
            map.Lazy(LazyRelation.NoLazy);
            map.Fetch(FetchKind.Join);
            map.Update(false);
            map.Insert(false);
        });


    #region Third Party Type
    ManyToOne(p => p.ThirdPartyType, m =>
    {
        m.Class(typeof(ThirdPartyType));
        m.Column("ThirdPartyTypeId");
        m.NotNullable(false);
        m.Fetch(FetchKind.Join);
        m.Lazy(LazyRelation.NoLazy);
        m.Update(true);
        m.Insert(true);
    });
    #endregion
}

调用栈信息 #

at NHibernate.Linq.Visitors.QueryModelVisitor.VisitGroupJoinClause(GroupJoinClause groupJoinClause, QueryModel queryModel, Int32 index)
at Remotion.Linq.Clauses.GroupJoinClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
at NHibernate.Linq.Visitors.QueryModelVisitor.Visit()
at NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root, Nullable`1 rootReturnType)
at NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)
at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
at NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query)
at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
at Remotion.Linq.QueryableBase`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Oddo.MAP.Server.Modules.Core.Business.ThirdPartyBusinessServices.FindAllThirdpartyNatural() in C:\projets\MAP_Old\Server\Modules\Core\Server.Modules.Core.Business\ThirdPartyBusinessServices.cs:line 251
at Oddo.MAP.Server.Modules.Core.Business.ThirdPartyBusinessServices.CheckMatchingForThirdParties(Boolean isThirdPartyNatural) in C:\projets\MAP_Old\Server\Modules\Core\Server.Modules.Core.Business\ThirdPartyBusinessServices.cs:line 565
at DynamicModule.ns.Wrapped_IThirdPartyBusinessServices_fcf8cfddd9494f5898ae07beeb4a9d19.<CheckMatchingForThirdParties_DelegateImplementation>__2(IMethodInvocation inputs, GetNextInterceptionBehaviorDelegate getNext)
--- End of stack trace from previous location ---
at DynamicModule.ns.Wrapped_IThirdPartyBusinessServices_fcf8cfddd9494f5898ae07beeb4a9d19.CheckMatchingForThirdParties(Boolean isThirdPartyNatural)
at Oddo.MAP.Host.Dedoublonnage.Console.AppBootstrapper.OnModulesLoaded() in C:\projets\MAP_Old\Host\Host.Dedoublonnage\Program.cs:line 78
at Oddo.Components.Hosting.Unity.WebApi.WebApiHostingUnityBootstrapper.Initialize()
at Oddo.MAP.Host.Dedoublonnage.Console.AppBootstrapper.Initialize() in C:\projets\MAP_Old\Host\Host.Dedoublonnage\Program.cs:line 43
at Oddo.MAP.Host.Dedoublonnage.Console.Program.Main(String[] args) in C:\projets\MAP_Old\Host\Host.Dedoublonnage\Program.cs:line 26

异常原因 #

从调用栈可以明确，异常源于当前使用的NHibernate版本的Linq Provider未实现GroupJoinClause的解析逻辑——也就是代码中Linq语法的左连接写法（join ... into ... DefaultIfEmpty()本质是GroupJoin操作），早期NHibernate版本对Linq的GroupJoin支持存在缺陷或完全未实现。

解决方法 #

1. 使用NHibernate原生QueryOver实现左连接 #

QueryOver是NHibernate原生的强类型查询API，对左连接支持完善，示例代码：

// 定义别名变量
TaxInformation ttiAlias = null;
ThirdParty resultAlias = null;

var resultList = session.QueryOver<ThirdParty>()
    .Where(tpa => tpa.ThirdPartySourceId == 1)
    // 左连接TaxInformation表
    .Left.JoinQueryOver<TaxInformation>(t => t.TaxInformation, () => ttiAlias, 
        Restrictions.EqProperty("Id", "ttiAlias.ThirdPartyId"))
    // 构造返回结果
    .SelectList(list => list
        .Select(t => t.Id).WithAlias(() => resultAlias.Id)
        .Select(t => t.LastName).WithAlias(() => resultAlias.LastName)
        .Select(t => t.FirstName).WithAlias(() => resultAlias.FirstName)
        .Select(t => t.DateOfBirth).WithAlias(() => resultAlias.DateOfBirth)
        .Select(t => t.LegalContactPostalAddressId).WithAlias(() => resultAlias.LegalContactPostalAddressId)
        .Select(t => t.BirthCityId).WithAlias(() => resultAlias.BirthCityId)
        .Select(t => t.BirthCityName).WithAlias(() => resultAlias.BirthCityName)
        .Select(t => t.Code).WithAlias(() => resultAlias.Code)
        .Select(t => t.ContactPostalAdress).WithAlias(() => resultAlias.ContactPostalAdress)
        .Select(() => ttiAlias).WithAlias(() => resultAlias.TaxInformation)
    )
    // 将查询结果映射到ThirdParty实体
    .TransformUsing(Transformers.AliasToBean<ThirdParty>())
    .List<ThirdParty>();

2. 升级NHibernate版本 #

如果项目依赖允许，将NHibernate升级到5.0及以上版本，新版本的Linq Provider已经完善了GroupJoin的支持，可以直接兼容当前的Linq左连接写法，无需修改查询代码。

3. 利用实体关联导航实现左连接加载 #

在ThirdParty实体中添加TaxInformation的导航属性（若未配置），并完善映射关系（比如OneToOne或ManyToOne，根据实际业务关系），之后可以直接通过Fetch左连接加载关联数据：

var resultList = session.Query<ThirdParty>()
    .Where(tpa => tpa.ThirdPartySourceId == 1)
    .FetchLeftJoin(t => t.TaxInformation)
    .ToList();

内容的提问来源于stack exchange，提问作者KiraKo