Hi there,

This stripped down code results in the following error...

                List<AccountToConfirm> accounts = (from a in m.Account
                                                       select new AccountToConfirm
                                                              {
                                                                  AccountID = a.AccountId,
                                                                  OD = (from aa in m.AccountAssignment
                                                                           where aa.AccountId == a.AccountId
                                                                           join od in m.OrderDetail on aa.OrderDetailId
                                                                               equals od.OrderDetailId
                                                                           select od).First()
                                                              }).ToList();

A nested query relies on a correlation filter which refers to the field 'AccountId', however this field wasn't found in the projection of the entity.

Any ideas?

Cheers, Ian.

Oh I see... the sub-query's projected entity needs to have an AccountID property!

So I add this...

        public class AccountOrder
        {
            public int AccountId { get; set; }
            public OrderEntity Order { get; set; }
        }

...and change the query to this...

                List<AccountToConfirm> accounts = (from a in m.Account
                                                        
                                                   select new AccountToConfirm
                                                              {
                                                                  AccountID = a.AccountId,
                                                                  Order = (from aa in m.AccountAssignment
                                                                           where aa.AccountId == a.AccountId
                                                                           join od in m.OrderDetail on aa.OrderDetailId
                                                                               equals od.OrderDetailId
                                                                           join o in m.Order on od.OrderId equals
                                                                               o.OrderId
                                                                           orderby o.Date ascending
                                                                           select new AccountOrder { AccountId = aa.AccountId, Order = o}).First()
                                                              }).ToList();

...and it says...

Unable to cast object of type 'System.Int32' to type 'EntityClasses.OrderEntity'.

So stuck again.

Code: List<AccountToConfirm> accounts = (from a in m.Account select new AccountToConfirm { AccountID = a.AccountId, OD = (from aa in m.AccountAssignment where aa.AccountId == a.AccountId join od in m.OrderDetail on aa.OrderDetailId equals od.OrderDetailId select od).First() }).ToList();

Quote: A nested query relies on a correlation filter which refers to the field 'AccountId', however this field wasn't found in the projection of the entity.

Would you please try the following:

               List<AccountToConfirm> accounts = (from aa in m.AccountAssignment
                                                     select new AccountToConfirm
                                                             {
                                                                 AccountID = aa.AccountId,
                                                                 OD = od.OrderDetail
                                                             }).ToList();

Hi Walaa,

I can't do...

List<AccountToConfirm> accounts = (from aa in m.AccountAssignment
                                                     select new AccountToConfirm
                                                             {
                                                                 AccountID = aa.AccountId,
                                                                 OD = od.OrderDetail
                                                             }).ToList();

...because 'od'is undefined. Anyhow, the code you quoted is from my initial post. The code that's resulting in the int32 error is this...

List<AccountToConfirm> accounts = (from a in m.Account
                                                        
                                                 select new AccountToConfirm
                                                             {
                                                                 AccountID = a.AccountId,
                                                                 Order = (from aa in m.AccountAssignment
                                                                         where aa.AccountId == a.AccountId
                                                                         join od in m.OrderDetail on aa.OrderDetailId
                                                                             equals od.OrderDetailId
                                                                         join o in m.Order on od.OrderId equals
                                                                             o.OrderId
                                                                         orderby o.Date ascending
                                                                         select new AccountOrder { AccountId = aa.AccountId, Order = o}).First()
                                                             }).ToList();

I fixed the issue from the first message. Please excuse the confusion.

Sorry that was a copy paste error from my part, the code I proposed should have been:

List<AccountToConfirm> accounts = (from aa in m.AccountAssignment
                                                     select new AccountToConfirm
                                                             {
                                                                 AccountID = aa.AccountId,
                                                                 OD = aa.OrderDetail
                                                             }).ToList();

This is different from your initial post coz I'm using AccountAssignment directly, not through Account.

Anyway glad you have solved it.

No I still had the int32 problem. But I've done a similar thing to what you suggested and accessed the Order entity that I'm after via the OrderDetail and it works.

So, from the code below, why does it work to project 'od.Order' but 'Order = o' gives the error... 'Unable to cast object of type 'System.Int32' to type 'EntityClasses.OrderEntity'?

