Hi,

I have quite a complicated Linq query that works in LinqPad, but breaks in LlblGen. I have isolated the pieces that are breaking and would like to know if these issues will be fixed after reading another post that indicated that the development team is aware of issues with joins.

The basic query that breaks is the following:

from l in metaData.Lead
join sdg1 in metaData.SmsSendData
    on new { TableId = (int)Table.Lead, Key = (int?)l.LeadId, SmsTypeId = (int)SmsType.CashActivationRequest } 
    equals new { sdg1.TableId, sdg1.Key, sdg1.SmsSend.SmsTypeId } 
    into sdg2
from sd in sdg2.DefaultIfEmpty()
select new { l.LeadId, sd.SmsSendId };

LlblGen gives the following error:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_3.SmsTypeId" could not be bound..

There seems to be a problem only when I try to access a property from a related object on the join i.e. sdg1.SmsSend.SmsTypeId.

I have rewritten the query to make it work using a subquery as follows:

from l in metaData.Lead
join sdSub in
(
    from d in metaData.SmsSendData
    where d.TableId == (int)Table.Lead
    && d.SmsSend.SmsTypeId == (int)SmsType.CashActivationRequest
    select d
) on l.LeadId equals sdSub.Key into sdg
from sd in sdg.DefaultIfEmpty()
select new { l.LeadId, sd.SmsSendId };

If I add another part of the big query, I get a different error message. Heres the query:

from l in metaData.Lead
join ag1 in metaData.Application on l.LeadId equals ag1.LeadId into ag2
from a in ag2.DefaultIfEmpty()
join sdSub in
(
    from d in metaData.SmsSendData
    where d.TableId == (int)Table.Lead
    && d.SmsSend.SmsTypeId == (int)SmsType.CashActivationRequest
    select d
) on l.LeadId equals sdSub.Key into sdg
from sd in sdg.DefaultIfEmpty()
select new { l.LeadId, sd.SmsSendId };

And the error message:

SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException : Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Regards, Johann

please provide more info: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12769 and http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7717

Hi Frans,

We are using the latest released version of the LlblGen 3 (15 November) with DotNet Framework 4 and the adapter model.

Johann

Hmm... We'll look into it.

I have a problem with this part:

from l in metaData.Lead
join sdg1 in metaData.SmsSendData
    on new { TableId = (int)Table.Lead, Key = (int?)l.LeadId, SmsTypeId = (int)SmsType.CashActivationRequest } 
    equals new { sdg1.TableId, sdg1.Key, sdg1.SmsSend.SmsTypeId } 
    into sdg2

In the ON clause, you use: sdg1.SmsSend.SmsTypeId. This will cause another join to be added, namely SmsSendData JOIN SmsSend.

However, the position of that join isn't clear: Your statement above look like: Lead L JOIN SmsSendData sdg1 ON (v1 = sdg1.TableID AND L.LeadId = sdg1.Key AND v2 = [problem]....

and then a problem arises. It should compare the value specified with SmsSend.SmsTypeId, however SmsSend isn't in the Join list, so it has to be already in the join list, however that's not possible as it joins with SmsSendData which is joined after it. -> catch 22. So this situation always requires a special piece of code in the linq provider to handle this. It's a very rare case (joining over a clause with a reference to a related table, that is very rare), you are the first who runs into this in 2.5 years our linq provider is now available

There are two ways to do this, I think:

1) first join SmsSendData with SmsSend, and then join Lead to it, there's no logic of that kind in our framework (there is actually, but not to the extend to completely rewrite joins in this way, it can revert joins if they're specified in the wrong order, but not to this extend, because it also changes the nature of the end result). I don't know if Linq to Sql does this (you say you use linqpad, so I presume you tried it out on linq to sql). I personally expect it doesn't and goes for the second option. or 2) instead of joining Lead with SmsSendData, it joins with a subquery: Lead L INNER JOIN (SELECT ssd.*, ss.SmsTypeId FROM SmsSendData ssd INNER JOIN SmsSend ss ON ssd.SmsSendId = ss.Id) sdg1 ON (v1 = sdg1.TableId, L.Lead = sdg1.Key, v2=sdg1.SmsStypeId)

