Left join query with EF 6

c# entity-framework-6 sql-server

Question

First, here is the simplified EF model I'm using :

public partial class MaterielRoulant : IKeyed<int>
{
    public MaterielRoulant()
    {
        this.RelationsMaterielRoulantEquipement = new List<RelationMaterielRoulantEquipement>();
    }

    public int Id
    {
        get;
        set;
    }

    public virtual ICollection<RelationMaterielRoulantEquipement> RelationsMaterielRoulantEquipement
    {
        get;
        private set;
    }
}

public partial class RelationMaterielRoulantEquipement : IKeyed<int>
{
    public int EquipementId
    {
        get;
        set;
    }

    public int Id
    {
        get;
        set;
    }

    public int MaterielRoulantId
    {
        get;
        set;
    }

}

public partial class Equipement : IKeyed<int>, IEditable, IArchivable, IAuditable
{
    public int Id
    {
        get;
        set;
    }
}

public partial class Filiation : IKeyed<int>
{
    public int EquipementFilsId
    {
        get;
        set;
    }

    public int EquipementPereId
    {
        get;
        set;
    }

    public int Id
    {
        get;
        set;
    }
}

I have a MaterielRoulan item which is linked to 0 or 1 Equipement. In turn this equipement might have 1 son (another equipement) or not. The SQL query I previously used is :

select * from MaterielRoulant mr
left outer join RelationMaterielRoulantEquipement rmre on mr.Id = rmre.MaterielRoulantId
left outer join Equipement e1 on rmre.EquipementId = e1.Id
left outer join Filiation f1 on e1.Id = f1.EquipementPereId
left outer join Equipement e2 on f1.EquipementFilsId = e2.Id

So my EF6 translation of this query, which doesn't work, is :

var query = DataContext.Set<MaterielRoulant>()
    .Join(DataContext.Set<RelationMaterielRoulantEquipement>().DefaultIfEmpty(), x => x.Id, x => x.MaterielRoulantId, (mr, rmre) => new { mr, rmre })
    .Join(DataContext.Set<Equipement>().DefaultIfEmpty(), x => x.rmre.EquipementId, x => x.Id, (x, e1) => new { x.mr, x.rmre, e1 })
    .Join(DataContext.Set<Filiation>().DefaultIfEmpty(), x => x.e1.Id, e => e.EquipementPereId, (x, f1) => new { x.mr, x.rmre, x.e1, f1 })
    .Join(DataContext.Set<Equipement>().DefaultIfEmpty(), x => x.f1.EquipementFilsId, x => x.Id, (x, e2) => new { x.mr, x.rmre, e2 })

When I start SQL Server Profiler, I see that my query is translated like this :

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM     [dbo].[MaterielRoulant] AS [Extent1]
        INNER JOIN  (SELECT [Extent2].[EquipementId] AS [EquipementId], [Extent2].[MaterielRoulantId] AS [MaterielRoulantId]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
            LEFT OUTER JOIN [dbo].[RelationMaterielRoulantEquipement] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[Id] = [Join1].[MaterielRoulantId]
        INNER JOIN  (SELECT [Extent3].[Id] AS [Id]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable2]
            LEFT OUTER JOIN [dbo].[Equipement] AS [Extent3] ON 1 = 1 ) AS [Join3] ON ([Join1].[EquipementId] = [Join3].[Id]) OR (([Join1].[EquipementId] IS NULL) AND ([Join3].[Id] IS NULL))
        INNER JOIN  (SELECT [Extent4].[EquipementFilsId] AS [EquipementFilsId], [Extent4].[EquipementPereId] AS [EquipementPereId]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable3]
            LEFT OUTER JOIN [dbo].[Filiation] AS [Extent4] ON 1 = 1 ) AS [Join5] ON ([Join3].[Id] = [Join5].[EquipementPereId]) OR (([Join3].[Id] IS NULL) AND ([Join5].[EquipementPereId] IS NULL))
        INNER JOIN  (SELECT [Extent5].[Id] AS [Id]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable4]
            LEFT OUTER JOIN [dbo].[Equipement] AS [Extent5] ON 1 = 1 ) AS [Join7] ON ([Join5].[EquipementFilsId] = [Join7].[Id]) OR (([Join5].[EquipementFilsId] IS NULL) AND ([Join7].[Id] IS NULL))
    )  AS [GroupBy1]

