NHibernate filter collection by subcollection items

Question

Health record may have Symptom, which consists of some Words. (ER diagram.)

What I need: by given set of Words return Health records with corresponding Symptoms.

I have this code:

public IEnumerable<HealthRecord> GetByWords(IEnumerable<Word> words)
{
    var wordsIds = words.Select(w => w.Id).ToList();
    Word word = null;
    HealthRecord hr = null;
    ISession session = NHibernateHelper.GetSession();
    {
        return session.QueryOver<HealthRecord>(() => hr)
            .WhereRestrictionOn(() => hr.Symptom).IsNotNull()
            .Inner.JoinAlias(() => hr.Symptom.Words, () => word)
            .WhereRestrictionOn(() => word.Id).IsIn(wordsIds)
            .List();
    }
}

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

What we should use here is: INNER SELECT, i.e. subquery. We can do that even with many-to-many maping, but the performance will suffer.

The (easier, my prefered) way would be to not use many-to-many mapping. Because with explicitly mapped pairing object SymptomWord, querying would be much more easier.

Word word = null;
Symptom symptom = null;

// the sub SELECT returning column Symptom.Id 
var subq = QueryOver.Of<Symptom>(() => symptom)
   // just symptoms refering the searched words
   .Inner.JoinAlias(() => symptom.Words, () => word)
   .WhereRestrictionOn(() => word.Id).IsIn(wordsIds)
   // the result of inner select is 
  .Select(s => symptom.Id);

And in the next step we can use it for filtering:

var list = session
    // just query over HealthRecord here
    .QueryOver<HealthRecord>()
    .WithSubquery
      // the ID of referenced Symptom is in that table
      .WhereProperty(hr => hr.Symptom.Id) 
      // and will be filtered with our subquery
      .In(subq)
    .List<HelthRecord>();

return list;

That should work, also check some similar issue here:

Some hint how to re-map many-to-many (because with a pairing table mapped as an object, we can construct similar and simplified construct, resulting in better SQL Statement)

Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?

edited May 23 '17 at 12:05

Community

1
1

answered May 18 '14 at 09:33

Radim Köhler

122,561
47
239
335

All goes fine until `words` contains only one item. Else, generated SQL with syntax error: `[ SELECT this_.Id as Id10_0_, this_.SymptomID as SymptomID10_0_ from HealthRecord this_ WHERE this_.SymptomID in (SELECT this_0_.Id as y0_ from (select * from (Symptom this_0_ inner join SymptomWords words3_ on this_0_.Id=words3_.SymptomID) inner join Word word1_ on words3_.WordID=word1_.Id WHERE word1_.Id in (?) as jetJoinAlias0, ?)) ]` – smg May 18 '14 at 15:28
I see. But do you know what? this will be ... the first problem in a row of many, I'd say. The `many-to-many` simply brings just issues. But your pairing table contains its own ID column. Which is briliant, because it could be mapped as standard entity. Search (subquery) over such a relation will be much more easier. And later... you will also be able to extend that object (SortBy, IsActive...) ... will have a look at it... but.. not sure if can help more – Radim Köhler May 18 '14 at 15:36
Well, I just retested and was not reproduce your issue. In my test example, I used the query **as is** and it is working for any amount of words. In fact, the SQL snippet you've shown in the comment is very weird (e.g. the braces like `from (Symptom this_0`). – Radim Köhler May 18 '14 at 15:59
I added SymptomWords entity as you advise. Final query is `session.QueryOver(() => hr).JoinAlias(() => hr.Symptom, () => symptom).JoinAlias(() => symptom.SymptomWords, () => sw).JoinAlias(() => sw.Word, () => word).WhereRestrictionOn(() => word.Id).IsIn(wordsIds).TransformUsing(Transformers.DistinctRootEntity).List()` – smg May 27 '14 at 09:57
Great to see that! Good job. Enjoy NHibernate ;) – Radim Köhler May 27 '14 at 10:00
And if I need all Words to be in Symptom, as described [here](http://stackoverflow.com/a/209823/3009578), what should I add instead `TransformUsing(Transformers.DistinctRootEntity)`? – smg May 27 '14 at 13:14

NHibernate filter collection by subcollection items

1 Answers1