Question

Hi,

Say I have a Person entity, and each person can have multiple Pets assigned to him. The assignments are managed through a third entity called PetAssignment (simply has Person and Pet properties).

How can I select all Persons (their names, addresses, etc.) as well as a comma-separated list of their Pets' names? I want this done in a way that DO only executes one query to the DB!

For example:

from person in session.Query.All<Person>
join assignment in session.Query.All<PetAssignment> on person equals assignment.Person into assignments
select new {
Name = person.Name,
PetNames = String.Join(", ", assignments.Select(a => a.Pet.Name))
};

I've done this using both a JOIN ... INTO as well as by doing a JOIN + GROUP BY. I either get multiple queries, or I get an exception:

The current transaction is different from the transaction bound to this instance.

I'm using DO 4.5

Answer 1

1

may be something like this?

var data=session.Query.All<PetAssignment>().Select(a=>new{PersonName=a.Person.Name, PetName=a.Pet,Name, ...}).ToArray();
//convert to needed format via linq

answered Nov 19 '11 at 17:19

proff
75●3●3●6

edited Nov 19 '11 at 17:23

Yes, an approach like this is necessary here. DO sends additional queries in two cases:

When you enumerate an IGrouping<TKey, TElement> - ie., there was a group clause in the original query, and you need to iterate elements of some groups.
When you enumerate an IQueryable<T> you've got as part of projection - ie., there was a subquery in your projection.

Additional queries are batched in both these cases. But to avoid them completely, you must "flatten" the result with .SelectMany.

(Nov 20 '11 at 02:31) Alex Yakunin

Flattening example:

var groups = // some query returning IGrouping objects
var flattened = 
  from group in groups
  let key = group.Key
  from element in group
  select new {key, element};

(Nov 20 '11 at 02:31) Alex Yakunin

About your example: I suspect there is no equivalent to string.Join in SQL, so DO handles this part of projection on the client side, and thus it considers there is a subquery returning IQueryable<string> that must be transformed to a single string on the client.

I'll give you a full example for your case shortly.

(Nov 20 '11 at 02:42) Alex Yakunin

Full example:

var pairs = 
    from pa in sessin.Query.All<PetAssignment>()
    select new {person = pa.Person, pet = pa.Pet};
var result =
    from pair in  pairs.AsEnumerable()
    group pair by pair.person into pets
    select "{0} ({1})".FormatWith(
      pets.Key, pets.ToCommaDelimitedString());

(Nov 20 '11 at 05:29) Alex Yakunin

Subscription:

Related questions