Hello,

I wrote this query:

from customerService in metaData.CustomerService
join service in metaData.Service on customerService.ServiceId equals service.Id
let concreteService = service as ConcreteServiceEntity
let person = concreteService.Person as NaturalPersonEntity
select new {service.Id};

It produces an error with message "The column 'F1_2' was specified multiple times for 'LPA_L1'".

The generated SQL is:

SELECT [LPA_L4].[CustomerId], [LPA_L4].[Customer_ServiceId] AS [CustomerServiceId], [LPA_L4].[ServiceId], [LPA_L5].[ServiceId] AS [F4_0], [LPA_L6].[ConcreteServiceId] AS [F1_1], [LPA_L6].[PersonId] AS [F1_2]
FROM (( [DB].[dbo].[Customer_Service] [LPA_L4]
              INNER JOIN [DB].[dbo].[Service] [LPA_L5]  ON  [LPA_L4].[ServiceId] = [LPA_L5].[ServiceId])
              LEFT JOIN [DB].[dbo].[ConcreteService] [LPA_L6]  ON  [LPA_L5].[ServiceId]=[LPA_L6].[ConcreteServiceId])

SELECT [LPA_L3].[CustomerId], [LPA_L3].[CustomerServiceId], [LPA_L3].[ServiceId], [LPA_L3].[F4_0], [LPA_L3].[F1_1], [LPA_L3].[F1_2], @p2 AS [LPFA_15], [LPA_L3].[F1_2]
FROM (SELECT [LPA_L4].[CustomerId], [LPA_L4].[Customer_ServiceId] AS [CustomerServiceId], [LPA_L4].[ServiceId], [LPA_L5].[ServiceId] AS [F4_0], [LPA_L6].[ConcreteServiceId] AS [F1_1], [LPA_L6].[PersonId] AS [F1_2]
      FROM (( [DB].[dbo].[Customer_Service] [LPA_L4]
              INNER JOIN [DB].[dbo].[Service] [LPA_L5]  ON  [LPA_L4].[ServiceId] = [LPA_L5].[ServiceId])
              LEFT JOIN [DB].[dbo].[ConcreteService] [LPA_L6]  ON  [LPA_L5].[ServiceId]=[LPA_L6].[ConcreteServiceId])) [LPA_L3]

SELECT [LPA_L1].[F4_0]
FROM (SELECT [LPA_L3].[CustomerId], [LPA_L3].[CustomerServiceId], [LPA_L3].[ServiceId], [LPA_L3].[F4_0], [LPA_L3].[F1_1], [LPA_L3].[F1_2], @p2 AS [LPFA_15], [LPA_L3].[F1_2]
      FROM (SELECT [LPA_L4].[CustomerId], [LPA_L4].[Customer_ServiceId] AS [CustomerServiceId], [LPA_L4].[ServiceId], [LPA_L5].[ServiceId] AS [F4_0], [LPA_L6].[ConcreteServiceId] AS [F1_1], [LPA_L6].[PersonId] AS [F1_2]
            FROM (( [DB].[dbo].[Customer_Service] [LPA_L4]  
                    INNER JOIN [DB].[dbo].[Service] [LPA_L5]  ON  [LPA_L4].[ServiceId] = [LPA_L5].[ServiceId])
                    LEFT JOIN [DB].[dbo].[ConcreteService] [LPA_L6]  ON  [LPA_L5].[ServiceId]=[LPA_L6].[ConcreteServiceId])) [LPA_L3]) [LPA_L1]

Am I doing something wrong or is it a bug? I've attached a solution with DB schema and an llblgenproj file in case you need it.

I'm not sure I can see what the let statments are doing...?

can you not just do

from customerService in metaData.CustomerService
join service in metaData.Service on customerService.ServiceId equals service.Id
select new {service.Id};

?

I'm not sure I can see what the let statments are doing...?

Well, I wanted to simple down the query. Originally the variables created with the let statements were used in the projection, but I removed this code for brevity. The original query produced the same error.

"as" keyword is not supported in projections. Anyway in this particular case your "let...as..." statements are doing nothing. Please post the original query you want to execute and we can help you in a proper workaround.

"as" keyword is not supported in projections.

Do you mean that I cannot use these variables created with let in projections?

Please post the original query you want to execute and we can help you in a proper workaround.

