联接，分组和添加LINQ [英] Joining, Grouping, and adding in LINQ

问题描述

我是LINQ的新手，正在尝试加入几个表，按1个字段分组，并添加另一个字段的总和.

I am new to LINQ and am trying to join a few tables, group by 1 field and add the sum of another field.

from t in db.Tables
join c in db.Codes on t.ID equals c.Level1_ID
join r in db.Requests on c.Request_ID equals r.ID
join cs in db.Coding_Sheets on r.Coding_Sheet_ID equals cs.ID
where c.Table_Master_ID.Equals("104")
&&r.Disposition.Equals("Approved")
&&r.Disposition_Date > (FYstart)
&&r.Disposition_Date < (FYend)
select new programFocus((string)t.Description.ToString(), Convert.ToInt32(r.Grant_Amount));

上面给了我类似的东西:
援助3
援助7
援助2
教育3
教育7
医学4
医疗2

我想要:

援助12
教育10
医疗6

这是我到目前为止的内容:

The above gives me something like:
Aid 3
Aid 7
Aid 2
Education 3
Education 7
Medical 4
Medical 2

I would want:

Aid 12
Education 10
Medical 6

This is what I have so far:

from t in db.Tables
join c in db.Codes on t.ID equals c.Level1_ID
join r in db.Requests on c.Request_ID equals r.ID
join cs in db.Coding_Sheets on r.Coding_Sheet_ID equals cs.ID
where c.Table_Master_ID.Equals("104")
&&r.Disposition.Equals("Approved")
&&r.Disposition_Date > (FYstart)
&&r.Disposition_Date < (FYend)                                   
group t by t.Description into g
select new programFocus
{
PRGFOCUS = g.FirstOrDefault().Description, //I think this part is right
DOLLARS = g.Sum(i => db.Requests.FirstOrDefault().Grant_Amount) //I need to convert to int32 (i think) but am not able to...
}

感谢您提供的任何帮助...

***更新***
以为我正在取得进步:DOLLARS = g.Sum(i => Convert.ToInt32(db.Requests.FirstOrDefault().Grant_Amount))

但是所有值都为0....

Thanks for any help you can offer...

***Update***
Thought I was making progress with: DOLLARS = g.Sum(i => Convert.ToInt32(db.Requests.FirstOrDefault().Grant_Amount))

but am getting 0 for all the values....

推荐答案

感谢您指出正确的方向.您是对的，从我所看到的来看，将其拆分起来更有意义.当我与LINQ其余部分在同一条语句中对分组和SUM进行汇总时，要花很多时间才能运行.
对结果进行前瞻性学习似乎是解决此问题的更好方法.
这是我现在拥有的，以防它对其他人有所帮助:

Thanks for pointing me in the right direction. You''re right, from what I have seen it makes more sense to split it up. When i had the grouping and SUM in all in the same statement as the rest of my LINQ it took a good amount of time to run.
Doing a foreach on the results seems to be a better way of solving this.
This is what I have right now in case it helps anyone else out there:

<pre lang="xml">public Dictionary<string, int> getProgramFocusData(DateTime FYstart, DateTime FYend)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();
            var matchingRequests = from t in db.Tables
                                   join c in db.Codes on t.ID equals c.Level1_ID
                                   join r in db.Requests on c.Request_ID equals r.ID
                                   join cs in db.Coding_Sheets on r.Coding_Sheet_ID equals cs.ID
                                   where c.Table_Master_ID.Equals("104")
                                   && r.Disposition.Equals("Approved")
                                   && r.Disposition_Date > (FYstart)
                                   && r.Disposition_Date < (FYend)
                                   select new programFocus((string)t.Description.ToString(), Convert.ToInt32(r.Grant_Amount));
            List<programFocus> programs = (List<programFocus>)matchingRequests.ToList();
            Dictionary<string, int> Results = new Dictionary<string, int>();
            foreach (programFocus p in programs)
            {
                string desc = p.PRGFOCUS;
                if (p.PRGFOCUS != null && !Results.ContainsKey(desc))
                {
                    var subresults = from sub in programs where sub.PRGFOCUS == desc select sub;
                    int dollars = 0;
                    foreach (programFocus subsub in subresults)
                        dollars += System.Convert.ToInt32(subsub.DOLLARS);
                    Results.Add(desc, dollars);
                }
            }
            return Results;
        }

我绝不是声称这是实现此目标的最佳方法，实际上，如果您知道实现此目标的更好方法，请告诉我.

I am in no way claiming this the best way to do this, in fact if you know of a better way of accomplishing this, please let me know.

您好，

如果您的第一个结果是:
，这并不是常见的查询问题.

Hi,

Not an uncommon issue for queries, if in your first result you have:

Aid 3
Aid 7
Aid 2
Education 3
Education 7
Medical 4
Medical 2

那么为什么不简单地根据这个结果查询，根据名称对值求和呢?在这种情况下，您实际上是在折叠所选数据.在我工作的地方，我们称其为枢轴，但是我不确定这是否是标准术语.就目前而言，在标准SQL查询中，您将先进行选择，然后从这些结果中再次选择.

不一定需要将整个事情组合成一个linq语句-如上选择数据，然后选择FROM来做最后的枢轴.

then why not simply query against this result, summing up the values based on the name? In this case you are essentially collapsing the selected data. Where I work we call this a pivot, but I''m not sure if that is standard nomenclature. As it stands, in a standard SQL query you would do the first select as you have done and then select again from those results.

There''s not necessarily any need to combine the whole thing into one linq statement - select your data as above and then select FROM that to do the final pivot.

这篇关于联接，分组和添加LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！