如何在Asp.Net MVC3中使用LINQ从多个表中获取数据? [英] How to fetch the data from multiple tables using LINQ in Asp.Net MVC3?

问题描述

我正在Asp.Net 4.0 C#中工作- MVC-3 .我有4个表，并希望使用LINQ查询从该表中获取数据.

我的表如下...

1)Project_Master

字段名称:

project_id(pk)
project_name
company_id(带有company_master的FK)
company_category_id(带有Company_Category_master的FK)
project_status_id(带有Project_Status_Master的FK)

2)Company_Master

字段名称:

company_id
company_name
company_category_id(带有Company_Category_Master的FK)

3)Company_Category_Master

字段名称:

Company_Category_Id
Company_Category_Name

4)Project_Status_Master

字段名称:

Project_Status_Id
Project_Status_Name

现在我想获取以下数据
(使用LINQ查询)

1. Company_Name
2.使用状态id(1)= complete(其中状态1表示已完成)的已完成项目总数
3.总项目
4. Company_categary_name

那么，如何使用linq查询获取数据?

在此先感谢...

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...

推荐答案

尝试一下很简单:

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>

尝试以下查询.

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!

这篇关于如何在Asp.Net MVC3中使用LINQ从多个表中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！