I am currently evaluating LLBLGen, we were looking at EF4.1 and had built a few test queries, so we want to try those queries on LLBLGen.

I have tried to simplify the query down as much as possible, and still get the error, here is the code

            var items = from c in linkData.CTSCall
                        from uwg in c.Workgroup.SMSUsersWorkgroupsWorkgroup
                        where uwg.UserID == UserID
                        select new
                            {
                                Number = c.Number,
                                LastBy = (c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().AuthorID == UserID ? "User" :
                                          c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().ContactID != null ? "Caller" :
                                          c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().AuthorID == c.OwnerID ? "Owner" : "Another")
                            };

            Console.WriteLine(items.Count());

I am just doing the Count() to force evaluation. Let me explain the code.

CTSCall has many CTSInput, each Call is associated with one SMSWorkgroup, and a User is associated with many Workgroups, through SMSUsersWorkgroups.

The code above is trying to figure out who last added an Input to the Call, the current User, the Caller, the Call Owner or Another user. For all Calls in the User's Workgroups.

The error I get is this

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

Additional information: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_5.call_id" could not be bound. The multi-part identifier "LPLA_5.call_id" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

It seems that the repeated line:

c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault()

in the code is causing confusion, each one in the SQL is given a new alias, but the compare in the SQL only ever uses the first alias given: LPLA_5, but the code has LPLA_6, LPLA_7

exec sp_executesql N'SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L3].[number] AS [Number], CASE WHEN CASE WHEN ( (SELECT TOP(@p6) [LPLA_5].[author_id] AS [AuthorID] FROM [ngsms].[dbo].[cts_inputs]  [LPLA_5]   WHERE ( ( ( ( [LPA_L3].[id] = [LPLA_5].[call_id])))) ORDER BY [LPLA_5].[created_date] DESC) = @p4) THEN 1 ELSE 0 END=1 THEN @p8 ELSE CASE WHEN CASE WHEN ( (SELECT TOP(@p10) [LPLA_6].[contact_id] AS [ContactID] FROM [ngsms].[dbo].[cts_inputs]  [LPLA_6]   WHERE ( ( ( ( [LPA_L3].[id] = [LPLA_5].[call_id])))) ORDER BY [LPLA_6].[created_date] DESC) IS NOT NULL) THEN 1 ELSE 0 END=1 THEN @p12 ELSE CASE WHEN CASE WHEN ( [LPA_L3].[owner_id] = (SELECT TOP(@p14) [LPLA_7].[author_id] AS [AuthorID] FROM [ngsms].[dbo].[cts_inputs]  [LPLA_7]   WHERE ( ( ( ( [LPA_L3].[id] = [LPLA_5].[call_id])))) ORDER BY [LPLA_7].[created_date] DESC)) THEN 1 ELSE 0 END=1 THEN @p16 ELSE @p18 END END END AS [LastBy] FROM (( [ngsms].[dbo].[sms_workgroups] [LPA_L2]  INNER JOIN [ngsms].[dbo].[cts_calls] [LPA_L3]  ON  [LPA_L2].[id]=[LPA_L3].[workgroup_id]) INNER JOIN [ngsms].[dbo].[sms_users_workgroups] [LPA_L4]  ON  [LPA_L2].[id]=[LPA_L4].[workgroup_id]) WHERE ( ( ( [LPA_L4].[user_id] = @p19)))) [LPA_L1]',N'@p2 bigint,@p6 bigint,@p4 uniqueidentifier,@p8 nvarchar(4),@p10 bigint,@p12 nvarchar(6),@p14 bigint,@p16 nvarchar(5),@p18 nvarchar(7),@p19 uniqueidentifier',@p2=1,@p6=1,@p4='25B5E96B-4F85-11D5-8DFD-00A0C9A7C874',@p8=N'User',@p10=1,@p12=N'Caller',@p14=1,@p16=N'Owner',@p18=N'Another',@p19='25B5E96B-4F85-11D5-8DFD-00A0C9A7C874'

