是否有使用查询语法LINQ查询中做一个ToList的一种巧妙的方法？(Is there a neat

考虑下面的代码：

StockcheckJobs = 
     (from job in (from stockcheckItem in MDC.StockcheckItems
                   where distinctJobs.Contains(stockcheckItem.JobId)
                   group stockcheckItem by new { stockcheckItem.JobId, stockcheckItem.JobData.EngineerId } into jobs
                   select jobs).ToList()
      let date = MJM.GetOrCreateJobData(job.Key.JobId).CompletedJob.Value
      orderby date descending 
      select new StockcheckJobsModel.StockcheckJob()
      {
          JobId = job.Key.JobId,
          Date = date,
          Engineer = (EngineerModel)job.Key.EngineerId,
          MatchingLines = job.Count(sti => sti.Quantity == sti.ExpectedQuantity),
          DifferingLines = job.Count(sti => sti.Quantity != sti.ExpectedQuantity)
      }).ToList()

有一个ToList()在中间，因为GetOrCreateJobData方法不能被翻译成SQL。

其结果是我不得不围绕着我的括号内做到这一点查询的第一部分，然后我用一个外部查询完事。

我知道我可以分裂成两个变量，但我并不想这样做（这是一个对象中初始化器太）。

有一些其他的语法，我可以用它来增加可读性，最好是去掉不需要外部的内部查询，当我必须做一个ToList （或以其他方式得到的LINQ到对象）在LINQ查询的中间？

在一个理想的世界，我想是这样的（尽可能接近可能的反正）：

StockcheckJobs =
     from stockcheckItem in MDC.StockcheckItems
     where distinctJobs.Contains(stockcheckItem.JobId)
     group stockcheckItem by new { stockcheckItem.JobId, stockcheckItem.JobData.EngineerId } into jobs
     MAGIC_DO_BELOW_AS_LINQ-TO-OBJECTS_KEYWORD_OR_SYNTAX
     let date = MJM.GetOrCreateJobData(jobs.Key.JobId).CompletedJob.Value
     orderby date descending 
     select new StockcheckJobsModel.StockcheckJob()
     {
         JobId = jobs.Key.JobId,
         Date = date,
         Engineer = new ThreeSixtyScheduling.Models.EngineerModel() { Number = jobs.Key.EngineerId },
         MatchingLines = jobs.Count(sti => sti.Quantity == sti.ExpectedQuantity),
         DifferingLines = jobs.Count(sti => sti.Quantity != sti.ExpectedQuantity)
     };

Answer 1:

我想提出两点意见与问题：

我真的不认为有在这里引入一个额外的变量任何可读性问题。事实上，我认为这使得它更具可读性，因为它分离从数据库上执行代码的“本地执行”的代码。
简单地切换到LINQ到对象， AsEnumerable优选ToList 。

这就是说，这里是你如何能留在查询土地一路而不会对整个查询表达式中间AsEnumerable（）/ ToList（）：通过欺骗C＃编译器将使用自定义扩展方法，而不是BCL。这是可能的，因为C＃使用“基于模式的”的方法（而不是被耦合与BCL）打开查询表达式入方法的呼叫和lambda表达式。

声明邪恶类这样的：

public static class To
{
    public sealed class ToList { }

    public static readonly ToList List;

    // C# should target this method when you use "select To.List"
    // inside a query expression.
    public static List<T> Select<T>
        (this IEnumerable<T> source, Func<T, ToList> projector)
    {
        return source.ToList();
    }
}

public static class As
{
    public sealed class AsEnumerable { }

    public static readonly AsEnumerable Enumerable;

    // C# should target this method when you use "select As.Enumerable"
    // inside a query expression.
    public static IEnumerable<T> Select<T>
        (this IEnumerable<T> source, Func<T, AsEnumerable> projector)
    {
        return source;
    }
}

然后，你可以写这样的疑问：

List<int> list = from num in new[] { 41 }.AsQueryable()
                 select num + 1 into result
                 select To.List;

IEnumerable<int> seq = from num in new[] { 41 }.AsQueryable()
                       select num + 1 into result
                       select As.Enumerable into seqItem
                       select seqItem + 1; // Subsequent processing

在你的情况，你的查询将变成：

StockcheckJobs =
     from stockcheckItem in MDC.StockcheckItems
     where distinctJobs.Contains(stockcheckItem.JobId)
     group stockcheckItem by new { stockcheckItem.JobId, stockcheckItem.JobData.EngineerId } into jobs
     select As.Enumerable into localJobs // MAGIC!
     let date = MJM.GetOrCreateJobData(localJobs.Key.JobId).CompletedJob.Value
     orderby date descending 
     select new StockcheckJobsModel.StockcheckJob()
     {
         JobId = localJobs.Key.JobId,
         Date = date,
         Engineer = new ThreeSixtyScheduling.Models.EngineerModel() { Number = localJobs.Key.EngineerId },
         MatchingLines = localJobs.Count(sti => sti.Quantity == sti.ExpectedQuantity),
         DifferingLines = localJobs.Count(sti => sti.Quantity != sti.ExpectedQuantity)
     };

我真的不认为这是任何形式的改善，虽然。相反，它是一种语言特性相当沉重的滥用。

Answer 2:

您可以修复的问题GetOrCreateJobData没有被翻译为SQL。

通过实施指定的方法调用表达自定义查询翻译，你才可以通过LINQ到SQL如何解释的方法控制。这是一个很好的文章，解释这个程序并链接到现有相关资源在： http://www.codeproject.com/Articles/32968/QueryMap-Custom-translation-of-LINQ-expressions

或者，你可以重构GetOrCreateJobData方法，它建立与表达相同的逻辑扩展方法，使LINQ到SQL自然可以解释它。根据该方法的复杂性，这可能是比我的第一个建议或多或少可行的。

Answer 3:

我发现，使用方法的语法使事情变得更清晰，不过这只是个人喜好。这当然使查询更好的上半部分，但使用let ，而可能在方法的语法，是有点更多的工作。

var result = stockcheckItem in MDC.StockcheckItems
    .Where(item => distinctJobs.Contains(item.JobId))
    .GroupBy(item => new { item.JobId, item.JobData.EngineerId })
    .AsEnumerable() //switch from Linq-to-sql to Linq-to-objects
    .Select(job => new StockcheckJobsModel.StockcheckJob()
    {
        JobId = job.Key.JobId,
        Date = MJM.GetOrCreateJobData(job.Key.JobId).CompletedJob.Value,
        Engineer = (EngineerModel)job.Key.EngineerId,
        MatchingLines = job.Count(sti => sti.Quantity == sti.ExpectedQuantity),
        DifferingLines = job.Count(sti => sti.Quantity != sti.ExpectedQuantity)
    })
    .Orderby(item => item.Date)
    .ToList()

Answer 4:

一种选择是做所有的SQL兼容工作锋线成一个匿名类型，

var jobs = 
     (from job in (from stockcheckItem in MDC.StockcheckItems
        where distinctJobs.Contains(stockcheckItem.JobId)
        group stockcheckItem by new 
             { stockcheckItem.JobId, stockcheckItem.JobData.EngineerId } 
         into jobs
        select new 
             {
                JobId = job.Key.JobId,
                Engineer = (EngineerModel)job.Key.EngineerId,
                MatchingLines = 
                    job.Count(sti => sti.Quantity == sti.ExpectedQuantity),
                DifferingLines = 
                    job.Count(sti => sti.Quantity != sti.ExpectedQuantity)
             }
      ).AsEnumerable()

StockcheckJobs = jobs.Select(j => new StockcheckJobsModel.StockcheckJob
    {
         JobId = j.JobId,
         Date = MJM.GetOrCreateJobData(j.JobId).CompletedJob.Value,
         Engineer = j.EngineerId,
         MatchingLines = j.MatchingLines,
         DifferingLines = j.DifferingLines
    }).OrderBy(j => j.Date).ToList();

显然不是测试，但你的想法。

文章来源: Is there a neat way of doing a ToList within a LINQ query using query syntax?