Left outer join in C# LINQ

c# entity-framework entity-framework-6 linq

Question

LINQ query

from rc in context.RC
join r in context.R on rc.RId equals r.Id 
join c in context.C on rc.CId equals c.Id 
join f in context.F on rc.FId equals f.Id into fg
from f in fg.DefaultIfEmpty()
join e in context.E on rc.EId equals e.Id into eg
from e in eg.DefaultIfEmpty()

Generated SQL is

SELECT  *
FROM    [dbo].[RC] AS [Extent1]
        INNER JOIN [dbo].[R] AS [Extent2] ON [Extent1].[RId] = [Extent2].[Id]
        INNER JOIN [dbo].[C] AS [Extent3] ON [Extent1].[CId] = [Extent3].[Id]
        INNER JOIN [dbo].[F] AS [Extent4] ON [Extent1].[FId] = [Extent4].[Id]
        INNER JOIN [dbo].[E] AS [Extent5] ON [Extent1].[EId] = [Extent5].[Id]

What I want to get is

SELECT  *
FROM    [dbo].[RC] AS [Extent1]
        INNER JOIN [dbo].[R] AS [Extent2] ON [Extent1].[RId] = [Extent2].[Id]
        INNER JOIN [dbo].[C] AS [Extent3] ON [Extent1].[CId] = [Extent3].[Id]
        LEFT JOIN [dbo].[F] AS [Extent4] ON [Extent1].[FId] = [Extent4].[Id]
        LEFT JOIN [dbo].[E] AS [Extent5] ON [Extent1].[EId] = [Extent5].[Id]

Not sure what am I missing in my LINQ query

Screen Shot of the code

enter image description here

Update

Based upon comment by @GertArnold, RC-F (tables) and RC-E (tables) are in N-1 association, where RC should hold a valid id key reference for F, but in RC table, some F ids are zeros, which is skipping data when I do inner join.

So now how can I force Entity-Framework/LINQ to generate LEFT JOIN syntax without modifying my DB schema

1
1
11/22/2016 2:02:25 PM

Accepted Answer

Based upon comment by @GertArnold, RC-F (tables) and RC-E (tables) are in N-1 association, where RC should hold a valid id key reference for F, but in RC table, some F ids are zeros, which is skipping data when I do inner join.

This is how I ended up querying the results.

            List<MyType> myResults;
            using (Entities context = new Entities())
            {
                var results = (from rc in context.RC
                    join r in context.R on rc.RId equals r.Id
                    join c in context.C on rc.CId equals c.Id
                    select rc).ToList();

                myResults = results.Select(rc => new MyType
                {
                    Id = rc.Id,
                    Rule = new IdName
                    {
                        Id = rc.R.Id,
                        Name = rc.R.Name
                    },
                    Conjuction = new IdName
                    {
                        Id = rc.C.Id,
                        Name = rc.C.Conjuncation
                    },
                    Field = new IdName
                    {
                        Id = rc.F!= null ? rc.F.Id : 0,
                        Name = rc.F!= null ? rc.F.Name : null
                    },
                    Expression = new IdName
                    {
                        Id = rc.E!= null ? rc.E.Id : 0,
                        Name = rc.E!= null ? rc.E.Expression : null
                    },
                    DisplayOrder = rc.Order,
                    Value1 = rc.Value,
                    Value2 = rc.Value2
                }).ToList();
            }
0
11/22/2016 2:32:18 PM


Related Questions





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