Geek asks and answers

How to create a CollectionBy property for a child collection in NHibernate 3 using the Linq provider?

I am trying to create a query that orders a child collection property. In SQL, this is pretty simple:

Select Table1.*
From Table1
Inner join Table2 on Table1.Id = Table2.Table1Id
OrderBy Table1.Column1, Table2.Column1

Here's how I did it in NHibernate 2, and it worked fine:

var result = Session.Linq<Table1>()
                 .OrderBy(x => x.Column1)
                     .ThenBy(x => x.Table2.FirstOrDefault().Column1);

After upgrading to NHibernate 3, this no longer works. It throws NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: Antlr.Runtime.NoViableAltException.

I am using NHibernate 3.1. Are there other solutions for such a request?

+3
source share
1 answer

Two requests do not match. The LINQ version (approximately) is:

SELECT Table1.*
FROM Table1
    INNER JOIN (SELECT TOP 1 * FROM Table2 WHERE Table2.Table1ID = Table1.Id) AS FirstTable2
ORDER BY Table1.Colum1, FirstTable2.Table2

EDIT

If you want to replicate the original sql, you might need something like:

Session.Linq<Table1>()
    .SelectMany(t1 => t1.Table2, (t1,t2) => new { t1, t2 })
    .OrderBy(t1t2 => t1t2.t1.Column1)
    .ThenBy(t1t2 => t1t2.t2.Column2)
    .Select(t1t2 => t1)
+4
source

More articles:


All Articles