My problem is to extract distinct PartId with another 4 different columns type boolean just like the SQL query does. Picture 1 presents output what I desire. And the question is how can I solve the same problem using LINQ and EF Core if we assume that we use dBcontext as default. What is more assume that object ApprovalNumberRecord is DbSet and has navigation to Part and Approval object.

SELECT
    B.PartId
    ,Sum(Case when B.ApprovalName = 'R-67' then 1 Else 0 End) as [R-67]
    ,Sum(Case when B.ApprovalName = 'R115' then 1  Else 0 End) as [R115]
    ,Sum(Case when B.ApprovalName = 'LPG-D' then 1  Else 0 End) as [LPG-D]
    ,Sum(Case when B.ApprovalName = 'CNG-D' then 1  Else 0 End) as [CNG-D]
FROM
    (SELECT 
         anr.PartId
         ,a.ApprovalName
         ,anr.IsVisible
         ,p.[Description]
     FROM 
         ApprovalNumberRecord AS anr
     LEFT JOIN
         Parts AS p ON anr.PartId = p.Id
     LEFT JOIN
         Approvals AS a ON anr.ApprovalId = a.Id) AS B 
GROUP BY
    B.PartId

Output of SQL statement

And this is the inner SELECT output:

OUTPUT of inner SQL statement

Underneath database models:

ApprovalNumberRecord class:

... //other fields and properties

public Part Part { get; set; }
public Approval Approval { get; set; }

Part class:

public Producer Producer{ get; set; }
public Category Category { get; set; }
public ICollection<ApprovalNumberRecord> ApprovalNumberRecords { get; set; }

Approval class:

none navigation objects inside

CodePudding user response：

var innerQuery =
  from anr in dbContext.ApprovalNumberRecords
  let p = anr.Part
  let a = anr.Approval
  select new { anr.PartId, a?.ApprovalName, anr.IsVisible, p?.Description };

var groupQuery =
  from B in innerQuery
  group B by B.PartId into g
  select new {
    PartId = g.Key,
    R_67 = g.Sum(x => x.ApprovalName == 'R-67' ? 1 : 0),
    R115 = g.Sum(x => x.ApprovalName == 'R111' ? 1 : 0),
    LPG_D = g.Sum(x => x.ApprovalName == 'LPG-D' ? 1 : 0),
    CNG_D = g.Sum(x => x.ApprovalName == 'CNG-D' ? 1 : 0)
  };