Where it should be :

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM     [dbo].[MaterielRoulant] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[EquipementId] AS [EquipementId], [Extent2].[MaterielRoulantId] AS [MaterielRoulantId]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
            LEFT OUTER JOIN [dbo].[RelationMaterielRoulantEquipement] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[Id] = [Join1].[MaterielRoulantId]
        LEFT OUTER JOIN  (SELECT [Extent3].[Id] AS [Id]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable2]
            LEFT OUTER JOIN [dbo].[Equipement] AS [Extent3] ON 1 = 1 ) AS [Join3] ON ([Join1].[EquipementId] = [Join3].[Id]) OR (([Join1].[EquipementId] IS NULL) AND ([Join3].[Id] IS NULL))
        LEFT OUTER JOIN  (SELECT [Extent4].[EquipementFilsId] AS [EquipementFilsId], [Extent4].[EquipementPereId] AS [EquipementPereId]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable3]
            LEFT OUTER JOIN [dbo].[Filiation] AS [Extent4] ON 1 = 1 ) AS [Join5] ON ([Join3].[Id] = [Join5].[EquipementPereId]) OR (([Join3].[Id] IS NULL) AND ([Join5].[EquipementPereId] IS NULL))
        LEFT OUTER JOIN  (SELECT [Extent5].[Id] AS [Id]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable4]
            LEFT OUTER JOIN [dbo].[Equipement] AS [Extent5] ON 1 = 1 ) AS [Join7] ON ([Join5].[EquipementFilsId] = [Join7].[Id]) OR (([Join5].[EquipementFilsId] IS NULL) AND ([Join7].[Id] IS NULL))
    )  AS [GroupBy1]

Inner join replaced by outer join.

I have tried using GroupJoin, which still give me the wrong answer with a much more complicated query. I would like to keep the lambda writing as it's used everywhere in my application but at this point any working solution would be great.

Thanks in advance

1
2
8/11/2015 9:04:30 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

You can write it like this:

var q = 
    from mr in db.MaterielRoulant
    from rmre in db.RelationMaterielRoulantEquipement.Where(x => mr.Id == x.MaterielRoulantId).DefaultIfEmpty()
    from e1 in db.Equipement.Where(x => rmre.EquipementId == x.Id).DefaultIfEmpty()
    from f1 in db.Filiation.Where(x => e1.Id == x.EquipementPereId).DefaultIfEmpty()
    from e2 in db.Equipement.Where(x => f1.EquipementFilsId == x.Id).DefaultIfEmpty()
    select new {...}

For expression syntax it'll look like this (for the first 2 joins)

db.MaterielRoulant
   .SelectMany(
      mr => 
         db.RelationMaterielRoulantEquipement
            .Where (x => mr.Id == x.MaterielRoulantId)
            .DefaultIfEmpty(), 
      (mr, rmre) => 
         new  
         {
            mr = mr, 
            rmre = rmre
         }
   )
   .SelectMany(
      temp => 
         db.Equipement
            .Where (x => rmre.EquipementId == x.Id)
            .DefaultIfEmpty(), 
      (temp, e1) =>  
        new  
        {
          mr = temp.mr, 
          rmre = temp.rmre, 
          e1 = e1
       }
   )...
1
8/11/2015 10:48:23 AM

Related Questions

How to specify left join in EF?

You should use ...GroupJoin... for that....Have a look at the example:...var customers = db.Customer .GroupJoin(db.SpecialCustomer, c => c.ID, g => g.CustomerId, (f, b) => new { f, b }) .SelectMany(z => z.b.DefaultIfEmpty(), (z, g) => new { z, g });
c# entity-framework orm
asked by Asif Mushtaq

