CODEWITHSUNDEEP
c#.netentity-framework
Naor
Naor

Reputation: 24063

exception when calling ToList()

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);
    }

Upvotes: 1

Views: 4232

Answers (2)

Slauma
Slauma

Reputation: 177133

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.

Upvotes: 2

ataddeini
ataddeini

Reputation: 4951

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();
}

Upvotes: 3

Related Questions