I have two classes (Request & RequestDetail). I need to a Linq To NHibernate
query 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?
LINQ Join Example. linq join between two tables, lists, linq join by multiple columns, linq join equals multiple conditions, Linq Inner Join with where clause, JOIN on multiple tables, GROUP JOIN, CROSS JOIN, LEFT OUTER JOIN Examples, linq join anonymous type
NHibernate 3.0 introduces the Linq to NHibernate provider, which allows the use of the Linq API for querying with NHibernate. IQueryable queries are obtained with the Query methods used on the ISession or IStatelessSession. (Prior to NHibernate 5.0, these methods were extensions defined in the NHibernate.Linq namespace.)
The simple LINQ inner join example is given below: Sometimes, you need to apply inner join with and condition. To write query for inner join with and condition you need to make two anonymous types (one for left table and one for right table) by using new keyword and compare both the anonymous types as shown below:
this is an example of how to join two tables in linq and select columns from different tables, also using order by clause. var q = (from order in GetOrderList () join cust in GetCustomerList () on order.CustomerId equals cust.CustomerId orderby cust.CustomerId select new { order.OrderId, order.Price, order.Quantity, order.OrderDate, ...
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();
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With