Joining IEnumerable to DataModel returns "Cannot be Translated" error-CodePudding

I'm trying to translate a complicated SQL stored procedure into linq. In many places in the stored procedure, the same data is pulled from the same location, in order to manipulate other data coming from elsewhere. So, to simplify things, I have turned this repeating query into a single linq operation, the results of which are stored in an IEnumerable.

private async Task<IEnumerable<TableA>> GetItems(DateTime today)
{
    var p = await (from pi in _context.TableA
                   join ph in _context.TableB
                        on pi.SomeCol equals ph.SomeCol
                   where ph.DateCol == today.Date
                   select pi).ToListAsync();

    return p;
}

This part works, but when I try to use the resulting IEnumerable in another linq query, things fall apart. I'm trying to get the process working using the test method below.

public async Task<IEnumerable<ListOfItems>> ListItemsToday(DateTime today)
{
    //run the method shown previously        
    var packItems = await GetItems(today);

    var items = (from ci in _context.ThirdTable
                 from pi in packItems.AsEnumerable()                 
                 where pi.colA == ci.colA && 
                       pi.colB == ci.colB &&
                       pi.colC == ci.colC
                 select new OutputModel
                 {
                     //fill model
                 }).ToList();

    return items;
}

The plan is to query packItems as I would any model, since it needs to join to ThirdTable on three different criteria, and it seems I can't do that with Enumerable.Join based on my Googling.

However, when I run this method, I get this error:

The LINQ expression 'ci => __AsEnumerable_0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

This error is why I added ".AsEnumerable()" to my packItems object above, but the error persists.

If I try transposing pi and ci in my linq query, I don't get an error anymore, but the program seems to get caught in a loop -- it gets to my "var items" section and just stays there.

How can/should I rewrite this query so it will work correctly?

CodePudding user response：

Credit for this answer goes to @Svyatoslav Danyliv. His comment above put me on the right track to getting this working. I'm answering this one so I can detail how I implemented his fix, in case anyone finds this later.

Converting the GetItems method to return an IQueryable object was the first step. The lines I changed are commented with changes below.

//private async Task<IEnumerable<TableA>> GetItems(DateTime today)
  private IQueryable<TableA> GetItems(DateTime today)
{
    var p = (from pi in _context.TableA
                   join ph in _context.TableB
                        on pi.SomeCol equals ph.SomeCol
                   where ph.DateCol == today.Date
                   select pi).AsQueryable();

    //"AsQueryable" has no async alternative, so I stripped out all the 
       threading.

    return p;
}

The only change in my ListItemsToday method was removing the "await" on the method call to fill var packItems, and the line

from pi in packItems.AsEnumerable()

became

from pi in packItems