Linq with multiple tables

Question

Working with Entity Framework in ASP.Net MVC 4, and need to use linq to output a line of information.

I have an ArticleView table and an Article table. I need to output an article with the most activity within a 24 hour period.

Here's my ArticleView table:

int ArticleID
DateTime ViewCreated

Article table:

int ID
string title

Sample Data:

ArticleView:
ArticleID   ViewCreated
   2          4/8/2014 1:48:40 PM
   2          4/8/2014 1:50:40 PM
   2          4/8/2014 1:55:20 PM
   3          4/8/2014 12:07:30 PM

Note: ViewCreated is automatically generated once someone views an article.

Article:
    ID       Title
    2        Article2
    3        Article3

Expected Output:

Article with the most activity within 24 hours is:

Article2(3)

What I have to work with:

var articles = db.Articles;
var articleviews = db.ArticleViews;

Just not sure how to approach this.

Will N · Accepted Answer · 2014-04-08 23:56:20Z

2

Something like this:

var viewsById = db.ArticleViews
    .Where(av => av.ViewCreated >= startDateTime && av.ViewCreated < endDateTime)
    .GroupBy(av => av.ArticleId)
    .Select(g => new { ArticleId = g.Key, Count = g.Count() })

var highestCount = viewsById.Max(v => v.Count);

var topArticles = viewsById.Where(a => a.Count == highestCount);

var topArticle = topArticles.First();

var message = String.Format("Article id: {0}, views: {1}", 
                         topArticle.ArticleId, topArticle.Count);

Filter the views to be within the specified date range.
Group them by article Id.
Make an anonymous object of each one, storing the article Id and the count of views for that article.
Take the one with the highest count.

edited Apr 8, 2014 at 23:56

answered Apr 8, 2014 at 23:47

Will N

1,61014 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brad Rem · Accepted Answer · 2014-04-09 00:21:32Z

0

You can do this is one statement by using a join:

var mostViewedArticleTitleAndCount = db.Articles
     .Join(ArticleViews.Where (av => av.ViewCreated > sinceDate),
        a => a.ID,
        v => v.ArticleID,
        (a,v) => new { a,v })
    .GroupBy (g => g.a.ID)
    .Select (g => new { g.Key, Title = g.First ().a.Title, Count = g.Count ()})
    .OrderByDescending (g => g.Count)
    .Select (g => g.Title + "(" + g.Count + ")")
    .First ();

For your test data this will output:

Article2(3)

edited Apr 9, 2014 at 0:21

answered Apr 9, 2014 at 0:16

Brad Rem

6,0262 gold badges27 silver badges50 bronze badges

Comments

AD.Net · Accepted Answer · 2014-04-09 01:10:48Z

0

To get the article you can simply do this:

var article = db.Articles.OrderByDescending(a=>a.ArticleViews.Count())
              .Take(1).FirstOrDefault();

Or

var article = db.Articles.OrderByDescending(a=>a.ArticleViews.Count())
              .FirstOrDefault()//this should work too

One shorter way of a suggested solution here is:

var mostViewedArticle = db.ArticleViews
    .Where(av => av.ViewCreated >= startDateTime && av.ViewCreated < endDateTime)
    .GroupBy(av => av.ArticleId)
    .OrderByDescending(g=> g.Count())
    .Select(g => g.First().Article)
    .Take(1)

Filtering by some date range
Grouping by ArticleId
Ordering by with the most count on top
Selecting the Article through navigation property
Taking only the First one.

edited Apr 9, 2014 at 1:10

answered Apr 9, 2014 at 1:02

AD.Net

13.4k2 gold badges30 silver badges47 bronze badges

Collectives™ on Stack Overflow

Linq with multiple tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related