If I simplify the query to remove the additional "from" for Users Workgroups, it works, so it appears to be a combination of things that breaks it.

If anyone has suggestions - either a better way to find the Latest of a set of related records (0..N) or how to avoid the situation, would be very grateful.

As I say, the query works fine in EF, although the SQL it generates is huge!

Regards Spike

Have been doing some additional digging, and found a work around, I am not really sure I like the workaround, as I still think there is a bug here.

            var items = from c in linkData.CTSCall
                       where c.Workgroup.SMSUsersWorkgroupsWorkgroup.Where(uw => uw.UserID == UserID).Any()
                        select new
                            {
                                Number = c.Number,
                                LastBy = (c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().AuthorID == UserID ? "User" :
                                          c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().ContactID != null ? "Caller" :
                                          c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().AuthorID == c.OwnerID ? "Owner" : "Another")
                            };

if we swap out the second **from and the where **with the above syntax, then the code works, we get the right result. but to me it is mixing syntaxes - which I do not really like, but does show that the LastBy LINQ syntax is right, but the SQL gen is at fault.

What's the file version of the SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll used? Right click on the file, and check the properties.

Hey Walaa,

SD.LLBLGen.Pro.ORMSupportClasses.NET20 Product Version: 3.1.11.0907 Date Modified: 9/30/2011 09:23

I downloaded the LLBLGen 6 days ago. Spike

The first query is complex because all the correlations and the CASE statements. Just to understand it better I reformatted the code and the generated sql:

var items = 
     from c in linkData.CTSCall
     from uwg in c.Workgroup.SMSUsersWorkgroupsWorkgroup
     where uwg.UserID == UserID
     select new
     {
          Number = c.Number,
          LastBy =  ( c.CTSInputsCall
                                .OrderByDescending(i => i.CreatedDate)
                                .FirstOrDefault().AuthorID == UserID 
                                     ? "User" 
                                     : c.CTSInputsCall
                                            .OrderByDescending(i => i.CreatedDate)
                                            .FirstOrDefault().ContactID != null 
                                                 ? "Caller" 
                                                 : c.CTSInputsCall
                                                      .OrderByDescending(i => i.CreatedDate)
                                                      .FirstOrDefault().AuthorID == c.OwnerID 
                                                           ? "Owner" 
                                                           : "Another"
                            )
     };

Console.WriteLine(items.Count());

Generated SQL

SELECT 
    TOP(@p2) COUNT(*) AS [LPAV_] 

FROM 
    (
        SELECT 
            [LPA_L3].[number] AS [Number], 
            CASE 
                WHEN 
                    CASE 
                        WHEN 
                        ( 
                            ( 
                                SELECT TOP(@p6) [LPLA_5].[author_id] AS [AuthorID] 
                                FROM [ngsms].[dbo].[cts_inputs] [LPLA_5] 
                                WHERE (((( [LPA_L3].[id] = [LPLA_5].[call_id] )))) 
                                ORDER BY [LPLA_5].[created_date] DESC
                            ) = @p4
                        ) 
                        THEN 1 
                        ELSE 0 
                    END=1 
                THEN @p8 
                ELSE 
                    CASE 
                        WHEN 
                            CASE 
                                WHEN 
                                ( 
                                    (
                                        SELECT TOP(@p10) [LPLA_6].[contact_id] AS [ContactID] 
                                        FROM [ngsms].[dbo].[cts_inputs] [LPLA_6] 
                                        WHERE (((( [LPA_L3].[id] = [LPLA_5].[call_id])))) 
                                        ORDER BY [LPLA_6].[created_date] DESC
                                    ) IS NOT NULL
                                ) 
                                THEN 1 
                                ELSE 0 
                            END=1 
                        THEN @p12 
                        ELSE 
                            CASE 
                                WHEN 
                                    CASE 
                                        WHEN 
                                        (   [LPA_L3].[owner_id] = 
                                                (
                                                    SELECT TOP(@p14) [LPLA_7].[author_id] AS [AuthorID] 
                                                    FROM [ngsms].[dbo].[cts_inputs] [LPLA_7] 
                                                    WHERE (((( [LPA_L3].[id] = [LPLA_5].[call_id])))) 
                                                    ORDER BY [LPLA_7].[created_date] DESC
                                                )
                                        ) 
                                        THEN 1 
                                        ELSE 0 
                                    END=1 
                                THEN @p16 
                                ELSE @p18 
                            END 
                    END 
            END AS [LastBy] 
        
        FROM 
        (
            ( 
                [ngsms].[dbo].[sms_workgroups] [LPA_L2] 
                INNER JOIN [ngsms].[dbo].[cts_calls] [LPA_L3] 
                    ON [LPA_L2].[id]=[LPA_L3].[workgroup_id]
            ) 
            INNER JOIN [ngsms].[dbo].[sms_users_workgroups] [LPA_L4] 
                ON [LPA_L2].[id]=[LPA_L4].[workgroup_id]
        ) 
        
        WHERE ((( [LPA_L4].[user_id] = @p19)))
    ) [LPA_L1]

