CODEWITHSUNDEEP
asp.net-mvclinq
ebeygin
ebeygin

Reputation: 93

Create report in MVC using LINQ

I need a report that shows the number of unique visitors each day. Here is the query:

SELECT User.Name
       ,Visit.VisitDate
       ,COUNT(Visit.Id) AS NumOfVists
FROM Visit JOIN User ON Visit.UserId = User.Id
GROUP BY User.Name, Visit.VisitDate

I have created the User(table) model and the Visit(table) model using code-first. I also have the dbcontext class which references these two models(tables). In my controller, I need to pass the results of this query into a View.

First of all, do I have to create a MODEL of this recordset? If yes, do I create a model named say, REPORT with 3 members called Name, VisitDate, and NumOfVisits?

If that's the case, what will the LINQ version of this query look like? and how do I pass it to the view?

Upvotes: 0

Views: 464

Answers (2)

Justin Morgan
Justin Morgan

Reputation: 30700

It's hard to say without knowing your exact schema, or how you want the result to be arranged, but I think the LINQ version of that would be something like:

from u in User
from v in u.Visit
group v by new { u.Name, v.VisitDate } into g
select new {g.Key.Name, g.Key.VisitDate, Count = g.Count()};

This will give you an IEnumerable<x> result, where x is an anonymous type with three members: a string, a DateTime, and an Int. You don't need to create a ViewModel for this, but you can if you want to:

from u in User
from v in u.Visit
group v by new { u.Name, v.VisitDate } into g
select new MyViewModel
{
    Name = g.Key.Name, 
    VisitDate = g.Key.VisitDate, 
    Count = g.Count()
};

Upvotes: 1

Thinking Sites
Thinking Sites

Reputation: 3542

You can pass the results of your query directly into the view. You don't need to create anything extra for it, but can if you want to. As for how to pass it into the view use 

return View(your model);

Upvotes: 0

Related Questions