Using extension method in Linq to Entities

Question

Code:

public static IEnumerable<TableRowModel> GetRows()
        {
            var to_ret = db.TableRows.select(x=> new TableRowModel(){
                TableRowId = x.TableRowId,
                Type = x.Type,
                Name = x.Name,
                CreatedAt = x.CreatedAt,
                ModifiedAt = x.ModifiedAt,
                Enums = x.Enums.Select(y => y.ToEnumModel()),
            });
            return to_ret;
        }

public static EnumModel ToEnumModel(this Enum x)
        {
            var to_ret = new EnumModel()
            {
                CFPId = x.CFPId,
                CreatedAt = x.CreatedAt,
                ModifiedAt = x.ModifiedAt,
            };
            return to_ret;
        }

I get the following error when using the GetRows method:

LINQ to Entities does not recognize the method

Given the error, it's understood that LINQ To Entities is not able to recognize the extension method ToEnumModel. I would like to know if there is a way around this? So that I would not be repeating ToEnumModel code again in GetRows extension.

You could put a `AsEnumerable` in your query before the `Select` so it will run the SQL and then do the mappings in memory. Also you might want to check out libraries that can do this for you like AutoMapper. — juharr, Oct 23 '17 at 17:32
@juharr Thanks, I did have a look at AutoMapper, thought custom extensions would give more control. Would love to know how Automapper works around this issue. — Shyamal Parikh, Oct 23 '17 at 17:41
You'd have the same thing, materialize your entities first then run that through AutoMapper to get the mapped classes. — juharr, Oct 23 '17 at 17:48
Just to be more clear, do you mean to say that AutoMapper too does something similar to what @stybol suggested in the answer below? Utilizing `AsEnumerable` — Shyamal Parikh, Oct 23 '17 at 17:57
Not you'd do something like `var queryResults = someQueryHere` and then you'd do `var realResutls = Mapper.Map>(queryResults);` — juharr, Oct 23 '17 at 17:59
@ShyamalParikh It depends on what mapping method is used. The AM `ProjectTo` method utilizes expression tree building, so the query executes at db side. L2E requires expressions. Another library that might help is [LINQKit](https://github.com/scottksmith95/LINQKit). — Ivan Stoev, Oct 23 '17 at 18:15
@IvanStoev I tried utilizing LinqKit. When using `AsExpandable` command as `x.TableRow.AsExpandable().ToTableRowModel(),` C# doesn't recognize `.AsExpandable()` method — Shyamal Parikh, Oct 25 '17 at 12:16
@IvanStoev I created a separate question: https://stackoverflow.com/questions/46946370/linqkit-asexpandable-not-working to address this issue with LinqKit. Would appreciate your input — Shyamal Parikh, Oct 26 '17 at 11:00

stelioslogothetis · Answer 1 · 2017-10-23T17:41:51.017

1

Under normal circumstances, when performing an operation like a Where in EntityFramework, it isn't actually executed in memory like when you operate on an enumerable (like a List). Instead, it converted into SQL which is then executed by the Db provider. This means that doing certain things, such as using extension methods on objects, is not an option, as the converter cannot turn such things into SQL.

The quickest and easiest solution would be to load your set in-memory, where it can be operated on like any other enumerable. You can do this like so:

var result = myDbSet.AsEnumerable().Etc()

Etc() here represents all other operations you want to run. Be advised however, that this will load all data into memory, which may be prohibitively slow and expensive in some scenarios. One way to alleviate this is to put AsEnumerable() right before you need to use the extension method, thus offloading as many operations as possible to the provider. For example this:

var result = myDbSet.Where([condition]).AsEnumerable().Select(item => item.ExtensionMethod());

Might be much quicker and lighter than this:

var result = myDbSet.AsEnumerable().Where([condition]).Select(item => item.ExtensionMethod());

edited Oct 23 '17 at 17:41

answered Oct 23 '17 at 17:34

stelioslogothetis

9,371
3
28
53

I tried this solution, it gives the same error: `LINQ to Entities does not recognize the method and this method cannot be translated into a store expression.` – Shyamal Parikh Oct 26 '17 at 05:49
Code in question updated to the exact replica of the code I am utilizing and facing same issue even after adding `.AsEnumerable()` before select query. – Shyamal Parikh Oct 26 '17 at 08:54
@ShyamalParikh Where exactly are you putting `.AsEnumerable()`? Are you putting it before `x.Enums.Select` or `db.TableRows.Select`? – stelioslogothetis Oct 26 '17 at 08:56
`x.Enums.AsEnumerable().Select` – Shyamal Parikh Oct 26 '17 at 09:40
@ShyamalParikh You need to put it before the outer Select. `db.TableRows.AsEnumerable().Select` – stelioslogothetis Oct 26 '17 at 09:42
That would be very expensive, need to find an alternative. Tried `LinqKit` not able to make it work. – Shyamal Parikh Oct 26 '17 at 09:43

score 0 · Answer 2 · answered Oct 23 '17 at 18:38

Your ability to use extension methods in EF is limited, but you can still extend your IQueryables

public static class Extensions
{
    public static IQueryable<MyModelVM> SelectVMs(this IQueryable<MyModel> models)
    {
        return models.Select(x => new MyModelVM { MyModelId = x.MyModelId });
    }
}

Using extension method in Linq to Entities

2 Answers2

Linked