set default value for nulls in result of a linq join

Question

when we have two tables with one to zero or one relationship. there might be records in tableA with no corresponding record in tableB.
So the Join Operation results null values for these records. My Goal is to replace these Nulls with a defalut value.
My Question is How can we do this in Database Context And in C# Context(when we have standard c# collections instead of db tables) using linq?

Example Tables :

  tableA{id , name , info}
  tableB{id , tableAid , number}

Query:

var result= from a in tableA join b in tableB on b.tableAid equals a.id 
            select new {id = a.id , name = a.name , number = b.number }

number in the result may be null If there is no record in tableB for a specific record in tableA.
i need such a thing:

if(result[i].number==null)result[i].numer=1

Can you post more details? Tables, linq query, ... – Mivaweb Jun 24 '15 at 06:18 — Mivaweb, Jun 24 '15 at 06:18

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

You need to use left join in linq for your scenario, After that @Patrick Hofman's code will work

var result= from a in tableA join b in tableB on b.tableAid equals a.id 
            into grp from c in grp.DefaultIfEmpty()
            select new {id = a.id , name = a.name , number = c != null ? (c.number ?? 0) : 0 }

edited May 23 '17 at 12:14

Community

1
1

answered Jun 24 '15 at 07:02

Palanikumar

6,940
4
40
51

it worked, thanks. more explanation please , how grouping in this situation works?? – Hamed Jun 24 '15 at 07:54
@hamednasari, You can find better explanation here https://msdn.microsoft.com/en-us/library/bb397895.aspx – Palanikumar Jun 24 '15 at 08:24

score 1 · Answer 2 · answered Jun 24 '15 at 06:33

1

If b is null, since it can't be found on the other end, you can use a null check with the ternary operator, possibly combined with a null-coalescing operator for b.number:

number = b != null ? (b.number ?? 0) : 0;

answered Jun 24 '15 at 06:33

Patrick Hofman

153,850
22
249
325

it sounds simpler than [this possible](http://stackoverflow.com/questions/16333068/how-do-i-add-default-null-values-in-a-linq-join-statement#16333956) approach.i guess no need to ?? – Hamed Jun 24 '15 at 06:48
I guess not. Did you try? – Patrick Hofman Jun 24 '15 at 06:50
It doesnt work,since it can't be found,it is not even available in join result to be checked!! – Hamed Jun 24 '15 at 07:25
@hamednaseri: True, the other answer fixes that. I just focussed on your actual question and not the bug in your query. – Patrick Hofman Jun 24 '15 at 07:26

set default value for nulls in result of a linq join

2 Answers2