Another one of my 'I was trying to do this and having no luck finding solutions' posts, so I'll post my solution and hopefully it will help someone else in the same predicament.
This time I was working on a .NET MVC project, and trying to do some reporting. Basically it's a simple app that records users' steps-per-day. The users are assigned to teams and I wanted to create a page that showed the ranking of the different teams each month based on their total number of steps. As you can imagine, I had three tables. tblTeams, tblUsers, and tblSteps. So, my problem was, I have a model built using Linq to SQL, and it works fine if you're working with one of the objects that relates to its table. But, in this case I just wanted to do a simple join query, with some grouping, and return that to the view to create an html table of the different teams and their totals. So, here's how I made it work:
First off, I created a new very simple model containing the class to hold the results of the query:
namespace StepsChallenge.Models
{
public class TeamTotalsModel
{
public string TeamName { get; set; }
public string Sum { get; set; }
}
}
Then, in my view I added a reference to this model:
Inherits="System.Web.Mvc.ViewPage<IEnumerable<StepsChallenge.Models.TeamTotalsModel>>"
OK, now we're wired-up. On to the data.
I wrote a Linq to SQL query that looked like this in my controller and passed it into an instance of the model class:
public class HomeController : Controller
{
public ActionResult Index()
{
//get monthly steps for the teams
var allTeamsMonthlySteps = from stepRecord in homeModel.Steps
join userRecord in homeModel.Users
on stepRecord.EmpID equals userRecord.EmpID
join teamRecord in homeModel.Teams
on userRecord.TeamID equals teamRecord.TeamID
where stepRecord.Date.Month == DateTime.Now.Month
group stepRecord by teamRecord.TeamName into result
orderby result.Key ascending
select new TeamTotalsModel
{
TeamName = result.Key.ToString(),
Sum = result.Sum(r => r.Steps).ToString()
};
return View(allTeamsMonthlySteps);
}
}
Then finally, back at the view I wrote something like this:
<table>
<tr>
<th>
TeamName
</th>
<th>
Sum
</th>
</tr>
<% foreach (var item in Model) { %>
<tr>
<td>
<%= Html.Encode(item.TeamName) %>
</td>
<td>
<%= Html.Encode(item.Sum) %>
</td>
</tr>
<% } %>
</table>
So, this isn't all that complicated, but the problem I was having is that every search I did on almost every piece of this pointed me back to all the Visual Studio drag-and-drop BS for working with Linq to SQL models where there's a one-to-one relationship between objects and tables. And, I couldn't find much help at all on writing a Linq to SQL query with multiple joins, grouping, and 'where' clauses, all together, and passing it to the view.
Anyway, maybe some poor slob like me will stumble across this and find what he needs. Until next time.
