Joining two tables with LINQ while also returning null records from the second table

Question

I have tables Message and Image that I'm joining. The tables look like this:

Message(MessageID, TimeStamp, Text, RoomID, ImageID, UserID)
Image(ImageID, Path, Type, UserID)

Not all messages will have an ImageID. Here's my current join:

List<Message> messages = Message.GetAll();
List<Image> images = Image.GetAll();

var resultTable = from m in messages 
    join i in images 
    on m.ImageID equals i.ImageID
    select new
    {
        MessageID = m.MessageID,
        TimeStamp = m.TimeStamp,
        Text = m.Text,
        RoomID = m.RoomID,
        ImageID = m.ImageID,
        UserID = m.UserID,
        Path = i.Path // Nullable
    };

I then bind resultTable to a ListView that needs the Path column from the Image table. My current join only returns messages with images. How would I select all messages, but if the message has an ImageID != null, then assign it a value for Path? I assume I should change this line: on m.ImageID equals i.ImageID at minimum.

Answer 1

You're currently doing an inner join, but you can use DefaultIfEmpty() to create a left outer join. This will also return the null records.

var resultTable = from m in messages 
    join i in images on m.ImageID equals i.ImageID into imgJoin
    from img in imgJoin.DefaultIfEmpty()
    select new
    {
        MessageID = m.MessageID,
        TimeStamp = m.TimeStamp,
        Text = m.Text,
        RoomID = m.RoomID,
        ImageID = m.ImageID,
        UserID = m.UserID,
        Path = img != null ? img.Path : ""
    };