I'm trying to do a left join between two table TaxInformation and ThirdParty knowing The join works correctly but when I try to do a left join i got the exception with the message: '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;
Mapping done via :
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);
}
);
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
}
The Call stack trace :
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(ObservableCollection1 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, Nullable1 rootReturnType)
at NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)
at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary2 filters, ISessionFactoryImplementor factory) at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary2 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.QueryableBase1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 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
The solution is to use .ToList() with every query launched.
This will load all the data into memory so it will be a consuming task.
In order to minimize to load all the uncessary staff we should do a select where we pick up only needed attributes.
public IList<ThirdPartyNatural> FindAllThirdpartyNatural()
{
var thirdPartyNaturals = DataAccessServicesProvider.Query<ThirdPartyNatural>().Where
(tpa => tpa.ThirdPartySourceId == 1
&& (tpa.ClosingDate == null || (tpa.ClosingDate.HasValue && tpa.ClosingDate.Value.Date > DateTime.Today)));
var thirdPartyTaxInformations = DataAccessServicesProvider.Query<ThirdPartyTaxInformation>();
var resultList = (from thirdPartyNatural in thirdPartyNaturals.Select(th => new { th.Id, th.LastName, th.FirstName, th.DateOfBirth, th.LegalContactPostalAddressId, th.BirthCityId, th.BirthCityName, th.Code, th.ContactPostalAdress }).ToList()
join thirdPartyTaxInformation in thirdPartyTaxInformations.Select(tax => new { tax.ThirdPartyId, tax.OpeningDate, tax.Code, tax.TaxCode }).ToList()
on thirdPartyNatural.Id equals thirdPartyTaxInformation.ThirdPartyId into thirdPartyList
from item in thirdPartyList.DefaultIfEmpty()
select new ThirdPartyNatural
{
Id = thirdPartyNatural.Id,
LastName = thirdPartyNatural.LastName,
FirstName = thirdPartyNatural.FirstName,
DateOfBirth = thirdPartyNatural.DateOfBirth,
LegalContactPostalAddressId = thirdPartyNatural.LegalContactPostalAddressId,
BirthCityId = thirdPartyNatural.BirthCityId,
BirthCityName = thirdPartyNatural.BirthCityName,
Code = thirdPartyNatural.Code,
ContactPostalAdress = thirdPartyNatural.ContactPostalAdress,
TaxCode = item?.TaxCode
}).OrderBy(ps => ps.LastName).ToList();
return resultList;
}