LINQ: No Translation to SQL

Question

Can you explain why example #1 fails with "No Translation to SQL" and example #2 works fine?

All in Repository:

EX#1:

public DomainPerson GetBestPerson()
{       
       var person= GetPeople().Where(p=>p.Quality=="Best").SingleOrDefault();
       return person;
}

public IQueryable<DomainPerson> GetPeople()
{
       var people= from p in Data.Persons
                   select MapToDomain(p);

       return people;
}

private DomainPerson MapToDomain(Data.Person dataPerson)
{   
       DomainPerson domainPerson= new DomainPerson{
                                  Id=dataPerson.Id,
                                  Name=dataPerson.Name,
                                  Quality=dataPerson.Quality,
                                  };
       return domainPerson;
}

EX#2

 public DomainPerson GetBestPerson()
{       
       var person= GetPeople().Where(p=>p.Quality=="Best").SingleOrDefault();
       return person;
}

public IQueryable<DomainPerson> GetPeople()
{
       var people= from p in Data.Persons
                   select new DomainPerson{
                   Id=dataPerson.Id,
                   Name=dataPerson.Name,
                   Quality=dataPerson.Quality,
                   };


       return people;
}

Answer 1

In the first, the expression tree produced by GetPeople() contains MapToDomain, which can't be converted to a SQL expression.

Consider changing MapToDomain so it returns an IQueryable<> (*and fix the remaining calls to MapToDomain(), so they call SingleOrDefault(). Easier done than explained, I did not compile this as I don't have the underlying objects available:

    public DomainPerson GetBestPerson()
    {       
           var person= GetPeople().Where(p=>p.Quality=="Best").SingleOrDefault();
           return person;
    }

    public IQueryable<DomainPerson> GetPeople()
    {
           return MapToDomain(Data.Persons);
    }

    IQueryable<DomainPerson> MapToDomain(IQueryable<Person> persons)
    {
        return persons.select(dataPerson => new DomainPerson{
                                      Id=dataPerson.Id,
                                      Name=dataPerson.Name,
                                      Quality=dataPerson.Quality,
                                      };
    }

Answer 2

The reason is LINQ to SQL works by translating expression trees (Expression<Func<T>>) to SQL and running it on the server. The method you're referencing in #1 is compiled down to IL. LINQ to SQL can't grab an expression tree representation from its body.

The second example is not really compiled down to IL. It's stored at data that can be read and translated at run time. My answer for this question explains this in detail.

Answer 3

It's counter-intuitive that the DomainPerson constructor can be translated, while the MapToDomain method cannot be.

Perhaps there's more to the story than the code that is shown. There's a syntax error in the second example here:

select new DomainPerson{

Is this really an anonymous type in the actual code?

Answer 4

As others have mentioned it has to do with the code that LINQ To SQL works by.

You have to look at it a bit like this. In your 1st example - if we look at the Expressions (ie the Expression Tree) that GetPeople will generate - in human words it generates a tree that specifies that we select from our table - and then apply the function MapToDomain over every row in this table which happens to return an object of the type DomainPerson.

In the second example, this time we not only know that the returning type is of DomainPerson - but we're also able to see which fields gets mapped to which properties (and even more importantly how). That means that when you later run your Where and reference p.Quality Linq To SQL can backtrace this to the SQL table and knows that DomainPerson.Quality maps to the Quality column in the Persons table.

It can look a bit odd at first why the 1st example doesn't work - but suppose your MapToDomain model looked like so:

public void DomainPerson MapToDomain(Data.Person person){
    return new DomainPerson {
        Quality = person.Quality + " Quality";
        //mode code here
    };
}

Now how is LINQ To SQL supposed to know this "special" mapping? - the anwer is simple - it can't.

So to put it simply - when you do anything in your select statement and you want to project your result, the actual projection needs to happen all as "inline" code (I am not certain of the exact term for that) if you wish to do anymore querying (ie. ordering, filtering, so forth) after that - cause otherwise the query engine has got no chance in the world to know how your mapping works.