@p2=1,
@p6=1,
@p4='25B5E96B-4F85-11D5-8DFD-00A0C9A7C874',
@p8=N'User',
@p10=1,
@p12=N'Caller',
@p14=1,
@p16=N'Owner',
@p18=N'Another',
@p19='25B5E96B-4F85-11D5-8DFD-00A0C9A7C874'

Yes, the problem seems the correlated filter at the subqueries. Some of this complex linq queries could lead to these aliases problems. For those cases we recommend to use our LLBLGen API or QuerySpec.

Anyway I will try to find any reason for this and will try to reproduce it.

I can'f find an easy way to reproduce this. Please attach a tiny repro case that includes a DB schema creation script and your .llblgenproj file.

That said, I see that maybe the query can be simplified to avoid a lot subquries. Could you please test this approach to see how it works?

var items = 
    from c in CTSCall
    join uwg in linkData.SMSUsersWorkgroupsWorkgroup on c.WorkgroupId equals uwg.id

    let authorId = (from i in c.CTSInputsCall orderby i.CreatedDate descending select i.AuthorId).FirstOrDefault()
    let contactId = (from i in c.CTSInputsCall orderby i.CreatedDate descending select i.ContactId).FirstOrDefault()

    where uwg.UserID == UserID

    select new
    {
        Number = c.Number,
        LastBy =  authorId == UserID 
                    ? "User" 
                    : (contactId != null 
                        ? "Caller"
                        : (authorID == c.OwnerID ? "Owner" : "Another")
                      )
    };

Indeed, using the same statement which produces an entity multiple times could lead to multiple aliases instead of the same aliased object (as this is ambiguous) and this is one of the few known issue for our linq provider, an issue which is unfortunately very hard to fix.

A let statement can help though. In your case it will also be much more efficient as your FirstOrDefault() query will run multiple times otherwise which isn't efficient at all (as it runs in the projection of the main query which will execute the FirstOrDefault query multiple times for each row of the main query)

Thanks for the multiple updates.

I have been doing more playing and trying to come up with a better way to do things.

Otis, I agree running the Query 3 times is not efficient, I tried doing a LET on the Last Added CTSInput, but that gave me other errors - it compiles fine, but something went very wrong.

var items = from c in linkData.CTSCall
                        from uwg in c.Workgroup.SMSUsersWorkgroupsWorkgroup
                        where uwg.UserID == UserID
                        let LastInput = c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault()
                        select new
                            {
                                Number = c.Number,
                                LastBy = (LastInput.AuthorID == UserID ? "U" :
                                          LastInput.ContactID != null ? "C" :
                                          LastInput.AuthorID == c.OwnerID ? "O" : "A"),
                            };

            var q = items.FirstOrDefault();

Now it give this error:

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

Additional information: An exception was caught during the execution of a retrieval query: Invalid column name 'contact_id'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

So, that is not a good solution. Also, the SQL it generated was huge! many many fields that were not needed!

