Conditional Join In LINQ?

Question

I am trying to write a query that grabs information from one database and joins it to information in a different database.

TableA
idA
valueA
idB

TableB
idB
valueB

The tricky part is that in TableA, idB isn't always defined, so when I do a normal join, I only get results where TableA has a idB value. What I want is to be able to grab all of the information from TableA even if it doesn't have a corresponding idB value.

Answer 1

Here is a query expression syntax version of the left join to follow up on tvanfosson's answer.

var query = from rowA in db.TableA
            join rowB in db.TableB
            on rowA.idB equals rowB.idB into b
            from item in b.DefaultIfEmpty()
            select new
            {
                idA = rowA.idA,
                valueA = rowA.valueA,
                idB = rowA.idB, 
                valueB = item != null ? item.valueB : 0 // or other default value
            };