LINQ refactoring help needed

Question

How would you refactor this code, with respect to the LINQ? I'm new to LINQ and haven't quite have a good handle on more complex queries (nested, grouping).

Can all of these three statements and foreach loop been converted into one LINQ statement?

void AddSeries(Series series, int phraseId)
{
    using (var db = Database.Instance)
    {
        foreach (var date in db.Ad.Select(ad => ad.DateTime.Date).Distinct())
        {
            var phraseCount = (from pc in db.PhraseCount
                               where pc.DateTime.Date == date && 
                                     pc.PhraseId == phraseId
                               select pc.Count).SingleOrDefault();

            var adCount = db.Ad.Where(ad => ad.DateTime.Date == date).Count();

            series.Add(date, phraseCount / adCount);
        }
    }
}

Answer 1

Here's my first shot. Hard without having your model.

var q = from ad in db.Ad
        group ad by ad.DateTime.Date into g
        select new
        {
            AdCount = g.Count(),
            Date = g.Key,
            PhraseCount = (from pc in db.PhraseCount
                           where pc.DateTime.Date == g.Key
                                && pc.PhraseId == phraseId
                           select pc).Count()
        }