LEFT JOIN or RIGHT JOIN using LINQ Entity-Framework

Updated for the error: ...You will need to create a new class with the required properties and return it.... var applicantList = (from app in context.APPLICANTs join a in context.Profiles on app.Profile_id equals a.PROFILE_ID into output from j in output.DefaultIfEmpty() ...
asp.net asp.net-mvc c# entity-framework linq
asked by cheedep

how to use left join in entity framework query?

Your should use ...DefaultIfEmpty... method, which returns the elements of an ...IEnumerable<T>..., or a default valued singleton collection if the sequence is empty:...from I in db.Invoices join O in db.Orders on I.InvoiceId equals O.InvoiceId join C in db.Customers on I.CustId equals C.CustId join OD in db.OrderDriverExtraCharges on O.OrderNumber...
asp.net-mvc c# entity-framework linq
asked by alexmac

Entity Framework left join on multiple tables creates inner join instead

The ...INNER JOINS... represent required n:1 associations. If you had navigation properties, the join would be a reference to another, required, entity, not a collection. EF knows from the data model that an ...OUTER JOIN... would have no effect here, so it generates an ...INNER JOIN....
c# entity-framework entity-framework-6 linq
asked by Gert Arnold

Entity Framework forcing Inner Join when Left Join DefaultIfEmpty() Syntax used

My mistake. I was wrapping this query in a function called GetEmployees() which returned an iQueryable list, and was filtering it:...results = HRFunctions.GetEmployees().Where(x => x.ad != null); ...Because there was a WHERE clause added that assumed AD should not be null, EntityFramework intelligently altered the LEFT JOIN to an INNER JOIN....Afte...
c# entity-framework-6 join linq-to-entities oracle
asked by James

How to do a left outer join in Entity Framework without using the query syntax?

You generally could get the exact expression used from any query expression by accessing the ...Expression... property of the query. Then just analyze that expression and reproduce it....var expr = products.Expression; ...On the other hand, every expression using the query syntax has a straight forward translation. Joins with the ...into... part ...
c# entity-framework linq
asked by Jeff Mercado

EF: How to execute a SQL-query with multiple joins?

Have you seen checked the ...Data Development Center... for ways to do this? ...Note '...Entity Framework allows you to query using LINQ with your entity classes. However, there may be times that you want to run queries using raw SQL directly against the database....' ...So if you can, use LINQ. Check ...here... and ...here... to start.
asp.net-mvc c# entity-framework-6 sql
asked by dpimente

EF - WithOptional - Left Outer Join?

This is the standard behavior for one-to-one FK association and cannot be avoided. What EF does is to maintain internally a hidden (shadow) property like ...int? BarId... for the ...Foo... entity....The only way to get rid of ...LEFT OUTER JOIN...s and keep bidirectional navigation properties is if you ...can afford changing... the ...Bar... db mod...
c# entity-framework entity-framework-6
asked by Ivan Stoev

linq left join with filtering in joined

Two Way solve Solution...Remove Where Case...Add where Case In Join table...Show for Where Case Blow Linq Query...first Query...IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS join ml in this.dbEntity.HOT_MEALS_PRICE.where(c=>c.HOTID==hotelId) on m.MEALSID equals ml.MEALSID into mls from mls1 in mls.DefaultIfEmpty() sel...
c# entity-framework-6 linq
asked by kari kalan

EF Core 2.1, force SelectMany to produce LEFT JOIN

One thing stands out, and should be checked in the real code:....Where(x => x.Student == null || !x.Student.IsDeleted && x.Student.MyStoreId == storeId) ...Should this be:....Where(x => x.Student == null || (!x.Student.IsDeleted && x.Student.MyStoreId == storeId)) ...Loose conditions like this may trip up EF on the condition x.Student.MyStoreId reg...
ef-fluent-api entity-framework entity-framework-6 entity-framework-core linq
asked by Steve Py

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow