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]