Also this isn't supported by our linq provider as it requires code to handle this particular case (which we didn't anticipate for), and we didn't implement that. It's not possible to handle this with generic logic which also handles other cases, as the situation isn't doable normally, as I described above: a special piece of code is required to transform this particular case into working SQL. It always saddens me when a special case like this pops up (the few queries we have in our tests which fail are all special cases btw, i.e. require special code only for that kind of query)

You rewrote the query into another one, which also failed, which is more severe, so we'll look into that one now.

The problem is the navigation in the subquery:

from l in metaData.Lead
join ag1 in metaData.Application on l.LeadId equals ag1.LeadId into ag2
from a in ag2.DefaultIfEmpty()
join sdSub in
(
    from d in metaData.SmsSendData
    where d.TableId == (int)Table.Lead
    && d.SmsSend.SmsTypeId == (int)SmsType.CashActivationRequest // <<<<<<<<<<
    select d
) on l.LeadId equals sdSub.Key into sdg
from sd in sdg.DefaultIfEmpty()
select new { l.LeadId, sd.SmsSendId };

this repro query works:

var q = from c in metaData.Contact
        join sc in metaData.StoreContact on c.ContactId equals sc.ContactId into csc
        from cscv in csc.DefaultIfEmpty()
        join vcSub in
            (
                from vc in metaData.VendorContact
                where vc.ContactTypeId == 10
                select vc
                ) on c.ContactId equals vcSub.ContactId into cvcSub
        from cvcSubv in cvcSub.DefaultIfEmpty()
        select new { c.ContactId, cvcSubv.VendorId };

however this one fails:

var q = from c in metaData.Contact
        join sc in metaData.StoreContact on c.ContactId equals sc.ContactId into csc
        from cscv in csc.DefaultIfEmpty()
        join vcSub in
            (
                from vc in metaData.VendorContact
                where vc.ContactType.ContactTypeId == 10
                select vc
                ) on c.ContactId equals vcSub.ContactId into cvcSub
        from cvcSubv in cvcSub.DefaultIfEmpty()
        select new { c.ContactId, cvcSubv.VendorId };

in the subquery a navigation from vc to ContactType is done.

This simply leads to the same problem as before, as the subquery is actually not existent for the linq provider: it's simply: metaData.VendorContract.Where(vc=>vc.ContactType.ContactTypeId==10) which is merged with the ON clause. As we already concluded the navigation in the ON clause causes a catch22 which requires a specific transformation to be solved which isn't implemented, this isn't a suitable workaround. there's another though:

To work around this (i.e. to make it a real subquery) add a projection:

var q = from c in metaData.Contact
        join sc in metaData.StoreContact on c.ContactId equals sc.ContactId into csc
        from cscv in csc.DefaultIfEmpty()
        join vcSub in
            (
                from vc in metaData.VendorContact
                where vc.ContactType.ContactTypeId == 10
                select new { vc.VendorId, vc.ContactId }
                ) on c.ContactId equals vcSub.ContactId into cvcSub
        from cvcSubv in cvcSub.DefaultIfEmpty()
        select new { c.ContactId, cvcSubv.VendorId };

So in your case:

from l in metaData.Lead
join ag1 in metaData.Application on l.LeadId equals ag1.LeadId into ag2
from a in ag2.DefaultIfEmpty()
join sdSub in
(
    from d in metaData.SmsSendData
    where d.TableId == (int)Table.Lead
    && d.SmsSend.SmsTypeId == (int)SmsType.CashActivationRequest
    select new { d.Key, d.SmsSendId }
) on l.LeadId equals sdSub.Key into sdg
from sd in sdg.DefaultIfEmpty()
select new { l.LeadId, sd.SmsSendId };

Hi Frans,

Thank you for the reply. Here is some more information.

Here is a SQL script to create a minimalistic database to reproduce the error.

CREATE TABLE [dbo].[SmsSend] (
    [SmsSendId] [int] IDENTITY(1,1) NOT NULL,
    [SmsTypeId] [int] NOT NULL,
CONSTRAINT [PK_SmsSend] PRIMARY KEY CLUSTERED 
(
    [SmsSendId] ASC
))
GO

CREATE TABLE [dbo].[SmsSendData](
    [SmsSendDataId] [int] IDENTITY(1,1) NOT NULL,
    [SmsSendId] [int] NOT NULL,
    [TableId] [int] NOT NULL,
    [Key] [int] NULL,
    [Value] [varchar](100) NULL,
CONSTRAINT [PK_SmsSendData] PRIMARY KEY CLUSTERED 
(
    [SmsSendDataId] ASC
))
GO

