Andrew Day Andrew Day - 3 months ago 15
MySQL Question

Linq inner join on complex query

var workbasketitems = new[] { //workbasketitems
new { TaskId = 10, WorkGroupId = 100, ActionUserId =1000, Work = "item 0" },
new { TaskId = 11, WorkGroupId = 101, ActionUserId =1001, Work = "item 1" },
new { TaskId = 12, WorkGroupId = 102, ActionUserId =1002, Work = "item 2" }
};
var workflowtasks = new[] { //workflowtasks
new { TaskId = 10, TaskDesc = "TaskDesc 0" },
new { TaskId = 11, TaskDesc = "TaskDesc 1" },
new { TaskId = 12, TaskDesc = "TaskDesc 2" }
};
var workgroup = new[] { //workgroup
new { WorkGroupId = 100, WGDesc = "WGDesc 0" },
new { WorkGroupId = 101, WGDesc = "WGDesc 1" },
new { WorkGroupId = 102, WGDesc = "WGDesc 2" }
};
var applicationuser = new[] { //applicationuser
new { AUId = 1000, AUDesc = "AUId 0" },
new { AUId = 1001, AUDesc = "AUId 1" }
};
var results = from wb in workbasketitems
join wft in workflowtasks on wb.TaskId equals wft.TaskId
join wg in workgroup on wb.WorkGroupId equals wg.WorkGroupId
select new { wft.TaskDesc, wg.WGDesc, wb.ActionUserId, wb.Work};
results.Dump();

var resultsInner = from wb in results
join au in applicationuser on wb.ActionUserId equals au.AUId into wbl
from auList in wbl.DefaultIfEmpty()
select new { wb.TaskDesc, wb.WGDesc, Desc = (auList == null? "BlAnk": auList.AUDesc), wb.Work};
resultsInner.Dump();


Results from Linqpad

Is there a way to combine the below Linq queries, and is that helpful as I want keep things maintainable. The above works in Linqpad with resultsInner being an Inner Join of the newly created results table.

Answer

You are almost there. Just add the extra left join that you are performing in the second query to the first query:

var results = from wb in workbasketitems
              join wft in workflowtasks on wb.TaskId equals wft.TaskId
              join wg in workgroup on wb.WorkGroupId equals wg.WorkGroupId
              join au in applicationuser on wb.ActionUserId equals au.AUId into wbl
              from auList in wbl.DefaultIfEmpty()
              select new { wft.TaskDesc, wg.WGDesc, Desc = (auList == null ? "BlAnk" : auList.AUDesc), wb.Work };

You can also use the other overload of DefaultIfEmpty to specify what to do in a case when the left join results with a null:

var results = from wb in workbasketitems
              join wft in workflowtasks on wb.TaskId equals wft.TaskId
              join wg in workgroup on wb.WorkGroupId equals wg.WorkGroupId
              join au in applicationuser on wb.ActionUserId equals au.AUId into wbl
              from auList in wbl.DefaultIfEmpty(new { AUId = wb.ActionUserId, AUDesc = "Blank" } )
              select new { wft.TaskDesc, wg.WGDesc, Desc = auList.AUDesc, wb.Work };
Comments