Try this it's very easy:
public class Program
{
private static void Main(string[] args)
{
var projectMasterList = new List<projectmaster>
{
new ProjectMaster {ProjectId = 1, ProjectName = "ProjectName1",CompanyId = 1,CompanyCategoryId = 1,ProjectStatusId = 1},
new ProjectMaster {ProjectId = 2, ProjectName = "ProjectName2",CompanyId = 2,CompanyCategoryId = 2,ProjectStatusId = 1},
new ProjectMaster {ProjectId = 3, ProjectName = "ProjectName3",CompanyId = 3,CompanyCategoryId = 3,ProjectStatusId = 2},
new ProjectMaster {ProjectId = 3, ProjectName = "ProjectName3",CompanyId = 1,CompanyCategoryId = 1,ProjectStatusId = 1},
};
var companyMasterList = new List<companymaster>
{
new CompanyMaster {CompanyId = 1,CompanyName = "Microsoft",CompanyCategoryId = 1},
new CompanyMaster {CompanyId = 2,CompanyName = "Google",CompanyCategoryId = 2},
new CompanyMaster {CompanyId = 3,CompanyName = "Apple",CompanyCategoryId = 3},
};
var companyCategoryMasterList = new List<companycategorymaster>
{
new CompanyCategoryMaster{CompanyCategoryId = 1,CompanyCategoryName = "CategoryName1"},
new CompanyCategoryMaster{CompanyCategoryId = 2,CompanyCategoryName = "CategoryName2"},
new CompanyCategoryMaster{CompanyCategoryId = 3,CompanyCategoryName = "CategoryName3"}
};
var projectStatusMasterList = new List<projectstatusmaster>
{
new ProjectStatusMaster {ProjectStatusId = 1,ProjectStatusName = "Completed"},
new ProjectStatusMaster {ProjectStatusId = 2,ProjectStatusName = "Started"}
};
var result = (from companyMaster in companyMasterList
join projectMaster in projectMasterList
on companyMaster.CompanyId equals projectMaster.CompanyId
select new QueryResult
{
CompanyName = companyMaster.CompanyName,
CompanyCategoryName =
companyCategoryMasterList.Where(
cat => cat.CompanyCategoryId == companyMaster.CompanyCategoryId)
.Select(cat => cat.CompanyCategoryName).SingleOrDefault(),
TotalCompletedProjects =
projectMasterList.Count(
proj =>
proj.CompanyId == companyMaster.CompanyId && proj.ProjectStatusId == 1),
TotalProjects =
projectMasterList.Count(proj => proj.CompanyId == companyMaster.CompanyId)
}).Distinct(new SelectListItemComparer());
foreach (var queryResult in result)
{
Console.WriteLine(queryResult.CompanyName + " " + queryResult.CompanyCategoryName + " " + queryResult.TotalProjects + " " + queryResult.TotalCompletedProjects);
}
Console.ReadLine();
}
}
public class ProjectMaster
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public int CompanyId { get; set; }
public int CompanyCategoryId { get; set; }
public int ProjectStatusId { get; set; }
}
public class CompanyMaster
{
public int CompanyId { get; set; }
public string CompanyName { get; set; }
public int CompanyCategoryId { get; set; }
}
public class CompanyCategoryMaster
{
public int CompanyCategoryId { get; set; }
public string CompanyCategoryName { get; set; }
}
public class ProjectStatusMaster
{
public int ProjectStatusId { get; set; }
public string ProjectStatusName { get; set; }
}
public class QueryResult
{
public string CompanyName { get; set; }
public int TotalCompletedProjects { get; set; }
public int TotalProjects { get; set; }
public string CompanyCategoryName { get; set; }
}
public class SelectListItemComparer : EqualityComparer<queryresult>
{
public override bool Equals(QueryResult x, QueryResult y)
{
return x.CompanyName.Equals(y.CompanyName);
}
public override int GetHashCode(QueryResult obj)
{
return obj.CompanyName.GetHashCode();
}
}
</queryresult></projectstatusmaster></companycategorymaster></companymaster></projectmaster>