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

bmanmike39Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
C++ 11 Fundamentals

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bmanmike39Author Commented:
Thank you for your help. It got me on the right path.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.