Optimizing a Query in Entity Framework

I'm wondering what can be done to optimize the following. I've tried to note the points at which most of the overhead is occurring. The main two points I see are

• The call to DiffDays from within the LINQ query.

• The ordering by DateTime LastOnline before putting results in memory.

Right now, with only 2000 rows, I'm looking at ~1-2 seconds for the results to return, average. Removing DiffDays and Ordering by DateTime cuts this down to about 600ms, but that's still a far cry away from my desired 50ms turnaround.

What are some things I can do to significantly improve the performance of the below code?

My dbContext:

```
public class DbContext : DbContext {
public DbContext() : base("name=DefaultConnection") { }
public DbSet Users { get; set; }
public DbSet Requests { get; set; }
public DbSet Reviews { get; set; }

public DbSet PromoCodes { get; set; }
public DbSet Photos { get; set; }

public DbSet Notifications { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder) {
modelBuilder.Conventions.Remove();
modelBuilder.Conventions.Remove();
modelBuilder.Configurations.Add(new PhotoConfiguration());
modelBuilder.Configurations.Add(new UserConfiguration());

base.OnModelCreating(modelBuilder);
}

}

public class PhotoConfiguration : EntityTypeConfiguration {
public PhotoConfiguration() {
// One-to-Many
HasRequired(s => s.User).WithMany(s => s.Photos).HasForeignKey(s => s.UserId);
}
}

public class NotificationConfiguration : EntityTypeConfiguration {
public NotificationConfiguration() {
// One-to-Many
HasRequired(s => s.User).WithMany(s => s.Notifications).HasForeignKey(s => s.UserId);
}
}

public class UserConfiguration : EntityTypeConfiguration {
public UserConfiguration() {
//

In a nutshell, and I could be misreading here... but you have a for loop that iterates 10 times. Each of those iterations makes a database call using the same parameters. I'm not sure what this could be accomplishing.

My recommendations would be:

-
Don't declare any variables within the for loop that you don't have to have for that specific loop. Each time you do that, you're having to allocate a piece of memory which makes it that much slower (even if it's a fraction of a millisecond).

-
Get the oldest possible value for the lower search range, and the youngest possible value for the upper search range - in date form - and then do a search on birthdate being between those two dates. You won't be causing the database to have to do business logic then.

-
Include the photos when you make the initial call, then later do something like
results.Where(r => !r.Photos.Any()).Select(r => r.Photo = myDefaultPhoto).ToList();

-
Once you've got a dataset to work with, having filtered out all the entries that you KNOW aren't acceptable results, then you can call the orderby the smaller set which should be a bit more performant.

StackExchange Code Review Q#80567, answer score: 2