I have a catch-22 with linq outer joins in runtime 2.6. Originally I got 'Extension node must override the property Expression.NodeType' with a new project. This was solved by using a runtime later than 7-7-2010. I actually used 25-12-2010.

But then production code was compromised. An expression (= 'Week', in the 4th line) was not evaluated any more. The problem:

            var queryResultWrong =
                from prs in db.WerknPeriodeTotalen
                join me in db.WerknWeekMemo
                    on new { prs.BedrijfNummer, prs.AdministratieNummer, Week = prs.PeriodeNummer + 1, prs.PersoonNummer }
                    equals new { me.BedrijfNummer, me.AdministratieNummer, me.Week, me.PersoonNummer }
                    into optMemo

                from om in optMemo.DefaultIfEmpty()
                select new WerknemerTotalenDto()
                {
                    BedrijfNummer = prs.BedrijfNummer,
                    AdministratieNummer = prs.AdministratieNummer,
                    PeriodeNummer = prs.PeriodeNummer,
                    PersoonNummer = prs.PersoonNummer
                };
            List<WerknemerTotalenDto> resultFout = queryResultWrong.ToList();

The message 'The multi-part identifier \"LPLA_1.LPFA_3\" could not be bound.' comes at runtime.

We have a workaround by splitting the query in 2 parts and do the expression in the first part, but we do not know if this construct is used in other places. The workaround:

            var queryResultOK1 =
                from prs in db.WerknPeriodeTotalen
                select new
                {
                    prs.BedrijfNummer,
                    prs.AdministratieNummer,
                    prs.Jaar,
                    prs.PeriodeNummer,
                    prs.PersoonNummer,
                    Week = prs.PeriodeNummer + 1,
                };
            var queryResultOK2 =
                from prs in queryResultOK1
                join me in db.WerknWeekMemo
                    on new { prs.BedrijfNummer, prs.AdministratieNummer, prs.Week, prs.PersoonNummer }
                    equals new { me.BedrijfNummer, me.AdministratieNummer, me.Week, me.PersoonNummer }
                    into optMemo

                from om in optMemo.DefaultIfEmpty()
                select new WerknemerTotalenDto()
                {
                    BedrijfNummer = prs.BedrijfNummer,
                    AdministratieNummer = prs.AdministratieNummer,
                    PeriodeNummer = prs.PeriodeNummer,
                    PersoonNummer = prs.PersoonNummer
                };
            List<WerknemerTotalenDto> resultOK = queryResultOK2.ToList();

Workaround works OK.

At this moment I work with 2 different runtimes (we do not dare to use the newer for production). A single set would be nicer.

Do you still get the same result if you use the very latest 2.6 runtime, from 15th April 2011 ?

Matt

Yes, in fact I filed this just after trying the april runtime. Our production is still using 2.6.09.1106 without the problem occurring. The problem is in 2.6.10.1119 (present in the two latest runtimes).

Would the following query (alone) work?

var queryResultWrong =
                    from prs in db.WerknPeriodeTotalen
                    join me in db.WerknWeekMemo
                        on new { prs.BedrijfNummer, prs.AdministratieNummer, Week = prs.PeriodeNummer + 1, prs.PersoonNummer }
                        equals new { me.BedrijfNummer, me.AdministratieNummer, me.Week, me.PersoonNummer }
                    select prs;

Do prs.PeriodeNummer & me.Week have the same dataType?

Works with the april 2011 runtime, I can consume the query.

Both have the same type (int).

So the issue is not with the expression in the Join clause, is it?

Could you please post the generated SQL to identify what it's complaining about.

here it is:

Query: SELECT [LPA_L1].[BEDRIJF] AS [BedrijfNummer], [LPA_L1].[ADM_NR] AS [AdministratieNummer], [LPA_L1].[DL_PERIODE] AS [PeriodeNummer], [LPA_L1].[PERS_NR] AS [PersoonNummer] FROM ( [BISNETF4_ERADATA].[dbo].[DLVAR] [LPA_L1]  LEFT JOIN [BISNETF4_ERADATA].[dbo].[WerknWeekMemo] [LPA_L2]  ON  ( [LPA_L1].[BEDRIJF] = [LPA_L2].[BedrijfNummer] AND [LPA_L1].[ADM_NR] = [LPA_L2].[AdministratieNummer] AND [LPLA_1].[LPFA_3] = [LPA_L2].[Week] AND [LPA_L1].[PERS_NR] = [LPA_L2].[PersoonNummer]))

And the sql from the 'old' working version (2.6.09.1106):

Query: SELECT [LPA_L1].[BEDRIJF] AS [BedrijfNummer], [LPA_L1].[ADM_NR] AS [AdministratieNummer], [LPA_L1].[DL_PERIODE] AS [PeriodeNummer], [LPA_L1].[PERS_NR] AS [PersoonNummer] FROM ( [BISNETF4_ERADATA].[dbo].[DLVAR] [LPA_L1]  LEFT JOIN [BISNETF4_ERADATA].[dbo].[WerknWeekMemo] [LPA_L2]  ON  ( [LPA_L1].[BEDRIJF] = [LPA_L2].[BedrijfNummer] AND [LPA_L1].[ADM_NR] = [LPA_L2].[AdministratieNummer] AND [LPA_L1].[DL_PERIODE] + @LLBLEP1 = [LPA_L2].[Week] AND [LPA_L1].[PERS_NR] = [LPA_L2].[PersoonNummer]))
Parameter: @LLBLEP1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

