The LINQ expression "could not be translated"

Question

I have migrated my application from dotNet core2.1 to Dotnet core5. The below function has stopped working. I tried to debug the code but I was not able find the solution. The line which is giving error is X => Convert.ToInt32(X.Value). If I don't do the conversion function works fine but the order of list gets disturbs.

Error :-

The LINQ expression 'DbSet<EbDepartmentMsts>()\r\n .OrderBy(e => Convert.ToInt32(e.DeptCode.ToString()))' could not be translated. Additional information: Translation of method 'object.ToString' failed. If this method can be mapped to your custom function, 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'

function

public List<SelectListItem> GetDepartmentCode()
        {
            var table = new List<SelectListItem>();
            try
            {
                table = (from eb in _context.EbDepartmentMsts

                         select new SelectListItem
                         {
                             Text = string.Concat(eb.DeptCode.ToString(), "~", eb.Description),
                             Value = eb.DeptCode.ToString()
                         }).OrderBy(X => Convert.ToInt32(X.Value)).ToList();

            }catch(Exception ex)
            {

            }
            return table;
        }

Materialize the LINQ query then `Select`. `from eb in _context.EbDepartmentMsts.ToList()`. — Yong Shun, Nov 17 '21 at 06:04
@YongShun but same code was working earlier when I was using .net core sdk 2.1 — vwadhwa3, Nov 17 '21 at 09:42

score 1 · Answer 1 · edited Feb 06 '22 at 05:14

You need to do this in three steps:

Project only the required fields into an anonymous type, if there were any filter criteria then this should be included here as well. Sort criteria can be included in many cases too!

select new { eb.DeptCode, eb.Description }
materialize the LINQ query from an IQueryable into an IEnumerable via .ToList(). This will bring the results into memory for local client execution

.ToList()
Perform a .Select() to project the results into SelectListItem. This is executing in the C# client so you can also use any Framework or Custom methods that you may need.
```
   .Select(x => new SelectListItem
   {
       Text = string.Concat(x.DeptCode.ToString(), "~", x.Description),
       Value = x.DeptCode.ToString()
   })
```

There are many solutions suggesting "just put .Tolist() in your query", but it is important that we make the best possible use of the database and minimise the data that comes back by projecting into an interim anonymous type. It doesn't have to be anonymous, but that will reduce maintenance effort and provide you with the most flexibility.

This is an example that executes the sorting on the client side:

table = (from eb in _context.EbDepartmentMsts
         select new { eb.DeptCode, eb.Description }
        )
        .ToList()
        .Select(x => new SelectListItem
        {
            Text = string.Concat(x.DeptCode.ToString(), "~", x.Description),
            Value = x.DeptCode.ToString()
        })
        .OrderBy(x => Convert.ToInt32(x.Value))
        .ToList();

OR, you can also execute the sorting in the database query:

table = (from eb in _context.EbDepartmentMsts
         orderby eb.DeptCode
         select new { eb.DeptCode, eb.Description }
        )
        .ToList()
        .Select(x => new SelectListItem
        {
            Text = string.Concat(x.DeptCode.ToString(), "~", x.Description),
            Value = x.DeptCode.ToString()
        })
        .ToList();

Whilst this will work, in general it is not good advice to _"Materialize First"_ that is too generic, what you have done is formulate an expression that _approximates_ the required result, then you have materialized that before performing the final projection. Which is 100% a best practises approach to this kind of issue. The reasoning is a bit vague for the OP to apply to this in the next scenario correctly. — Chris Schaller, Feb 03 '22 at 02:28
Ya, thanks for the advice. Will be great to formulate the expression before materializing the data by using the supported functions as mentioned in [this answer](https://stackoverflow.com/a/17996264/8017690). Anyway, appreciate the knowledge sharing. =) — Yong Shun, Feb 03 '22 at 02:44

The LINQ expression "could not be translated"

1 Answers1