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)
?
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 JOIN
s. 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.