Daelmo. That works, so that will be a useful replacement, it is not ideal, but I need to stick with LINQ, I have a generic ListService that takes a Class/Method name, this returns an IQueryable var that is processed and the results packaged as XML. So, anything that allows me to stick with LINQ is what I need to do.

Otis, are there plans to correct the multi-alias issue in the future? Or is it here for a while?

I am not really working today, but will try and come up with a small solution (DB and Project) that exhibit the issue. If that is still required, it seems you know about this issue.

Thanks Spike

First, sorry you ran into this error. I can assure you, it's a rare edge case, but nevertheless, you ran into it and therefore it's a problem.

Spike wrote:

Thanks for the multiple updates.

I have been doing more playing and trying to come up with a better way to do things.

Otis, I agree running the Query 3 times is not efficient, I tried doing a LET on the Last Added CTSInput, but that gave me other errors - it compiles fine, but something went very wrong.

var items = from c in linkData.CTSCall
                        from uwg in c.Workgroup.SMSUsersWorkgroupsWorkgroup
                        where uwg.UserID == UserID
                        let LastInput = c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault()
                        select new
                            {
                                Number = c.Number,
                                LastBy = (LastInput.AuthorID == UserID ? "U" :
                                          LastInput.ContactID != null ? "C" :
                                          LastInput.AuthorID == c.OwnerID ? "O" : "A"),
                            };

            var q = items.FirstOrDefault();

Now it give this error:

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

Additional information: An exception was caught during the execution of a retrieval query: Invalid column name 'contact_id'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

So, that is not a good solution. Also, the SQL it generated was huge! many many fields that were not needed!

That's because how Linq is processed. The expression tree is likely huge as well, and you have several projections nested inside each other: the 2 from clauses, which is a selectmany (which leads to a cross-join!), the let, which is another select and which leads to multiple times the data you assigned through let into the projection, the actual select at the end. Then you have several ?: statements, which each results in a CASE statement.

The linq engine doesn't optimize away fields which aren't needed, as the DB system does that already.

Looking at your query, it doesn't really make sense to me, but I don't know your domain. What you could try is:

var items = from c in linkData.CTSCall
            from uwg in c.Workgroup.SMSUsersWorkgroupsWorkgroup
            where uwg.UserID == UserID
            let LastInput = c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault()
            let authorID = LastInput.AuthorID
            let contactID = LastInput.ContactID
            select new
                {
                    Number = c.Number,
                    LastBy = (authorID == UserID ? "U" :
                              contactID != null ? "C" :
                              authorID == c.OwnerID ? "O" : "A"),
                };

            var q = items.FirstOrDefault();

but I haven't tested this.

In general our linq provider works OK, there are a few edge cases which don't, and in general you don't run into them. One is the problem with multiple times the same related entity / collection in the projection which could lead (but not always) to multiple aliases being assigned to it. Another is the way we do hierarchical queries (nested queries in projections). Our system is much more efficient than what EF does for example, but it has the downside that not all nested queries are usable in the projection. In general this isn't a problem as most of the time a nested query has a relationship with the outer query.

Otis, are there plans to correct the multi-alias issue in the future? Or is it here for a while? I am not really working today, but will try and come up with a small solution (DB and Project) that exhibit the issue. If that is still required, it seems you know about this issue.

It's very hard to fix. The main issue is that in some situations you do want different aliases and in other situations you don't. It requires sub tree interpretation and that's not that simple. We did solve most of the problems related to this however, so it's only in a few cases that this unfortunately fails. We did try to fix this but as said, the situations in which it failed were complex: you don't simply see a 'property', but multiple expressions in the tree which is a property access, and multiple levels deep could lead to a situation which states 'yes, this doesn't need a new alias'. We haven't come up with a solution for this yet. We'll try in the future, but when, that's not planned yet.

