I'm pretty new to ASP/MVC but have had some prior programming experience.

I am trying to retrieve statistics about URL clicks - total clicks and unique clicks by IP address. I started with:

ViewBag.ClicksToday = context.EmailLinkClicks
     .Where(c => c.CreatedOn == DateTime.Today).Count();

ViewBag.ClicksWeek = context.EmailLinkClicks
     .Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7)).Count();

ViewBag.ClicksMonth = context.EmailLinkClicks
     .Where(c => c.CreatedOn.Month == DateTime.Today.Month).Count();

ViewBag.UniqueClicksToday = context.EmailLinkClicks.Where(c => c.CreatedOn == DateTime.Today)
     .Select(c => c.IPAddress).Distinct().Count();

ViewBag.UniqueClicksWeek = context.EmailLinkClicks.Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7))
     .Select(c => c.IPAddress).Distinct().Count();

ViewBag.UniqueClicksMonth = context.EmailLinkClicks.Where(c => c.CreatedOn.Month == DateTime.Today.Month)
     .Select(c => c.IPAddress).Distinct().Count();

Then I thought why not make it a function:

private static int GetClicks(AppContext context, string period, bool unique)
    int Clicks = 0;
    switch (period)
        case "today":
            var ClicksToday = context.EmailLinkClicks
                .Where(c => c.CreatedOn == DateTime.Today);
            Clicks = unique ? ClicksToday.Select(c => c.IPAddress).Distinct().Count()
                : ClicksToday.Count();
        case "week":
            var ClicksWeek = context.EmailLinkClicks
                .Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7));
            Clicks = unique ? ClicksWeek.Select(c => c.IPAddress).Distinct().Count()
                : ClicksWeek.Count();
        case "month":
            var ClicksMonth = context.EmailLinkClicks
                .Where(c => c.CreatedOn.Month == DateTime.Today.Month);
            Clicks = unique ? ClicksMonth.Select(c => c.IPAddress).Distinct().Count()
                : ClicksMonth.Count();
    return Clicks;

But I still have to call quite a lot of code:

ViewBag.ClicksToday = GetClicks(context, "today", false);
ViewBag.ClicksWeek = GetClicks(context, "week", false);
ViewBag.ClicksMonth = GetClicks(context, "month", false);

ViewBag.UniqueClicksToday = GetClicks(context, "today", true);
ViewBag.UniqueClicksWeek = GetClicks(context, "week", true);
ViewBag.UniqueClicksMonth = GetClicks(context, "month", true);

My questions:

  1. Is there any way to further simplify/reduce this code?

    • perhaps have the total & unique in one value? [total],[unique]
  2. Is it poor practice to pass period as a string and then interrogate it?

  3. Where should you store these kind of database queries? It currently resides in my controller and then returned to the view.


1 Answer 1


Here's the first thing I came up with. Wrote it in Notepad, so it might not compile exactly as is.

private static int GetClicks(AppContext context, Period period, bool unique)
    int Clicks = 0;
    switch (period)
        var query = context.EmailLinkClicks.AsQueryable();
        case Period.Today:
            query = query.Where(c => c.CreatedOn == DateTime.Today);
        case Period.Week:
            query = query.Where(c => c.CreatedOn > System.Data.Entity.DbFunctions.AddDays(c.CreatedOn, -7));
        case Period.Month:
            query = query.Where(c => c.CreatedOn.Month == DateTime.Today.Month);

    var ipAddresses = query.Select(c => c.IpAddress);

    if (unique)
        query = query.Distinct();

    return query.Count();

enum Period 

Update: As to where to store these queries... Doing it in your controller isn't the worst thing in the world. There are a ton of different places you could put that code though. The Repository pattern seems to be popular. I'm not strict about that kind of thing though.

Update: if you wanted to get both the total and unique stats in the same call, you could do something like this: https://gist.github.com/alexdresko/8059762/bcf233b86869042c4b6f360f6b461999b2bf4899

Update: But what you probably want, assuming you want to create some kind of report, is this: https://gist.github.com/alexdresko/8059762/c4afa948f020e8395bb27d565269c914aad926a7

That one will allow you to simply iterate over the collections of stats in your view, display all relevant information.

  • \$\begingroup\$ Good advice in general, but: 1. I'm not completely sure about the use of .AsQueryable(), I think the omitting that and explicitly specifying the type of query would be cleaner. 2. What happens when period has an invalid value? 3. You're not using ipAddresses anywhere, which means you're not calculating the case when unique == true correctly. \$\endgroup\$
    – svick
    Dec 22, 2013 at 1:10
  • \$\begingroup\$ 1. You have to use AsQueryable() or the query chaining I'm doing in the example won't work. 2. Period won't have an invalid value because I'm using an enum. 3. Oops, you're right about that. I did mention in my OP that I wrote that code in notepad. \$\endgroup\$ Dec 22, 2013 at 3:28
  • \$\begingroup\$ 1. You don't, you can write IQueryable<EmailLinkClick> query = context.EmailLinkClicks; and it will work. 2. That's not how enums work in .Net, you can always do something like (Period)42 and you're going to get an invalid value. \$\endgroup\$
    – svick
    Dec 22, 2013 at 4:13
  • \$\begingroup\$ 1. What's the difference between what you wrote and what I wrote, except that mine is quite a bit shorter? 2. Who does that? :) And how would you get around it without a bunch of silly code? \$\endgroup\$ Dec 22, 2013 at 4:33
  • \$\begingroup\$ 1. The difference is very small, yes. I think that my approach makes it easier to understand what does the line actually do. But your way is okay too. 2. I don't think default: throw InvalidOperationException();, or something like that is silly code. \$\endgroup\$
    – svick
    Dec 22, 2013 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.