vendredi 31 juillet 2015

Entity Framework GroupBy Multiple Tables Using Foreign Key

I have two tables, and I calculate Post Views from Views table using ViewDate column and then I want to get PostTItle using .GroupBy for Entity Framework using foreign key PostID.

Posts table:

PostID   PostTitle
--------------------
1         post one
2         post two 
3         post three
4         post four
5         post five
6         post six

Views table:

ViewID     ViewDate             PostID   
---------------------------------------
  1        2015 07 17 19:00:00        1
  2        2015 07 17 20:00:00        1
  3        2015 07 17 21:00:00        2
  4        2015 07 18 19:00:00        2
  5        2015 07 19 19:00:00        2
  6        2015 07 21 19:00:00        1
  7        2015 07 23 19:00:00        2

so far this is what I have done

    return _db.ObjectSet.Where(p => DateTime.Now >= EntityFunctions.AddDays(p.ViewDate, -14))
        .GroupBy(y => y.PostID, y => y.ViewDate, (ID, Date) => new ExampleViewModel
        {
            Post_ID = ID,
            View_Date = Date.Count()
        }).OrderByDescending(z => z.View_Date).Take(5);

but using this solution I can only assign Post_ID and View_Date to ExampleViewModel, How can I get the PostTitle using the foreign key?

Note: I am trying to get most viewed (Hot) Posts in last 14 days

Please help

Aucun commentaire:

Enregistrer un commentaire