TomDog wrote:

Otis wrote:

Sure I can wrap the Query in a derived table, but... where should the leftjoin be? Between the two from clauses ? Or between: customer.SalesOrderHeader ?

customer.SalesOrderHeader. If you look at the output in LINQPad(made a bit more readable):

SELECT Sales.Customer.CustomerID, Sales.SalesOrderHeader.SalesOrderID AS AddressID
FROM Sales.Customer LEFT OUTER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesOrderID < 10 AND Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

which, to my mind, pretty much fits what the comprehension syntax query looks like. To look at it another way, how else would this query (or any query with a filter in a left join ON clause) be generated? (again without manually joining).

Ok. The thing is, I tried this for hours and always ran into an alias problem which I couldn't solve (it had to replace an alias with the original alias it had, but that wasn't retrievable anymore), but I'll give it one more try on monday.

Otis wrote:

Ok. The thing is, I tried this for hours and always ran into an alias problem which I couldn't solve (it had to replace an alias with the original alias it had, but that wasn't retrievable anymore), but I'll give it one more try on monday.

What a great way to start the week . Doesn't get any better either as while your there you might want to check out a couple of crashes i've encountered with left join's via associations and derived table's. Much the same queries as before but I chucked in

var customers = AWHelper.MetaData.Customer.AsQueryable();
var customersDerivedTable = customers.Where(c => c.CustomerId > 10);

to force a derived table in the query. The first one is a SQL error complaining about the case of 'CustomerId'

AWHelper.TraceOut("One Left Association Join with Derived Table");
      //var q1 = AWHelper.MetaData.Customer.SelectMany(customer => customer.SalesOrderHeader.DefaultIfEmpty(), (customer, soh) => new {customer.CustomerId, soh.SalesOrderId});
      var q1 = from customer in customersDerivedTable
               from soh in customer.SalesOrderHeader.DefaultIfEmpty()
               select new {customer.CustomerId, soh.SalesOrderId};
      var z = q1.ToList();

the second is our old friend '...Bad alias?'

      AWHelper.TraceOut("One Left and one inner Association Join with Derived Table");
      var q5 = from customer in customersDerivedTable
               from ca in customer.CustomerAddress
               from soh in customer.SalesOrderHeader.DefaultIfEmpty()
               select new {customer.CustomerId, ca.AddressId, soh.SalesOrderId};
      var f = q5.ToList();

both are fine if i use customers instead of customersDerivedTable. See LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder in http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=39290

Yes, these are all caused by the same issue I described earlier where a 'where' clause was placed between two or more nested from clauses. the problem is that the derived table doesn't have a projection, and with nested froms it gets therefore a projection of an anonymous type and two entities, from which the NEXT from clause will select. the derived table gets an alias, and that's the source of the next from to select from. However it's not said that this is a derived table (as the where you put there can also be placed in the main query) and as it doesn't have a projection, (the anonymous type with the two entities is really not that useful) the where is moved up. However, the 'where' accesses the table INSIDE the query which was re-aliased (due to the fact that it was 'assumed' that it would be a derived table). Now, what was the original alias? As soon as I solve that, this is solved, but I spend a lot of time on this, and couldn't find any solution.

remember, ALL your problems come from the fact that you keep using the same nested from query style with related entities AND defaultifempty. It all runs into the same problem. I managed to fix it for a great deal, but not if a where or orderby is placed between nested from clauses AND no projection is seen. This causes the problem I described in the paragraph above.

this query:

 var q1 = from customer in customersDerivedTable
             from soh in customer.SalesOrderHeader.DefaultIfEmpty()
             select new {customer.CustomerId, soh.SalesOrderId};
     var z = q1.ToList();

is equal to:

 var q1 = from customer in AWHelper.MetaData.Customer
             where customer.CustomerId > 10
             from soh in customer.SalesOrderHeader.DefaultIfEmpty()
             select new {customer.CustomerId, soh.SalesOrderId};
     var z = q1.ToList();

