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]

Answer 1

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
    });

Answer 2

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
                }
            }