I have an exception for executing linq query, Exception occurs when write and execute the linq statement as one block. If that statement wrote in more than one block it throw this exception.

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

using LLBL version 2.6 linq file version 2.6.10.517 Linq to sql

A sample project was sent to support At llblgen DOT com

Hi Mohamed,

It indeed throws an exception. I have to try to reproduce it with a small case. The subquery its ok but when you join it in a outer query it fails in the alias. In the meantime this is workaround: Change your first query by this:

var rateQ = from a01 in metaData.CurrencyRates
          select a01;

Hi Daelmo,

Thanks for your replay.

I have the same case in another queries, and some tables contains more than 100 columns (it is legacy database ) so it's perfect if you fix this issue.

what do you think...

As the query is rather complex to debug (a lot of joins, means a massive expression tree... ) it's tedious to get to the core of the issue.

Looking at the result query which fails:

SELECT [LPA_L2].[PortfolioNo], [LPA_L2].[SecurityEName], [LPA_L2].[AmountCcyBalance] FROM ( ( SELECT COALESCE([LPA_L4].[PFNo], @LO01) AS [PortfolioNo], COALESCE([LPA_L4].[SecCode], @LO02) AS [SecurityEName], [LPA_L7].[Rate], COALESCE([LPA_L4].[AmountBalance], @LO03) AS [AmountCcyBalance] FROM ((( [LLBLTest].[dbo].[Transactions] [LPA_L4]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L5] ON [LPA_L4].[PFNo] = [LPA_L5].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L6] ON [LPA_L4].[SecCode] = [LPA_L6].[Code]) INNER JOIN ( SELECT [LPLA_6].[CurrencyCode], [LPLA_6].[CurrencyCodeTo], [LPLA_6].[Rate] FROM [LLBLTest].[dbo].[CurrencyRates] [LPLA_6] ) [LPA_L7] ON [LPA_L6].[Currency] = [LPA_L7].[CurrencyCode]) ) [LPA_L1] LEFT JOIN ( SELECT COALESCE([LPA_L8].[PFNo], @LO04) AS [PortfolioNo], COALESCE([LPA_L8].[SecCode], @LO05) AS [SecurityEName], COALESCE([LPA_L8].[AmountBalance], @LO06) AS [AmountCcyBalance] FROM (( [LLBLTest].[dbo].[Transactions] [LPA_L8]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L9] ON [LPA_L8].[PFNo] = [LPA_L9].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L10] ON [LPA_L8].[SecCode] = [LPA_L10].[Code]) ) [LPA_L2] ON ( [LPLA_10].[PortfolioNo] = [LPA_L2].[PortfolioNo] AND [LPLA_10].[SecurityEName] = [LPA_L2].[SecurityEName] ))

The [LLBLTest].[dbo].[SecurityData] table is aliased twice. In v2.6 there's a problem with members representing the same source multiple times. It sometimes goes wrong as it only assigns a single alias to such a source. In this case, it assigns LPA_L6 to it. The next time it runs into it, it's INSIDE a scope as you can see (inside a subquery). It tests whether the alias assigned to it is inside a scope or not (i.e. out of reach). It's not as LPA_L6 isn't inside the scope it deals with currently, so it allows it, while it should have picked the LPA_L1 alias instead. This also goes wrong for the other one, metaData.PortfolioData. It goes wrong in the last join, between the two queries.

We did some work on this for v3 as it was an extensive change. I'll see if this same query works on v3.

(edit) in v3 it also fails. Hmm.... Looking into it.

(edit) changing: join c in rateQ on s.Currency.Value equals c.CurrencyCode.Value

in the query pfPositionFromDateQ into: join c in metaData.CurrencyRates on s.Currency.Value equals c.CurrencyCode.Value

fixes it (with the same SQL query as a result, but with the proper aliases)

