This query in LINQPad against Northwind.

from o in Order
from x in o.Customer.CustomerCustomerDemos.DefaultIfEmpty()
select o

Results in this SQL

SELECT DISTINCT [LPA_L2].[CustomerID] AS [CustomerId],
  [LPA_L2].[EmployeeID] AS [EmployeeId],
  [LPA_L2].[Freight],
  [LPA_L2].[OrderDate],
  [LPA_L2].[OrderID] AS [OrderId],
  [LPA_L2].[RequiredDate],
  [LPA_L2].[ShipAddress],
  [LPA_L2].[ShipCity],
  [LPA_L2].[ShipCountry],
  [LPA_L2].[ShipName],
  [LPA_L2].[ShippedDate],
  [LPA_L2].[ShipPostalCode],
  [LPA_L2].[ShipRegion],
  [LPA_L2].[ShipVia]
FROM
 (( [Northwind].[dbo].[Customers] [LPA_L1] 
RIGHT JOIN
 [Northwind].[dbo].[Orders] [LPA_L2]  ON  [LPA_L1].[CustomerID]=[LPA_L2].[CustomerID])
LEFT JOIN
 [Northwind].[dbo].[CustomerCustomerDemo] [LPA_L3]  ON  [LPA_L1].[CustomerID]=[LPA_L3].[CustomerID])

No inner joins which is what I want.

But this more elaborate query

from e in Employee
from o in e.Orders.DefaultIfEmpty()
from x in o.Customer.CustomerCustomerDemos.DefaultIfEmpty()
select o

Produces

SELECT DISTINCT [LPA_L2].[CustomerID] AS [CustomerId],
  [LPA_L2].[EmployeeID] AS [EmployeeId],
  [LPA_L2].[Freight],
  [LPA_L2].[OrderDate],
  [LPA_L2].[OrderID] AS [OrderId],
  [LPA_L2].[RequiredDate],
  [LPA_L2].[ShipAddress],
  [LPA_L2].[ShipCity],
  [LPA_L2].[ShipCountry],
  [LPA_L2].[ShipName],
  [LPA_L2].[ShippedDate],
  [LPA_L2].[ShipPostalCode],
  [LPA_L2].[ShipRegion],
  [LPA_L2].[ShipVia]