I'd have thought for 'od.Order' to be non-null would require a prefetch.

List<AccountToConfirm> accounts = 
                                                       (from a in m.Account
                                                        
                                                   select new AccountToConfirm
                                                              {
                                                                  AccountID = a.AccountId,
                                                                  Order = (from aa in m.AccountAssignment
                                                                           where aa.AccountId == a.AccountId
                                                                           join od in m.OrderDetail on aa.OrderDetailId
                                                                               equals od.OrderDetailId
                                                                           join o in m.Order on od.OrderId equals
                                                                               o.OrderId
                                                                           orderby o.Date ascending
                                                                           select new AccountOrder { AccountId = aa.AccountId, Order = od.Order }).First()
                                                              }).ToList();

why does it work to project 'od.Order' but 'Order = o' gives the error... 'Unable to cast object of type 'System.Int32' to type 'EntityClasses.OrderEntity'?

That's because at compilation time, 'o' wouldn't be evaluated to an entity yet. But od.Order is known to be of type OrderEntity.

But the query compiles fine - if I roll over the 'o' then a tool tip comes up that says its of type OrderEntity plus I'm accessing the 'Date' property in the 'order by'. 'od' is introduced into the query in the same way as 'o' but its OK to project 'od'. The error is thrown by the LLBLGen runtime. Are you sure this isn't a bug?

it's not supported. the nested query is a query which is ran as a normal linq query, however linq to llblgen pro doesn't support projections which store an entity instance into a variable:

from c in metaData.Customer from o in metaData.Order select new {c, o};

that's not supported, and you do the same thing.

Nested queries are also a bit problematic as they require that the two sets which are merged together at runtime have a correlation which can be used at runtime to bind a row in the parent set to 1 or more rows in the child set (nested query result). The current mechanism for that is to find a correlation filter on the projection which binds the projection of the nested query to the outer query.

The outer set, accounts, and the nested set, order details have nothing in common directly, there's no filter to be defined in-memory which binds two rows of these sets together: another entity is necessary but that entity isn't fetched, as that data isn't there. You tried to work around that, but that's not going to work, as that's not supported.

So i.o.w.: what you want cant be done. You could look into prefetch paths though, which might be a better option.

But I _have _worked around it and it is working! Instead of directly projecting a variable introduced in a join I projected a property of a variable introduced in a join .

Off the top of my head and based on my query, this doesn't work...

from a in m.Account
 join o in m.Order on a.OrderId equals o.OrderId
 join c in m.Contact on o.ContactId equals c.ContactId
select new {c};

This does work...

from a in m.Account
 join o in m.Order on a.OrderId equals o.OrderId
 join c in m.Contact on o.ContactId equals c.ContactId
select new {o.Contact};

If the runtime is capable of fetching 'o.Contact' in the second query then it doesn't seem too much of stretch to get 'c' in the first. Does the 'join' correspond to a relation in a relation predicate bucket? Of course one doesn't get the related entity fetched just because a relation is added.

I suppose LLBLGen finds the correlation filter in the second query. Isn't this a cool way of doing a prefetch?

So i.o.w.: what you want cant be done. You could look into prefetch paths though, which might be a better option.

Yes this looks to be the case. With composite queries, one either tries to refer to an entity instance and gets...

Unable to cast object of type 'System.Int32' to type 'JGSR.BFWeekender.DataAccessServices.EntityClasses.SomeClass

...or, with a medium trust environment, one tries to refer to properties of entity instances and gets a security exception!

So the entities don't get loaded and the properties require reflection?

Ian wrote:

So i.o.w.: what you want cant be done. You could look into prefetch paths though, which might be a better option.

Yes this looks to be the case. With composite queries, one either tries to refer to an entity instance and gets...

Unable to cast object of type 'System.Int32' to type 'JGSR.BFWeekender.DataAccessServices.EntityClasses.SomeClass

...or, with a medium trust environment, one tries to refer to properties of entity instances and gets a security exception! So the entities don't get loaded and the properties require reflection?

The security exception comes from the projection compilation, which should normally simply work unless medium trust level has been adjusted on the server.

The anonymous types constructed from entity fetches is indeed a problem, which is hard to solve due to our dual fetch pipeline, and which is postponed till we revisit the runtime in a v3.x version.