Click here to Skip to main content
12,354,122 members (73,219 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# ASP.NET LINQ MVC
I am working in Asp.Net 4.0 C#-- MVC-3. I have 4 tables and wants to fetch data from that tables with LINQ query.

my TABLES are as below...

1) Project_Master

Field Names :

project_id (pk)
project_name
company_id (FK with company_master)
company_category_id (FK with Company_Category_master)
project_status_id (FK with Project_Status_Master)

2)Company_Master

Field Names :

company_id
company_name
company_category_id (FK with Company_Category_Master)

3) Company_Category_Master

Field Names :

Company_Category_Id
Company_Category_Name

4) Project_Status_Master

Field Name :

Project_Status_Id
Project_Status_Name

Now i want to get following data
(using LINQ Query)


1. Company_Name
2. Total completed project using status id(1)=complete (where staus 1 means completed)
3. Total Project
4. Company_categary_name

So, How to fetch data with linq query?

Thanks in advance...
Posted 29-Mar-12 19:55pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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>
  Permalink  
Comments
Indies Services 4-Apr-12 0:15am
   
hey thanks :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try following query.

var result =from comp_mast in Company_Master
join (proj_mast in Project_Master Group By company_id into proj_mast_grp) on comp_mast.company_id equals proj_mast_grp.Key
join cat_mast in Company_Category_Master on comp_mast.company_category_id  equals cat_mast.Company_Category_Id
select new {
    Company_Name = comp_mast.company_name,
    Total_Completed_Projects=proj_mast_grp.Select(pmg => pmg.project_status_id==1).Count(),
    Total_Projects=proj_mast_grp.Count(),
    Company_categary_name=cat_mast.Company_Category_Name
};


Hope this help!
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160621.1 | Last Updated 30 Mar 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100