SQL to LINQ - Case Expression
14:20 10 Jul 2020

I am re-writing a SQL statement to LINQ for the first time. I am not too sure how to properly write out my case expression. The runtime does not like how I have written it (I used this link as an example)

My goal is to get my data organized by the case expression. Please let me know what I can modify to accomplish my goal.

My error says:


    InvalidOperationException: The LINQ expression 'DbSet
    .Where(c => c.ID ==(Nullable)10)
    .OrderBy(c => c.column1)
    .ThenBy(c => _sortOrder_0.TryGetValue(
    key: c.column3,
    value:_order_1)?_order_1:_defaultOrder_2)' 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 either AsEnumerable(), ToList(), or ToListAsync()


Original SQL query:



    SELECT column1, column2, column3 from table where ID = # order by column1, 
    Case column3 When 'X' Then 1 When 'Y' Then 2 When 'Z' Then 3 End




LINQ:


public async Task Index()
{
    var sortOrder = new Dictionary
        {
            {"X", 1 },
            {"Y", 2 },
            {"Z", 3 },
        };
    var defaultOrder = sortOrder.Max(x => x.Value) + 1;
    int order;
    var dataTest = _context.TableModel
        .Where(x =>
        (x.ID == 10)
        )
        .Select(x => new TableModel
        {
            column1 = x.column1,
            column2 = x.column2,
            column3 = x.column3,
        })
        .OrderBy(x => x.column1)
        .ThenBy(x => sortOrder.TryGetValue(x.column3, out order) ? order : defaultOrder)
        .AsNoTracking()
        .ToListAsync();
}



Side note: I am using ID = 10 for testing purposes.


              
      

          
                  c#
                linq
                entity-framework-core
                linq-to-sql