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