the problem is that the first nested from clauses are forming a SelectMany. Both sides of the select many are seen as queries. Normally, you either have a full query with a projection, or an entity, so that's not a problem. In this particular case, you have a where and an entity, but no projection. This leads to a problem because the join can be done with the entity the where works on and teh where can be moved to the bottom as everything is in the same query scope (no subqueries). However, that decision is made at the end, when everything else is already handled, you can't decide that earlier. problem is, the second from: 'from soh in customer.SalesOrderHeader.DefaultIfEmpty()' works on the set produced by the where + entity and therefore refers to THAT set's alias. but, as the where isn't in a separate query, that alias isn't in the final query as well, so that second from clause has to refer to the INNER works of the elements of the where.

In some cases, this might work. It fails when the where is placed in a 3-from clause set after the first two.

So the typical workaround here is to move the where to below the from clauses. I know this is in theory a limitation which shouldn't be there, but writing a linq provider is too complex to cover all situations with 100% failproof code when there's no standard for expression trees and no documentation.

As I've said earlier, I don't know a solution to this, other than to write the queries differently so they work. I'll try to see if I can come up with a solution today, but if it takes too long, I'll close it and mark this as one of the few known issues, which luckily has a workaround which does work.

Ok, my final attempt to fix this mess.

var q = from customer in metaData.Customer.Where(c=>c.CustomerId > 10)
        from soh in customer.SalesOrderHeaderCollection.DefaultIfEmpty()
        select new { customer.CustomerId, soh.SalesOrderId };

Doesn't crash but also forgets the where clause. This is because the DefaultIfEmpty() works on customer - SalesOrderheader, where 'Customer' is seen as an entity directly from its own table, not from the derived table created by the where.

One 'solution' to all this is to produce a projection for the derived table with all the entity fields, (and for nested froms, this could lead to a lot of fields), and then select from there. Not ideal, but these queries aren't optimal anyway (you really should write them differently so they produce more efficient sql).

Though, there's a problem. Lets go back to your query here:

var q4 = from customer in AWHelper.MetaData.Customer
             from soh in customer.SalesOrderHeader.Where(soh => soh.SalesOrderId < 10).DefaultIfEmpty()
             select new {customer.CustomerId, soh.SalesOrderId};

You said, you wanted the left join over customer.SalesOrderHeader. However, that's tucked into a subquery, a derived table, call it DT. Unreachable for DefaultIfEmpty.

So we now have customer and soh's from DT and these should be joined by a left join, correct? However, what should be used for the ON clause?

Seriously, I have no idea. So, the where.... shouldn't be a derived table, though... when is that known? When it's evaluated and handled. Though, then I get a QueryExpression which contains everything but no projection. Great, so I can re-use the elements in the outer query! But... the outer query refers to the query-without-projection through an alias as if it's a separate set (because it was unknown the query wasn't a full query).

I can do everything with aliases, rewrite them etc. no problem. The thing is though: to rewrite it to what? the original aliases aren't always known. Sure, a set with properties 'Customer' and 'SalesOrderheader' and you read from that set a Customer typed element, it's likely you're referring to the Customer property and it can be tracked back from there.

But what if you join with self? Which one to pick then? And yes, that's possible.

so rewriting your 'derived table' query, I get: var q = from customer in metaData.Customer from soh in customer.SalesOrderHeaderCollection.DefaultIfEmpty() where customer.CustomerId > 10 select new { customer.CustomerId, soh.SalesOrderId };

which produces a nice left join and a where clause, and the results you want.

There are 1001 ways to break a linq provider, even the Linq to Sql one. However, most of the time, the queries themselves are pretty obscure and ambiguistic: it's unclear what should be done when reading the query: there are multiple interpretations possible as it seems. This is because the projection of Linq onto SQL isn't 1:1.

I'm sorry. Trust me, I seriously feel a little depressed because of this and why I can't solve it. However, we too have to make choices. So I once again want to ask you: write the queries the way they do work, this isn't solveable at the moment, if ever. Yes I know linq to sql works with this, but linq to sql has other things they don't support which we do support

This is disappointing but as long as there are well documented workaround's then we might manage. Correct me if I'm wrong but the rule seems to be; don't use related entities AND defaultifempty if the query will result in a derived table - use an explicit join instead.

You say the queries aren't optimal - what could I do to improve them? given my constraint that the where clauses must be in a separate statement from the projection.

btw An example of this taken even further so that the projection is in a different method is in toolStripButtonLinq_Click in here: http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=532337&changeSetId=39315