ALTER TABLE [dbo].[SmsSendData]  WITH CHECK ADD  CONSTRAINT [FK_SmsSendData_SmsSend] FOREIGN KEY([SmsSendId])
REFERENCES [dbo].[SmsSend] ([SmsSendId])
GO

ALTER TABLE [dbo].[SmsSendData] CHECK CONSTRAINT [FK_SmsSendData_SmsSend]
GO

CREATE TABLE [dbo].[Lead](
    [LeadId] [int] IDENTITY(1,1) NOT NULL,
    [Comments] [nvarchar](max) NULL
 CONSTRAINT [PK_Lead] PRIMARY KEY CLUSTERED 
(
    [LeadId] ASC
))
GO


INSERT INTO Lead (Comments) VALUES ('abc')
INSERT INTO SmsSend (SmsTypeId) VALUES (1)
INSERT INTO SmsSendData (SmsSendId, TableId, [Key]) VALUES (1, 1, 1)

Here is an updated query to match the data:

from l in Leads
join sdg1 in SmsSendData
    on new { TableId = 1, Key = (int?)l.LeadId, SmsTypeId = 1 } equals new { sdg1.TableId, sdg1.Key, sdg1.SmsSend.SmsTypeId } 
    into sdg2
from sd in sdg2.DefaultIfEmpty()
select new { l.LeadId, sd.SmsSendId }

Here is the working query that is generated by Linq to Sql:

exec sp_executesql N'
SELECT [t0].[LeadId], [t1].[SmsSendId] AS [SmsSendId]
FROM [Lead] AS [t0]
LEFT OUTER JOIN ([SmsSendData] AS [t1]
INNER JOIN [SmsSend] AS [t2] ON [t2].[SmsSendId] = [t1].[SmsSendId]) 
    ON (@p0 = [t1].[TableId]) 
    AND (([t0].[LeadId]) = [t1].[Key]) 
    AND (@p1 = [t2].[SmsTypeId])',
N'@p0 int,@p1 int',
@p0=1,@p1=1

Regards, Johann

Where's the first ON clause in that query? No wonder it produced a query, if the db supports that (I didn't know it was allowed on sqlserver btw). So it didn't have a transformation either, the query produced was accepted by sqlserver. Our framework always produces ON clauses, as that's the ansi standard, however it seems sqlserver can do without the ON clause... due to the mandatory ON clause in our framework, we can't deal with the query. E.g. what does 'left join' mean if there's no clause over which two sets are joined together? Mind you, the clause which is specified is on the 3rd table, which is irrelevant for the first join as the first join is a left join.

Btw, I have the feeling you didn't properly read my replies as you posted table DDL SQL, e.g. how to work around it.

Hi Frans,

I didn't see your previous post before posting my last message. I was using an old browser window, so sorry about that.

In terms of the Linq to Sql generated query, there are two ON clauses for the two joins. They are using brackets and specifying the ON clause for the first join after the ON clause for the second join.

Johann

I see the on clause for the first join is after the second join, I missed that. This kind of construct isn't supported: the ON clause always has to follow the join statement. It's also weird to specify an ON clause on a related value, as that implies actually another join + another on clause.

now that I look more closer to the query and your tables, I see you have formulated the ON clause which should have been a where clause, which causes all the problems.

When I rewrite my test to:

var q = from c in metaData.Contact
        join sc in metaData.StoreContact on c.ContactId equals sc.ContactId into csc
        from cscv in csc.DefaultIfEmpty()
        join vc in metaData.VendorContact on cscv.ContactId equals vc.ContactId into cscvvc
        from cscvvcv in cscvvc.DefaultIfEmpty()
        where cscvvcv.ContactType.ContactTypeId==10
        select new { c.ContactId, cscvvcv.VendorId};

it works properly. For your query this means:

from l in metaData.Lead
join sdg1 in metaData.SmsSendData
on l.LeadId, SmsTypeId = sdg1.Key into sdg2
from sd in sdg2.DefaultIfEmpty()
where sd.TableId == (int)TableId && sd.SmsSend.SmsTypeId == (int)SmsType.CashActivationRequest
select new { l.LeadId, sd.SmsSendId };