How to use join with multiple conditions in linq-to-Nhibernate

Question

I have two classes (Request & RequestDetail). I need to a Linq To NHibernatequery between two classes by join.

var q = SessionInstance.Query<Request>()
       .Where(x => x.State == "Init");

var q2 = SessionInstance.Query<RequestDetail>();
q2 = q2.Where(xx => xx.Purpose.Contains("Purpose Sample")); // This line has a error When execution ‍‍`q.ToList()‍`

q = q.Join(q2, request => request.Id, detail => detail.Id, (request, detail) => request);

return q.ToList();

When I added a Where condition to q2, Result has a runtime error. Message of exception is : Specified method is not supported.

Stack Trace :

   at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName(IASTNode querySource)
   at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.Process(IASTNode tree)
   at NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process()
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryIdentifier, 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.NhQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery)
   at NHibernate.Linq.NhQueryProvider.Execute[TResult](Expression expression)
   at Remotion.Data.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Why?

score 28 · Answer 1 · answered Nov 16 '12 at 05:33

28

It could be a bug.

Here is workaround for your problem:

var q = (from request in session.Query<Request>()
        join detail in session.Query<RequestDetail>() on request.Id equals detail.Id
        where request.State == "Init" && detail.Purpose.Contains("Purpose Sample")
        select request).ToList();

answered Nov 16 '12 at 05:33

SHSE

2,423
17
16

7

This answer needs to be ANNOUNCED TO THE WORLD! This is the way it can and does work! I can't believe how many times we've looked and not found any solution other than the horrid QueryOver stuff, or being forced to create new mappings! – user2415376 Sep 17 '14 at 17:46

score 9 · Answer 2 · answered Jan 03 '17 at 12:56

In fluent-notation:

var q = session.Query<Request>()
        .Join(session.Query<RequestDetail>(), request => request.Id, detail => detail.Id, (request, detail) => new {
            r = request,
            d = detail
        })
        .Where(rd => rd.r.State == "Init" && rd.d.Purpose.Contains("Purpose Sample"))
        .Select(rd => rd.r)
        .ToList();

How to use join with multiple conditions in linq-to-Nhibernate

2 Answers2