TomDog wrote:

This is disappointing but as long as there are well documented workaround's then we might manage. Correct me if I'm wrong but the rule seems to be; don't use related entities AND defaultifempty if the query will result in a derived table - use an explicit join instead.

Yes, explicit joins work regardless. The defaultifempty will also work in many situations, just not always (but still in a lot of cases) when a where is enclosed inside nested froms.

You say the queries aren't optimal - what could I do to improve them? given my constraint that the where clauses must be in a separate statement from the projection.

You can do this by appending the projection to an anonymous type to a query with nested froms, append the where to THAT and have a normal entity projection. THEN append the select to an anonymous type to that result. That allows you to append wheres to the initially nested from stuff (though use 'Any' or Contains where possible, its more efficient) and you don't have to worry if the projection you want will fit.

the toolStripButtonLinq_Click query fails?

Otis wrote:

Yes, explicit joins work regardless. The defaultifempty will also work in many situations, just not always (but still in a lot of cases) when a where is enclosed inside nested froms.

More bad news, I can make it crash without a defaultifempty or a where clause in sight

query = from soh in AWHelper.MetaData.SalesOrderHeader
              from sod in soh.SalesOrderDetail
              select soh;
//SQL error
var y = (from soh in query
               select new {soh.SalesOrderId}).ToList();

//Bad alias? error
var x = (from soh in query
               select new {soh.SalesOrderId, soh.Customer.AccountNumber}).ToList();

//this will do it as well
var sohquery = from soh in AWHelper.MetaData.SalesOrderHeader select soh;

