Joining, Grouping, and adding in LINQ

Question

0.00/5 (No votes)

See more:

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

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...
}

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....

Posted 5-Apr-11 4:28am

Matt Kloss

Updated 5-Apr-11 5:33am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Matt Kloss · Accepted Answer · 2011-04-06T02:54:00

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.

Dave Kerr · Accepted Answer · 2011-04-05T06:02:00

Hi,

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

VB

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

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.

Joining, Grouping, and adding in LINQ

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0