Get result function in LINQ without translate to store expression

Question

I need to get result from a function that it need to run in LINQ query. This result bind to grid but in run time I encounter with this error:

LINQ to Entities does not recognize the method 'System.String GetName(System.Type, System.Object)' method, and this method cannot be translated into a store expression.

This is my Code:

public IQueryable GetForRah_CapacityList(XQueryParam param)
{
    var result = (from x in Data()
                  select new
                  {
                      Rah_CapacityId = x.Rah_CapacityId,
                      Rah_CapacityName = x.Rah_CapacityName,
                      Rah_St = Enum.GetName(typeof(Domain.Enums.CapacityState), x.Rah_St),
                      Rah_LinesId = x.Rah_LinesId
                  }).OrderByDescending(o => new { o.Rah_CapacityId });
    return result;
}

Salah Akbari · Answer 1 · 2017-01-23T09:19:17.447

7

GetName couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. You can modify the code as below:

 var result = (from x in Data().AsEnumerable()
              select new
              {
                  Rah_CapacityId = x.Rah_CapacityId,
                  Rah_CapacityName = x.Rah_CapacityName,
                  Rah_St = Enum.GetName(typeof(Domain.Enums.CapacityState), x.Rah_St),
                  Rah_LinesId = x.Rah_LinesId
              }).OrderByDescending(o => new { o.Rah_CapacityId });

With .ToList() after data is loaded, any further operation (such as select) is performed using Linq to Objects, on the data already in memory.

EDIT: Also your method's return type is IQueryable while your query is IOrderedEnumerable of anonymous type, so you should either change the method's type to System.Object or as a better solution create a class, send the values into the class's properties, and then return it.

edited Jan 23 '17 at 09:19

answered Jan 23 '17 at 08:59

Salah Akbari

39,330
10
79
109

1

Instead of `ToList`(which creates a new List for no reason) you can use `AsEnumerable` – Tim Schmelter Jan 23 '17 at 09:00
Instead of ToList I must use AsQueryable() but unfortunately still I receive that error. – Sasan Jan 23 '17 at 09:06
Result of my method is IQueryable – Sasan Jan 23 '17 at 09:07
@S.Akbari you haven't changed the query, you wanted surely "Rah_St = x.Rah_St" , but you have now exact the same as in original query in question – Maksim Simkin Jan 23 '17 at 09:07
@SasanKarimi [What's the difference(s) between .ToList(), .AsEnumerable(), AsQueryable()?](http://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable) – Salah Akbari Jan 23 '17 at 09:08
'System.Linq.IOrderedEnumerable<>' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?) – Sasan Jan 23 '17 at 09:09
@SasanKarimi Clearly your method's return type is `IQueryable` while your query is `IOrderedEnumerable` of `anonymous type`. So you should consider this also. – Salah Akbari Jan 23 '17 at 09:13
when I use your code I couldn't compile. I recieve up error. – Sasan Jan 23 '17 at 09:16
@SasanKarimi Since you want to return Anonymous Type from your method you should either change the method's type to `System.Object` or create a class, shove the values into the property, and then return it. – Salah Akbari Jan 23 '17 at 09:17
I need to IQueryable result – Sasan Jan 23 '17 at 09:24
1

`return result.AsQueryable();` – bolt19 Jan 23 '17 at 09:34
I did that. But recieve "At least one object must implement IComparable." error. – Sasan Jan 23 '17 at 09:36

Tim Schmelter · Accepted Answer · 2017-01-23T09:31:42.220

You can't use this method in Linq-To-Entities because LINQ does not know how to translate Enum.GetName to sql. So execute it in memory with Linq-To-Objects by using AsEnumerable and after the query use AsQueryable to get the desired AsQueryable:

So either:

var result = Data()
             .OrderBy(x=> x.CapacityId)
             .AsEnumerable()
             .Select(x => new
             {
                  Rah_CapacityId = x.Rah_CapacityId,
                  Rah_CapacityName = x.Rah_CapacityName,
                  Rah_St = Enum.GetName(typeof(Domain.Enums.CapacityState), x.Rah_St),
                  Rah_LinesId = x.Rah_LinesId
             })
             .AsQueryable();

You should first use OrderBy before you use AsEnumerable to benefit from database sorting performance. The same applies to Where, always do this before AsEnumerable(or ToList).

I couldn't use AsEnumerable. I want IQueryable result. – Sasan Jan 23 '17 at 09:26 — Sasan, Jan 23 '17 at 09:26

Get result function in LINQ without translate to store expression

2 Answers2

Linked

Related