Joining returning null if one of the column is null

Question

I have two tables, Parts and Blob.

db.Parts

Id   name   x    y   z  partimageid
-----------------------------------
1    bolt   30   40  5       4 
2    screw  33   4   6     null

db.Blob

Id   content      name
------------------------------
4    fsbfvb       picture.png

How can I get a part detail with image content?

Currently I am doing like this:

public async Task<IHttpActionResult> GetPart(int id)
{    
    var result = from part in db.Parts
                 join image in db.Blob on part.PartImageId equals image.Id
                 where part.Id == id
                 select new { part, image.Content};
    return Ok(result);
}

It's working, but if image is not there - part also coming as null. what am I doing wrong here?

Use the example shown in the answers : https://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join Inner join returns null if one of the rows is null. — Emre Savcı, Jun 20 '18 at 05:43

score 0 · Accepted Answer · answered Jun 20 '18 at 06:08

Your LINQ statement is executed as an INNER JOIN. If you want a LEFT JOIN then you could modify it to something like this:

var result = from part in db.Parts
                 join image in db.Blob on part.PartImageId equals image.Id into images
                 from image in images.DefaultIfEmpty()
                 where part.Id == id
                 select new { part, image.Content};

Joining returning null if one of the column is null

1 Answers1