Looking at the wrong query again, it is unclear to me why the first query (rateQ) joined with pfPositionFromDateQ is causing this. What's also odd is that it picks as the wrong alias for the table to be an alias inside LPA_L2 instead of LPA_L1. This seems to be due to a long standing issue inside the linq provider with derived table joins mixed with regular relationship joins: the derived table join has to be linked with an existing element and apparently this goes wrong in this particular case.

It's a very hard issue to fix, as the problem domain when it occurs exactly is unclear and so many factors are at play that it's very cumbersome to get this fixed inside the linq provider.

What I'd like to ask you is, why the workaround with the direct join of currencyrates doesn't work for you? Could you elaborate on that a bit? (a small piece of code of a query which doesn't work with this workaround would be enough)

Thanks Frans for your answer.

The workaround with the direct join of currencyrates works fine. but I have another scenario that one of my tables contains about 170 columns, and third scenario gets date from tow tables or more.

In this query I just simplify this query to produce problem, but actually currencyrates join with another tables.

mohamed wrote:

Thanks Frans for your answer.

The workaround with the direct join of currencyrates works fine. but I have another scenario that one of my tables contains about 170 columns, and third scenario gets date from tow tables or more.

In this query I just simplify this query to produce problem, but actually currencyrates join with another tables.

I'll see if I can find a cause for this, but I don't have big expectations I will... Although it's very weird that the query fails.

When I remove the left join from the final query, it works, which suggests the error is somehow related to the defaultifempty.

Do you need the left join? As you're selecting the pfPositionToDateQ row, which might be a little odd, considering the fact that a 'null' on the side of pfPositionToDateQ makes no sense to the consuming code: the related pfPositionFromDateQ value isn't in the end result...

Will look into why this fails here.

The problem is caused by the fact that in both queries, there's a join which results in the same anonymous type. To determine the real source of a property on the anonymous type, the property gets the alias of the source it represents (e.g customer join order -> anonymous type with customer and order properties, both get the alias of the entity they represent: the customer and order sets joined). This goes OK till there's another join in the same query with exactly the same anonymous type. In that case, .NET re-uses the anonymous type and thus also the properties. This means that a property now gets another alias (so it has 2).

In v2.6 we assumed (wrongly) that the property was always unique, but this wasn't the case. So in v2.6 this isn't fixable. In v3 we made a change (an extensive change actually) which makes it possible to assign multiple aliases to the same property. In v3 the query also fails, for a different reason: the fix we implemented for this didn't work in all cases it turns out: as multiple aliases for the same property implies that only at any given time just 1 is valid, it has to be determined when which alias is valid, and this goes wrong in this particular query.

As this isn't fixable in v2.6, I'd suggest you rewrite the query in our own query api. This might be cumbersome, but at least your query will work.

For v3 we'll try to fix our 'fix'. We're sorry we can't fix this in v2.6, backporting the changes we made in the v3 linq provider can't be done, it's too much.

(edit). Still digging... it looks like it's another issue than what I described above. For example, if I add an orderby to your query:

 var q0 = from pfrom in pfPositionFromDateQ
          join pto in pfPositionToDateQ on new { pfrom.PortfolioNo, pfrom.SecurityEName } equals new { pto.PortfolioNo, pto.SecurityEName } into pto_join
          orderby pfrom.SecurityEName
          from tx in pto_join.DefaultIfEmpty()
          select tx;

it also works. The reason is that the groupjoin (pto_join) is INSIDE a join in the expression tree, but is the actual query, and for some yet unknown reason it gets the alias of the outside join (which is simply the groupjoin joining the defaultifempty).

Still, if you can get rid of the defaultifempty here it works too, or adding an orderby will fix it too as it then wraps the groupjoin in a proper query, not causing an alias issue.

This might help dealing with this issue without the necessity to rewrite the queries.

I found the fix in v3 and fixed it. I'll now see if I can backport it to v2.6 and will run your query as well to see if it fixes that one.

Otis wrote:

I found the fix in v3 and fixed it. I'll now see if I can backport it to v2.6 and will run your query as well to see if it fixes that one.

Greattttttt

FIXED!

See attached dll.

Warning: when trying to reproduce this in Northwind, I ran into the limitation I described above, which caused a null ref exception in v2.6. I fixed that in v3, but this can't be fixed in v2.6 due to the problem with duplicate usage of the same anonymous type in an expression tree.

(edit) I also added a v3 version, just in case you or a colleague needs it. The v3 one is the v3_....zip file.

Otis wrote:

FIXED!

See attached dll.

Warning: when trying to reproduce this in Northwind, I ran into the limitation I described above, which caused a null ref exception in v2.6. I fixed that in v3, but this can't be fixed in v2.6 due to the problem with duplicate usage of the same anonymous type in an expression tree.

(edit) I also added a v3 version, just in case you or a colleague needs it. The v3 one is the v3_....zip file.

Also throw that exception (wrong alias)

same query? I ran it successfully (your test project) ... Please be specific, we can't see your query in front of us. Also what version etc. did you try.

Just change the join condition to be

var pfPositionFromDateQ = from a in metaData.Transactions join b in metaData.PortfolioData on a.Pfno.Value equals b.PortfolioId join s in metaData.SecurityData on a.SecCode.Value equals s.Code join c in rateQ on new { CurrencyCode = s.Currency, CurrencyCodeTo = b.Currency } equals new { c.CurrencyCode, c.CurrencyCodeTo } select new { PortfolioNo = a.Pfno ?? 0, SecurityEName = a.SecCode ?? 0, c.Rate, AmountCcyBalance = (a.AmountBalance ?? 0m) };

attached class1

(edit) linq file version 2.6.2010.0809

We'll look into it.

thank you.

Query works in V3. Will now see if I can reproduce it in v2.6

(edit) works too in v2.6. Are you absolutely sure you use the fixed linq provider I posted at runtime? (i.e. if you have referenced it in 1 project, but in another you reference the old one and that one is compiled later, it might overwrite it? Perhaps you have the libs in the gac? (don't do that)

SELECT [LPA_L2].[PortfolioNo], [LPA_L2].[SecurityEName], [LPA_L2].[AmountCcyBalance] FROM ((SELECT COALESCE([LPA_L4].[PFNo], 0 /* @LO01 /) AS [PortfolioNo], COALESCE([LPA_L4].[SecCode], 0 / @LO02 /) AS [SecurityEName], [LPA_L7].[Rate], COALESCE([LPA_L4].[AmountBalance], 0 / @LO03 /) AS [AmountCcyBalance] FROM ((([LLBLTest].[dbo].[Transactions] [LPA_L4] INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L5] ON [LPA_L4].[PFNo] = [LPA_L5].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L6] ON [LPA_L4].[SecCode] = [LPA_L6].[Code]) INNER JOIN (SELECT [LPLA_6].[CurrencyCode], [LPLA_6].[CurrencyCodeTo], [LPLA_6].[Rate] FROM [LLBLTest].[dbo].[CurrencyRates] [LPLA_6]) [LPA_L7] ON ([LPA_L6].[Currency] = [LPA_L7].[CurrencyCode] AND [LPA_L5].[Currency] = [LPA_L7].[CurrencyCodeTo]))) [LPA_L1] LEFT JOIN (SELECT COALESCE([LPA_L8].[PFNo], 0 / @LO04 /) AS [PortfolioNo], COALESCE([LPA_L8].[SecCode], 0 / @LO05 /) AS [SecurityEName], COALESCE([LPA_L8].[AmountBalance], 0 / @LO06 */) AS [AmountCcyBalance] FROM (([LLBLTest].[dbo].[Transactions] [LPA_L8] INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L9] ON [LPA_L8].[PFNo] = [LPA_L9].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L10] ON [LPA_L8].[SecCode] = [LPA_L10].[Code])) [LPA_L2] ON ([LPA_L1].[PortfolioNo] = [LPA_L2].[PortfolioNo] AND [LPA_L1].[SecurityEName] = [LPA_L2].[SecurityEName]))

Otis wrote:

Query works in V3. Will now see if I can reproduce it in v2.6

(edit) works too in v2.6. Are you absolutely sure you use the fixed linq provider I posted at runtime? (i.e. if you have referenced it in 1 project, but in another you reference the old one and that one is compiled later, it might overwrite it? Perhaps you have the libs in the gac? (don't do that)

It seems works with previous query, really I didn't register any dll in gac. only changes that i do is deleting bin and obj folders and rebuild solution. lest's get last confirmation from my side after re-write my query.

(edit)Yes until previous query worked with linqSuppourt dll with file version (2.6.2010.809), But when try to complete the rateQ query logic, it throw the same error. the rateQ query is

 var rateQ = from a0 in metaData.CurrencyRates
                     join b1 in
                        (
                           (from t in metaData.CurrencyRates
                            group t by new
                            {
                               CurrencyCode = t.CurrencyCode.Value,
                               CurrencyCodeTo = t.CurrencyCodeTo.Value
                            } into g
                            select new
                            {
                               CurrencyCode = g.Key.CurrencyCode,
                               CurrencyCodeTo = g.Key.CurrencyCodeTo,
                               TrxTime = g.Max(p => p.TrxTime)
                            }))
                           on new { a0.TrxTime, CurrencyCode = a0.CurrencyCode.Value, CurrencyCodeTo = a0.CurrencyCodeTo.Value }
                       equals new { b1.TrxTime, b1.CurrencyCode, b1.CurrencyCodeTo }
                     select new
                     {
                        CurrencyCode = a0.CurrencyCode,
                        CurrencyCodeTo = a0.CurrencyCodeTo,
                        Rate = a0.Rate
                     };

I tried to delete the bin/obj folders to ensure to remove previous dll version from VS. also will attach the full cs file

(edit)Attached

mohamed wrote:

Otis wrote:

Query works in V3. Will now see if I can reproduce it in v2.6

(edit) works too in v2.6. Are you absolutely sure you use the fixed linq provider I posted at runtime? (i.e. if you have referenced it in 1 project, but in another you reference the old one and that one is compiled later, it might overwrite it? Perhaps you have the libs in the gac? (don't do that)

It seems works with previous query, really I didn't register any dll in gac. only changes that i do is deleting bin and obj folders and rebuild solution. lest's get last confirmation from my side after re-write my query.

(edit)Yes until previous query worked with linqSuppourt dll with file version (2.6.2010.809),

Why did you then say it didn't? We can only use queries which fail, and then trimmed down to the minimum. Looking at the query below, it looks totally different from what you posted earlier.

But when try to complete the rateQ query logic, it throw the same error. the rateQ query is

 var rateQ = from a0 in metaData.CurrencyRates
                     join b1 in
                        (
                           (from t in metaData.CurrencyRates
                            group t by new
                            {
                               CurrencyCode = t.CurrencyCode.Value,
                               CurrencyCodeTo = t.CurrencyCodeTo.Value
                            } into g
                            select new
                            {
                               CurrencyCode = g.Key.CurrencyCode,
                               CurrencyCodeTo = g.Key.CurrencyCodeTo,
                               TrxTime = g.Max(p => p.TrxTime)
                            }))
                           on new { a0.TrxTime, CurrencyCode = a0.CurrencyCode.Value, CurrencyCodeTo = a0.CurrencyCodeTo.Value }
                       equals new { b1.TrxTime, b1.CurrencyCode, b1.CurrencyCodeTo }
                     select new
                     {
                        CurrencyCode = a0.CurrencyCode,
                        CurrencyCodeTo = a0.CurrencyCodeTo,
                        Rate = a0.Rate
                     };

Will see if we can reproduce it with this. (v3 and v2.6)

(edit) The query works in v3:

SELECT [LPA_L2].[PortfolioNo], [LPA_L2].[SecurityEName], [LPA_L2].[AmountCcyBalance] FROM ( ( SELECT COALESCE([LPA_L4].[PFNo], @p2) AS [PortfolioNo], COALESCE([LPA_L4].[SecCode], @p4) AS [SecurityEName], [LPA_L7].[Rate], COALESCE([LPA_L4].[AmountBalance], @p6) AS [AmountCcyBalance] FROM ((( [LLBLTest].[dbo].[Transactions] [LPA_L4]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L5] ON [LPA_L4].[PFNo] = [LPA_L5].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L6] ON [LPA_L4].[SecCode] = [LPA_L6].[Code]) INNER JOIN ( SELECT [LPA_L9].[CurrencyCode], [LPA_L9].[CurrencyCodeTo], [LPA_L9].[Rate] FROM ( ( SELECT [LPA_L10].[CurrencyCode], [LPA_L10].[CurrencyCodeTo], [LPA_L10].[LPAV] AS [TrxTime] FROM ( SELECT [LPLA_7].[CurrencyCode], [LPLA_7].[CurrencyCodeTo], MAX([LPLA_7].[TrxTime]) AS [LPAV] FROM [LLBLTest].[dbo].[CurrencyRates] [LPLA_7]
GROUP BY [LPLA_7].[CurrencyCode], [LPLA_7].[CurrencyCodeTo] ) [LPA_L10] ) [LPA_L8]
INNER JOIN [LLBLTest].[dbo].[CurrencyRates] [LPA_L9] ON ( [LPA_L9].[TrxTime] = [LPA_L8].[TrxTime] AND [LPA_L9].[CurrencyCode] = [LPA_L8].[CurrencyCode] AND [LPA_L9].[CurrencyCodeTo] = [LPA_L8].[CurrencyCodeTo])) ) [LPA_L7] ON ( [LPA_L6].[Currency] = [LPA_L7].[CurrencyCode] AND [LPA_L5].[Currency] = [LPA_L7].[CurrencyCodeTo])) ) [LPA_L1]
LEFT JOIN ( SELECT COALESCE([LPA_L11].[PFNo], @p8 ) AS [PortfolioNo], COALESCE([LPA_L11].[SecCode], @p10) AS [SecurityEName], COALESCE([LPA_L11].[AmountBalance], @p12) AS [AmountCcyBalance] FROM (( [LLBLTest].[dbo].[Transactions] [LPA_L11]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L12] ON [LPA_L11].[PFNo] = [LPA_L12].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L13] ON [LPA_L11].[SecCode] = [LPA_L13].[Code]) ) [LPA_L2] ON ( [LPA_L1].[PortfolioNo] = [LPA_L2].[PortfolioNo] AND [LPA_L1].[SecurityEName] = [LPA_L2].[SecurityEName]))

will now test in v2.6

(edit) also works on v2.6:

SELECT [LPA_L2].[PortfolioNo], [LPA_L2].[SecurityEName], [LPA_L2].[AmountCcyBalance] FROM ( ( SELECT COALESCE([LPA_L4].[PFNo], @LO01) AS [PortfolioNo], COALESCE([LPA_L4].[SecCode], @LO02) AS [SecurityEName], [LPA_L7].[Rate], COALESCE([LPA_L4].[AmountBalance], @LO03) AS [AmountCcyBalance] FROM ((( [LLBLTest].[dbo].[Transactions] [LPA_L4]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L5] ON [LPA_L4].[PFNo] = [LPA_L5].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L6] ON [LPA_L4].[SecCode] = [LPA_L6].[Code]) INNER JOIN (SELECT [LPA_L9].[CurrencyCode], [LPA_L9].[CurrencyCodeTo], [LPA_L9].[Rate] FROM ( ( SELECT [LPA_L10].[CurrencyCode], [LPA_L10].[CurrencyCodeTo], [LPA_L10].[LPAV] AS [TrxTime] FROM ( SELECT [LPLA_7].[CurrencyCode], [LPLA_7].[CurrencyCodeTo], MAX([LPLA_7].[TrxTime]) AS [LPAV] FROM [LLBLTest].[dbo].[CurrencyRates] [LPLA_7]
GROUP BY [LPLA_7].[CurrencyCode], [LPLA_7].[CurrencyCodeTo] ) [LPA_L10] ) [LPA_L8]
INNER JOIN [LLBLTest].[dbo].[CurrencyRates] [LPA_L9] ON ( [LPA_L9].[TrxTime] = [LPA_L8].[TrxTime] AND [LPA_L9].[CurrencyCode] = [LPA_L8].[CurrencyCode] AND [LPA_L9].[CurrencyCodeTo] = [LPA_L8].[CurrencyCodeTo])) ) [LPA_L7] ON ( [LPA_L6].[Currency] = [LPA_L7].[CurrencyCode] AND [LPA_L5].[Currency] = [LPA_L7].[CurrencyCodeTo])) ) [LPA_L1]
LEFT JOIN ( SELECT COALESCE([LPA_L11].[PFNo], @LO04) AS [PortfolioNo], COALESCE([LPA_L11].[SecCode], @LO05) AS [SecurityEName], COALESCE([LPA_L11].[AmountBalance], @LO06) AS [AmountCcyBalance] FROM (( [LLBLTest].[dbo].[Transactions] [LPA_L11]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPA_L12] ON [LPA_L11].[PFNo] = [LPA_L12].[PortfolioID]) INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPA_L13] ON [LPA_L11].[SecCode] = [LPA_L13].[Code]) ) [LPA_L2] ON ( [LPA_L1].[PortfolioNo] = [LPA_L2].[PortfolioNo] AND [LPA_L1].[SecurityEName] = [LPA_L2].[SecurityEName]))

(you can see it's v2.6, as the parameter names are different).

Now, I altered my own query I had. When I use yours, it fails with the exception. I'll check what's different (as it looks really minor)

Why did you then say it didn't? We can only use queries which fail, and then trimmed down to the minimum. Looking at the query below, it looks totally different from what you posted earlier.

As I have huge complexity query. so I start to flat this query then start build one above one with adding more logic. Also the output of rateQ is the same but start adding complexity

Ah I see the difference with my query. You join with rateQ a second time, in pfPositionToDateQ as well.

This is likely due to the duplicate alias assignment to a member of an anonymous type which fails in v2 (and what I described above. I'll now re-try the second join in v3 to verify it's indeed that.

So my previous post, I missed the second join with rateQ in the second query (previous tests which succeeded I did copy your query btw)

(edit) the second join also fails in v3. So it's not the duplicate alias assignment it seems (though that's still a problematic thing, as when it decides it's in-scope it will pick the wrong alias).

It's the group by which is shared among two parts of the query. When I replace join c in rateQ on new { CurrencyCode = st.Currency, CurrencyCodeTo = bt.Currency } equals new { c.CurrencyCode, c.CurrencyCodeTo }

in pfPositionFromDateQ

with the full ( (from t in metaData.CurrencyRates group t by new { CurrencyCode = t.CurrencyCode.Value, CurrencyCodeTo = t.CurrencyCodeTo.Value } into g select new { CurrencyCode = g.Key.CurrencyCode, CurrencyCodeTo = g.Key.CurrencyCodeTo, TrxTime = g.Max(p => p.TrxTime) })) on new { a0.TrxTime, CurrencyCode = a0.CurrencyCode.Value, CurrencyCodeTo = a0.CurrencyCodeTo.Value } equals new { b1.TrxTime, b1.CurrencyCode, b1.CurrencyCodeTo } select new { CurrencyCode = a0.CurrencyCode, CurrencyCodeTo = a0.CurrencyCodeTo, Rate = a0.Rate })

it works. This is because the group by expression is re-used and has already been materialized into a groupby collection object at some point, causing a problem.

So the query which works:

var rateQ = from a0 in metaData.CurrencyRates
            join b1 in
                (
                (from t in metaData.CurrencyRates
                    group t by new
                    {
                        CurrencyCode = t.CurrencyCode.Value,
                        CurrencyCodeTo = t.CurrencyCodeTo.Value
                    } into g
                    select new
                    {
                        CurrencyCode = g.Key.CurrencyCode,
                        CurrencyCodeTo = g.Key.CurrencyCodeTo,
                        TrxTime = g.Max(p => p.TrxTime)
                    }))
                on new { a0.TrxTime, CurrencyCode = a0.CurrencyCode.Value, CurrencyCodeTo = a0.CurrencyCodeTo.Value }
            equals new { b1.TrxTime, b1.CurrencyCode, b1.CurrencyCodeTo }
            select new
            {
                CurrencyCode = a0.CurrencyCode,
                CurrencyCodeTo = a0.CurrencyCodeTo,
                Rate = a0.Rate
            };

var pfPositionFromDateQ = from a in metaData.Transactions
                        join b in metaData.PortfolioData on a.Pfno.Value equals b.PortfolioId
                        join s in metaData.SecurityData on a.SecCode.Value equals s.Code
                        join c in (from a0 in metaData.CurrencyRates
                        join b1 in
                        (
                        (from t in metaData.CurrencyRates
                            group t by new
                            {
                                CurrencyCode = t.CurrencyCode.Value,
                                CurrencyCodeTo = t.CurrencyCodeTo.Value
                            } into g
                            select new
                            {
                                CurrencyCode = g.Key.CurrencyCode,
                                CurrencyCodeTo = g.Key.CurrencyCodeTo,
                                TrxTime = g.Max(p => p.TrxTime)
                            }))
                        on new { a0.TrxTime, CurrencyCode = a0.CurrencyCode.Value, CurrencyCodeTo = a0.CurrencyCodeTo.Value }
                    equals new { b1.TrxTime, b1.CurrencyCode, b1.CurrencyCodeTo }
                    select new
                    {
                        CurrencyCode = a0.CurrencyCode,
                        CurrencyCodeTo = a0.CurrencyCodeTo,
                        Rate = a0.Rate
                    })
                        on new { CurrencyCode = s.Currency, CurrencyCodeTo = b.Currency }
                                        equals new { c.CurrencyCode, c.CurrencyCodeTo }
                        select new
                        {
                            PortfolioNo = a.Pfno ?? 0,
                            SecurityEName = a.SecCode ?? 0,
                            c.Rate,
                            AmountCcyBalance = (a.AmountBalance ?? 0m)
                        };


var pfPositionToDateQ = from at in metaData.Transactions
                        join bt in metaData.PortfolioData on at.Pfno.Value equals bt.PortfolioId
                        join st in metaData.SecurityData on at.SecCode.Value equals st.Code
                        join c in rateQ on new { CurrencyCode = st.Currency, CurrencyCodeTo = bt.Currency }
                                        equals new { c.CurrencyCode, c.CurrencyCodeTo }
                        select new
                        {
                        PortfolioNo = at.Pfno ?? 0,
                        SecurityEName = at.SecCode ?? 0,
                        c.Rate,
                        AmountCcyBalance = (at.AmountBalance ?? 0m)
                        };

var q0 = from pfrom in pfPositionFromDateQ
        join pto in pfPositionToDateQ on new { pfrom.PortfolioNo, pfrom.SecurityEName } equals new { pto.PortfolioNo, pto.SecurityEName } into pto_join
        from tx in pto_join.DefaultIfEmpty()
        select tx;

var xx = q0.ToList();

The reason this doesn't create the problem is that the group by expression in the second sub query (pfPositionToDateQ ) isn't equal to the first one as it's not referring to the same source.

Could you check whether this workaround for this massive query is suitable for your real queries? In short: check whether you're re-using query fragments in a linq query with group by or default if empty. If so, try to write out the query in full instead of re-using parts of the query, as the expression objects are different then.

Ok.. Let me some times to do that.

Yes it works, but writing this query in that way may scatter the logic and will be difficult for upcoming changes. so if it available to write it as fragmented queries ?

mohamed wrote:

Yes it works, but writing this query in that way may scatter the logic and will be difficult for upcoming changes. so if it available to write it as fragmented queries ?

You can fix this by creating a method which produces the fragments.

First create a temp class which holds the anonymous results:

public class RateQReults
{
    public int? CurrencyCode { get; set; }
    public int? CurrencyCodeTo { get; set; }
    public decimal? Rate { get; set; }
}

then create a small method (refactor the rateQ query), to produce the rateQ stuff:

private static IQueryable<RateQReults> GetRateQSnippet(LinqMetaData metaData)
{
    IQueryable<RateQReults> rateQ = from a0 in metaData.CurrencyRates
                                    join b1 in
                                        (
                                            (from t in metaData.CurrencyRates
                                            group t by new
                                            {
                                                CurrencyCode = t.CurrencyCode.Value,
                                                CurrencyCodeTo = t.CurrencyCodeTo.Value
                                            } into g
                                            select new
                                            {
                                                CurrencyCode = g.Key.CurrencyCode,
                                                CurrencyCodeTo = g.Key.CurrencyCodeTo,
                                                TrxTime = g.Max(p => p.TrxTime)
                                            }))
                                        on new { a0.TrxTime, CurrencyCode = a0.CurrencyCode.Value, CurrencyCodeTo = a0.CurrencyCodeTo.Value }
                                    equals new { b1.TrxTime, b1.CurrencyCode, b1.CurrencyCodeTo }
                                    select new RateQReults()
                                    {
                                        CurrencyCode = a0.CurrencyCode,
                                        CurrencyCodeTo = a0.CurrencyCodeTo,
                                        Rate = a0.Rate
                                    };
    return rateQ;
}

and then refactor the query to use two instances of rateQ:

IQueryable<RateQReults> rateQ1 = GetRateQSnippet(metaData);
IQueryable<RateQReults> rateQ2 = GetRateQSnippet(metaData);

var pfPositionFromDateQ = from a in metaData.Transactions
                        join b in metaData.PortfolioData on a.Pfno.Value equals b.PortfolioId
                        join s in metaData.SecurityData on a.SecCode.Value equals s.Code
                        join c in rateQ1
                                    on new { CurrencyCode = s.Currency, CurrencyCodeTo = b.Currency }
                                        equals new { c.CurrencyCode, c.CurrencyCodeTo }
                        select new
                        {
                            PortfolioNo = a.Pfno ?? 0,
                            SecurityEName = a.SecCode ?? 0,
                            c.Rate,
                            AmountCcyBalance = (a.AmountBalance ?? 0m)
                        };


var pfPositionToDateQ = from at in metaData.Transactions
                        join bt in metaData.PortfolioData on at.Pfno.Value equals bt.PortfolioId
                        join st in metaData.SecurityData on at.SecCode.Value equals st.Code
                        join c in rateQ2 on new { CurrencyCode = st.Currency, CurrencyCodeTo = bt.Currency }
                                        equals new { c.CurrencyCode, c.CurrencyCodeTo }
                        select new
                        {
                        PortfolioNo = at.Pfno ?? 0,
                        SecurityEName = at.SecCode ?? 0,
                        c.Rate,
                        AmountCcyBalance = (at.AmountBalance ?? 0m)
                        };

var q0 = from pfrom in pfPositionFromDateQ
        join pto in pfPositionToDateQ on new { pfrom.PortfolioNo, pfrom.SecurityEName } equals new { pto.PortfolioNo, pto.SecurityEName } into pto_join
        from tx in pto_join.DefaultIfEmpty()
        select tx;

var xx = q0.ToList();

Tested it and this works. Does this make changing easy?

It will otherwise be a very serious problem to solve for us, as it will require a lot of refactoring inside the linq provider. This problem only occurs when you have a groupby which you formulate in a query which you use multiple times in another query.

(I haven't tried, but perhaps you can even refactor both positiontodate Q's into a method, removing 1 duplicate)