Where-clause for LINQ-to-NHibernate FetchMany?

Question

Using LINQ-to-NHibernate is there a way to narrow down what FetchMany() returns?

Given the following class structure

public class Foo
{
  public virtual int Id { get; set; }
  public virtual IList<Bar> Bars { get; set; }
}
public class Bar
{
  public virtual string Description { get; set; }
}

How can I do this:

session.Query<Foo>()
  .Where(foo => foo.Id > 30)
  .FetchMany(foo => 
    foo.Bars.Where(bar => bar.Description.StartsWith("x")));

And NHibernate will return all Foo's with an Id > 30 and for those Foo's all the attached Bar's where the Bar's description starts with the letter 'x'?

I found some posts that use the old QueryOver() stuff but I explicitely want to use NHibernate's LINQ provider.

Any ideas?

Update

I think I need to clarify what I want as a result.

<Foo Id="1">
  <Bar Description="x1"/>
  <Bar Description="b1"/>
</Foo>
<Foo Id="31">
  <Bar Description="x2"/>
  <Bar Description="x3"/>
  <Bar Description="b2"/>
</Foo>
<Foo Id="32">
  <Bar Description="b3"/>
</Foo>

From the data outlined above I expect the following result

<Foo Id="31">
  <Bar Description="x2"/>
  <Bar Description="x3"/>
</Foo>
<Foo Id="32"/>

The additional Where clause should only work on the Bar's! It should not further narrow down the list of Foo's! Just reduce what FetchMany() returns.

@GertArnold Right now that query won't even compile... If I remove the second Where-clause it will return all Foo's with an Id > 30 plus ALL Bars attached to each one of them. I can't find a way to do any filtering on what the call to 'FetchMany()' will return. — Sebastian Weber, Jul 14 '13 at 07:32
@SarabjeetSingh sorry I can't really remember. The idea with the filters DanP mentioned sounds vaguely familiar but I'm not sure if we really used that one. — Sebastian Weber, Apr 21 '21 at 04:58
@SebastianWeber I just tried the filters option as suggested by DanP and it is working as per my expectations. Thank you. — Sarabjeet Singh, Apr 21 '21 at 08:24
@SarabjeetSingh Just stumbled across the [release notes for EFCore 5.0](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#filtered-include) where they added support for just that feature — Sebastian Weber, Apr 26 '21 at 11:18

score 1 · Answer 1 · answered Jul 16 '13 at 17:11

I'm fairly sure you're out of luck with the current linq provider - but for a non-linq (and cross-cutting) option, you might want to have a look at the filter functionality included in NHibernate - it would probably be the best bet for implementing this in a large scale / complex project.

Aron · Answer 2 · 2013-07-19T02:15:38.760

var query = from foo in session.Query<Foo>()
            where foo.Id >30
            from bar in foo.Bars
            where bar.Description.StartsWith("x")
            select new { Id = foo, Bar = bar};
var results = query.ToList().ToLookup(x => x, x => x.Bar);
foreach(var foo in results.Keys)
{
   var barsWhichStartWithX = results[foo];
   //DO STUFF
}

Although this may produce inefficient SQL (I don't use nHibernate so I wouldn't know). You could also try this...Also the above would miss out foos without bars.

var foosQuery = session.Query<Foo>().Where(foo => foo.Id > 30);
var foos = foosQuery.Future();
var barsQuery = from f in foosQuery
                from bar in foo.Bars
                select new { Id = foo.Id, Bar = bar};
var foosDict = foos.ToDictionary(x => x.Id);
var bars = barsQuery.ToList().ToLookup(x => foosDict[x.Id], x => x.Bar);
foreach(var foo in foos)
{
   var barsWhichStartWithX = bars[foo];
   //DO STUFF
}

score 0 · Answer 3 · answered Jul 15 '13 at 17:08

0

Perhaps not exactly what you're after, but certainly worth considering is the NHibernate.CollectionQuery library.

It allows you to query uninitialized NHibernate collections on an entity using Linq - but would require an additional query/round-trip to get them (unlike FetchMany, which grabs the entire collection in the same round-trip).

answered Jul 15 '13 at 17:08

DanP

6,310
4
40
68

It will produce SELECT N+1 problem. – hazzik Jul 16 '13 at 00:02
We have a rather complex object hierarchy. Manually querying every collection would add too much overhead (both in code and in number of round-trips). But it looks like an interesting project. – Sebastian Weber Jul 16 '13 at 06:21

score 0 · Answer 4 · answered Jul 16 '13 at 14:01

0

You will need a reference from child object to the parent.

var result = from foo in session.Query<Foo>().FetchMany(f => f.Bars)
             from bar in session.Query<Bar>()
             where foo.Id == bar.FooId && // FooId is missing in your entity
                   foo.Id > 30
                   bar.Description.StartsWith("x")
             select foo;

answered Jul 16 '13 at 14:01

mynkow

4,408
4
38
65

There is no "back-link" from Bar to Foo in our model and we don't plan to introduce one just for the purpose of these queries. – Sebastian Weber Jul 16 '13 at 14:56

Where-clause for LINQ-to-NHibernate FetchMany?

4 Answers4