How to perform left outer join in Linq

Question

I have 3 table with following data & structure:

Table Modules:

Id          Modulename
----------------------
1           Corp
2           Local
3           Medium

Table RuleChange:

Id      CanDrop     CanLoad     SensorId
----------------------------------------
10      yes         No          113
11      Yes         No          113
12      No          Yes         113
12      No          Yes         114

Table Rules:

Id      Message     ModuleId
----------------------------
10      Xyz         1
11      CVV         0
12      RTV         2
13      LBL         2

I need to perform a left outer join on 3 tables Rules, Modules, RuleChange along with a where clause

Task: list all the rules along with its RuleChange data and module name.

SensorId=113

Expected output:

Id      Message     Modulename      CanLoad     CanDrop
-------------------------------------------------------
10      Xyz          Corp               Yes      No
11      CVV          Null               No       Yes     
12      RTV          Local              Yes      No
13      LBL          Local              Null     Null

What I tried:

var query = from sr in _context.Rules
            join m in (
                       (from md in _context.Modules
                        select new { md.Id, md.ModuleName })) on sr.ModuleId equals m.Id into moduleRule
                        from m in moduleRule.DefaultIfEmpty()
                        join t in (
                            (from e in _context.RuleChanges
                             where
         e.SensorId == sensorId
                             select new
                             {
                                 e.Sid,
                                 e.CanLoad,
                                 e.Drop
                             })) on sr.Sid equals t.Sid into RuleDiff
                        from t in RuleDiff.DefaultIfEmpty()
                        select new
                        {
                            sr.Sid,
                            sr.Message,
                            CanLoad = t.CanLoad,
                            Drop = t.Drop,
                            sr.ModuleId,
                            ModuleName = m.ModuleName
                        };

var result = query.ToList();

However, I get this error:

NullReferenceException: Object reference not set to an instance of an object..

I think it's due to a null key in join operation.. but I failed to figure out that key.

I noticed that EF generates a SQL query and it can produce the desired output:

SELECT 
    [t].[Id], [t].[ModuleName], 
    [t0].[Sid], [t0].[CanLoad], [t0].[Drop], 
    [sr].[Sid] AS [Sid0], [sr].[Message], [sr].[ModuleId]
FROM
    [SuricataRules] AS [sr]
LEFT JOIN 
    (SELECT 
         [md].[Id], [md].[ModuleName]
     FROM 
         [Modules] AS [md]) AS [t] ON [sr].[ModuleId] = [t].[Id]
LEFT JOIN 
    (SELECT 
         [e].[Sid], [e].[CanLoad], [e].[Drop]
     FROM 
         [RuleChanges] AS [e]
     WHERE 
         [e].[SensorId] = @__sensorId_0) AS [t0] ON [sr].[Sid] = [t0].[Sid]

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — user10089632, Sep 01 '17 at 05:00
i think its due to a null key in join operation.. but i am failed to figure out that key .. — Binson Eldhose, Sep 01 '17 at 05:06
I think that problem is you join Rule with ModuleId = 0 on Module which give you null and in finally select you use m.ModuleName — Jaroslav Surala, Sep 01 '17 at 05:06
@JaroslavSurala.. how can re-write the the query that select a null value if moduleId relationship not available in table (i think Left outer join is using for that) — Binson Eldhose, Sep 01 '17 at 05:09
@BinsonEldhose yes that is correct, in finally select use m?.ModuleName instead or you can use m?.Modulename ?? "something that you want" — Jaroslav Surala, Sep 01 '17 at 05:10
I tried Id= md.Id==null?0:md.Id, ModuleName= md.ModuleName == null ? "N/A":md.ModuleName but no luck — Binson Eldhose, Sep 01 '17 at 05:12
@BinsonEldhose Do you tried also this checks in other fields in finally select? Because if your inpunt will be sensorid = 13 you will have RuleDiff null so in finally select is t?.Canload, t?.Drop — Jaroslav Surala, Sep 01 '17 at 05:18
@JaroslavSurala yes now i applied null guard for every fields.. — Binson Eldhose, Sep 01 '17 at 05:20
It's worth clarifying the actual ORM - LINQ to SQL is not the same as Entity Framework. Also EF Core is not the same as EF6 and below. Looking at the generated SQL query, it's not EF6, which means it's probably EF Core, hence it could be a EFC bug. In order to identify the issue, specify the exact ORM and version. — Ivan Stoev, Sep 01 '17 at 07:32
Ok. But the query is overcomplicated (with proper navigation properties it should be trivial) and it's hard to tell what exactly the problem is without having the entity model. Please post the relevant entity model (classes and fluent configuration if any). — Ivan Stoev, Sep 01 '17 at 08:21

Andriy Tolstoy · Accepted Answer · 2017-09-01T13:01:07.910

In this case the access to CanLoad property CanLoad = t.CanLoad, causes the NullReferenceException exception. So use the null-conditional operator to check it:

select new
{
    sr.Sid,
    sr.Message,
    CanLoad = t?.CanLoad,
    Drop = t?.Drop,
    sr.ModuleId,
    ModuleName = m?.ModuleName
};

Using method syntax:

rules
    .GroupJoin(modules, _ => _.ModuleId, _ => _.Id, (r, ms) => new { r, ms })
    .SelectMany(_ => _.ms.DefaultIfEmpty().Select(m => new { _.r, m }))
    .GroupJoin(ruleChanges, _ => _.r.Id, _ => _.Id, (rm, rc) => new { rm, rc })
    .SelectMany(_ => _.rc.DefaultIfEmpty().Select(rc => new {_.rm, rc}))
    .Select(_ => new 
    { 
        Id = _.rm.r.Id, 
        Message = _.rm.r.Message, 
        Modulename = _.rm.m?.Modulename, 
        CanLoad = _.rc?.CanLoad, 
        CanDrop = _.rc?.CanDrop
    });

score 0 · Answer 2 · answered Sep 01 '17 at 06:25

Can you write the query in SQL?

I also get confused something with linq so this is what I do when things get too complicated!

I will write/test my query in sql and then paste the query in the "Database.SQLQuery" method with entity framework. You can modify the below snippet to your needs. In this case I am returning only the IDClient in a int List

            using (var _context = new DB.Entities())
            {
                var IDClients = _context.Database.SqlQuery<int>("select distinct idclient FROM dbo.ReportClient LEFT OUTER JOIN dbo.ReportClientDocument ON dbo.ReportClient.IDClient = dbo.ReportClientDocument.IDClient");
                foreach (var IDClient in IDClients)
                {
                    // process the row here
                }
            }

How to perform left outer join in Linq

2 Answers2