How to use Extension method inside a LINQ statement?

Question

I have an extension method to convert Address into a oneline string:

public static class AddressExtensions
{
    public static string ToOneLine(this Address address)
    {
        var sb = new StringBuilder();
        sb.Append(address.Street);
        if (!string.IsNullOrWhiteSpace(address.City)) sb.Append(string.Format("{0}, ",address.City));
        if (!string.IsNullOrWhiteSpace(address.State)) sb.Append(string.Format("{0}, ", address.State));
        if (!string.IsNullOrWhiteSpace(address.Zip)) sb.Append(string.Format("{0}, ", address.Zip));
        return sb.ToString();
    }
}

Then I use it to transfer data from domain model into my DTO. The code below uses foreach and works fine:

var entity=_repository.GetAll();
var model = new List<SummaryViewModel>();
        foreach (var e in entity)
        {
            model.Add(new SummaryViewModel
            {
                Address = e.Address.ToOneLine(),
                Name = e.Name,
                Id = e.Id
            });
        }

But when using LINQ,

        var model = entity.Select(e => new SummaryViewModel
        {
            Address = e.Address.ToOneLine(), Id = e.Id
        }).ToList();

I got a System.NotSupportedException.

LINQ to Entities does not recognize the method 'System.String ToOneLine
(Domain.Models.Address)' method, and this method cannot be translated 
into a store expression.

What happened? I thought both method should work.

What is the correct way to use my extension method inside a LINQ statement?

Reopened since the solution in the proposed duplicate question does not apply here. — D Stanley, Jul 03 '14 at 15:09
As Daniel A. White said - extension methods are only available for methods which are "wrapped/implemented" internally in orm. That's why it's considered as bad practice to return collection of data without dereffered execution (for example .ToList) when you're working with database. — fex, Jul 03 '14 at 15:12

D Stanley · Accepted Answer · 2014-07-03T15:37:21.917

7

Becasue EF can't translate that extension method to SQL. Simplest way to fix it is to shift to Linq-to-Objects using AsEnumerable() (which is effectively what foreach does):

var model = entity.AsEnumerable()
                  .Select(e => new SummaryViewModel
{
    Address = e.Address.ToOneLine(), Id = e.Id
}).ToList();

Unlike ToList, using AsEnumerable does not create an additional collection in memory, it just binds the Linq calls to Enumerable instead of Queryable, so the mapping is done in memory instead of in SQL.

edited Jul 03 '14 at 15:37

answered Jul 03 '14 at 15:10

D Stanley

149,601
11
178
240

I would be careful about this type of code. If you look at the query that EF generates for this code, it will select all of the columns from your entity instead of just the ones you are projecting. The reason is that it is projecting after it has pulled from the database since you are treating it as an IEnumerable. This can have performance implications if you do not have a covering index. – Dismissile Jul 03 '14 at 17:44

score 0 · Answer 2 · answered Jul 03 '14 at 15:07

0

Resolve your query, then map it.

var model = entity.ToList().Select(e => new SummaryViewModel
    {
        Address = e.Address.ToOneLine(), Id = e.Id
    }).ToList();

As it stands right now, by using deferred execution, the database is trying to interpret your ToOneLine() extension method. By resolving it immediately with ToList(), you can properly iterate over the collection on the server and map it to your model.

answered Jul 03 '14 at 15:07

David L

32,885
8
62
93

Thanks for the answer. But it appears `AsEnumerable()` is the way to go. – Blaise Jul 03 '14 at 15:45
@Blaise They do the same thing. Changing it to Enumerable and calling an extension method is going to require it to execute the query. It doesn't work on IQueryable because EF can't translate it into SQL. AsEnumerable isn't doing magic here. The only thing I don't like about this answer is the double call to ToList, which kind of smells. – Dismissile Jul 03 '14 at 17:37

score 0 · Answer 3 · edited May 23 '17 at 12:23

This line:

foreach (var e in entity)

implicitly does the same thing as .AsEnumerable(). Which enumerates and pulls object out of your database, when using the LINQ-expression LINQ2Entities is trying to convert it to a database expression. Ie executing it outside of the model-scope.

For a more in depth description of what foreach actually does, have a look at How do foreach loops work in C#? , in reality entity doesn't have to be an IEnumerable<T>, it just has to implement a GetEnumerator(). Cool, huh? :)

Ie in your solution, doing

var model = entity.AsEnumerable().Select(e => new SummaryViewModel
    {
        Address = e.Address.ToOneLine(), Id = e.Id
    }).ToList();

would solve the issue.

How to use Extension method inside a LINQ statement?

3 Answers3