CODEWITHSUNDEEP
.netdatabaseentity-frameworklinqentity-framework-6
J. Doe
J. Doe

Reputation: 687

Executing Linq query throws exception

I have a method in which I filter a table and return those rows that match the filer. Here's the method:

public List<RealEstate> GetMatchingRealestatesQuery(RealestateFilter f, RealestateSiteDbContext context)
    {
        IQueryable<RealEstate> realestates = context.RealEstates.Where(x => x.Status == RealEstateStatus.Open);
        realestates = realestates.Where(x => f.Cities.Any( y => x.ZipCode.City.Id == y.Id));
        if (f.MaxRent > 0)
            realestates = realestates.Where(x => x.Rent <= f.MaxRent);
        if (f.MinArea > 0)
            realestates = realestates.Where(x => x.Area >= f.MinArea);
        if (f.MinRooms > 0)
            realestates = realestates.Where(x => x.Rooms <= f.MinRooms);
        realestates = realestates.Where(x => f.RealestateTypes.Has(x.Type));
        realestates = realestates.Where(x => f.RentalPeriod.Has(x.RentalPeriod));

        return realestates.ToList();
    }

However, whenever I call the method I get the following exception:

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

I'm simply building an IQueryable and then executing the query by calling .ToList. What's the cause of this exception?

Upvotes: 0

Views: 43

Answers (1)

Rion Williams
Rion Williams

Reputation: 76557

The issue here is that LINQ doesn't know how to translate your complex objects / classes into SQL code.

Generally, if you are going to be attempting to filter out calls and comparing them with objects in memory, you'll need to ensure that LINQ knows how to handle those (e.g. use only collections of primitive types): 

public List<RealEstate> GetMatchingRealestatesQuery(RealestateFilter f, RealestateSiteDbContext context)
{
    // This works just fine as status is going to be a boolean
    var realestates = context.RealEstates.Where(x => x.Status == RealEstateStatus.Open);
    // Here's where things get tricky as LINQ doesn't know what City is
    // Is there some ID that you could use that might make this easier, 
    // such as x.ZipCode.City.CityId or something?
    realestates = realestates.Where(x => f.Cities.Any( y => x.ZipCode.City == y));

    // Other code omitted for brevity

    return realestates.ToList();
}

If that isn't possible, then usually these kinds of queries seldom get to take advantage of deferred execution and often require you to store the entire collection in memory and then filter, in memory via a ToList() call :

 public List<RealEstate> GetMatchingRealestatesQuery(RealestateFilter f, RealestateSiteDbContext context)
{
    // This will wipe out any deferred execution and perform the
    // rest of your operations in-memory
    var realestates = context.RealEstates.Where(x => x.Status == RealEstateStatus.Open).ToList();

    // Other code omitted for brevity

    return realestates;
}

Again - this approach isn't really ideal as you are pulling back far more data than you need, but to avoid that you'll just need to restructure exactly what you are querying and ensure that LINQ knows how to translate it.

Update (The Actual Fix)

The actual resolution to the issue involved removing the use of an actual collection of entities being used within the lamdba call on the following line :

realestates = realestates.Where(x => f.Cities.Any( y => x.ZipCode.City.Id == y.Id));

Since LINQ doesn't know how to translate the properties of the Cities collection and evaluate them, this blows up. However, you can store the collection of objects being queried, in memory, as primitive types and you should then be able to use :

var cities = f.Cities.Select(c => c.ZipCode.City.Id).ToArray();
realestates = realestates.Where(x => cities.Any(c => c == x.Id);

Upvotes: 1

Related Questions