from customerService in metaData.CustomerService
join service in metaData.Service on customerService.ServiceId equals service.Id
let concreteService = service as ConcreteServiceEntity
let person = concreteService.Person as NaturalPersonEntity
select new
           {
               ServiceId = service.Id, 
               FirstName = person.FirstName,
               LastName = person.LastName
           };

Which Linq Support Classes lib version are you using?

Walaa wrote:

Which Linq Support Classes lib version are you using?

3.0.10.927

Deividas wrote:

"as" keyword is not supported in projections.

Do you mean that I cannot use these variables created with let in projections?

Please post the original query you want to execute and we can help you in a proper workaround.

from customerService in metaData.CustomerService
join service in metaData.Service on customerService.ServiceId equals service.Id
let concreteService = service as ConcreteServiceEntity
let person = concreteService.Person as NaturalPersonEntity
select new
           {
               ServiceId = service.Id, 
               FirstName = person.FirstName,
               LastName = person.LastName
           };

This doesn't really work. The problem is that the 'as' operator isn't working in the DB as it is in-memory. You use it in the query as if the query runs in memory, doing a type cast. But that's not the case. A 'let' statement is really just a select, no type casting is done.

The problem also surfaces when you think about what happens if concreteService.Person isn't a NaturalPersonEntity. 'person' then should be null and the query should crash as you don't test for null. However testing for null is not really doable, as there's no typecast.

So I recon you want all services which are ConcreteServiceEntity instances and are assigned to customers and which have a related Person which is a NaturalPersonEntity

You should look into using OfType<T>() and .Cast<T>() to filter the sequences you're joining.

A 'let' statement is really just a select, no type casting is done.

from customerService in metaData.CustomerService
join service in metaData.Service on customerService.ServiceId equals service.Id
let concreteService = service as ConcreteServiceEntity
select new
         {
             ServiceId = service.Id,
             CreationDate = concreteService.CreationDate
         };

If no type casting is done, then why does this query work? Here CreationDate is a column in the ConcreteService table. Is it an unexpected behavior?

The problem also surfaces when you think about what happens if concreteService.Person isn't a NaturalPersonEntity. 'person' then should be null and the query should crash as you don't test for null. However testing for null is not really doable, as there's no typecast.

Well, I assumed from the query above that there was a typecast. And testing for null is indeed needed in this case.

You should look into using OfType<T>() and .Cast<T>() to filter the sequences you're joining.

How can I do this with joins? I assume, that OfType and Cast only work in the 'from' clause, because the following query doesn't work.

from customerService in metaData.CustomerService
join concreteService in metaData.Service.Cast<ConcreteServiceEntity>() on customerService.ServiceId equals concreteService.Id
select new
           {
               ServiceId = service.Id, 
               CreationDate = concreteService.CreationDate
           };

Deividas wrote:

A 'let' statement is really just a select, no type casting is done.

from customerService in metaData.CustomerService
join service in metaData.Service on customerService.ServiceId equals service.Id
let concreteService = service as ConcreteServiceEntity
select new
         {
             ServiceId = service.Id,
             CreationDate = concreteService.CreationDate
         };

If no type casting is done, then why does this query work? Here CreationDate is a column in the ConcreteService table. Is it an unexpected behavior?

'let' is actually a select. When you place a 'let' in the query, you simply get a select with all elements you had before that in the projection. Normally this works OK, but with inheritance there's a problem: it projects all fields as Fx_y fields. This is necessary because it's otherwise not possible to have a complete hierarchy of fields in the projection from multiple branches in a hierarchy. This works OK as well, except when you perform two let statements in a query. As a let is a select, you get two inheritance projections in the projection, so twice the Fx_y aliases, which fails. It's impossible to use other aliases in this situation, as there's no index in the query which states 'these aliases are for hierarchy / type A, the others are for hierarchy / type B'.

It's also not a 'cast', it's a select, so there's no type change happening. The query works, but in fact it's doing different things than you ordered but the result is the same. With two let + as statements you run into problems and things fail. This can't be fixed as I described above.

'let' statements should be avoided in queries, and in your query as well, so the query has to be rewritten to achieve what you want.

You should look into using OfType<T>() and .Cast<T>() to filter the sequences you're joining.

How can I do this with joins? I assume, that OfType and Cast only work in the 'from' clause, because the following query doesn't work.

