Join Operation between Lists in SharePoint 2010 using SPMetal and LINQ to SharePoint

Required class:

Entity Class generated using SPMEtal.exe

Procedure To create Entity class:

     create a text file and copy following text in green

set SPMETAL="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN\SPMETAL.EXE"
%SPMETAL% /web:http://sitename/ /namespace:SharePointLINQ.JoinLists1 /code:EntityClass.cs

pause

Add the entity class into your sharepoint project solution

Add the reference of the class in project

join Operation between two or more  list using Entity class:

List Used Here are:

PartphaseDesign

PartDesign

DesignDocument

PartPhaseMaster

so we are combining 4 lists and giving the result in linq .we can bind this linq result to any grid.

CODE:

                EntityList<PartPhaseDesignItem> entPhaseDesign = dt.GetList<PartPhaseDesignItem>(objBusinessEntity.PartPhaseDesign);
                EntityList<PartPhaseMasterItem> entPhaseMaster = dt.GetList<PartPhaseMasterItem>(objBusinessEntity.PartPhaseMaster);
                EntityList<DesignDocumentDocument> entAttachments = dt.GetList<DesignDocumentDocument>(objBusinessEntity.DesignDocument);
                EntityList<PartDesignItem> entPartDesign = dt.GetList<PartDesignItem>(objBusinessEntity.PartDesign);
                var linqPhaseDesign = from d in entPhaseDesign.ToList()
                                      select d;
                var linqPhaseMaster = from m in entPhaseMaster.ToList()
                                      select m;
                var linqpartdesign = from c in entPartDesign.ToList()
                                     where c.BOMNo.BOMNo == strBOMID
                                     select c;
                var linqattach = from at in entAttachments.ToList()
                                 select at;
                if (linqpartdesign.Count() != 0)
                {
                                       var phaseDoc = from phaseitem in entPhaseDesign.ToList().Where(phaseitem => phaseitem.PartPhaseDesignNo.PartDesignNo == linqpartdesign.ToList()[0].PartDesignNo)
                                   from proj in linqattach.Where(proj =>
                                       phaseitem.Id.ToString() == proj.ItemID && strColumnName == proj.ColumnName
                                       && strListName == proj.ListName).DefaultIfEmpty()
                                   select new
                                       {

                                           //PartPhaseNo = (parthphase == null) ? "N/A" : parthphase.PartPhaseNo,
                                           //PhaseName = (parthphase == null) ? "N/A" : parthphase.PhaseName,
                                           //ListName = (parthphase == null) ? "N/A" : parthphase.ListName,
                                           PartPhaseID = (phaseitem == null) ? "N/A" : phaseitem.Id.ToString(),
                                           Remarks = (phaseitem == null) ? "N/A" : phaseitem.Remarks,
                                           FileName = (proj == null) ? "N/A" : proj.Name,
                                           Filepath = (proj == null) ? "N/A" : proj.Path + "/" + proj.Name,
                                           AssignedTo = (phaseitem == null) ? "N/A" : phaseitem.AssignedToName.ToString(),
                                           TargetDate = (phaseitem == null) ? System.DateTime.Now.Date : Convert.ToDateTime(phaseitem.TargetDate),
                                           ExistingData = (phaseitem == null) ? "N/A" : phaseitem.ExistingData,
                                           DocumentID = (proj == null) ? "N/A" : proj.Id.ToString(),
                                           PartphaseNo = (phaseitem == null) ? "N/A" : phaseitem.PartPhaseNo.PartPhaseNo.ToString(),
                                           //PartdesignID = (partdes == null) ? "N/A" : partdes.PartDesignNo
                                       };

                    var chklist1 = from parthphase in entPhaseMaster.ToList()
                                   from phaseitem in phaseDoc.ToList().Where(proj => proj.PartphaseNo == parthphase.PartPhaseNo).DefaultIfEmpty()
                                   orderby parthphase.PartPhaseNo
                                   select new
                                   {

                                       PartPhaseNo = (parthphase == null) ? "N/A" : parthphase.PartPhaseNo,
                                       PhaseName = (parthphase == null) ? "N/A" : parthphase.PhaseName,
                                       ListName = (parthphase == null) ? "N/A" : parthphase.ListName,
                                       PartPhaseID = (phaseitem == null) ? "N/A" : phaseitem.PartPhaseID.ToString(),
                                       Remarks = (phaseitem == null) ? "" : phaseitem.Remarks,
                                       FileName = (phaseitem == null) ? "N/A" : phaseitem.FileName,
                                       Filepath = (phaseitem == null) ? "N/A" : phaseitem.Filepath + "/" + phaseitem.FileName,
                                       AssignedTo = (phaseitem == null) ? "" : phaseitem.AssignedTo.ToString(),
                                       TargetDate = (phaseitem == null) ? System.DateTime.Now.Date : Convert.ToDateTime(phaseitem.TargetDate),
                                       ExistingData = (phaseitem == null) ? "" : phaseitem.ExistingData,
                                       DocumentID = (phaseitem == null) ? "N/A" : phaseitem.DocumentID.ToString(),
                                       PartdesignID = (phaseitem == null) ? "N/A" : linqpartdesign.ToList()[0].PartDesignNo
                                   };

Where the item is returning null give some value either empty string.... Very very important