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

asked Nov 18 '11 at 16:43

ara's gravatar image


edited Nov 20 '11 at 06:48

Dmitri%20Maximov's gravatar image

Dmitri Maximov

One Answer:

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's gravatar image


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 Alex%20Yakunin's gravatar image

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 Alex%20Yakunin's gravatar image

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 Alex%20Yakunin's gravatar image

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 Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview


Once you sign in you will be able to subscribe for any updates here



Asked: Nov 18 '11 at 16:43

Seen: 2,158 times

Last updated: Nov 20 '11 at 06:48

powered by OSQA