exception when calling ToList()

Question

As continue to this question, I have this method:

public IEnumerable<Splitting> Get(Guid companyId, long customerId)
{
    CustomerRepository customersRep = new CustomerRepository();
    var customers = customersRep.Get(companyId);
    return GetQuery().Join(customers,
         s => s.CustomerId,
         c => c.Id,
         (s, c) => new { s, c }).
         Where(sc => sc.c.Id == customerId).
         Select(sc => sc.s);
}

When I am doing:

var query=Get(someGuid, someCustomerId);
query.ToList(); //This throws an exception

I got the exception:

Unable to create a constant value of type 'MyProj.Domain.Business.Entities.Company.Customers.Customer'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

What is the meen of this exception and how can I solve it?

UPDATE:
GetQuery() returns Context.CreateObjectSet<T>() where T in this case is Splitting class.
CustomerRepository.Get(companyId) is:

    public IEnumerable<Customer> Get(Guid companyId)
    {
        return GetQuery().
            Where(x => x.CompanyId == companyId).
            Where(x => x.IsDeleted == false).
            AsEnumerable().
            OrderBy(x => x.Name.IsNumeric()).
            ThenBy(x => x.Name);
    }

What is `GetQuery()`? Can you show the code for both `GetQuery` and `CustomerRepository.Get`? — Alex Aza, Jun 03 '11 at 02:28
This doesn't have anything to do with `ToList()`. You'll get the same exception if you use `Count()` or if you iterate with a `foreach`. — John Saunders, Jun 03 '11 at 18:38

score 3 · Answer 1 · answered Jun 03 '11 at 02:08

You've got a deferred query there in IEnumerable<Splitting> Get(Guid companyId, long customerId) and I think you're trying to execute it out of scope.

Add ToList() to the end of your Select(sc => sc.s) and you should be good to go. That will ensure the query actually gets executed before it returns.

public IEnumerable<Splitting> Get(Guid companyId, long customerId)
{
    CustomerRepository customersRep = new CustomerRepository();
    var customers = customersRep.Get(companyId);
    return GetQuery().Join(customers,
         s => s.CustomerId,
         c => c.Id,
         (s, c) => new { s, c }).
         Where(sc => sc.c.Id == customerId).
         Select(sc => sc.s).
         ToList();
}

Meaning that the query is evaluated when it's used, not when it's defined. In EF for example, you can dynamically build a query across multiple methods and then execute it once when it's done, which is more efficient. [Here's a quick overview](http://roque-patrick.com/windows/final/bbl0392.html). — ataddeini, Jun 03 '11 at 02:23

score 2 · Accepted Answer · answered Jun 03 '11 at 18:32

I think that the problem is your customers collection which you pass into the Join method. This collection isn't an IQueryable but an in-memory collection which EF will pull from the database first when your Join gets executed. It's an in-memory collection because you are using AsEnumerable() in your IEnumerable<Customer> Get(Guid companyId) method and then order the result. While this would work in LINQ to Objects it is not allowed in LINQ to Entities and leads to the exception you had. (If Get would return a collection of primitive types (like int) instead of a Customer collection it would also work in LTE, as the exception says.)

Now, I see that you need AsEnumerable() because of this IsNumeric() method in your OrderBy() clause which could not be translated to SQL. But do you actually need the sorting when you pass in the result into Join? You return finally entities of type Splitting and if you would want a special sorting you had to add it somewhere before the final ToList().

So, what I would try is to omit AsEnumerable() and the sorting:

public IQueryable<Customer> Get(Guid companyId)
{
    return GetQuery().
        Where(x => x.CompanyId == companyId).
        Where(x => x.IsDeleted == false);
}

This way you have an IQueryable. I'd expect that this would work and the whole query including the Join would be executed in the database.

exception when calling ToList()

2 Answers2