linq join with case condition

Question

Hi may i know how to do a select "case" condition in using linq? The commented out code are my question. how do i put the condition there? my code:

var r = from u in Users
    join p in Payments on u.Id equals p.UserId
    join soi in SaleOrderItems on p.ReferenceId equals soi.Id
           //if soi.InventoryTypeId == 1
              //then join i in Inventories on soi.InventoryOrCourseId equals i.Id
           //elseif soi.InventorytypeId ==2
              //then join c in Courses on soi.InventoryOrCourseId equals c.Id
    where u.Id == 5
    select new{ u, p, soi, either i or c};

Answer 1

You have to use some outer join trick to accomplish this, one straightforward method is via DefaultIfEmpty(). Essentially you create an inner join then expand it with missing rows:

var r = from u in Users
    join p in Payments on u.Id equals p.UserId
    join soi in SaleOrderItems on p.ReferenceId equals soi.Id
    join i in Inventories on new {a = soi.InventoryTypeId, b = soi.InventoryOrCourseId } equals new {a = 1, b = i.Id} into g1
    from oi in g1.DefaultIfEmpty()
    join c in Courses on new {a = soi.InventoryTypeId, b = soi.InventoryOrCourseId } equals new {a = 2, b = c.Id} into g2
    from oc in g2.DefaultIfEmpty()
    where u.Id == 5
    select new{ u, p, soi, ic = oi ?? oc};

Be careful about this last statement ic = oi ?? oc, since the types differ the anonymous type will use System.Object declaration so it can accommodate both types, if you want to use strong typed support maybe a better option would be to return both oc and ic and then test. You should best decide that based on how you use this query late ron.