LINQ to SQL -Get All Records from Both Collections

I have a LINQ anonymous collection called ProgramRevisions:

RevisionID     RevisionName
1              Government
2              开发者_开发百科Business
3              Public Sector

I also have an anonymous collection called Expenditures

ExpDisplay     ExpSort      ExpProgramRevID
N/A            0001         1
Water/Sewer    0002         1
Trash/Bulk     0004         1
Debt Service   0003         2
Loan Collect   0005         2

I need to get this result set:

ExpDisplay    ExpSort       ExpProgramRevID     ProgramRevName
N/A            0001         1                   Government
Water/Sewer    0002         1                   Government
Trash/Bulk     0004         1                   Government
Debt Service   0003         2                   Business
Loan Collect   0005         2                   Business
NULL           NULL         3                   Public Sector

In other words, I need all rows that match on ProgramRevID, AND I need an entry for each ProgramRevision whether or not it has a matching row in Expenditures.

I can't seem to get my head around the query I need to do this. Any suggestions?

This will return the exact result you expected (revisions LEFT JOIN expenditures ):

var revisions = new List<Revision>();
revisions.Add(new Revision { Id = 1, Name = "Government" });
revisions.Add(new Revision { Id = 2, Name = "Business" });
revisions.Add(new Revision { Id = 3, Name = "Public Sector" });

var expenditures = new List<Expenditure>();
expenditures.Add(new Expenditure { Display = "N/A", ExpSort = "0001", RevisionId = 1 });
expenditures.Add(new Expenditure { Display = "Water/Sewer", ExpSort = "0002", RevisionId = 1 });
expenditures.Add(new Expenditure { Display = "Trash/Bulk", ExpSort = "0003", RevisionId = 1 });
expenditures.Add(new Expenditure { Display = "Debt Service", ExpSort = "0004", RevisionId = 2 });
expenditures.Add(new Expenditure { Display = "Loan Collect", ExpSort = "0005", RevisionId = 2 });

var result = revisions
    .GroupJoin(expenditures,
        r => r.Id,
        e => e.RevisionId,
        (r, e) => new { Revision = r, Expenditures = e.DefaultIfEmpty() }
    ).SelectMany(x => 
          x.Expenditures.Select(e => new { Revision = x.Revision, Expenditure = e })
    ).Select(x => new
    {
        Display = (x.Expenditure == null ? null : x.Expenditure.Display),
        ExpSort = (x.Expenditure == null ? null : x.Expenditure.ExpSort),
        RevisionId = x.Revision.Id,
        RevisionName = x.Revision.Name
    });