SQL to LINQ - Case Statement

user_13815658 0
Viewed 511

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 statement. 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 statement. Please let me know what I can modify to accomplish my goal.

My error says:


    InvalidOperationException: The LINQ expression 'DbSet<Table>
    .Where(c => c.ID ==(Nullable<int>)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<IActionResult> Index()
{
    var sortOrder = new Dictionary<string, int>
        {
            {"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. Thank you for your time!

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

Try using ternary operator for all cases in your ThenBy clause:

 .ThenBy(x => x.column3 == "X" 
      ? 1 
      : x.column3 == "Y" 
          ? 2 
          : x.column3 == "Z" 
                ? 3 
                : defaultOrder)
user_25012790

It'll be kind of yucky, but...

context.TableModel.Where(x => x.ID == 10)
    .OrderBy(x => x.column1)
    .ThenBy(x=> x.column3 == "X" ? 1
        : x.column3 == "Y" ? 2
        : x.column3 == "Z" ? 3
        : defaultOrder)
    .AsNoTracking()
    .ToListAsync();

Basically just a giant nested if statement.
Also, the select statement is redundant if your table only has three columns.

My suggestion is to make an enum out of all your column3 values and then orderby that instead. It'll be more efficient because enum assigns int values to each item.

user_63775690
Related