var w = (from soh in sohquery 
              from sod in soh.SalesOrderDetail
               select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

Otis wrote:

You can do this by appending the projection to an anonymous type to a query with nested froms, append the where to THAT and have a normal entity projection. THEN append the select to an anonymous type to that result. That allows you to append wheres to the initially nested from stuff (though use 'Any' or Contains where possible, its more efficient) and you don't have to worry if the projection you want will fit.

Not really following you - could you give an example?

Otis wrote:

the toolStripButtonLinq_Click query fails?

No work fine's but I was just showing where I was heading with seperation of the projection and the predicates. FWIW I'm building up to a feature request for the TypedList Designer to create LINQ equivalents of TypeList's.

TomDog wrote:

Otis wrote:

Yes, explicit joins work regardless. The defaultifempty will also work in many situations, just not always (but still in a lot of cases) when a where is enclosed inside nested froms.

More bad news, I can make it crash without a defaultifempty or a where clause in sight

query = from soh in AWHelper.MetaData.SalesOrderHeader
              from sod in soh.SalesOrderDetail
              select soh;
//SQL error
var y = (from soh in query
               select new {soh.SalesOrderId}).ToList();

//Bad alias? error
var x = (from soh in query
               select new {soh.SalesOrderId, soh.Customer.AccountNumber}).ToList();

//this will do it as well
var sohquery = from soh in AWHelper.MetaData.SalesOrderHeader select soh;

var w = (from soh in sohquery 
              from sod in soh.SalesOrderDetail
               select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

These should work at least. I'll look into it.

Otis wrote:

You can do this by appending the projection to an anonymous type to a query with nested froms, append the where to THAT and have a normal entity projection. THEN append the select to an anonymous type to that result. That allows you to append wheres to the initially nested from stuff (though use 'Any' or Contains where possible, its more efficient) and you don't have to worry if the projection you want will fit.

Not really following you - could you give an example?

I gave one in my last post but your queries above are what I had in mind. It surprises me they don't work, so I'll look into that.

Otis wrote:

the toolStripButtonLinq_Click query fails?

No work fine's but I was just showing where I was heading with seperation of the projection and the predicates. FWIW I'm building up to a feature request for the TypedList Designer to create LINQ equivalents of TypeList's.

That was already planned (generate as query). As it makes more sense to have the typedlists as a query than as a typed datatable when working with linq anyway.

It's funny but this relates to the cause of the previous issue plaguing this thread: This also fails: var q1 = from c in metaData.Customer join o in metaData.Order on c.CustomerId equals o.CustomerId select c; var q = from c in q1 select new { c.CustomerId, c.CompanyName };

for the same reason. q1 doesn't have a 'Select' or Projection set, that's the projection inside the Join statement (which always has a projection). This means that the join is seen as a simple join without a projection. This is correct, as the projection is the one in q.

Problem is... 'c' in the projection refers to ... the set produced by q1 as a whole (with the alias of q1). This at runtime breaks because there's no set with that alias, the join is in the same scope, q1 isn't a subquery.

This is caused by the fact that the lambda which is the projection of q gets a single parameter passed in, which is a value from the source set, which is q1. What's necessary is to track back what the parameter refers to in the q1 set, i.e. the customer. However that's impossible to find back if it's a join with self for example. So the only way to deal with this is to make q1 a true query with the projection in q1.

Problem with that is that... whatever select you're doing, the source, if a select many/join, should become a subquery... I'm not sure if multiple joins will hold up. I'll check.

With 2 lines of code I fixed the problem. I'm now looking into if this change could lead to a solution to your previous problems as well. The fix basicly forces a projection onto a join, always, so instead of depending on the expression it is used with (e.g. a where as one side of a join gets a projection forced as a join side is always a query or a single entity) it does this itself.

this makes it possible to append projections onto joins which already had a projection. So we're getting somewhere at least.

still misplaced where clauses cause big problems: this works:

var q = from customer in metaData.Customer
        from ca in customer.CustomerAddressCollection
        from soh in customer.SalesOrderHeaderCollection
        where ca.AddressId != null
        where soh.SalesOrderId == null
        select customer;

this doesn't

var q = from customer in metaData.Customer
        from ca in customer.CustomerAddressCollection
        where ca.AddressId != null
        from soh in customer.SalesOrderHeaderCollection
        where soh.SalesOrderId == null
        select customer;

which should work as well.

Otis wrote:

FWIW I'm building up to a feature request for the TypedList Designer to create LINQ equivalents of TypeList's.

That was already planned (generate as query). As it makes more sense to have the typedlists as a query than as a typed datatable when working with linq anyway.

Great!I was hoping you'd say that. I anticipate us being big users of typedlists and not being able use linq predicates with them is a major block to us using linq everywhere instead of the LLBLGen API predicates. It might have a side benefit of getting around some of these problems as the joins could all be done explicitly but it wouldn’t be a pain as they are maintained by the designer.

With 2 lines of code I fixed the problem. I'm now looking into if this change could lead to a solution to your previous problems as well. So we're getting somewhere at least.

Good to hear - I'll be waking up with a bit hope in the morning then.

TomDog wrote:

Otis wrote:

FWIW I'm building up to a feature request for the TypedList Designer to create LINQ equivalents of TypeList's.

That was already planned (generate as query). As it makes more sense to have the typedlists as a query than as a typed datatable when working with linq anyway.

Great!I was hoping you'd say that. I anticipate us being big users of typedlists and not being able use linq predicates with them is a major block to us using linq everywhere instead of the LLBLGen API predicates. It might have a side benefit of getting around some of these problems as the joins could all be done explicitly but it wouldn’t be a pain as they are maintained by the designer.

True, it would give more power to the typedlist as a concept. The designer will also be different, with a visual 'entity + relationships' model visual so you can just pick the relationships to use by selecting them in the visual model (not all entities are visible of course, only the ones in the typedlist)

With 2 lines of code I fixed the problem. I'm now looking into if this change could lead to a solution to your previous problems as well. So we're getting somewhere at least.

Good to hear - I'll be waking up with a bit hope in the morning then.

I wrote an expression tree rewriter which would extract where/orderby clauses inside sides of a join and move them upwards. It took a few hours but it finally gets somewhere. I still get some alias errors here and there, but I think these can be solved. Fun thing is: your queries then will work properly. but no promisses yet, it still crashes some queries, so I hope to have some success soon.

One test left...

for obscure reasons this still fails

var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId into co
        from x in co
        select x.CustomerId;

But I've made the rest working... Stay tuned!

DONE!

This also works:

var customers = metaData.Customer.AsQueryable();
var customersDerivedTable = customers.Where(c => c.CustomerId <100);
var q1 = from customer in customersDerivedTable
         from soh in customer.SalesOrderHeaderCollection.DefaultIfEmpty()
         select new { customer.CustomerId, soh.SalesOrderId };
var z = q1.ToList();

I'll attach a new build after it's succesfully build with the buildsystem. This will take another couple of minutes because I've added a class which has to be added to the specific project file in the build system as well.

(edit) Enjoy. (Attached) See below

I hope it will stay up on all your queries. Please post in this thread if your queries still fail. Our tests all run OK, I added a couple of wicked ones with your code to check if I covered all the bases (which is hard to do with unittests, but it's a start), and reasoning about the changes made today have given me a good feeling that things will be better now, also more efficient.

I added some significant preprocessing here and there, so it might be some queries we didn't anticipate which did work now fail, but that's life with linq I guess (and we then therefore will add code to fix those again... Linq... Never a dull moment! )

Otis wrote:

I hope it will stay up on all your queries. Please post in this thread if your queries still fail. Our tests all run OK, I added a couple of wicked ones with your code to check if I covered all the bases (which is hard to do with unittests, but it's a start), and reasoning about the changes made today have given me a good feeling that things will be better now, also more efficient.

Great to hear Frans, though you might need to add a few more of my examples to your unit tests. I can report the SQL is now a LOT easier on the eye which makes me feel good to. Your patch fixed all the problems in my real code which lets me get one with things but some of the SalesOrderDetail stuff I posted before still fails, as does

var x = (from customer in customers
               from soh in customer.SalesOrderHeader.Where(soh => soh.SalesOrderId < 10).DefaultIfEmpty()
               select new {customer.CustomerId, soh.SalesOrderId}).ToList();

though it's now just a SQL error. They are all here: http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=39375 , anyway seems your on track to having it all sorted.

TomDog wrote:

Otis wrote:

I hope it will stay up on all your queries. Please post in this thread if your queries still fail. Our tests all run OK, I added a couple of wicked ones with your code to check if I covered all the bases (which is hard to do with unittests, but it's a start), and reasoning about the changes made today have given me a good feeling that things will be better now, also more efficient.

Great to hear Frans, though you might need to add a few more of my examples to your unit tests. I can report the SQL is now a LOT easier on the eye which makes me feel good to. Your patch fixed all the problems in my real code which lets me get one with things but some of the SalesOrderDetail stuff I posted before still fails, as does

var x = (from customer in customers
               from soh in customer.SalesOrderHeader.Where(soh => soh.SalesOrderId < 10).DefaultIfEmpty()
               select new {customer.CustomerId, soh.SalesOrderId}).ToList();

though it's now just a SQL error. They are all here: http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=39375, anyway seems your on track to having it all sorted.

Hmm... Looks like a query I tried... Will check. Please post which queries still fail.

Alias propagation issue. Works now. All queries in 'Barf' now work. All queries in LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder also now work.

More to test? I must say, I didn't expect this to fix, as it's so complex (stuff happens all over the tree, moving subtrees around has consequences and which ones is hard to overlook), but a neat visitor class once again brought relief

Otis wrote:

Alias propagation issue. Works now. All queries in 'Barf' now work. All queries in LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder also now work.

More to test?

No just the ones in those two methods, though I did also have a SQL error with an invalid alias in my real code

var oc = MetaData.OcOccurrence.AsQueryable();

var q = from Occurrence in oc
              join RiskMatrix0 in
                (from RiskMatrix in MetaData.QaocRiskMatrix where RiskMatrix.RiskSource == "O" select RiskMatrix)
                on new {part0 = Occurrence.ReoccurrenceProbability, part1 = Occurrence.SeverityFactor} equals new {part0 = RiskMatrix0.LikelihoodCode, part1 = RiskMatrix0.SeverityCode} into join2
              from RiskMatrix in join2.DefaultIfEmpty()
              select new
                       {
                         Occurrence.OccurrenceNo
                       };

if you give me a new assembly I can test it.

TomDog wrote:

Otis wrote:

Alias propagation issue. Works now. All queries in 'Barf' now work. All queries in LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder also now work.

More to test?

No just the ones in those two methods, though I did also have a SQL error with an invalid alias in my real code

var oc = MetaData.OcOccurrence.AsQueryable();

var q = from Occurrence in oc
              join RiskMatrix0 in
                (from RiskMatrix in MetaData.QaocRiskMatrix where RiskMatrix.RiskSource == "O" select RiskMatrix)
                on new {part0 = Occurrence.ReoccurrenceProbability, part1 = Occurrence.SeverityFactor} equals new {part0 = RiskMatrix0.LikelihoodCode, part1 = RiskMatrix0.SeverityCode} into join2
              from RiskMatrix in join2.DefaultIfEmpty()
              select new
                       {
                         Occurrence.OccurrenceNo
                       };

Should work now too. (though haven't tried exactly this query, but the overall look of it is similar to several of my tests). the alias error was in the where clause? (due to the defaultifempty which changed the alias)

if you give me a new assembly I can test it.

Attached. :) See below

Otis wrote:

Should work now too. (though haven't tried exactly this query, but the overall look of it is similar to several of my tests). the alias error was in the where clause? (due to the defaultifempty which changed the alias)

No joy: Application_ThreadException: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The column prefix 'LPLA_2' does not match with a table name or alias name

DECLARE @RiskSource1 VarChar
SET @RiskSource1='O'

SELECT
 DISTINCT TOP 5 [LPA_L1].[Occurrence_No] AS [OccurrenceNo] 
FROM
 ( [AQD].[oc_Occurrence] [LPA_L1]  
LEFT JOIN
 [AQD].[qaoc_RiskMatrix] [LPA_L2]  ON  ( [LPA_L1].[Reoccurrence_Probability] = [LPA_L2].[Likelihood_Code] AND [LPA_L1].[Severity_Factor] = [LPA_L2].[Severity_Code])) 
WHERE
 ( ( ( ( ( [LPLA_2].[Risk_Source] = @RiskSource1)))))

Ok. I know the cause, (where clause moved in the tree), I've to see where I can change some restrictions in the alias creation so this doesn't have any inpact. Will look into it.

(edit) I can repro it with

var q = from c in metaData.Customer
        join o in
            (from order in metaData.Order where order.EmployeeId > 4 select order)
        on c.CustomerId equals o.CustomerId into co
        from o in co.DefaultIfEmpty()
        select new { c.CustomerId };

Fixed (See attached dll)

I'll remove the other attachments, as they only clog up space

Yep sorted Yahoo!

TomDog wrote:

Yep sorted Yahoo!

Awesome!

pfew

Oops not so fast - these two in Barf() are still failing

var sohquery = from soh in AWHelper.MetaData.SalesOrderHeader select soh;

var w = (from soh in sohquery 
              from sod in soh.SalesOrderDetail
               select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

query = from soh in AWHelper.MetaData.SalesOrderHeader
              from sod in soh.SalesOrderDetail
              select soh;

//Bad alias? error
var x = (from soh in query
               select new {soh.SalesOrderId, soh.Customer.AccountNumber}).ToList();

LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder doesn't crash however

      var q4 = from customer in customers
               from soh in customer.SalesOrderHeader.Where(soh => soh.SalesOrderId < 10).DefaultIfEmpty()
               select new {customer.CustomerId, soh.SalesOrderId};
      var x = q4.ToList();

produces

SELECT DISTINCT TOP 4 [lpa_l1].[customerid] AS [customerid],
                      [lpa_l2].[salesorderid] AS [salesorderid]
FROM   ([adventureworks].[sales].[customer] [lpa_l1]
        LEFT JOIN [adventureworks].[sales].[salesorderheader] [lpa_l2]
          ON [lpa_l1].[customerid] = [lpa_l2].[customerid])
WHERE  (((([lpa_l2].[salesorderid] < @SalesOrderId1))))

instead of

SELECT TOP (5) [t2].[CustomerID], [t2].[value] AS [SalesOrder]
FROM (
    SELECT [t0].[CustomerID], [t1].[SalesOrderID] AS [value]
    FROM [Sales].[Customer] AS [t0]
    LEFT OUTER JOIN [Sales].[SalesOrderHeader] AS [t1] ON ([t1].[SalesOrderID] < @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
    ) AS [t2]

like linq to sql does(debatable but filter should be in the ON clause)

SD.LLBLGen.Pro.LinqSupportClasses.NET35 Assembly Version: 2.6.0.0 Win32 Version: 2.6.08.0910