CODEWITHSUNDEEP
c#entity-frameworklinq
Usman Khan
Usman Khan

Reputation: 686

LINQ Query error: Unable to create a constant value of type. Only primitive types or enumeration types are supported in this context

I have a list of objects that holds contract information List<Contract> endedContracts that has already been filtered out from a larger list. I am now trying to match the info in this list to records in my database with some further filters. 

var endedContracts = _contracts
    .Where(x => x.Contract.IsContractInLastBillingPeriod(referencePeriod))
    .Where(x => x.Contract.IsContractCoveredByLiveInvoices(x.Contract.Invoices)).ToList();

When I run the below query I get the error.

var crystallisedCommissions = _context.Contracts
   .Where(x => x.Statement.Sent)
   .Where(x => x.Statement.Broker == endedContracts.First().Broker.Code)
   .Where(x => !Period.IsPeriodBeforeReferencePeriod(x.Statement.Period, CUT_OFF_PERIOD))
   .Where(x => endedContracts.Any(y => y.Contract.Identifier == x.Identifier
                                    && y.Contract.StartDate == x.ContractStartDate
                                    && y.Contract.EndDate == x.ContractEndDate)).ToList();

The exact error:

Unable to create a constant value of type 'Contract'. Only primitive types or enumeration types are supported in this context."

Upvotes: 1

Views: 5365

Answers (2)

Alsein
Alsein

Reputation: 4765

Notice that endedContracts is a collection in memory and the linq is to be transalted to sql which will be executed within the database service Entity Framework cannot upload the whole data collection to the database, so when the query is executing, there are no endedContracts.

Thus you have 2 options to make it work:

  1. Let the endedContracts be a query object (IQueryable) rather than executing it (ToList()) then the whole query would be translated and executed in the database service.

  2. Executing the query to retrive both data sets and execute in-memory linq (that may be a serious performance problem).

Conclusion, the iteration of both datasets must take place in the same machine, the .NET application or the database.

Upvotes: 1

DavidG
DavidG

Reputation: 118937

endedContracts is an in memory list and cannot be used directly in this query. Instead, get the value you need outside of the query, for example:

//Get the code here
var brokerCode = endedContracts.First().Broker.Code;

var crystallisedCommissions = _context.Contracts
   .Where(x => x.Statement.Sent)
   .Where(x => x.Statement.Broker == brokerCode) //Use the code here
   .Where(x => !Period.IsPeriodBeforeReferencePeriod(x.Statement.Period, CUT_OFF_PERIOD))
   .Where(x => endedContracts.Any(y => y.Contract.Identifier == x.Identifier
                                    && y.Contract.StartDate == x.ContractStartDate
                                    && y.Contract.EndDate == x.ContractEndDate)).ToList();

Upvotes: 1

Related Questions