C# linq Perform join with default value if join returns null

Question

I am trying to perform a join and I want default value if join returns null.

My code is :

MyVar = from parent in db.ParentTable
        join child1 in db.Child1 on parent.Id equals child1.ParentId into j1

        select new ParentClass
        {
          Id = Parent.Id,
          Name = Parent.Name,
          Children = from child1 in db.Child1
                     join rm in db.Table1 on Child1.ID equals rm.ChildId
                     where Child1.ParentId == parent.Id && rm.OperationId == Op.ID
                     select new Child
                     {
                      Name = child1.Name,
                      ID = child1.ID,
                      Flag = rm.Value
                     }
        }
        }).FirstOrDefault();

structure of db is : Parent table :

Id | Name
1  | A
2  | B

Child1 table :

ParentId | Id | Name
1        | 21 | A1
1        | 22 | A2
2        | 23 | A3
1        | 24 | A4

Table1 :

Id | Child1Id | Value | OpID
1  | 21       | False | 123
2  | 21       | False | 124
3  | 21       | true  | 125
4  | 22       | true  | 126

and so on..

Note : Multiple entries in Child1 can have same Parent and multiple entries in Table1 can have same Child1Id.

Question : If Children's join returns empty I want show my custom Object like this :

{
  Name = child1.Name,
  ID =   child1.ID,
  Flag = False
 }

This must be a default object if join returns empty array and should have child1.Name and ID. How Do I achieve that?

Looks like left-outer-join. If an element in the left collection has no matching element in the right collection, use a default element as match. See https://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Harald Coppoolse, Sep 12 '17 at 11:51

Gilad Green · Accepted Answer · 2017-09-12T12:20:59.857

1

You need to use a left join and in addition to add to the joining condition also the where clase of Op.ID:

Children = from child1 in db.Child1
           where child1.ParentId == parent.Id
           join rm in db.Table1 on new { child1.ID, OpId = Op.ID } equals new { rm.ChildId, rm.OperationId } into joint
           from rm in joint.DefaultIfEmpty()
           select new Child
           {
               Name = child1.Name,
               ID = child1.ID,
               Flag = rm == null ? false : rm.Value
           }

edited Sep 12 '17 at 12:20

answered Sep 12 '17 at 12:02

Gilad Green

36,708
7
61
95

Thanks for the reply, it gives syntax error on `Op.ID` on line 'join rm in db.Table1 on new { child1.ID, Op.ID }' saying, `An anonymous type cannot have multiple properties with the same name` – NotABot Sep 12 '17 at 12:20
@thewarri0r9 - see updated. Didn't notice the names were the same – Gilad Green Sep 12 '17 at 12:21
You sir are awesome !! Thanks – NotABot Sep 12 '17 at 12:31
@thewarri0r9 - glad it helped :) – Gilad Green Sep 12 '17 at 12:31

C# linq Perform join with default value if join returns null

1 Answers1