One of the main reasons is that Linq isn't 1:1 convertible to SQL, so transformations have to be applied. As there's not much documentation how to do these transformations (as everyone is most of the time doing 'the thing that works' but there's no reasoning behind it, as expression trees are so intertwined and complex) in some situations, things could break or don't work because some transformation isn't implemented. We covered most situations over the years by now so the only thing that's still somewhat of a problem in edge cases is when things aren't transformed as it should. This sounds easier than it is unfortunately, as it only breaks in rare edge cases with complex expression trees and multi-step transformations.

This is also the reason we wrote QuerySpec, a fluent query api as an alternative to Linq to fall back to if your query runs into one of those rare situations. See: http://weblogs.asp.net/fbouma/archive/2011/04/08/introducing-llblgen-pro-queryspec-a-new-fluent-query-api.aspx

I see you need IQueryable, so QuerySpec (which is in beta for now, will be in v3.5) isn't usable in your situation.

To help you further, could you elaborate a bit what your query is suppose to do? Perhaps we can give you another workaround?

Hey Otis,

To help you further, could you elaborate a bit what your query is suppose to do? Perhaps we can give you another workaround?

Right, let me explain where I am at, we have a custom built application today, currently written in ASP Classic, VB6 COM and SQL. We are migrating to .NET 4 technologies (MVC, jQuery, AJAX)

We have a very rich platform at the moment despite the old technology set being used, we have a number of lists, these present data to users, they are hierarchical in nature, as the data sets are often very big, so users can drill down to get to the data they are interested in. This all works very well with a Web Client.

To evaluate the technologies, we chose some of the more complex queries we do today in SQL that produce those lists, the example I have given above is derived from one of those queries we had an issue with, I just eliminated everything to get to the point where the error still occurred, but the statement was as simple as possible.

The final system will have ~700 lists, currently we have in the order of ~1000 lists, but we are making our List Service a lot richer, and hope to eliminate a number of lists we have today as we move to the new platform.

So, to what I have and what the query is supposed to give.

In terms of Entities, we have: * Calls and Inputs. A Call can have zero..many Inputs. * Users and Workgroups. A User has a Many-to-Many link to Workgroups. * A Call is associated with one Workgroup and Optionally one User (Owner) * An Input as One Author (User) and Optionally One Contact (External User)

The query I am building is tryng to figure out two things. 1. Find Calls in My Workgroups (in this context Me is UserID) 2. Who was the Last Person to add an Input to those Calls

So, if we break the code down, then

var items = from c in linkData.CTSCall
                        from uwg in c.Workgroup.SMSUsersWorkgroupsWorkgroup
                        where uwg.UserID == UserID

The above portion does the "Find Calls in My Workgroup", and

 select new
                            {
                                LastBy = (c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().AuthorID == UserID ? "U" :
                                          c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().ContactID != null ? "C" :
                                          c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault().AuthorID == c.OwnerID ? "O" : "A"),
                            };

The above portion does the "Who was the Last Person to add an Input to the Call."

Let me explain that one a little more. The first thing we do is

c.CTSInputsCall.OrderByDescending(i => i.CreatedDate).FirstOrDefault()

This gets us the Last Input added to the Call, we then check properties of that to see who added that last input. * AuthorID == UserID - then the current User added the Input * Contact != null - then the Input was added through the Extranet by a Contact * AuthorID == Call's OwnerID - then the Input was added by the Call Owner (note that the call may be owned by someone else, but is still in one of your Workgroups) * Finally - it must be Another user who added the Input

The issue only arises when we have more than one from clause, if I switch to using the expression in my second post, the issue goes away, so it only appears to be an issue there.

from c in linkData.CTSCall
                        where c.Workgroup.SMSUsersWorkgroupsWorkgroup.Where(uw => uw.UserID == UserID).Any()

I read the post about QuerySpec, as I have been writing SQL SP's for so long now, this does appeal to me - a lot, but, sadly, I am one person in a large team. They are all young and do not like SQL, they all want to stick to LINQ.

I guess I have my solution, to use the above syntax, as I already mentioned, this is from one of our more complex queries, from my perspective, the query is still much shorter than the SQL equivelent.

My model has 350 entities. * EF gives me a 4 minute start time * LLBLGen give me 5 seconds, that is a world of difference

The generated SQL from EF is 100's of lines long, from LLBLGen it is much shorter, and more like I would write, so for me LLBLGen is my first choice.

Thanks Spike

Spike wrote:

Daelmo. That works, so that will be a useful replacement, it is not ideal, but I need to stick with LINQ, I have a generic ListService that takes a Class/Method name, this returns an IQueryable var that is processed and the results packaged as XML. So, anything that allows me to stick with LINQ is what I need to do.

So, that worked, nice. See the generated code to see if that is acceptable. I think it should be simpler than the original version (without the second 'from').

Spike wrote:

I read the post about QuerySpec, as I have been writing SQL SP's for so long now, this does appeal to me - a lot, but, sadly, I am one person in a large team. They are all young and do not like SQL, they all want to stick to LINQ.

You don't have to strictly do all things in one query engine. I like Linq, but for very complex queries I prefer to use LLBLGen API and -his successor- QuerySpec as the generated Sql is more predictable.

Spike, thanks for the large explanation . I've scheduled some time next tuesday to look into your particular query and see whether we missed a spot somewhere (as I explained above) or that it indeed falls into the dreaded multi-alias issue. I'll also see if I can find another workaround query for you.

4 minutes startup time... there's a reason we generate some code while others don't. . I'll try to see if we can find a solution for you so you don't have to suffer those 4 minutes but can enjoy our quick startup time and short queries Stay tuned.

On AdventureWorks, I can reproduce it:

var q = from p in metaData.Product
        from bom in p.UnitMeasure.BillOfMaterialsCollection
        where bom.Bomlevel == 2
        select new
        {
            Size = p.Size,  // Number 
            Foo = (p.WorkOrderCollection.OrderByDescending(wo => wo.DueDate).FirstOrDefault().OrderQty == 1 ? "Big" :
                    p.WorkOrderCollection.OrderByDescending(wo => wo.DueDate).FirstOrDefault().OrderQty == 2 ? "Normal" :
                    p.WorkOrderCollection.OrderByDescending(wo => wo.DueDate).FirstOrDefault().OrderQty == 3 ? "Small" : "Irrelevant")
        };

var count = q.Count();

Console.WriteLine(count);

Where: Product == CTSCall UnitMeasure == Workgroup BillOfMaterials == SMSUsersWorkgroups WorkOrder == CTSInputs

Query doesn't make sense on the adventureworks domain, but it reproduces the issue . It can't be made smaller to reproduce it, which suggests it is indeed something in the edge-cases area, as removing a from clause or the where makes it work properly...

We'll look into the cause of this and if it's another thing than the aliasing of similar expression trees, we'll try to fix it.

The core of the problem is c.CTSInputsCall in c.CTSInputsCall.OrderByDescending. This is a navigation and implies a correlation relationship, which is converted into a where clause. The c.CTSInputsCall member access gets an alias the first time, and the second and third time this alias is simply re-used.

The reason is this:

var q = from o in metaData.Order select new { o.Customer.CompanyName, o.Customer.Country};

here, o.Customer implies the same entity, and therefore should get the same alias.

However in your case, the related entity is a query and gets a new alias. This is correctly done and is also reflected into the SQL query, the source, order by and projection of the subqueries all use the proper alias, the correlation relationship (the where clauses) in these subqueries however don't.

What's not done is a thing we dubbed alias overwriting, which replaces the original alias with one which is assigned to the element later on (due to further evaluation). In this case, the alias of the source of the subquery should be the one used in the correlation relationship, not the one that's cached.

We'll see if we can fix this and if it affects other queries. I hope to have a fix for you tomorrow (tuesday)

Otis,

You seem to have a good handle on the issue being seen, glad you can reproduce it - developers nightmare, not being able to reproduce issues!

Many thanks for looking in to this matter. Spike.

It turned out to be a bug about forgetting to overwrite aliases in correlated relationships at a specific point. It's fixed. See the attached dll for the updated linq provider.

Hopefully this fixes your more bigger query

Hey Otis,

Sorry for the delay.

OK, this is working well now for the query. So that was a very good fix. Thank you.

Regards Spike