from customerService in metaData.CustomerService
join concreteService in metaData.Service.Cast<ConcreteServiceEntity>() on customerService.ServiceId equals concreteService.Id
select new
           {
               ServiceId = service.Id, 
               CreationDate = concreteService.CreationDate
           };

the idea indeed is to use a type changing filter to filter on the type you want and therefore making the query simpler and that it works (as you work with known types). I'll see if I can reproduce the problem you ran into. 'doesn't work' is unclear, so I have to see if I can reproduce 'a' problem or can create a join with a similar query as you want to write.

where's 'service' coming from in the projection btw... ?

'doesn't work' is unclear, so I have to see if I can reproduce 'a' problem or can create a join with a similar query as you want to write.

I've rewritten the query to use the same DB schema attached to my first post:

from customerService in metaData.CustomerService
join concreteService in metaData.Service.Cast<ConcreteServiceEntity>() on customerService.ServiceId equals concreteService.Id
select new
           {
               ServiceId = concreteService.Id, 
               PersonId = concreteService.PersonId
           }

It produces the following error:

"The multi-part identifier "LPLA_2.ConcreteServiceId" could not be bound. The multi-part identifier "LPLA_2.ConcreteServiceId" could not be bound. The multi-part identifier "LPLA_2.ConcreteServiceId" could not be bound. The multi-part identifier "LPLA_2.PersonId" could not be bound."

The generated sql query is:

SELECT [LPLA_2].[ConcreteServiceId] AS [ServiceId], [LPLA_2].[PersonId]
FROM ( [DB].[dbo].[Customer_Service] [LPA_L1]
       INNER JOIN [DB].[dbo].[Service] [LPA_L2]  ON  [LPA_L1].[ServiceId] = [LPLA_2].[ConcreteServiceId])
WHERE ( ( ( ( ( [LPLA_2].[ConcreteServiceId] IS NOT NULL)))))

When I use:

var q = from d in metaData.Department
    join b in metaData.Employee.Cast<BoardMemberEntity>() on d.DepartmentId equals b.ManagesDepartmentId
    select new { d.DepartmentId, b.CompanyCarId };

I get an error indeed:

SELECT [LPA_L1].[DepartmentID] AS [DepartmentId], [LPA_L4].[CompanyCarID] AS [CompanyCarId] 
FROM ((( [InheritanceTwo].[dbo].[Department] [LPA_L1]  
    INNER JOIN [InheritanceTwo].[dbo].[Employee] [LPA_L2]  ON  [LPA_L1].[DepartmentID] = [LPA_L3].[ManagesDepartmentID]) --<<<<<<<<<<<<<<<<<< wrong
    LEFT JOIN [InheritanceTwo].[dbo].[Manager] [LPA_L3]  ON  [LPA_L2].[EmployeeID]=[LPA_L3].[ManagerID]) 
    LEFT JOIN [InheritanceTwo].[dbo].[BoardMember] [LPA_L4]  ON  [LPA_L3].[ManagerID]=[LPA_L4].[BoardMemberID]) 
WHERE ( ( ( ( ( [LPA_L4].[BoardMemberID] IS NOT NULL)))))

This is a bug: it doesn't re-order the joins properly (it can't join employee to department, as the relationship used here is on 'manager' which is a subtype). At the moment, it places all relationships from root to subtype at the position it sees an inheritance entity. In our own query system, this always works, with linq, it doesn't. It always works in our own system as you automatically define the relationship as Department - Manager, and this places the relationships properly in the right order.

Equivalent:

var toFetch = new EntityCollection<BoardMemberEntity>();
var filter = new RelationPredicateBucket();
filter.Relations.Add(DepartmentEntity.Relations.ManagerEntityUsingManagesDepartmentId);
adapter.FetchEntityCollection(toFetch, filter);

This one works properly.

The question now is of course: why is it wrong in the linq case and not in our own query system. Partly it's because in linq, it's using dynamic relationships, not the generated ones: it produces a dynamic relationship but it has less info available and this breaks in the end in your particular scenario.

To switch to our own relationships under the hood, use a workaround in linq:

var q = from d in metaData.Department
    from b in d.Managers.Cast<BoardMemberEntity>()
    select new { d.DepartmentId, b.CompanyCarId };

here I use a mechanism in the linq provider which uses the d.Managers 'navigation' (== relationship!) instead of the implied 'from - from' clause which would suggest a cross join.

