Two sub-queries in the linq expression will generate duplicated results

[xiaoyvr]

trafficstars

Version: 5.3.10

If there are two sub-queries like this:

// Cat, Dog and Person are all entities. 
session.Query<Person>()
    .Select(t => new { 
        dogNames = p.Dogs.Select(p => p.Name).ToArray(),
        catNames = p.Cats.Select(p => p.Name).ToArray(),
    }).ToList();

If there is two Dogs and two Cats. The result will have 4 records of dogNames and catNames instead of just 2.

[fredericDelaporte]

Under the hood all this is converted to SQL with joins on both dogs and cats. Joining more than one relationship having many elements results in a Cartesian product which duplicates rows. In other words, what you attempt to do is not supported. Only one "collection" association should be loaded per query, otherwise Cartesian products occur.

[hazzik]

Note for future implementors:

The likely query generated from the Linq in question should be similar to this:

SELECT p.Id personId, d.Id dogId, d.Name dogName, c.Id catId, c.Name catName
FROM Person p
LEFT JOIN Dog d on d.PersonId = p.Id
LEFT JOIN Cat c on c.PersonId = p.Id

Then client side transformer should be applied:

results.GroupBy(
    x => x.personId,
    x => x,
    (i, x) => new
    {
        dogNames = x.GroupBy(y => y.dogId, y => y.dogName).ToList(),
        catNames = x.GroupBy(y => y.catId, y => y.catName).ToList()
    }).ToList();

[xiaoyvr]

I do understand why it is happening. But the semantic of Select is an one-to-one mapping, the end result should be consistent with it. It is fine if it's not supported yet, currently I am using multiple queries and ToFuture to achieve the same result.

Even so, is there anyway to throw an NotSupportedException? Otherwise there is a pretty high chance to introduce uncaught defects in a production environment.