As part of my continuing efforts(See Inconsistent Inner Join ) to left join using navigators I experimented trying to left join with a many to many navigator:

from c in Customer
from cd in c.CustomerDemographics.DefaultIfEmpty()
select new {c.ContactName, c.CustomerId, cd.CustomerDesc}

Where CustomerDemographics is a m:n relationship between Customer and CustomerDemographics.

The resulting SQL is:

SELECT [LPA_L1].[ContactName],
  [LPA_L1].[CustomerID] AS [CustomerId],
  [LPA_L3].[CustomerDesc]
FROM
 (( [dbo].[Customers] [LPA_L1] 
INNER JOIN
 [dbo].[CustomerCustomerDemo] [LPA_C2]  ON  [LPA_L1].[CustomerID]=[LPA_C2].[CustomerID])
LEFT JOIN
 [dbo].[CustomerDemographics] [LPA_L3]  ON  [LPA_L3].[CustomerTypeID]=[LPA_C2].[CustomerTypeID])

Which unfortunately for me has a INNER JOIN between Customers and CustomerCustomerDemo which I find weird given that I have specified DefaultIfEmpty.

Using NorthWind and LLBL v3.1.

Which runtime library version (build no.) are you using?

Walaa wrote:

Which runtime library version (build no.) are you using?

3.1.11.225

'DefaultIfEmpty' is not a directive to get a left join, it's a directive to deal with a sequence which can contain nulls. It's translated to LEFT joins by linq to <db> providers as there's no other way to specify left joins. As the navigator is a m:n relationship, it's not a join over a single relationship but over two relationships. The query generated actually has a left join, and does obey what defaultifempty means: it can contain nulls.

This is of course not what you 'really' meant with the defaultifempty, but it's the same problem as with the thread you linked to: the directive has to be set on more than one relationship, but it's unclear at the point when this happens which relationships to alter: the one formed by the two from clauses is the one at hand and which is reformed to a relationship, but the rest is added to that, adding the other m:n relationship as well, but that could very well be from another navigator hop, that's unknown: the provider doesn't know whether a property is a m:n property or not, it is converted to an entity with relationship/s.

So long story short, we can't fix this.