CODEWITHSUNDEEP
c#entity-frameworklinq
DesertFoxAZ
DesertFoxAZ

Reputation: 489

Using methods in Linq expressions

I am using LINQ in a Web API project that accesses the database with Entity Framework. I created my API controllers using the scaffolding

We do not want the Web API to expose our EF model classes so we are using DTO's instead. I've modified the controller methods to use the DTO's and have "Translate" methods that copies values between the model class and DTO for a particular entity and I use that in several methods within the class.

For this post I'm using a simple Person class with three properties: FirstName, LastName, MiddleName.

public IQueryable<PersonDto> GetPersons()
{
    var personDtos = from p in db.Persons
    order by p.LastName, p.FirstName
    select new PersonDto()
    {
        FirstName = p.FirstName,
        LastName = p.LastName,
        MiddleName = p.MiddleName
    };

    return personDtos;
}

private PersonDto Translate(Person p)
{
    if (person == null)
        return null;

    return new PersonDto()
    {
        FirstName = p.FirstName,
        LastName = p.LastName,
        MiddleName = p.MiddleName
    }
}

As you can see there's a refactoring opportunity there by calling the Translate method in the LINQ expression, as below:

public IQueryable<PersonDto> GetPersons()
{
    var personDtos = from p in db.Persons
    order by p.LastName, p.FirstName
    select Translate(p);

    return personDtos;
}

Seems good, except when I run it, I get an exception:

LINQ to Entities does not recognize the method 'Translate'

Is there a way to make this work? Some of our classes in our data model have quite a few properties so it would be good to call the Translate method here if it is possible.

Upvotes: 3

Views: 1096

Answers (3)

Lucas Lim&#227;o
Lucas Lim&#227;o

Reputation: 105

You can use ProjectTo a Queryable Extension of the AutoMapper.

db.Persons.ProjectTo<PersonDTO>().ToList()

Upvotes: -1

Rahul
Rahul

Reputation: 77866

That's cause LINQ to Entities has to translate the expression into SQL query and your method can't be translated since it has no idea how to. One way is to call AsEnumerable() and have your method called on LINQ to objects rather

var personDtos = from p in db.Persons.AsEnumerable()
order by p.LastName, p.FirstName
select Translate(p);

Upvotes: 3

Eric J.
Eric J.

Reputation: 150108

LinqToEntities does not know how to change your method Translate() into something that can be executed in the database. AsEnumerable() will ensure that processing happens client side after it is called:

var personDtos = (from p in db.Persons
    order by p.LastName, p.FirstName
    select p)
    .AsEnumerable()
    .Translate(p);

will get you quite close. However, Translate() takes a single person, not an IEnumerable of them. You could change it to

private IEnumerable<PersonDto> Translate(IEnumerable<Person> persons)
{
    if (persons == null) yield break;

    foreach (var p in persons)
    {
        yield return new PersonDto()
        {
            FirstName = p.FirstName,
            LastName = p.LastName,
            MiddleName = p.MiddleName
        }
    }
}

Upvotes: 1

Related Questions