Limit QueryOver with child collection using nHibernate
I am trying to get a parent where all the entities in the child collection are in another list.
For example:
public class Parent {
public virtual int Id {get;set;}
public virtual List<Child> Children {get;set;}
}
public class Child {
public virtual int Id {get;set;}
public virtual string Name {get;set;}
}
I've tried various combinations of Joins and Restrictions but couldn't seem to get it right.
So please help with suggestions.
The current example is below:
public IList<Lead> GetAllAvailable(string[] names)
{
var result = Session.CreateCriteria<Parent>()
.CreateCriteria("Children")
.Add(Expression.In("Name", names)).List<Parent>();
return result;
}
Edit:
This is the sql equivalent:
select *
from dbo.Parent
join ( select p.id
from dbo.Parent p
join dbo.ParentToChildren on p.Id = dbo.ParentsToChildren.Parent_Id
join dbo.Child on dbo.ParentToChildren.Child_Id = dbo.Child.Id
where Name in ( 'foo', 'bar' )
group by p.Id
having count(1) > 1
) as foo on dbo.Parent.Id = foo.Id
source to share
Here's my suggestion:
var parents = session.QueryOver<Child>()
.WhereRestrictionOn(x => x.Name).IsIn(names)
.Select(Projections.Group<Child>(x => x.Parent))
.Where(Restrictions.Ge(Projections.Count<Child>(x => x.Parent), names.Length))
.List<Parent>();
The idea is this: find all children who have Name
as one of the records names
. Group these children by theirs Parent
. This Child
will require a property Parent
mapped to the appropriate parent, but that's a good idea. For all groups with size equal (or larger, but it shouldn't be, so you can replace Ge
with Eq
) names.Length
, return their parent; because if the group size is names.Length
all names were found if neither of the two parent parents have the same name .
Generated request:
SELECT
this_.Parent as y0_
FROM
Child this_
WHERE
this_.Name in (
/* */
)
GROUP BY
this_.Parent
HAVING
count(this_.Parent) >= /* names.Length */;
I created a test application that produced promising results.
If you need to do more with the parents, such as paging or fetching children, you can subdivide this issue into a sub-query (note that the line is .Fetch(x=>x.Children).Eager
not required, this is just an example of what you can do next with the query):
var parentSubQuery =
QueryOver.Of<Child>()
.WhereRestrictionOn(x => x.Name).IsIn(names)
.Select(Projections.Group<Child>(x => x.Parent))
.Where(Restrictions.Ge(Projections.Count<Child>(x => x.Parent), names.Length));
var parents = session.QueryOver<Parent>()
.Fetch(x=>x.Children).Eager // not necessary, just an example
.WithSubquery.WhereProperty(x => x.Id).In(parentSubQuery )
.List();
SQL (without Fetch
):
SELECT
this_.Id as Id1_0_
FROM
Parent this_
WHERE
this_.Id in (
SELECT
this_0_.Parent as y0_
FROM
Child this_0_
WHERE
this_0_.Name in (
/* names */
)
GROUP BY
this_0_.Parent
HAVING
count(this_0_.Parent) >= /* names.length */
);
Update:
If Parent โ Child is many-to-many things get a little more complicated:
Parent parent = null;
var parentSubQuery = QueryOver.Of<Child>()
.WhereRestrictionOn(x => x.Name).IsIn(names)
.JoinQueryOver(x => x.Parents, () => parent)
.Where(Restrictions.Ge(Projections.Count(() => parent.Id), names.Length))
.Select(Projections.Group(() => parent.Id));
var parents = session.QueryOver<Parent>()
.WithSubquery.WhereProperty(x => x.Id).In(parentSubQuery)
.List();
The main difference is that instead of grouping with a direct Parent
property, Child
I first needed to join the parent collection. To refer to each parent, I entered an alias Parent
.
The generated SQL is pretty close to the original approach:
SELECT
this_.Id as Id2_0_
FROM
Parent this_
WHERE
this_.Id in (
SELECT
parent1_.Id as y0_
FROM
Child this_0_
inner join
ChildToParent parents3_
on this_0_.Id=parents3_.ChildId
inner join
Parent parent1_
on parents3_.ParentId=parent1_.Id
WHERE
this_0_.Name in (
/* names */
)
GROUP BY
parent1_.Id
HAVING
count(parent1_.Id) >= /* names.Length */
);
For my test scenario, this works, so hopefully it works for you as well.
source to share