• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

How would I loop through all the records in my db and add up all the numbers in a column, using entity framework (ASP.NET C#)

How would I loop through all the records in my db and add up all the numbers in a column, using entity framework, and put the results in a label?

This is what I have:
LAMEMEntities2 ic = new LAMEMEntities2();

account_total ui = ic.account_total.ToList().Where(x => x.membership_number == uIdentity).First();

// How do I add up (loop through) the cost for each product user selected?

var name = ui.name;
var prod = ui productName;
var cost = ui.price; 

// show results in a label

Open in new window

0
bmanmike39
Asked:
bmanmike39
2 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
You need to use LINQ. Could you construct a sample to show how your table looks like, for example:

name        productName       price
--------        -------------------       -----------
Jack             A                              10.0
Jack             B                              5.0
Debi            C                               7.0
Debi            B                              5.0
etc.

Do you want to see:

Jack                  15.0
Debi                 12.0
?
0
 
bmanmike39Author Commented:
Jack                  15.0
Debi                 12.0
?
0
 
Snarf0001Commented:
You shouldn't call .ToList() first, that's going to bring back all records from the database and then start filtering them.  If you call .ToList() at the end, it will pass the where clause to the database and bring back a small subset.

You need to use .GroupBy (as well before the .ToList), and that will aggregate on the database side and bring you back only a small set from the db.

LAMEMEntities2 ic = new LAMEMEntities2();

account_total ui = ic.account_total.ToList().Where(x => x.membership_number == uIdentity).First();

var totals = ic.account_total.Where(x => x.membership_number == uIdentity).GroupBy(g => new { g.name, g.productName })
	.Select(x => new {
		x.Key.name,
		x.Key.productName,
		TotalPrice = x.Sum(p => p.price),
		TotalRecords = x.Count()
	}).ToList();

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
bmanmike39Author Commented:
I get back my query, not the total.

I also really do not need it grouped considering its only one user.
0
 
Snarf0001Commented:
Okay, you lost me a bit then.
As I understood, you did need it grouped as you're still trying to "loop through all records adding".

Can you post a sample of what data you're getting back, and what you want it to be?
That will help.
0
 
Fernando SotoRetiredCommented:
Hi bmanmike39;

Can you please restate the question it is a little confusing to me. For example you state, "How do I add up (loop through) the cost for each product user selected?", does that mean that you want the total cost returned back of all products as one value / Grand Total or something else? In your original question you also have this

var name = ui.name;
var prod = ui productName;
var cost = ui.price;

Where ui is the value returned from the EF query. This value does NOT represent one single monetary value but a single value of account_total.
0
 
bmanmike39Author Commented:
I figured it out. It was:

var TotPrice  = ic.InvoiceItems.Where(v => v.membership_number == uIdentity).Sum(x => (decimal)x.price);

Thanks for the help!
0
 
bmanmike39Author Commented:
Thank you for your help. It got me on the right path.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now