使用嵌入式列表进行Linq到SQL分组会导致查询过多

本文关键字：分组会 查询 SQL 嵌入式 列表 Linq | 更新日期: 2023-09-27 18:09:49

我正在开发一个查询来抓取和连接c#中的一些SQL表，并且我在数据集中分组和枚举值有一些麻烦。我的问题在下面。这为我提供了我所寻找的格式的数据，但是当我尝试添加如下所示的枚举列表时，它花费的时间太长了。当我查看引擎盖时，我可以看到它执行了太多的SQL查询。我想把它减到一个。使用LinqPad: 

void Main()
{
    var nightlyRuns = (from a in LoadTestSummaries
                join b in LoadTestTestSummaryData
                on a.LoadTestRunId equals b.LoadTestRunId
                where a.TargetStack == "LoadEnv" &&
                a.TestGuid != null &&
                a.StartTime != null &&
                a.LoadTestRunId != null
                orderby a.StartTime
                group new {a, b} by new
                {
                    a.TestGuid,
                    a.Name,
                    a.Description,
                    a.StartTime,
                    a.Duration,
                    a.NumAgents,
                    a.NumHosts,
                    a.PassFail,
                    a.ResultsFilePath,
                    a.Splunk
                } 
                into g
                let scenarioStart = g.Min(s => s.a.StartTime) ?? g.Min(s => s.a.DateCreated)        
                let testCases = g.Select(s => s.b)
                orderby scenarioStart 
                select new 
                {
                    TestGuid = g.Key.TestGuid,
                    ScenarioRun = new 
                    {
                        Name = g.Key.Name,
                        Description = g.Key.Description,
                        StartTime = scenarioStart,
                        Duration = g.Key.Duration,
                        NumAgents = g.Key.NumAgents,
                        NumHosts = g.Key.NumHosts,
                        Result = g.Key.PassFail,
                        ResultsFilePath = g.Key.ResultsFilePath,
                        SplunkLink = g.Key.Splunk,
                        // PROBLEM: Causes too many queries:
                        TestRuns = from t in testCases select t.TestCaseId
                    }
                }).ToLookup(g => g.TestGuid, g => g.ScenarioRun);
    nightlyRuns["ba593f66-695f-4fd1-99c3-71253a2e4981"].Dump();
}

"TestRuns"行导致了过多的查询。知道我哪里做错了吗?

使用嵌入式列表进行Linq到SQL分组会导致查询过多

很难回答测试，但我认为我们可以避免这样的分组和多个查询:(https://msdn.microsoft.com/en-us/library/bb311040.aspx)

           var nightlyRuns = (from a in LoadTestSummaries
            join b in LoadTestTestSummaryData
            on a.LoadTestRunId equals b.LoadTestRunId
            where a.TargetStack == "LoadEnv" &&
            a.TestGuid != null &&
            a.StartTime != null &&
            a.LoadTestRunId != null
            into testGroup
            select new 
            {
                TestGuid = a.TestGuid,
                ScenarioRun = new 
                {
                    Name = a.TestGuid,
                    Description = a.Description,
                    StartTime = a.StartTime ?? a.DateCreated,
                    Duration = a.Duration,
                    NumAgents = g.Key.NumAgents,
                    NumHosts = a.NumHosts,
                    Result = a.PassFail,
                    ResultsFilePath = a.ResultsFilePath,
                    SplunkLink = a.Splunk,
                    // PROBLEM: Causes too many queries:
                    TestRuns =testGroup
                }
            }).OrderBy(x=>x.StartTime).ToLookup(x => x.TestGuid, x => x.ScenarioRun);
            nightlyRuns["ba593f66-695f-4fd1-99c3-71253a2e4981"].Dump();
相关文章：