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)?

1
1
6/12/2018 7:54:13 PM

Accepted Answer

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.

2
6/12/2018 7:53:57 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow