CODEWITHSUNDEEP
c#lambdaentity-framework-4linq-to-entities
Sachin Kainth
Sachin Kainth

Reputation: 46760

Entity Framework: Unable to create a constant value of type 'System.Collections.Generic.IList`1'

This has caused me no end of problems today. I have this simple query

var result =
    DataContext.Accommodations.Where(a => 
        (criteria.MinPrice == null || a.AccommodationRates.Any(r => r.From >= criteria.MinPrice)) &&
        (criteria.MaxPrice == null || a.AccommodationRates.Any(r => r.To <= criteria.MaxPrice)) &&
        (criteria.Locations == null || criteria.Locations.Count == 0 || a.AccommodationPlaceJoins.Any(j => criteria.Locations.Contains(j.Place.PlaceName)))
);

The last line of this query is causing me problems

(criteria.Locations == null ||
 criteria.Locations.Count == 0 ||
 a.AccommodationPlaceJoins.Any(j => criteria.Locations.Contains(j.Place.PlaceName)))

The error it gives is

Unable to create a constant value of type 'System.Collections.Generic.IList`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

I'm not even trying to create a list. All I'm trying to do here is bring back accommodations which are associated to a place (where the place name in the Place table which is linked to the Accommodation table via the AccommodationPlaceJoin table) is equal to any one of the place names in criteria.Locations (which is of type IList).

I've tried changing this line to this, but it didn't work.

(criteria.Locations == null ||
 criteria.Locations.Count == 0 ||
 a.AccommodationPlaceJoins.Any(j => criteria.Locations.Any(l => l == j.Place.PlaceName)))

Upvotes: 18

Views: 22740

Answers (2)

Eric Conklin
Eric Conklin

Reputation: 747

I had this same issue when trying to pass a list of strings for comparison into an EF core query recently and found that it resolves the issue to assign an empty array or empty list to the value in case it is null rather than doing the null check right in the EF Core query.

This allows you to query the EF Core database without the null error and you don't need a bunch of conditional branching for separate queries - just one query with a bit of null coalescence.

So your code fixed this way would look like this:

var locations = criteria.Locations == null ? new List<Location>() : criteria.Locations;
    var result =
    DataContext.Accommodations.Where(a => 
        (criteria.MinPrice == null || a.AccommodationRates.Any(r => r.From >= criteria.MinPrice)) &&
        (criteria.MaxPrice == null || a.AccommodationRates.Any(r => r.To <= criteria.MaxPrice)) &&
        (locations.Any() ? a.AccommodationPlaceJoins.Any(j => criteria.Locations.Contains(j.Place.PlaceName))) : true);

Keep in mind I am not sure of the type for locations or accomodationplacejoins so I cannot be sure the above code will exactly work but the general idea is we are doing a .Any() instead of checking for null. This way we can keep our query a little simpler. We are then using a ternary expression within the last block of our where clause so if the locations list turns out to be empty, we skip the condition entirely and just return true which coalesces the empty list if it is empty.

Upvotes: 0

Slauma
Slauma

Reputation: 177163

The constant value EF can't create is null for the comparison criteria.Locations == null. You need to split the query into two cases and do the check for the empty list outside of the query, for example like so:

var result = DataContext.Accommodations.Where(a => 
    (criteria.MinPrice == null || 
        a.AccommodationRates.Any(r => r.From >= criteria.MinPrice)) &&
    (criteria.MaxPrice == null ||
        a.AccommodationRates.Any(r => r.To <= criteria.MaxPrice)));

if (criteria.Locations != null && criteria.Locations.Count > 0)
{
    result = result.Where(a => a.AccommodationPlaceJoins
        .Any(j => criteria.Locations.Contains(j.Place.PlaceName)));
}

Edit

BTW: Composing the whole query would make it better readable in my opinion and will simplify the SQL that has to be sent to the database:

IQueryable<Accommodation> result = DataContext.Accommodations;

if (criteria.MinPrice != null)
    result = result.Where(a => a.AccommodationRates
        .Any(r => r.From >= criteria.MinPrice));

if (criteria.MaxPrice != null)
    result = result.Where(a => a.AccommodationRates
        .Any(r => r.To <= criteria.MaxPrice));

if (criteria.Locations != null && criteria.Locations.Count > 0)
    result = result.Where(a => a.AccommodationPlaceJoins
        .Any(j => criteria.Locations.Contains(j.Place.PlaceName)));

Upvotes: 49

Related Questions