Back to your query, this is slightly not what you can use, as your query is a m:1, but you can rewrite the query to make it work:

from service in metaData.ConcreteService
join customerService in metaData.CustomerService on service.Id equals customerService.ServiceId
select new { ServiceId = service.Id, CreationDate = service.CreationDate};

Here, the from clause already specifies a subtype which you're using, so let isn't needed. This could have been written as:

from service in metaData.ConcreteService
where service.CustomerServices.Any()
select new { ServiceId = service.Id, CreationDate = service.CreationDate};

which tests whether the service selected has any related rows in the CustomerServices table. (so service.CustomerServices is the navigator on the CustomerService m:1 Service relationship)

the trick to keep in mind is to already join with the subtype using metadata.Subtype instead (as that already does the cast / filtering for you).

Your original query then should be something like:

from customerService in metaData.CustomerService
join concreteService in metaData.ConcreteService on customerService.ServiceId equals service.Id
join person in metaData.NaturalPersonEntity on concreteService.PersonId equals person.Id
select new
         {
             ServiceId = service.Id,
             FirstName = person.FirstName,
             LastName = person.LastName
         };

But it's guess work without your actual model, inheritance types etc. so I'm not sure if this last query will work after all. The general idea is to use subtypes at the join, and the TPE inheritance entities first, if the relationship is inherited.

I do understand it's a problem for this particular situation, and it's due to how linq constructs the joins that this pops up.

If you need further assistance with this problem, just post in this thread again, preferably with more info about the types etc.

About the ordering of relationships problem, this is an issue which is complicated (read: time consuming) and scheduled to be fixed at a later point, likely in 3.1, as there are workarounds which work.

The problem occurs because a relationship is constructed on Subtype with a related entity over a relationship which is inherited and this goes wrong, at least in the situation where we could reproduce it.

edit we added a workitem for 3.1 to add the relationship ordering for linq queries, as it's currently failing in the constructs you provided.

Great. Thanks a lot. This really helped.

We wrote the fix in the v3.0 runtime code as it didn't need architectural changes, so the fix is available in the next build (buildnr 11302010 or higher).

Otis wrote:

We wrote the fix in the v3.0 runtime code as it didn't need architectural changes, so the fix is available in the next build (buildnr 11302010 or higher).

Could you clarify this, what did you fix (we talked about several things here)?

Deividas wrote:

Otis wrote:

We wrote the fix in the v3.0 runtime code as it didn't need architectural changes, so the fix is available in the next build (buildnr 11302010 or higher).

Could you clarify this, what did you fix (we talked about several things here)?

this query:

from customerService in metaData.CustomerService
join concreteService in metaData.Service.Cast<ConcreteServiceEntity>() on customerService.ServiceId equals concreteService.Id
select new
         {
             ServiceId = concreteService.Id, 
             PersonId = concreteService.PersonId
         }

now works (your message above: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=106328&ThreadID=18921 ) I've attached the dll. (see below)

In short, the dynamic relation created by the linq provider between A and N using an ON clause which uses field of N which are inherited from a supertype which is actually related to A is now properly broken up into multiple relationships, which previously simply failed with a crappy join and broken sql.

Hmm... the query we had in a test failed with the same error, but when I use the query above with the cast it still fails...

Not fixed.... retracting to base and checking it out again.

Edit: seems that when you specify a subtype in the join, it works, specifying a supertype fails (through the cast, the supertype ends up in the relationship).

Code is almost complete, needs a bit of tweaking. We'll get to that tomorrow (wednesday). Sorry for giving you false hope in this case, you've to wait a day.

(it's an annoyance really, the query itself is actually odd: from customerService in metaData.CustomerService join concreteService in metaData.Service.Cast<ConcreteServiceEntity>() on customerService.ServiceId equals concreteService.Id select new { ServiceId = concreteService.Id, PersonId = concreteService.PersonId }

(fails)

is equal to:

from customerService in metaData.CustomerService join concreteService in metaData.ConcreteService on customerService.ServiceId equals concreteService.Id select new { ServiceId = concreteService.Id, PersonId = concreteService.PersonId }

which now works. Before both failed. We had a test with subtype joined over supertype's fields in a test and checked that, we didn't have a test with a supertype joined over subtype's fields (using the cast).

Fixed. See attached dll.