Hello,

Have been happily using LLBLGen so far, what a wonderful piece of software. Congrats to whole the team!

The question:

I was wondering whether there would be an elegant way to create a grouping in a correlated query.

I want to fill up a class that consists of the following structure:

Person (the class) |_______ PersonId (int) |_______ Accounts (complex type) (a person can be member of multiple accounts) |_______ AccountId (int) |_______ Roles (complex type) (the person can be assigned multiple roles for a given account)

Tried but failed:

var q = from p in metaData.Person
            join pwd in metaData.Password on p.PersonId equals pwd.PersonId
            where pwd.Password == password
            select new
            {
                PersonId = p.PersonId,
                Accounts = from r in metaData.Role ('Role' is the link table/entity which contains all the information needed)
                                    where p.PersonId == r.PersonId
                                    group r by r.AccountId into g
                                    select new
                                    {
                                        AccountId = g.Key,
                                        RoleTypes = g.Select(r => r.RoleTypeId)
                                     }
             };

return q.FirstOrDefault();

The code above gives me following error:

Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query

I'm probably heavily abusing the group by functionality. When I remove the grouping the correlation works.

Is there a way to elegantly fill up my class?

Thanks in advance, Tom

Runtime build: 12 02 2009 Runtime version: 2.6.0.0

Indeed, the problem is on g.Select(..... Itis effectively a nested query, though 'grouping' doesn't exist till the query is executed, so it can't make additional constructs out of it. This is caused by the fact that it's an IGrouping<> typed element, and not just a flat set of data, but a nested structure: for every key there are n rows of data below it in a hierarchy. It might be linq to sql executes this properly, but that's due to the fact it postpones this query for every row till the main query is enumerated (so for every row a query is executed).

So with the description what you want to achieve we might be able to guide you to a construct which is supported.

(Edit) Maybe an approximate SQL code would give us an idea about what you are trying to achieve.

Thanks for your reply,

I ended up replacing the hierarchical query with two seperate LINQ queries.

// One that gets me the PersonId
Person person = (from p in metaData.Person
                             join pwd in metaData.Password on p.PersonId equals pwd.PersonId
                             where pwd.Password == password
                             select new Person()
                            {
                                 PersonId = p.PersonId
                             }).FirstOrDefault();

// and one that gets me a flat list of all his/her roles.
var roleLines = (from r in metaData.Role
                            where r.PersonId == person.PersonId
                            select new
                            {
                                AccountId = r.AccountId,
                                RoleType = (RoleType)r.RoleTypeId
                            }).ToList();

Using LINQ to Objects, the Accounts get grouped together by AccountId. I directly fill up the Accounts property.

person.Accounts = (from rl in roleLines
                                 group rl by rl.AccountId into g
                                 select new Account()
                                 {
                                    AccountId = g.Key,
                                    RoleTypes = g.Select(rt => rt.RoleType)
                                 }).ToList();

I've got my filled up Person object now! I'm not sure this is the way to go though

For the record: I didn't try it with Linq to SQL (my first attempt with the hierarchical set).