EF Core query from Include but no need to return in result

Question

EF Core query from Include but no need to return in result

c# entity-framework entity-framework-core sql

Question

Okay, so this is probably from not knowing how to use EF core correctly as this is my 2nd day using but I appear to have to run .Include() in order to query against the "inner join" this method creates.

I've got this bit working and filtering down my results, but I don't want to return the include in the model as it's making a 256kb JSON response turn into a 2.8Mb JSON response.

    public IEnumerable<Property> GetFeaturedProperties()
    {
        var properties = _db.Properties
                    .Include(p => p.GeoInfo)
                    .Include(p => p.FeaturedUntil)
                    .ToArray();

        var featuredProperties = new List<Property>();
        var DateTimeNow = DateTime.Now;
        var midnightToday = DateTimeNow.AddHours(-DateTimeNow.Hour)
                                       .AddMinutes(-DateTimeNow.Minute)
                                       .AddSeconds(-DateTimeNow.Second-1);

        for (var i = 0; i < properties.Count(); i++)
        {
            var property = properties[i];
            if(property.FeaturedUntil.Any(p => p.FeaturedDate >= midnightToday))                
                featuredProperties.Add(property);                
        }

        return featuredProperties;
    }

So the offending line is .Include(p => p.FeaturedUntil). As this is an Array of dates that can be up anything from 10-1000 rows per joined row. It includes ALL data, even historical so this is really racking up data.

Can I run my query and then run something to .RemoveInclude(p => p.FeaturedUntil)?

The Angry Saxon

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 6/12/2018 7:53:57 PM

You don't need to load the navigation properties in order to apply filtering. When you access a navigation property inside LINQ to Entities query, it's translated to the corresponding SQL construct, including JOINs. No real object/collections are involved. The whole query (with some exceptions) executes at server (database) side.

In your case, the following simple query will do the job:

public IEnumerable<Property> GetFeaturedProperties()
{
    var DateTimeNow = DateTime.Now;
    var midnightToday = DateTimeNow.AddHours(-DateTimeNow.Hour)
                                   .AddMinutes(-DateTimeNow.Minute)
                                   .AddSeconds(-DateTimeNow.Second-1);

    return _db.Properties
        .Include(p => p.GeoInfo) // assuming you want to return this data
        .Where(p => p.FeaturedUntil.Any(p => p.FeaturedDate >= midnightToday))
        .ToList();
}

For more info, see How Queries Work documentation topic.

EF Core query from Include but no need to return in result

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related