LINQ to SQL 分组并传递到视图

发布于 2025-01-03 19:01:16 字数 1659 浏览 1 评论 0原文

我对 Asp.Net、MVC3、Linq 以及与之相关的其他所有内容都是新手。我非常习惯 PHP 和 ColdFusion 所以请原谅我的无知！本质上，我想做的是在 ColdFusion 中重新创建情况，在其中我可以执行 cfoutput 的组属性的等效操作。我习惯于只调用存储过程，然后执行

，然后对任何具有非独特数据的列执行另一个内部 cfoutput 。效果就像一个魅力！在 Asp.Net 中则不然。

我想继续使用我的存储过程，该过程从具有一对多关系的两个表返回联接。举例来说，假设我有 3 列：全名、头衔和毕业年份。毕业年份是连接表中的列，因此存储过程的结果如下所示：

Jim Professor 2005
吉姆教授 2008
Jim 教授 2011

我将其发送至 View。我假设视图的工作是根据其中一列（可能是全名）对数据进行分组。我想输出一个包含 3 列的 HTML 表，在这种情况下，我将只有一行：

Jim Professor 2005, 2008, 2011

我在 google 上搜索了大量使用称为组键的东西的示例。这似乎对我没有帮助，因为我对仅输出一个值“Jim”（或无论分组值是什么）不感兴趣，我需要为每一行输出“Jim”和“Professor”值。我的想法是我需要 2 个 foreach 循环，外层循环显示全名和标题，内层循环遍历毕业年份的所有可能匹配项。我似乎无法获得组中的毕业年份，尤其是使用此 IGrouping 语法。键只能存储一个值，并且我需要该行上的每个值，我实际上只需要迭代一两个值。在执行辅助 linq 分组之后，我是否应该尝试创建自定义视图模型，然后将其发送到强类型视图？

编辑： 好的，我有可以工作的代码，但它看起来效率很低，因为我基本上必须重新定义存储过程中的所有列/值。它几乎让我想要忘记存储过程，只使用 LINQ 来完成所有事情。看来我要求的是一种“多列分组”和链接帮助很大。

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);

我当然必须创建一个 ViewModel 才能使其工作。在我看来，我可以使用 for 循环来迭代学位和专业列表。这是最好的方法吗？我通常只需要组键来显示整行信息，并且只想在 20 列行中迭代列表一次或两次，而不是在大多数示例中只显示一次组键并迭代所有内容我懂了。

原文

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.

分享到QQ

分享到微博