FROM
 ((( [Northwind].[dbo].[Employees] [LPA_L1] 
LEFT JOIN
 [Northwind].[dbo].[Orders] [LPA_L2]  ON  [LPA_L1].[EmployeeID]=[LPA_L2].[EmployeeID])
LEFT JOIN
 [Northwind].[dbo].[Customers] [LPA_L3]  ON  [LPA_L3].[CustomerID]=[LPA_L2].[CustomerID])
INNER JOIN
 [Northwind].[dbo].[CustomerCustomerDemo] [LPA_L4]  ON  [LPA_L3].[CustomerID]=[LPA_L4].[CustomerID]

Which this time has an inner join between CustomerCustomerDemo and Customer, not what I want.

Why the difference? Shouldn't both CustomerCustomerDemo and Customer joins be either Inner or left/right.

Which linq runtime library version (build number) is this?

Walaa wrote:

Which linq runtime library version (build number) is this?

3.0.10.1119

We will check it out.

The DefaultIfEmpty() call will set the last relationship in the list of relationships to navigate to get to that point to LEFT join. The bug is that there should have been 2 relationships (Order - Customer, Customer - CustomerCustomerDemo), but for some reason there are 3: Order - Customer, Customer - CustomerCustomerDemo and again Order - Customer. This means, the wrong relationship gets set to LEFT join (namely a duplicate one).

Looking int why this relationship is added there.

Fixed. See attached dll.

Otis wrote:

Fixed. See attached dll.

Awsoume - so glad there was a fix

However I hit another problem, probably unrelated but here it goes.

from o in Order
from ccd in o.Customer.CustomerCustomerDemos.DefaultIfEmpty()
select new {o.ShipName, ccd.CustomerDemographic.CustomerDesc}

produces this SQL

SELECT [LPA_L2].[ShipName],
  [LPA_L4].[CustomerDesc]
FROM
 ((( [Northwind].[dbo].[Customers] [LPA_L1] 
RIGHT JOIN
 [Northwind].[dbo].[Orders] [LPA_L2]  ON  [LPA_L1].[CustomerID]=[LPA_L2].[CustomerID])
LEFT JOIN
 [Northwind].[dbo].[CustomerCustomerDemo] [LPA_L3]  ON  [LPA_L1].[CustomerID]=[LPA_L3].[CustomerID])
INNER JOIN
 [Northwind].[dbo].[CustomerDemographics] [LPA_L4]  ON  [LPA_L4].[CustomerTypeID]=[LPA_L3].[CustomerTypeID])

inner join is back.

I can get around it by doing an explicit join.

from o in Order
from ccd in o.Customer.CustomerCustomerDemos.DefaultIfEmpty()
join cd in CustomerDemographic on ccd.CustomerTypeId equals cd.CustomerTypeId into customerDemographics
from cd in customerDemographics.DefaultIfEmpty()
select new {o.ShipName, cd.CustomerDesc}

which gives

SELECT [LPA_L2].[ShipName],
  [LPA_L4].[CustomerDesc]
FROM
 ((( [Northwind].[dbo].[Customers] [LPA_L1] 
RIGHT JOIN
 [Northwind].[dbo].[Orders] [LPA_L2]  ON  [LPA_L1].[CustomerID]=[LPA_L2].[CustomerID])
LEFT JOIN
 [Northwind].[dbo].[CustomerCustomerDemo] [LPA_L3]  ON  [LPA_L1].[CustomerID]=[LPA_L3].[CustomerID])
LEFT JOIN
 [Northwind].[dbo].[CustomerDemographics] [LPA_L4]  ON  [LPA_L3].[CustomerTypeID] = [LPA_L4].[CustomerTypeID])

Explicit join is a pain, is there another way?

The inner join isn't the one affected by DefaultIfEmpty, as it's 'ccd.CustomerDemographic' and not 'Customer.CustomerCustomerDemos'

In these cases, the join type is determined from the nullability of the FK field. If it's nullable, the join is towards the PK side, otherwise it's inner join. Looking at the db, the fk field isn't null, so the join type is actually correct. To get a left join towards the PK side there, make the FK field nullable.

Otis wrote:

In these cases, the join type is determined from the nullability of the FK field. If it's nullable, the join is towards the PK side, otherwise it's inner join. Looking at the db, the fk field isn't null, so the join type is actually correct. To get a left join towards the PK side there, make the FK field nullable.

So your saying if CustomerDemographic.CustomerTypeId was made optional in the LLBL project then there would be a left join? but CustomerDemographic.CustomerTypeId is part for the primary key so can't be made optional.

If the fk is non-nullable, an inner join and left join are equal (as they're joined towards the fk side)

Otis wrote:

If the fk is non-nullable, an inner join and left join are equal (as they're joined towards the fk side)

Sure, if they were the only two tables in the query but not if there are more as in the SQL 4 post above. i.e. those two queries produce different results.

TomDog wrote:

Otis wrote:

If the fk is non-nullable, an inner join and left join are equal (as they're joined towards the fk side)

Sure, if they were the only two tables in the query but not if there are more as in the SQL 4 post above. i.e. those two queries produce different results.

select * from a left join b

vs.

select * from a inner join b

if a is FK side makes no difference, if fk field is non-nullable, because every 'a' has a 'b' and the a left join b will pull the 'b's belonging to the 'a's in the selection into the resultset.

example: select * from [order details] od inner join [products] p on od.ProductID = p.ProductId

is equal to select * from [order details] od left join [products] p on od.ProductID = p.ProductId

as there can't be a row in od which doesn't have a row in p.

Back to your query: I see where it fails: the FK side is sometimes null, due to the left joins already in the source set. This indeed makes the inner join fail to produce any rows, leading to a different overall resultset.

This is exactly what 'obey weak relations' solves, but which isn't used in the linq provider. In the case you describe, I can understand you'd want this to be happening, as it would bring the resultset you'd want to see. But is that resultset matching the query? I.o.w.: what's the meaning of the query you wrote and which resulted in an inner join at a spot where you didn't like to have one? After all, using that exact same navigator in a simpler select where the inner join is useful, shouldn't that also result in a left join in that case?

Otis wrote:

I can understand you'd want this to be happening, as it would bring the resultset you'd want to see. But is that resultset matching the query? I.o.w.: what's the meaning of the query you wrote and which resulted in an inner join at a spot where you didn't like to have one? After all, using that exact same navigator in a simpler select where the inner join is useful, shouldn't that also result in a left join in that case?

I'm not quite sure what your asking, if your asking how I would like it to behave I guess one of * If the FK side is joined by a left join then the navigator results in a left join otherwise inner join * Simply always left join since, as you say, left join == inner join when FK is non nullable * Have some sort way of telling the navigation to be a left join, maybe with assigning CustomerDemographic to a variable with a let statement and oring with null

TomDog wrote:

Otis wrote:

I can understand you'd want this to be happening, as it would bring the resultset you'd want to see. But is that resultset matching the query? I.o.w.: what's the meaning of the query you wrote and which resulted in an inner join at a spot where you didn't like to have one? After all, using that exact same navigator in a simpler select where the inner join is useful, shouldn't that also result in a left join in that case?

I'm not quite sure what your asking, if your asking how I would like it to behave I guess one of * If the FK side is joined by a left join then the navigator results in a left join otherwise inner join * Simply always left join since, as you say, left join == inner join when FK is non nullable * Have some sort way of telling the navigation to be a left join, maybe with assigning CustomerDemographic to a variable with a let statement and oring with null

I meant: your desired resultset isn't produced with the generated SQL from the query. The question then is: was the SQL generated wrong or was the query simply not the right query for the resultset you desired?

I understand what you mean with the points, there's one problem: we can't always use left joins for non-nullable navigators. What if you wanted to filter out rows which were introduced by LEFT joins due to nullable FK fields ? An inner join does that. Always using left joins in this case therefore limits the scope in which the code can be used (it's also not consistent btw).

The right way to formulate what you want is precisely formulate what you want -> by specifying DefaultOrEmpty() on the navigator to actively force it to become a left join.

Otis wrote:

I meant: your desired resultset isn't produced with the generated SQL from the query. The question then is: was the SQL generated wrong or was the query simply not the right query for the resultset you desired?

Yes that is the question.

Otis wrote:

I understand what you mean with the points, there's one problem: we can't always use left joins for non-nullable navigators. What if you wanted to filter out rows which were introduced by LEFT joins due to nullable FK fields ? An inner join does that. Always using left joins in this case therefore limits the scope in which the code can be used

Can you give me an example of that?

Otis wrote:

(it's also not consistent btw).

Not consistent with what? I've simplified the query a little:

from c in Customer
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerId, ccd.CustomerDemographic.CustomerDesc}

which returns no rows unless I put data into CustomerCustomerDemo, then if you try LINQToSQL in LINQPad:

from c in Customers
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerDemographics.CustomerDesc}

it returns 91 items irrespective of CustomerCustomerDemo data.

Otis wrote:

The right way to formulate what you want is precisely formulate what you want -> by specifying DefaultOrEmpty() on the navigator to actively force it to become a left join.

This is kind of my point, I can't use the navigator in this instance to actively force it to become a left join - I have to do an explicit join - no CustomerDemographic navigator in sight.

from c in Customer
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
join cd in CustomerDemographic on ccd.CustomerTypeId equals cd.CustomerTypeId into customerDemographics
from cd in customerDemographics.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerId, cd.CustomerDesc}

TomDog wrote:

Otis wrote:

I meant: your desired resultset isn't produced with the generated SQL from the query. The question then is: was the SQL generated wrong or was the query simply not the right query for the resultset you desired?

Yes that is the question.

Let me answer it for you: no the query doesn't match the desired SQL. You use a navigator and 'assume' it will be a left join. That's only the case if the fk field is nullable. It's not, so it will be an inner join.

Otis wrote:

I understand what you mean with the points, there's one problem: we can't always use left joins for non-nullable navigators. What if you wanted to filter out rows which were introduced by LEFT joins due to nullable FK fields ? An inner join does that. Always using left joins in this case therefore limits the scope in which the code can be used

Can you give me an example of that?

If you use multiple navigators in a join, and one is nullable, you'll get a left join. If you wanted it to be an inner join, joining it with a normal navigator which results in an inner join solves this.

Otis wrote:

(it's also not consistent btw).

Not consistent with what? I've simplified the query a little:

from c in Customer
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerId, ccd.CustomerDemographic.CustomerDesc}

which returns no rows unless I put data into CustomerCustomerDemo, then if you try LINQToSQL in LINQPad:

from c in Customers
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerDemographics.CustomerDesc}

it returns 91 items irrespective of CustomerCustomerDemo data.

Not consistent with using customerDemoDemographics.CustomerDesc alone in a query, which will be an inner join, not a left join.

Otis wrote:

The right way to formulate what you want is precisely formulate what you want -> by specifying DefaultOrEmpty() on the navigator to actively force it to become a left join.

This is kind of my point, I can't use the navigator in this instance to actively force it to become a left join - I have to do an explicit join - no CustomerDemographic navigator in sight.

from c in Customer
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
join cd in CustomerDemographic on ccd.CustomerTypeId equals cd.CustomerTypeId into customerDemographics
from cd in customerDemographics.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerId, cd.CustomerDesc}

I meant, specifying .DefaultIfEmpty() on ccd.CustomerDemographic.CustomerDesc.

Anyway, it's very simple: if you want a left join, specify a left join: specify in the query what you want, don't assume things. That nullable navigators result in left joins is because of the nullable nature and it makes things 'easy', but most of all because linq to sql does this too and joining in Linq is horribly cumbersome.

Otis wrote:

If you use multiple navigators in a join, and one is nullable, you'll get a left join. If you wanted it to be an inner join, joining it with a normal navigator which results in an inner join solves this.

So would adding 'where entity!=null' to force a result set equivalent to an inner join.

BTW AFAICT LINQToSQL does option 1 from my earlier post.

Otis wrote:

I meant, specifying .DefaultIfEmpty() on ccd.CustomerDemographic.CustomerDesc.

How?

TomDog wrote:

Otis wrote:

If you use multiple navigators in a join, and one is nullable, you'll get a left join. If you wanted it to be an inner join, joining it with a normal navigator which results in an inner join solves this.

So would adding 'where entity!=null' to force a result set equivalent to an inner join.

where filtering isn't the same as on clause filtering. Especially in queries which touch large tables this can have significant impact.

BTW AFAICT LINQToSQL does option 1 from my earlier post.

Hmm... They rework the query? Strange decision IMHO as it can lead to ambiguous queries (as in: the developer can't rely on ground rules what the SQL will become, IMHO).

Otis wrote:

I meant, specifying .DefaultIfEmpty() on ccd.CustomerDemographic.CustomerDesc.

How?

I meant to apply it on the navigator. I now see that's silly as it's a m:1. I meant it in general. The case here indeed is not sufficient, you have to declare an explicit join to make it a left join (which is indeed awkward, but that's due to linq's stupid join syntax).

Otis wrote:

TomDog wrote:

Otis wrote:

If you use multiple navigators in a join, and one is nullable, you'll get a left join. If you wanted it to be an inner join, joining it with a normal navigator which results in an inner join solves this.

So would adding 'where entity!=null' to force a result set equivalent to an inner join.

where filtering isn't the same as on clause filtering. Especially in queries which touch large tables this can have significant impact.

BTW AFAICT LINQToSQL does option 1 from my earlier post.

Hmm... They rework the query? Strange decision IMHO as it can lead to ambiguous queries (as in: the developer can't rely on ground rules what the SQL will become, IMHO).

Seems to

from c in Customers
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerDemographics.CustomerDesc}

remove the DefaultIfEmpty and the ccd.CustomerDemographics becomes an inner. Looks like they went with favouring the most common IMHO use case (If the FK side is joined by a left join then the navigator results in a left join otherwise follow the normal rules over inner vs left) over predictability. Since I favour ease of use over performance or predictability I like their choice better.

I take your point that where filtering doesn't have the same performance as on clause filtering but I think the times where you'd want to turn a left join into inner join equivalent result set would be few and far between.

Be interesting to know what other LINQToRDBMS implementations do.

TomDog wrote:

Otis wrote:

TomDog wrote:

Otis wrote:

If you use multiple navigators in a join, and one is nullable, you'll get a left join. If you wanted it to be an inner join, joining it with a normal navigator which results in an inner join solves this.

So would adding 'where entity!=null' to force a result set equivalent to an inner join.

where filtering isn't the same as on clause filtering. Especially in queries which touch large tables this can have significant impact.

BTW AFAICT LINQToSQL does option 1 from my earlier post.

Hmm... They rework the query? Strange decision IMHO as it can lead to ambiguous queries (as in: the developer can't rely on ground rules what the SQL will become, IMHO).

Seems to

from c in Customers
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerDemographics.CustomerDesc}

remove the DefaultIfEmpty and the ccd.CustomerDemographics becomes an inner. Looks like they went with favouring the most common IMHO use case (If the FK side is joined by a left join then the navigator results in a left join otherwise follow the normal rules over inner vs left) over predictability. Since I favour ease of use over performance or predictability I like their choice better.

Oh, I don't disagree with that, it's just that with a situation like this, a change in this isn't going to make all people happy, as in: it's a situation which is debatable what should be done, and by definition some people favor X, others Y. With a situation which never really works for all of the users, it's clear what should be done: change it. With a situation like this, it's not really doable... I mean: the change isn't that hard, it's the side effects the change has which are not that easy to deal with.

I take your point that where filtering doesn't have the same performance as on clause filtering but I think the times where you'd want to turn a left join into inner join equivalent result set would be few and far between.

Be interesting to know what other LINQToRDBMS implementations do.

Frankly I have no idea....

Otis wrote:

TomDog wrote:

Otis wrote:

[quotenick="TomDog

BTW AFAICT LINQToSQL does option 1 from my earlier post.

Hmm... They rework the query? Strange decision IMHO as it can lead to ambiguous queries (as in: the developer can't rely on ground rules what the SQL will become, IMHO).

Seems to

from c in Customers
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerDemographics.CustomerDesc}

remove the DefaultIfEmpty and the ccd.CustomerDemographics becomes an inner. Looks like they went with favouring the most common IMHO use case (If the FK side is joined by a left join then the navigator results in a left join otherwise follow the normal rules over inner vs left) over predictability. Since I favour ease of use over performance or predictability I like their choice better.

Oh, I don't disagree with that, it's just that with a situation like this, a change in this isn't going to make all people happy, as in: it's a situation which is debatable what should be done, and by definition some people favor X, others Y. With a situation which never really works for all of the users, it's clear what should be done: change it. With a situation like this, it's not really doable... I mean: the change isn't that hard, it's the side effects the change has which are not that easy to deal with.

I take your point that where filtering doesn't have the same performance as on clause filtering but I think the times where you'd want to turn a left join into inner join equivalent result set would be few and far between.

Be interesting to know what other LINQToRDBMS implementations do.

Frankly I have no idea....

I think your seeing my point of view just fine but I'll add a few more points just the same. From my very small survey EF seems to always left join if the FK side is joined I don't know how valid the comparison is but Linq to objects is the same in terms of result set:

var cus = Customer.WithPath(cp => cp.Prefetch<CustomerCustomerDemoEntity>(c => c.CustomerCustomerDemos).SubPath(p => p.Prefetch(c => c.CustomerDemographic))).ToList();
var cusproj = from c in cus
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select
    new
        {
            c.ContactName,
            CustomerId = ccd == null ? null : ccd.CustomerId,           
            CustomerDesc = ccd == null ? null : ccd.CustomerDemographic.CustomerDesc
        };
cusproj.Dump();

i.e only the presence of the DefaultIfEmpty changes the number of rows returned. Which leads me to this hypothesis that the rule of thumb that Microsoft use for their 3 LINQ implementations is that items in the projection never effect the number or rows returned. If that is true I quite like it from a predictability point of view. This is largely some idle theorizing on my part, I don't seriously expect you to change LLBL's behaviour, having said that what side effects of making this change woud you be worried about? - changing the result set of some of your customers queries or something broader than that?

TomDog wrote:

Otis wrote:

TomDog wrote:

Otis wrote:

[quotenick="TomDog

BTW AFAICT LINQToSQL does option 1 from my earlier post.

Hmm... They rework the query? Strange decision IMHO as it can lead to ambiguous queries (as in: the developer can't rely on ground rules what the SQL will become, IMHO).

Seems to

from c in Customers
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select new {c.ContactName, ccd.CustomerDemographics.CustomerDesc}

remove the DefaultIfEmpty and the ccd.CustomerDemographics becomes an inner. Looks like they went with favouring the most common IMHO use case (If the FK side is joined by a left join then the navigator results in a left join otherwise follow the normal rules over inner vs left) over predictability. Since I favour ease of use over performance or predictability I like their choice better.

Oh, I don't disagree with that, it's just that with a situation like this, a change in this isn't going to make all people happy, as in: it's a situation which is debatable what should be done, and by definition some people favor X, others Y. With a situation which never really works for all of the users, it's clear what should be done: change it. With a situation like this, it's not really doable... I mean: the change isn't that hard, it's the side effects the change has which are not that easy to deal with.

I take your point that where filtering doesn't have the same performance as on clause filtering but I think the times where you'd want to turn a left join into inner join equivalent result set would be few and far between.

Be interesting to know what other LINQToRDBMS implementations do.

Frankly I have no idea....

I think your seeing my point of view just fine but I'll add a few more points just the same. From my very small survey EF seems to always left join if the FK side is joined I don't know how valid the comparison is but Linq to objects is the same in terms of result set:

var cus = Customer.WithPath(cp => cp.Prefetch<CustomerCustomerDemoEntity>(c => c.CustomerCustomerDemos).SubPath(p => p.Prefetch(c => c.CustomerDemographic))).ToList();
var cusproj = from c in cus
from ccd in c.CustomerCustomerDemos.DefaultIfEmpty()
select
    new
        {
            c.ContactName,
            CustomerId = ccd == null ? null : ccd.CustomerId,           
            CustomerDesc = ccd == null ? null : ccd.CustomerDemographic.CustomerDesc
        };
cusproj.Dump();

i.e only the presence of the DefaultIfEmpty changes the number of rows returned.

You can't compare linq to objects with linq to <db form>. The behavior can differ (in joins for example).

Like I said, it's (to me) an ambiguous query as in: what SQL will be created isn't straight forward. Changing this will mark a change which will make some queries not possible. These might be rare, but so is this.

Which leads me to this hypothesis that the rule of thumb that Microsoft use for their 3 LINQ implementations is that items in the projection never effect the number or rows returned. If that is true I quite like it from a predictability point of view. This is largely some idle theorizing on my part, I don't seriously expect you to change LLBL's behaviour, having said that what side effects of making this change woud you be worried about? - changing the result set of some of your customers queries or something broader than that?

Changing the query results of some existing queries. This can be a cumbersome affair. Even if we mark it as a breaking change, people might overlook it and it will pop up at runtime, which is a situation we'd like to avoid. I understand your point and I agree that it can for some be more logical if it gets changed, no debate there

I'd have liked it when Microsoft would have documented this but I think they decided this on the fly as well (linq to sql had different behavior than EF with some linq queries for example). It's all water under the bridge now so not much we can do (semantically. The change isn't that big code-wise ).

Frankly I'd like to see a lot of things changed in linq, but alas, they only do that when they need it themselves.

Thanks for your time on this and the feedback