CODEWITHSUNDEEP
c#nhibernate
Bruno Ferreira
Bruno Ferreira

Reputation: 1

C# NHibernate root aggregate filtering child collection with subquery

I'm trying to retrieve a list of root elements and its child collection, applying a subquery filter in child collection. This subquery needs to bring the last occurrence of root's child elements.

The expected result is a list of root elements, where each of them have only the last occurence of its child on child collection attribute.

The class representation of these elements:

public class Root {
   public virtual long id { get; set; }
   public virtual ISet<Child> childList { get; set; }
}

public class Child {
   public virtual long id { get; set; }
   public virtual DateTime occurrence { get; set; }
   public virtual Root parent { get; set; }
}

I've tried to create a query using NHibernate's QueryOver, unfortunately it didn't work as expected.

This is the code I've tried:

Root rootAlias = null;
Child childAlias = null;

var query = QueryOver.Of(() => rootAlias)
   .Left.JoinAlias(
      () => rootAlias.childList, 
      () => childAlias, 
      Restrictions.Where<Child>(
         cd => cd.occurrence == QueryOver.Of<Child>()
            .Where(cx => cx.parent.id == rootAlias.id)
            .Select(cx => cx.occurrence)
            .OrderBy(cx => cx.occurrence).Desc
            .Take(1)
            .As<DateTime>()
   )                    
);

I'd be glad if you guys can help me with this issue, or even point me another way to reach the result described above.

Upvotes: 0

Views: 546

Answers (1)

Bruno Ferreira
Bruno Ferreira

Reputation: 1

After searching and reading hundreds of similar posts, I've found a solution for my issue.

As I said in the question: "The expected result is a list of root elements, where each of them have only the last occurence of its child on child collection attribute."

And this is the solution considering my classes structure:

// Aliases
Root rootAlias = null;
Child x_temp = null;
Child childAlias = null;

// Result list.
IList<Root> roots = null;

// Subquery which retrieves a list of child's id by max occurrence and grouping by 
// root id.
var subquery = CurrentSession.QueryOver<Child>(() => x_temp)
    .SelectList(list => list
        .Select(() => x_temp.id)
        .SelectMax(() => x_temp.occurrence)
        .SelectGroup(() => x_temp.root.id)
    )
    .List<object[]>()
        .Select(p => p[0]).ToArray();

// Query root elements, joining with child collection, applying
// a restriction on child's id attribute through 
// JoinAlias' "With clause" parameter.
var query = QueryOver.Of(() => rootAlias).Left.JoinAlias(
    () => rootAlias.childList,
    () => childAlias,
    Restrictions.On(() => childAlias.id).IsIn(subquery)
);

// retrieves the final result list through the query.
roots = query.GetExecutableQueryOver(CurrentSession).List();

That's it!

I hope it can be usefull for someone else with similar issue.

Upvotes: 0

Related Questions