Object A has a collection of object B. In NHibernate using LINQ, how to retrieve a list of A having at least one B

Question

Let's take as example these domain objects:

public class A 
{
    public Guid Id { get; set; }
    public ICollection<B> CollectionOfB { get; set; }
}

public class B 
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

I need to retrieve any A object having a B with these names name1, name2 using NHibernate 3.x.

For example, imagine that you get which B ones want to retrieve from an array of names as string string[] names = new string[] { "name1", "name2" }.

I thought about .Query<A>().Where(someA => some.CollectionOfB.Any(someB => names.Contains(someB.Name))), and ~~I doubt that this would be compiled as an SQL query by the NHibernate LINQ provider.~~ maybe this would be compiled into a not very optimal SQL query.

What would be the optimal way of building this query using NHibernate 3.x LINQ provider?

@DanielHilgarth Really? By the way, I'm not sure if this would load the entire "CollectionOfB" of each `A` in memory, that's why I'm asking this question :D — Matías Fidemraizer, Jan 07 '13 at 12:29
You could always check the generated SQL... It won't load the entire collection into the memory for the query, because the complete query will be translated into one SQL statement. — Daniel Hilgarth, Jan 07 '13 at 12:33
@DanielHilgarth I see. Yeah, I know I could check it using some SQL profiler. The problem is I'm in a very early stage of my development and I'd like to check that some things would work as expected and, at the end of the day, if those are going to be efficient. — Matías Fidemraizer, Jan 07 '13 at 16:50
Can someone explain WHY 4 users found this question "not a real question"? — Matías Fidemraizer, Jan 08 '13 at 15:41
Well, I am not so sure. maybe they thought that it doesn't meet the SO standards. — Andro Selva, Jan 09 '13 at 12:33
@AndroSelva Sadly I believe that there's no reason. If anyone checks the accepted answer it's going to find that, finally, the question made sense: my LINQ query was improved! — Matías Fidemraizer, Jan 09 '13 at 14:22

Jamie Ide · Accepted Answer · 2013-01-07T17:48:05.120

The query you thought about:

var someAs = session.Query<A>().Where(someA => some.CollectionOfB.Any(someB => names.Contains(someB.Name)));

works fine, I tested it on a similar structure. NHibernate issues a correlated subquery containing an IN clause.

[edit] In regards to your comment, it's optimal in the sense that the intent of the query is clear from the code. Performance has to be measured and database engines use a query optimizer that can often translate ugly looking queries into very efficient ones, so, like many things, you can't judge a query on appearance.

I changed my sample query to use an inner join and got a better execution plan (it eliminated a sort operation). If I re-write the query using LINQ query syntax it uses the better execution plan but the query does not contain an inner join; instead it uses the older style join in the where clause.

var someAs = (from someA in session.Query<A>()
              join someB in session.Query<B>() on someA.Id equals someB.A_Id
              where names.Contains(someB.Name)
              select someA).ToArray();

Nice, thank you for testing it. Sometimes NHibernate isn't smart enough to translate such things, more when we talk about pure .NET objects - like the names' array -. One "subquestion": is this the optimal way of querying for such data? Would be other approach to get the same results, even with a modified object graph? I believe that because a good underlying SQL query would be `SELECT * FROM A INNER JOIN B on A.Id=B.A_Id WHERE B.Name = 'value1' OR B.Name = 'value2'`. Obviously, an association to A should be added in B. Do you know a way of achieving this query using the LINQ provider? — Matías Fidemraizer, Jan 07 '13 at 16:45

Yurii Hohan · Answer 2 · 2013-01-07T13:02:52.733

if you extend class B to have a reference to A:

public class B 
{
    public A A {get;set;}
    public Guid Id { get; set; }
    public string Name { get; set; }
}

And then do subquery like this:

var aObjectsToRetrieve = Query<B>().Where(x=>x.Name.StartsWith("name1")).Select(x=>x.A.Id);

And then apply Exists() in the query of A to get the A objects you need. If you have an array of names, you could try something like this (or a conjunction depending on your scenario):

  var disjunction = new Disjunction();
  var names = new[] {"name1", "name2"};
  foreach (string name in names)
            disjunction.Add(Restrictions.InsensitiveLike(PropName<User>(x => x.Name), name));

Thanks for taking your time. I believe Jaime Ide's answer, which simply verifies that my query would work, should work fine. By the way, I've asked him for, if possible, enhancing the query itself using the LINQ provider only. — Matías Fidemraizer, Jan 07 '13 at 16:48

Object A has a collection of object B. In NHibernate using LINQ, how to retrieve a list of A having at least one B

2 Answers2