We had 25 or so fixes since November 2009 on the linq provider for v2.6, so it's unclear which one made your code fail.

What I also find confusing is what version exactly does work and which one started failing?

We think we have a repro case on northwind, but to be sure, I'd like to have a repro query from you which fails on v2.6 but works on the runtime you use in production. The repro query should be as simple as possible and preferably on a known database (adventureworks or northwind). This way we know the reprocase is equal to your situation.

The last linq fix for v2.6 was added on 19 november 2010, a VB.NET specific string compare fix, so that can't be the reason.

I do not know a exact version where the problem started. We use 2.6.09.1106 and upgraded to 2.6.10.1119. We did not use other runtime versions

The code I gave you is already a stripped down version from the production code. It is brought back to a simpel outer join which still has the problem. It is located in a testbed so reproducing the problem with different runtimes is easy.

At the moment I can not see older runtimes on your site. If you have a url to a download section with older runtimes I 'll be happy to test it, next week as Easter is calling.

Have a nice weekend.

pirbert wrote:

I do not know a exact version where the problem started. We use 2.6.09.1106 and upgraded to 2.6.10.1119. We did not use other runtime versions

The code I gave you is already a stripped down version from the production code. It is brought back to a simpel outer join which still has the problem. It is located in a testbed so reproducing the problem with different runtimes is easy.

At the moment I can not see older runtimes on your site. If you have a url to a download section with older runtimes I 'll be happy to test it, next week as Easter is calling.

Have a nice weekend.

I'll see if I can reproduce it with the code we have for northwind, to see whether I can dig up some odd-ness, track back the cause and then track back the change in that area in subversion to see why a change was made that way.

(which will be tuesday as well ) vrolijk pasen

Can't reproduce it:

LinqMetaData metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
        join o2 in metaData.Order
        on new {SC= o.ShipCountry, CID = o.CustomerId, SV = o.ShipVia +1, EID = o.EmployeeId } equals 
            new { SC = o2.ShipCountry, CID = o2.CustomerId, SV = o2.ShipVia, EID = o2.EmployeeId}
        select o;

gives:

SELECT DISTINCT 
        [LPA_L1].[OrderID] AS [OrderId], [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L1].[EmployeeID] AS [EmployeeId],
        [LPA_L1].[OrderDate], [LPA_L1].[RequiredDate], [LPA_L1].[ShippedDate], [LPA_L1].[ShipVia], [LPA_L1].[Freight], [LPA_L1].[ShipName],
        [LPA_L1].[ShipAddress], [LPA_L1].[ShipCity], [LPA_L1].[ShipRegion], [LPA_L1].[ShipPostalCode], [LPA_L1].[ShipCountry] 
FROM ( [Northwind].[dbo].[Orders] [LPA_L1]  INNER JOIN [Northwind].[dbo].[Orders] [LPA_L2]  
        ON  ( [LPA_L1].[ShipCountry] = [LPA_L2].[ShipCountry] AND 
            [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID] AND 
            [LPA_L1].[ShipVia] + @LLBLEP1 = [LPA_L2].[ShipVia] AND 
            [LPA_L1].[EmployeeID] = [LPA_L2].[EmployeeID]))

runtime lib version: 04152011. Linq support classes: 2.6.10.1119.

Will see if I can augment the query a bit to make it fail...

(edit) when I add an 'into oj' and 'from x in oj.DefaultIfEmpty()', the query indeed fails (alias error in the ON clause at the spot where the expression should be.)

Will try on 3.1 to see if this works there.

The problem seems to be related with the creation of a new object. The code Walaa sugested works OK. Our own tests also indicate that the problem occurs when there is a new object selected at the end of the query.

pirbert wrote:

The problem seems to be related with the creation of a new object. The code Walaa sugested worked OK. Our own tests also indicate that the problem occurs when there is a new object selected at the end of the query.

No it's with the DefaultIfEmpty clause. Why this happens is unclear. If I remove it from the query (like the one I posted) it works, when I add it, it fails like your query.

(edit) v3.1 fails too btw. So it's a generic failure, not present in some v2.6 library, it fails in both code bases.

I tried 3.1 as well (for the new project). But there is another issue in the generator, it seems that the order of the (PK) columns is different in the constructor of an entity. It used to be the order of appearance in the record but now it seems to be alphabetical. That is nasty when it is a relation table with two int columns (and no PK of its own).

pirbert wrote:

I tried 3.1 as well (for the new project). But there is another issue in the generator, it seems that the order of the (PK) columns is different in the constructor of an entity. It used to be the order of appearance in the record but now it seems to be alphabetical. That is nasty when it is a relation table with two int columns (and no PK of its own).

that's correctable through setting options to preserve the field ordering. See field ordering in the manual for details. By default there's no field ordering. You can set it in the project properties to keep the ordering as in the target table/view.

The issue for this thread is caused by a fix made on 9-aug-2010:

Linq: When a groupjoin with multiple joins was used multiple times in a query with a defaultifempty, it could cause invalid SQL due to alias mismatches.

Problem now is... how to make sure this fix keeps working and make your situation work too. The issue fixed by the fix above was about nested expressions. This is also the case in your situation, however in your situation it's not a new scope.

Fixed it. All is well. I'll backport the fix to v2.6 and 3.0. Just a sec. Will then attach the fixed dll for you to a new post.

Fixed in attached dll. I've attached a .zip with for v2.6, v3.0 and v3.1 the hotfix for this bug. Use the dll in the folder of the version you're using.

Thanks, the fix works (in 2.6 and 3.1).