Limit child entities without limiting parent entities - NHibernate

Question

I'm trying to limit the result set of a mapped collection.

Here is a simple model:

public class Table1 {
    public virtual long Id { get; set; }
    public virtual IList<Table2> Table2s { get; set; }
}


public class Table2 {
    public virtual long Id { get; set; }
    public virtual long Table1Id { get; set; }
    public virtual Table1 Table1 { get; set; }
    public virtual string Field { get; set; }
}

public class Table1Map : ClassMap<Table1> {
    public Table1Map () {
        Table("Table1");
        Id(x => x.Id).Column("Id").Not.Nullable().CustomType("Int64").GeneratedBy.Native();
        HasMany<Table2>(x => x.Table2s).Inverse().Not.LazyLoad().KeyColumns.Add("Table1Id").Fetch.Join();
    }
}

public class Table2Map : ClassMap<Table2> {
    public Table2Map () {
        Table("Table2");
        Id(x => x.Id).Column("Id").Not.Nullable().CustomType("Int64").GeneratedBy.Native();
        Map(x => x.Table1Id).Column("Table1Id").Not.Nullable().CustomType("Int64");
        Map(x => x.Field).Column("Field").Not.Nullable().CustomType("AnsiString").Length(25);
        References<Table1>(x => x.Table1, "Table1Id").Cascade.None();
    }
}

I want to select all Table1s. I also want to select all Table2s that meet a certain criteria (Table2.Field = 'value'), but I don't want to limit my Table1s, so select null Table2s if they don't meet the criteria. If I want to do this in SQL I'd do the following:

SELECT *
FROM 
Table1
LEFT OUTER JOIN Table2 ON Table1.Id = Table2.Table1Id
WHERE
Table2.Field = 'value' or Table2.Field IS NULL

How should I structure my NHibernate query to achieve the desired result? I'd like a list of Table1s, and within each Table1 I'd like either an empty list of Table2s (because no Table2s met the criteria), or a list of Table2s that met the creteria.

I'm trying something like the following, but this will obviously not work:

List<Table1> result = new List<Table1>();
IQueryable<Table1> query = session.Query<Table1>();
if (value != null) {
    query = query.Where(x => x.Table2s.Field == value);
}
query = query.OrderBy(x => x.Id);
result = query.ToList();

score 0 · Accepted Answer · edited May 23 '17 at 11:51

I think this is not possible the way you do this. Hibernate loads the complete entity with all its properties (if not lazyloading is activated). What should hibernate do, if you save such a loaded entity of type table1 without all table2's?

You should create some kind of viewobject (dvo) that contains the relevant parts of table1 and a list of table2 childs that fit the criteria. The select could possible be done by projection.

score 0 · Answer 2 · edited Jan 08 '16 at 09:04

0

There is pretty good documentation - 16.4. Associations

http://nhibernate.info/doc/nh/en/index.html#queryqueryover-associations

The QueryOver syntax would look like this

IQueryOver<Table1, Table2> myQuery = 
  session.QueryOver<Table1>()
   .Left.JoinQueryOver<Table2>(t => t.Table2s)
     .Where(
       Restrictions.Or(
         Restrictions.On<Table2>((t2) => t2.ID).IsNull, 
         Restrictions.On<Table2>((t2) => t2.Field).IsLike("value")
         )
       );
var list = myQuery.List<Table1>();

then the list will return the collection of all combinations meeting the criteria. (Later order by or distinct or other porjections could be added...)

edited Jan 08 '16 at 09:04

Owen Pauling

11,349
20
53
64

answered Dec 07 '12 at 15:27

Radim Köhler

122,561
47
239
335

This is a pretty good answer, but somewhat limiting. What if `Table1` not only has a collection of `Table2`s, but `Table3`s which you also want to filter. The method provided only accommodates two entities. Is there a more generic method? Is there another approach altogether? Thanks! – Michael Dec 07 '12 at 16:27
Yes! ;) The NHibernate Criteria API is very powerful. You can use projections (select some properties, select distinct... sum, avg) you can join anyhow... you can filter in the WHERE clause...Anything. Please, take a look documentation (http://nhforge.org/doc/nh/en/index.html#querycriteria) or Ayende's blog – Radim Köhler Dec 07 '12 at 16:30
Do you have a working example? I don't find the documentation particularly helpful for my example. I tried to implement your suggestion above, neglectiong "Table3" for now, and got back no results. If you have a working example that uses the powerful Criteria API with multiple mapped collections, that would be a great help! – Michael Dec 07 '12 at 17:16
There is a starting point, where to continue: when finished the query *as in my answer above*, you can continue adding new criteria: `myQuery.RootCriteria.CreateCriteria(...` – Radim Köhler Dec 07 '12 at 17:34

Limit child entities without limiting parent entities - NHibernate

2 Answers2