NHibernate create criteria, add Restrictions on related table with nullable foreign key

Question

I work on a Api project in .NET core 2.2 with NHibernate (5.2.5)

Table A has nullable foreign key from table B

I need to create a criteria to filter Table A, using a nullable foreign key (which is not null) that points to Table B, where the Status in Table B is 3

This is something what I have tried:

var criteria = _session.CreateCriteria<AgencyAgreement>();criteria.Add(Restrictions.Eq("BookingCartItem.Status", (int)BookingCartStatus.Cancelled));

also with and operator

criteria.Add(Restrictions.And(Restrictions.IsNotNull("BookingCartItem"), Restrictions.Eq("BookingCartItem.Status", (int)BookingCartStatus.Cancelled)));

but this is what I get as Error

at NHibernate.Persister.Entity.AbstractPropertyMapping.GetColumns(String propertyName)\r\n at NHibernate.Persister.Entity.AbstractPropertyMapping.ToColumns(String alias, String propertyName)\r\n at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetColumnsUsingProjection(ICriteria subcriteria, String propertyName)\r\n at NHibernate.Criterion.CriterionUtil.GetColumnNamesUsingPropertyName(ICriteriaQuery criteriaQuery, ICriteria criteria, String propertyName, Object value, ICriterion critertion)\r\n at NHibernate.Criterion.SimpleExpression.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)\r\n at NHibernate.Criterion.LogicalExpression.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)\r\n at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition()\r\n at NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persister, CriteriaQueryTranslator translator, ISessionFactoryImplementor factory, ICriteria criteria, String rootEntityName, IDictionary2 enabledFilters)\r\n at NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persister, ISessionFactoryImplementor factory, CriteriaImpl rootCriteria, String rootEntityName, IDictionary2 enabledFilters)\r\n at NHibernate.Impl.SessionImpl.ListAsync(CriteriaImpl criteria, IList results, CancellationToken cancellationToken)\r\n at NHibernate.Impl.CriteriaImpl.ListAsync(IList results, CancellationToken cancellationToken)\r\n at NHibernate.Impl.CriteriaImpl.ListAsync[T](CancellationToken cancellationToken)\r\n at HASELT.merakzy.Services.Features.Documents.QueryTravelAgreements.Handler.Handle(Request request, CancellationToken cancellationToken) in C:\Users\Asus\Desktop\merakzy-sourcecode\merakzy-master\src\HASELT.merakzy.Services\Features\AgencyDocuments\QueryTravelAgreements.cs:line 115\r\n at MediatR.Pipeline.RequestPostProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at MediatR.Pipeline.RequestPreProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next)\r\n at lambda_method(Closure , Object )\r\n at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()\r\n at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n at System.Threading.Tasks.ValueTask`1.get_Result()\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()\r\n at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextExceptionFilterAsync()

Answer 1

var criteria = _session.CreateCriteria<AgencyAgreement>();
criteria.CreateCriteria(nameof(AgencyAgreement.BookingCartItem))
    .Add(Restrictions.Eq("Status", (int)BookingCartStatus.Cancelled));

or using linq if the properties are known at compile time

var query = _session.Query<AgencyAgreement>();
query = query.Where(a => a.BookingCartItem.Status == (int)BookingCartStatus.Cancelled);