CODEWITHSUNDEEP
c#linq.net-core
vwadhwa3
vwadhwa3

Reputation: 163

The LINQ expression "could not be translated"

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

Upvotes: 1

Views: 6554

Answers (1)

Yong Shun
Yong Shun

Reputation: 51160

You need to do this in three steps:

  1. 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 }

  2. materialize the LINQ query from an IQueryable into an IEnumerable via .ToList(). This will bring the results into memory for local client execution

    .ToList()

  3. 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();

Upvotes: 1

Related Questions