Calculating monthly reports or statistics – MVC Entity Framework LinQ

I am using MVC (C#) and the Entity Framework. I have a sql server database with tables called Profile, Gallery, ArtWork :

PROFILE:
UserName,
FirstName,
etc…

  • Control the Sql that Linq sends to db explanation
  • How does ADO.Net know which version of the SQL Native Client to use if you have both SQL 2005 and 2008 in your environment?
  • Select WHERE @where = @term returning an empty row
  • Sql query for full-text on a column
  • Aggregate totals and create data for weeks that no data exists
  • SQL Server Stored Procedure - Executing different queries by CASE
  • GALLERY:
    GalleryID,
    UserName,
    GalleryName,

    ARTWORK:
    ImageGuid,
    GalleryID,
    Description,
    PublishDate,
    Views,
    Downloads,

    I have the following relationship between the tables:

    Profile->Gallery->ArtWork (Foreign keys in bold above)

    What I would like to know is the most efficient way to aquire the following for the stats page:

    1. The Top 10 Downloaded bits of art work of the month

    2. The most Downloaded designer of the month.

    Its easy enough to construct the LinQ for the above but I don’t want to be hitting the database everytime a user views the stats page. There could be 30,000+ designers having 5000+ bits of artwork each, and i dont want these results being calculated everytime someone hits the stats page. I only want to hit the database daily for stat 1, and monthly for stat 2.

    Am I best using a view for each stat in the database and hitting that all the time, or would it be best setting a timer to hit the database daily and monthly (if so how please?), or is there a better way.

    Thanks In Advance

  • MVC create SQL database using SMOLite
  • How to migrate a Visual Studio 2012 ASP.Net MVC 4 project to use an SQL Server instance instead of a data file?
  • How to display image from SQL image table that has imagepath
  • Configuring ASP.NET MVC app's IIS 7.5 Application Pool identity as login on SQL Server 2008 R2
  • unable to access remote database in C#
  • Populating Html.DropDownList with foreign key table vlaues
  • One Solution collect form web for “Calculating monthly reports or statistics – MVC Entity Framework LinQ”

    I would store the query result in the application Cache. If you do this it doesn’t matter so much how you construct the query, since it will only be run once until the cached item expires.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.