Is there a neat way of doing a ToList within a LINQ query using query syntax?

Question

Consider the code below:

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()

There is a ToList() in the middle because the GetOrCreateJobData method can't be translated into sql.

As a result I've had to surround the first part of my query in brackets to do this, then I've used an outer query to finish up.

I know I could split this into two variables, but I don't want to do that (this is within an object initialiser too).

Is there some other syntax I can use to increase readability, preferably removing the need for an outer an inner query, when I have to do a ToList (or otherwise get to linq-to-objects) in the middle of a linq query?

---

In an ideal world I'd like something like this (as close as is possible anyway):

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

You can fix the issue of GetOrCreateJobData not being translatable to SQL.

By implementing a custom query translator for the specified method call expression, you can gain control over how LINQ-to-SQL interprets the method. There is a good article explaining this procedure and linking to relevant resources available at: http://www.codeproject.com/Articles/32968/QueryMap-Custom-translation-of-LINQ-expressions

Alternatively, you could refactor the GetOrCreateJobData method to an extension method which builds the same logic with expressions, so that LINQ-to-SQL can interpret it naturally. Depending on the complexity of the method, this may be more or less feasible than my first suggestion.

Answer 2

I would raise two points with the question:

1. I really don't think there's any readability issue with introducing an extra variable here. In fact, I think it makes it more readable as it separates the "locally executing" code from the code executing on the database.
2. To simply switch to LINQ-To-Objects, AsEnumerable is preferable to ToList.

That said, here's how you can stay in query-land all the way without an intermediate AsEnumerable() / ToList() on the entire query-expression : by tricking the C# compiler into using your custom extension methods rather than the BCL. This is possible since C# uses a "pattern-based" approach (rather than being coupled with the BCL) to turn query-expressions into method-calls and lambdas.

Declare evil classes like these:

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;
    }
}

And then you can write queries like this:

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

In your case, your query would become:

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)
     };

I really don't see this as any sort of improvement, though. Rather, it's pretty heavy abuse of a language feature.

Answer 3

I find that using method syntax makes things clearer, but that's just personal preference. It certainly makes the top half of the query better, but using a let, while possible in method syntax, is a bit more work.

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()