CODEWITHSUNDEEP
asp.netlinqasp.net-mvc-3linq-groupcfoutput
Vince Ruppert
Vince Ruppert

Reputation: 41

LINQ to SQL grouping and passing onto a view

I am new to Asp.Net, MVC3, Linq, and everything else related to it. I'm very used to PHP and ColdFusion so pardon my ignorance! Essentially what I am trying to do is re-create the situation in ColdFusion where I can perform the equivalent of a cfoutput's group attribute. I'm so used to just calling a stored procedure and then doing

<cfoutput group="id">

and then another inner cfoutput for any columns that have non-distinct data. Works like a charm! Not so much in Asp.Net.

I would like to stay with using my stored procedure, which is returning a join from two tables with a one-to-many relationship. For example's sake let's say I have 3 columns: a full name, a title, and a graduation year. The graduation year is the column from the joined table, so my result from the stored procedure looks like this:

Jim Professor 2005
Jim Professor 2008
Jim Professor 2011

I am sending this to the View. I am assuming it's the View's job to then group the data based on one of the columns (probably the full name). I want to output an HTML table with 3 columns and in this situation I would have ONE row:

Jim Professor 2005, 2008, 2011

I have googled tons of examples that use this thing called a group key. This does not seem to help me because I'm not interested in just outputting one value "Jim" (or whatever the grouped value is), I need both "Jim" and "Professor" values to be output for each row. My thinking is I would need 2 foreach loops, the outer loop displaying the fullname and title and the inner loop going through all possible matches for the graduation years. I cannot seem to get the graduation years in a group, especially with this IGrouping syntax. The key can only store one value and I need every value on that row, I only really need one or two values to be iterated over. Should I try and create a custom view model after I perform a secondary linq grouping and then send that to a strongly typed view?

EDIT: Ok, I have code that works but it seems very inefficient as I basically have to re-define all of the columns/values that I have from my stored procedure. It almost makes me want to forget stored procedures and just use LINQ for everything. It seems what I was asking for is a kind of "group on multiple columns" and link helped immensely.

var records = db.getRecords();
var groups = from r in records
    group r by new
    {
       r.id
    }
    into row
    select new ListVM()
    {
       id = row.Key.id,
       fullname = row.Select(x => x.fullname).First(),
       title = row.Select(x => x.title).First(),
       degrees = row.Select(x => x.degree_name).ToList(),
       majors = row.Select(x => x.major_name).ToList()
    };
return View(groups);

I of course had to create a ViewModel for this to work. In my view then I can use for loops to iterate over the degrees and majors lists. Is this the best way to do this? I just generally need more than just the group key to display my entire row of information, and only want to iterate over lists once or twice in a 20 column row, as opposed to only displaying the group key once and iterating over everything in most examples I see.

Upvotes: 0

Views: 1602

Answers (2)

Vince Ruppert
Vince Ruppert

Reputation: 41

I believe I've finally found out how to solve what I was looking for. A "group join" seems to solve my problem with ease. The information I found on this page solved it: http://geekswithblogs.net/WillSmith/archive/2008/05/28/linq-joins-and-groupings.aspx

Upvotes: 0

Mariusz.W
Mariusz.W

Reputation: 1377

I'm not that big specialist with Linq and MVC, but faced with your problem I would:

  1. Deal with data preparation in controller/model, after being taught that view should be concerned with displaying things only.

  2. I would use knowledge from these topics to solve your particular problem:

    a) grouping by multiple columns: Group By Multiple Columns

    b) Concatenation as an aggregate function: Using LINQ to concatenate strings

    c) Using aggregates and grouping by multiple columns How to write a LINQ query combining group by and aggregates?

Once you have data in your view model, just display it.

Upvotes: 0

Related Questions