Generate FULL JOIN with LinqToSQL

Question

I have this linq query :

(from  rapportBase in New_RapportReferencementBases
join rapportExtensionAll in New_RapportReferencementExtensionBases on rapportBase.New_RapportReferencementId equals rapportExtensionAll.New_RapportReferencementId into jointureRapportExtension
from rapportExtension in jointureRapportExtension.DefaultIfEmpty()

join packExtensionAll in New_PackExtensionBases on rapportExtension.New_PackId equals packExtensionAll.New_PackId into jointurePackExtension
from packExtension in jointurePackExtension.DefaultIfEmpty()
join packBaseAll in New_PackBases on packExtension.New_PackId equals packBaseAll.New_PackId into jointurePackBase
from packBase in jointurePackBase.DefaultIfEmpty()

join domaineBaseAll in New_DomaineBases on packExtension.New_DomaineId equals domaineBaseAll.New_DomaineId into jointureDomaineBase
from domaineBase in jointureDomaineBase.DefaultIfEmpty()
join domaineExtensionAll in New_DomaineExtensionBases on domaineBase.New_DomaineId equals domaineExtensionAll.New_DomaineId into jointureDomaineExtension
from domaineExtension in jointureDomaineExtension.DefaultIfEmpty()  

join compteBaseAll in AccountBases on domaineExtension.New_AccountId equals compteBaseAll.AccountId into jointureCompteBase
from compteBase in jointureCompteBase.DefaultIfEmpty()
join compteExtensionAll in AccountExtensionBases on compteBase.AccountId equals compteExtensionAll.AccountId into jointureCompteExtension
from compteExtension in jointureCompteExtension.DefaultIfEmpty()   
select rapportBase)

which generate :

SELECT [t0].[New_RapportReferencementId], [t0].[CreatedOn], [t0].[CreatedBy], [t0].[ModifiedOn], [t0].[ModifiedBy], [t0].[OwningUser], [t0].[OwningBusinessUnit], [t0].[statecode] AS [Statecode], [t0].[statuscode] AS [Statuscode], [t0].[DeletionStateCode], [t0].[VersionNumber], [t0].[ImportSequenceNumber], [t0].[OverriddenCreatedOn], [t0].[TimeZoneRuleVersionNumber], [t0].[UTCConversionTimeZoneCode]
FROM [New_RapportReferencementBase] AS [t0]
LEFT OUTER JOIN [New_RapportReferencementExtensionBase] AS [t1] ON [t0].[New_RapportReferencementId] = [t1].[New_RapportReferencementId]
LEFT OUTER JOIN [New_PackExtensionBase] AS [t2] ON [t1].[New_PackId] = ([t2].[New_PackId])
LEFT OUTER JOIN [New_PackBase] AS [t3] ON [t2].[New_PackId] = [t3].[New_PackId]
LEFT OUTER JOIN [New_DomaineBase] AS [t4] ON [t2].[New_DomaineId] = ([t4].[New_DomaineId])
LEFT OUTER JOIN [New_DomaineExtensionBase] AS [t5] ON [t4].[New_DomaineId] = [t5].[New_DomaineId]
LEFT OUTER JOIN [AccountBase] AS [t6] ON [t5].[New_AccountId] = ([t6].[AccountId])
LEFT OUTER JOIN [AccountExtensionBase] AS [t7] ON [t6].[AccountId] = [t7].[AccountId]

But I want to generate :

SELECT [t0].[New_RapportReferencementId], [t0].[CreatedOn], [t0].[CreatedBy], [t0].[ModifiedOn], [t0].[ModifiedBy], [t0].[OwningUser], [t0].[OwningBusinessUnit], [t0].[statecode] AS [Statecode], [t0].[statuscode] AS [Statuscode], [t0].[DeletionStateCode], [t0].[VersionNumber], [t0].[ImportSequenceNumber], [t0].[OverriddenCreatedOn], [t0].[TimeZoneRuleVersionNumber], [t0].[UTCConversionTimeZoneCode]
FROM [New_RapportReferencementBase] AS [t0]
FULL OUTER JOIN [New_RapportReferencementExtensionBase] AS [t1] ON [t0].[New_RapportReferencementId] = [t1].[New_RapportReferencementId]
FULL OUTER JOIN [New_PackExtensionBase] AS [t2] ON [t1].[New_PackId] = ([t2].[New_PackId])
FULL OUTER JOIN [New_PackBase] AS [t3] ON [t2].[New_PackId] = [t3].[New_PackId]
FULL OUTER JOIN [New_DomaineBase] AS [t4] ON [t2].[New_DomaineId] = ([t4].[New_DomaineId])
FULL OUTER JOIN [New_DomaineExtensionBase] AS [t5] ON [t4].[New_DomaineId] = [t5].[New_DomaineId]
FULL OUTER JOIN [AccountBase] AS [t6] ON [t5].[New_AccountId] = ([t6].[AccountId])
FULL OUTER JOIN [AccountExtensionBase] AS [t7] ON [t6].[AccountId] = [t7].[AccountId]

In other word, i want to generate full outer join for this query and not just left.

someone know how to do this in a simple way ?

thanks

Answer 1

See this post from the VB Team:

http://blogs.msdn.com/vbteam/archive/2008/02/12/converting-sql-to-linq-part-9-full-outer-join-bill-horst.aspx

Looks a little verbose.

Otherwise, there may be a way to achieve what you want using Foreign Keys and the Union operator.

Answer 2

There isn't a Full Outer Join in Linq. You have to do two left joins and concat them together. Here's some pseudocode that looks like linq:

var foj =
    (from l in left
    join r in right on l.Id equals r.Id into g
    from r in g.DefaultIfEmpty()
    select new { l, r })
    .Concat
    (from r in right
    join l in left on r.Id equals l.Id into g
    from l in g.DefaultIfEmpty()
    where l == null
    select new { l, r });

Probably be better to push this logic into a stored procedure if you're planning on using Linq to Sql.

Answer 3

Sadly I saw that before but i wanted to be sure.

The thing we did is to do it in a stored procedure and access it via linqtosql method.

then we map table result, like linqtosql do, using grouping to obtain collection of enties link to another entity.

for example, if we have an account with some contacts link to it. We request all contact with for each of them the account with a full join and them group on account id to obtain list of contact of each.

It work very well but it would be better if linqtosql was able to generate full join....