c# - 如何使用 Entity Framework 6 从 10 多个表中获取记录并从中显示报告？

Question

实现包含以下内容：

1. List<id>从表中获取。此表包含应为其生成报告的记录。如果此表没有记录，则不会生成报告。
2. 其余的细节在代码中是内联的。

代码如下所示：

List<int> totalValidRecords = ; //This comes from a table on the basic of which the report will be generated.

foreach(int id in totalValidRecords)
{
   List<Region> regions= //gets list of record from Region table.

   foreach(Region region in regions)
   {
      List<Country> countries= //gets list of countries from country table based on region.

      foreach(Country country in counties)
      {
        List<State> states = //gets list of states from State table based on country.

        foreach(State state in states)
        {
           List<District> states = //gets list of districts from District table based on state.

           //Other logic which computes and access other rest of dependent tables data.
        }
      }
   }

}

该代码运行良好，但仅获取几条记录（大约 20 条记录）大约需要 20 秒。

生成报告的这种延迟可能是因为发生了很多Database调用，但我不能忽略这些调用，因为这些调用是生成reports.

请让我知道是否需要对这些问题进行更多说明。

标签： c#entity-framework-6

Answer 1

假设您的模型如下所示：

public class Base
{
    public int Id {get;set;}
}

public class Region : Base
{
}

public class Country : Base
{
    public Region Region {get;set;}
    public int RegionId {get;set;}
}

public class State : Base
{
    public Country Country {get;set;}
    public int CountryId {get;set;}
}

public class District : Base 
{
    public State State {get;set;}
    public int StateId {get;set;}
}

比你可以通过几个s编写单个查询；join

var answer = (from region in db.Regions.Where(x => totalValidRecords.Contains(x.Id))
              join country in db.Country on region.Id equals country.RegionId 
              join state in db.States on country.Id equals state.CountryId 
              join district in db.Districts on state.Id equals district.StateId 
              select new 
              {
                  regionId = region.Id,
                  countryId = country.Id,
                  stateId = state.Id,
                  districtId = district.Id
                  //other fields
              }).ToList();