CODEWITHSUNDEEP
c#linqlinq-to-sql
raklos
raklos

Reputation: 28545

improve linq call to make one db call

Can I have some help in improving this linq. I'm basically returning speakers from the db but setting HasNew property true if there has been a video by them posted in the past week. 

   public IQueryable<Speaker> GetSpeakers()
    {
        var speakers =  db.Speakers.OrderBy(x => x.DisplayName);
        var newVidsSpeakers = db.Videos.Where(x => x.DatePosted > DateTime.Now.AddDays(-7)).Select(x=> x.Speaker).Distinct();
        foreach (var item in newVidsSpeakers)
        {
            var sp = speakers.SingleOrDefault(x => x.ID == item.ID);
            sp.HasNew = true;
        }
        return speakers;
    }

Upvotes: 0

Views: 141

Answers (2)

Dennis
Dennis

Reputation: 20571

Without knowing what LINQ provider you are using (e.g. the default LINQ To SQL or a Object Relational Mapper like Mindscape Lightspeed or NHibernate) or information on the database schema it is difficult to give any advice.

So until that is known, the best thing that I can do is re-arrange the LINQ statements to give the chosen provider more knowledge/context so that it can better optimise query thus possibly reduce the number of database calls.

Note: You may find that it is not possible to reduce this to a single database call.

This is how I would have written a similar query.

public IEnumerable<Speaker> GetSpeakers()
{
    var speakers = db.Speakers;
    var lastWeek = DateTime.Now.Date.AddDays(-7);
    var recentVideos = db.Videos.Where(x => (x.DatePosted.Date >= lastWeek)).ToArray();
    
    foreach (var speaker in speakers)
        speaker.HasNew = recentVideos.Any(x => (x.Speaker == speaker));
        
    return speakers.OrderBy(x => x.DisplayName);
}
  • Get the speakers unordered as perform ordering to late as possible. If the ordering is for presentation then do it later in the in the view.
  • Store what the date was last week. I'm using DateTime.Now.Date and x.DataPosted.Date so that we are not comparing the time, i.e., you want to find all videos since last week even if you search at 11:59pm.
  • Find all recent videos as an array so that we do not enumerate the collection multiple times in the next part.
  • Iterate over all the speakers and check if that speaker has any recent videos.
  • Then, if required, order the speakers by display name.

Upvotes: 1

BrokenGlass
BrokenGlass

Reputation: 160962

Why are you making the second call anyway? You should just be able to do:

public IQueryable<Speaker> GetSpeakers()
{
    var speakers = db.Speakers.OrderBy(x => x.DisplayName);
    var newVidsSpeakers = db.Videos.Where(x => x.DatePosted > DateTime.Now.AddDays(-7)).Select(x => x.Speaker).Distinct();
    foreach (var speaker in newVidsSpeakers)
    {
        speaker.HasNew = true;
    }
    return speakers;
}

Upvotes: 1

Related Questions