Left Outer Join LINQ Query

Here is a good example for creating a left outer join in LINQ for entities;

var myQuery = (from x in db.tAXModelKitXref
                           join p in db.vPRODUCTSTKDBs on x.ProdCode equals p.ProdCode into kits let k = kits.FirstOrDefault()
                           orderby x.UniqueId
                           select new
                           {
                               UniqueId = x.UniqueId,
                               ProdCode = x.ProdCode,
                               ProdCodeDesc = k.ProdCodeDesc, 
                               KitCode = x.KitCode,
                               KitCodeDesc = "",
                               ModelKitStatus = x.ModelKitStatus,
                               ModelKitNotes = x.ModelKitNotes
                           }).ToList();

var myQuery = (from x in db.tAXModelKitXref

join p in db.vPRODUCTSTKDBs on x.ProdCode equals p.ProdCode into kits let k = kits.FirstOrDefault()

orderby x.UniqueId

select new

{

UniqueId = x.UniqueId,

ProdCode = x.ProdCode,

ProdCodeDesc = k.ProdCodeDesc,

KitCode = x.KitCode,

KitCodeDesc = "",

ModelKitStatus = x.ModelKitStatus,

ModelKitNotes = x.ModelKitNotes

}).ToList();

If you need to perform other queries to get extra data for your main query list, first perform your main query first and convert the results to a list (see above query). You can then perform extra queries on this list to populate more fields. (see query below).

var myResults = (from items in myQuery
                             select new
                             {
                                 items.UniqueId,
                                 items.ProdCode,
                                 items.ProdCodeDesc,
                                 items.KitCode,
                                 KitCodeDesc = ((from k in db.tCARKITS where k.KitCode.Equals(items.KitCode) select new { KitCodeDesc = k.KitCodeDesc }).FirstOrDefault() == null ? "" : (from k in db.tCARKITS where k.KitCode.Equals(items.KitCode) select new { KitCodeDesc = k.KitCodeDesc }).FirstOrDefault().KitCodeDesc ),
                                 items.ModelKitStatus,
                                 items.ModelKitNotes
                             }).ToList();

var myResults = (from items in myQuery

select new

{

items.UniqueId,

items.ProdCode,

items.ProdCodeDesc,

items.KitCode,

KitCodeDesc = ((from k in db.tCARKITS where k.KitCode.Equals(items.KitCode) select new { KitCodeDesc = k.KitCodeDesc }).FirstOrDefault() == null ? "" : (from k in db.tCARKITS where k.KitCode.Equals(items.KitCode) select new { KitCodeDesc = k.KitCodeDesc }).FirstOrDefault().KitCodeDesc ),

items.ModelKitStatus,

items.ModelKitNotes

}).ToList();

Then just do this to display the results onto a grid;

MainGrid.DataSource = myResults;
            foreach (Telerik.WinControls.UI.GridViewDataColumn cols in MainGrid.Columns)
                cols.BestFit();

MainGrid.DataSource = myResults;

foreach (Telerik.WinControls.UI.GridViewDataColumn cols in MainGrid.Columns)

cols.BestFit();

Left Outer Join LINQ Query

Search