c# - 统计MVC中Child中的Records数量

Question

我想在 MVC 中获得与以下问题相同的结果。

    public ActionResult Index()

    {
        //List<Systems> systems;
        //var query = db.SystemFamily.Select(c => c.SystemFamilyID).ToList();
        //foreach (var sid in query)
        //{
        //    systems = db.Systems.Select(c => c.SystemFamilyID == sid).ToList();
        //}
        //int count = systems.Count();//Here you will  get count

        //ViewBag.Counts = count; 
        var viewmodel = new Sys_SysFam();
        foreach (var item in db.SystemFamily)
        {
            int id = item.SystemFamilyID;

            //SystemsCount 
            int count = db.Systems.Where(x => x.SystemFamilyID == id).Count();   // Here while debugging it has the value for id but throws exception. 
            item.SystemsCount = count;
        }
        ViewBag.Count = db.SystemFamily.Where(x=>x.deleteFlag==false).Count();   //All these work
        int count1 =db.Systems.Count();    // I even tried it with a where condition passing a SystemFamilyID. Even that worked
        ViewBag.SCount = count1;
        return View(db.SystemFamily.Where(x=>x.deleteFlag==false).ToList());
    }

我得到异常

EntityFramework.SqlServer.dll 中发生了“System.Data.Entity.Core.EntityCommandExecutionException”类型的异常，但未在用户代码中处理

附加信息:执行命令定义时出错。有关详细信息，请参阅内部异常。这是内部异常
已经有一个与此命令关联的打开的 DataReader，必须先将其关闭。

this is the result that needs to be achieved

Answer 1

你得到的异常基本上是你在迭代另一个查询的结果时执行查询。

在你的代码中尝试 foreach 语句

      foreach (var item in db.SystemFamily.ToList())
      {
        int id = item.SystemFamilyID;

        //SystemsCount 
        int count = db.Systems.Where(x => x.SystemFamilyID == id).Count();   
        item.SystemsCount = count;
     }

或者您可以在您的连接字符串中允许 MARS 添加 MultipleActiveResultSets=true 到您的连接字符串的提供者部分(指定数据源、初始目录等)。