c# - Entity Framework查询在23个子查询后失败，需要添加更多

Question

我有一个 Entity Framework 查询，用于将两个表中的 31 列输出到 GridView 。 29 列是位，因此它们显示为 True 或 False。尝试更新它以显示 Yes 或 No，我向每个位列添加了一个子查询，以从位值为 1 或 0 或 NULL 的查找表中选择 Yes 或 No 或 NULL。这适用于大约 23 列，之后查询不返回任何结果并且 gridview 不加载。我可以将剩余的列作为位返回并显示 True 或 False，但是一旦我更改其中任何一个以使用子查询，查询就会失败并且 gridview 不会加载。我认为问题可能是命令超时或连接超时。我为命令超时添加了这个

db.Database.CommandTimeout = 3000; db.Configuration.AutoDetectChangesEnabled = false;

我在 web.config 中添加了以下内容:

Connection Timeout=6000

仍然没有任何效果。这可能是内存或缓存问题吗？这是查询

using (var db = new EthicsEntities())
{
    db.Database.CommandTimeout = 3000;
    //db.Configuration.LazyLoadingEnabled = true;
    db.Configuration.AutoDetectChangesEnabled = false;

    string sstartyear = Convert.ToString(startyear);
    string eendyear = Convert.ToString(endyear);
    var questrpt = (from f in db.FDS
                    join q in db.Questions
                    on f.FDS_Num equals q.FDS_Num
                    where f.Filed_Year >= startyear
                    && f.Filed_Year <= endyear
                    orderby f.Filed_Year, f.FDS_Num          

                    select new 
                    {
                        fdsnum = q.FDS_Num,
                        yearr = f.Filed_Year,
                        //rQ01 = q.Q1,
                        rQ01 = (from y in db.YesNoes where y.YN_Bit == q.Q1 select y.YN_Value).FirstOrDefault(),
                        //rQ02 = q.Q2,
                        rQ02 = (from y in db.YesNoes where y.YN_Bit == q.Q2 select y.YN_Value).FirstOrDefault(),
                        //rQ03 = q.Q3,
                        rQ03 = (from y in db.YesNoes where y.YN_Bit == q.Q3 select y.YN_Value).FirstOrDefault(),
                        //rQ04 = q.Q4,
                        rQ04 = (from y in db.YesNoes where y.YN_Bit == q.Q4 select y.YN_Value).FirstOrDefault(),
                        //rQ05 = q.Q5,
                        rQ05 = (from y in db.YesNoes where y.YN_Bit == q.Q5 select y.YN_Value).FirstOrDefault(),
                        //rQ06 = q.Q6,
                        rQ06 = (from y in db.YesNoes where y.YN_Bit == q.Q6 select y.YN_Value).FirstOrDefault(),
                        //rQ07 = q.Q7,
                        rQ07 = (from y in db.YesNoes where y.YN_Bit == q.Q7 select y.YN_Value).FirstOrDefault(),
                        //rQ08 = q.Q8,
                        rQ08 = (from y in db.YesNoes where y.YN_Bit == q.Q8 select y.YN_Value).FirstOrDefault(),
                        //rQ09 = q.Q9,
                        rQ09 = (from y in db.YesNoes where y.YN_Bit == q.Q9 select y.YN_Value).FirstOrDefault(),
                        //rQ10 = q.Q10,
                        rQ10 = (from y in db.YesNoes where y.YN_Bit == q.Q10 select y.YN_Value).FirstOrDefault(),
                        //rQ11 = q.Q11,
                        rQ11 = (from y in db.YesNoes where y.YN_Bit == q.Q11 select y.YN_Value).FirstOrDefault(),
                        //rQ12 = q.Q12,
                        rQ12 = (from y in db.YesNoes where y.YN_Bit == q.Q12 select y.YN_Value).FirstOrDefault(),
                        //rQ13 = q.Q13,
                        rQ13 = (from y in db.YesNoes where y.YN_Bit == q.Q13 select y.YN_Value).FirstOrDefault(),
                        //rQ14 = q.Q14,
                        rQ14 = (from y in db.YesNoes where y.YN_Bit == q.Q14 select y.YN_Value).FirstOrDefault(),
                        //rQ15 = q.Q15,
                        rQ15 = (from y in db.YesNoes where y.YN_Bit == q.Q15 select y.YN_Value).FirstOrDefault(),
                        //rQ16 = q.Q16,
                        rQ16 = (from y in db.YesNoes where y.YN_Bit == q.Q16 select y.YN_Value).FirstOrDefault(),
                        //rQ17 = q.Q17,
                        rQ17 = (from y in db.YesNoes where y.YN_Bit == q.Q17 select y.YN_Value).FirstOrDefault(),
                         //rQ18 = q.Q18,
                         rQ18 = (from y in db.YesNoes where y.YN_Bit == q.Q18 select y.YN_Value).FirstOrDefault(),
                         //rQ19 = q.Q19,
                         rQ19 = (from y in db.YesNoes where y.YN_Bit == q.Q19 select y.YN_Value).FirstOrDefault(),
                         //rQ20 = q.Q20,
                         rQ20 = (from y in db.YesNoes where y.YN_Bit == q.Q20 select y.YN_Value).FirstOrDefault(),
                         rQ21 = q.Q21,
                         //rQ21 = (from y in db.YesNoes where y.YN_Bit == q.Q21 select y.YN_Value).FirstOrDefault(),
                         rQ22 = q.Q22,
                         //rQ22 = (from y in db.YesNoes where y.YN_Bit == q.Q22 select y.YN_Value).FirstOrDefault(),
                         rQ23 = q.Q23,
                         //rQ23 = (from y in db.YesNoes where y.YN_Bit == q.Q23 select y.YN_Value).FirstOrDefault(),
                         rQ24 = q.Q24,
                         //rQ24 = (from y in db.YesNoes where y.YN_Bit == q.Q24 select y.YN_Value).FirstOrDefault(),
                         rQ25 = q.Q25,
                         //rQ25 = (from y in db.YesNoes where y.YN_Bit == q.Q25 select y.YN_Value).FirstOrDefault(),
                         rQ26 = q.Q26,
                         //rQ26 = (from y in db.YesNoes where y.YN_Bit == q.Q26 select y.YN_Value).FirstOrDefault(),
                         //rQ27 = q.Q27,
                         rQ27 = (from y in db.YesNoes where y.YN_Bit == q.Q27 select y.YN_Value).FirstOrDefault(),
                         //rQ28 = q.Q28,
                         rQ28 = (from y in db.YesNoes where y.YN_Bit == q.Q28 select y.YN_Value).FirstOrDefault(),
                         //rQ29 = q.Q29
                         rQ29 = (from y in db.YesNoes where y.YN_Bit == q.Q29 select y.YN_Value).FirstOrDefault()
                     }).ToList();

    grdQuestions.DataSource = questrpt;
    grdQuestions.DataBind();
    grdQuestions.HeaderRow.TableSection = TableRowSection.TableHeader;
}

Answer 1

在这种情况下，您应该在客户端进行一些查询处理。 IE 将 YesNos 提取到本地字典中，并在客户端进行最终选择。像这样:

var questrpt =  from f in db.FDS
                join q in db.Questions
                on f.FDS_Num equals q.FDS_Num
                where f.Filed_Year >= startyear
                && f.Filed_Year <= endyear
                orderby f.Filed_Year, f.FDS_Num
                select new {fds=f, question=q};

var yn = from y in db.YesNos
            select new (y.YN_Bit,y.YN_Value);

var ynByBit = yn.ToDictionary(y => y.YN_Bit, y => YN_Value);

var result = from q in questrpt.ToList()
                select new 
                (
                    fds.FDS_Num,
                    yearr = fds.Filled_Year,
                    rQ01 = ynByBit[q.Q1],
                    rQ02 = ynByBit[q.Q2],
                    rQ03 = ynByBit[q.Q3],
                    //. . .
                );