How to use Linq to group, sum or retrieve total. My current code returns nothing

Question

I have this code which I'm trying to use in retrieving the fish pond with the highest use of feed in a month, so I need to group by Pool_Name, sum feed usage and then select the highest record after sorting.

Here's an example code:

var hfeed = db.feed_fish
    .Where(x => (DbFunctions.TruncateTime(x.Feed_Time) >= frm 
              && DbFunctions.TruncateTime(x.Feed_Time) <= todat))
    .GroupBy(x => x.Pond_Name)
    .Select(y => new feed_fish
    {
        Pond_Name = y.Key,
        Total_feed_weight = y.Sum(d => d.Total_feed_weight)
     })
    .OrderByDescending(y=>y.Total_feed_weight).First();

A sample of the data...

Pond_Name      Total_Feed_Weight
Pond 1          56
Pond 2          33
Pond 1          45
Pond 2          54

What I need to return is a list or iQueryable that totals the Total_feed_weight and returns the highest so...

Pond 1      101
Pond 2      87

I should be also able to access first so I get the highest consuming pond.

Updated

 List<feed_fish> hfeed = db.feed_fish.Where(x => (DbFunctions.TruncateTime(x.Feed_Time) >= frm && DbFunctions.TruncateTime(x.Feed_Time) <= todat))
                            .ToList().GroupBy(r => r.Pond_Name, (key, enumerable) =>
                       {
                           return new feed_fish { Pond_Name = key, Total_feed_weight = enumerable.Sum(k => k.Total_feed_weight) };
                       }).OrderByDescending(t => t.Total_feed_weight).ToList();

Output

Pond Name: FarmAx_web.feed_fish. Total Feed:

Update: To test the Where clause I just returned it as datasource to a grid and it brought the right results...

 var hfeed = db.feed_fish.Where(x => (DbFunctions.TruncateTime(x.Feed_Time) >= frm && DbFunctions.TruncateTime(x.Feed_Time) <= todat)).ToList();
                        fishwtaveragegrid.DataSource = hfeed;
                        fishwtaveragegrid.DataBind();

Have you tested this individually, i mean, does the where clause return anything — TheGeneral, Apr 04 '19 at 06:41
Group and ordering looks legit, check `Where` condition returns correct records. — Fabio, Apr 04 '19 at 06:45
You can do a `.ToString()` on everything before the call to `.First()` to see what SQL query is being produced. My guess is that there are no feedings between your `frm` and `todat` values. — StriplingWarrior, Apr 04 '19 at 20:22
Definitely think its your time comparison in the where that is the problem, as other posters have said. — TJ Bandrowsky, Apr 04 '19 at 20:37
Thanks all, I will post the output of the tostring() on everything... — noetico, Apr 04 '19 at 22:36
I agree there's something wrong with the Where clause. Anything look funny to anyone? — noetico, Apr 04 '19 at 22:40
Ok so please kindly look at the original post, I just tested the where clause by passing it to a gridview as a List and its working alright, so my issue is from the aggregation... Please pros, sorry to bother you guys — noetico, Apr 04 '19 at 22:50

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I think you did it right. Since I don't have a database I just mocked your data creating a list of objects:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApp8
{
    class Program
    {
        public class feed_fish
        {
            public string Pond_Name { get; set; }
            public int Total_feed_weight { get; set; }
            public override string ToString()
            {
                return $"Name: {Pond_Name } Weight: {Total_feed_weight}";
            }
        }
        static void Main(string[] args)
        {
            List<feed_fish> list = new List<feed_fish>()
            {
                new feed_fish{Pond_Name ="Pond 1", Total_feed_weight=56},
                new feed_fish{Pond_Name ="Pond 2", Total_feed_weight=33},
                new feed_fish{Pond_Name ="Pond 1", Total_feed_weight=45},
                new feed_fish{Pond_Name ="Pond 2", Total_feed_weight=54},
                new feed_fish{Pond_Name ="Pond 3", Total_feed_weight=100},
                new feed_fish{Pond_Name ="Pond 3", Total_feed_weight=200}
            };

            List<feed_fish> gruppedList = list.GroupBy(r => r.Pond_Name, (key, enumerable) =>
             {
                 return new feed_fish { Pond_Name = key, Total_feed_weight = enumerable.Sum(k => k.Total_feed_weight) };
             }).OrderByDescending(t => t.Total_feed_weight).ToList();

            foreach (var item in gruppedList)
            {
                Console.WriteLine(item.ToString());
            }
            Console.ReadKey();
        }
    }
}

And the output is (gruppedList):

Name: Pond 3 Weight: 300

Name: Pond 1 Weight: 101

Name: Pond 2 Weight: 87

I hope it helps

UPDATE

Could you please try to do this and tell us if it works for you:

var hfeed = db.feed_fish.Where(x => (DbFunctions.TruncateTime(x.Feed_Time) >= frm && DbFunctions.TruncateTime(x.Feed_Time) <= todat)).ToList();

List<feed_fish> gruppedList = hfeed.GroupBy(r => r.Pond_Name, (key, enumerable) =>
             {
                 return new feed_fish { Pond_Name = key, Total_feed_weight = enumerable.Sum(k => k.Total_feed_weight) };
             }).OrderByDescending(t => t.Total_feed_weight).ToList();

            foreach (var item in gruppedList)
            {
                Console.WriteLine(item.ToString());
            }

Thanks a lot, I have added your code to mine and I am editing my original to show it, the output of the list in my case is to use hfeed[0] but this shows the object name, do I remove the final ToList() since I have already used ToList() before the Grouping... — noetico, Apr 04 '19 at 21:48
@noetico sure, no problem! I'm not sure why you are not getting valid results. Try to feed my code with hfeed list i.e. in my code where I use `list.GroupBy` try to use `hfeed.GroupBy` from your `datasource` example. List has the same structure, so it should just work fine — Pavel Kovalev, Apr 04 '19 at 23:16
Thanks a lot. Please look at the update to my original post, I fed your code in and returned a list. Please confirm I did it correctly... — noetico, Apr 05 '19 at 00:11
@noetico looks like you did it right. Are you getting results as expected? — Pavel Kovalev, Apr 05 '19 at 04:56
Strangely I'm not, at most it returns the object name, let me give it another shot... — noetico, Apr 05 '19 at 07:42
A billion Thanks!!!! this worked! This is going to be one of those paste-in-a-safe place snippets. Thank you. Now I returned it to a Grid and indeed it listed it. I will go ahead and use this and report back. Thanks. — noetico, Apr 05 '19 at 07:50
Extremely glad for your help... And big Thanks to EVERYONE who helped here. I appreciate it so much. Thanks thanks thanks... — noetico, Apr 05 '19 at 07:56

How to use Linq to group, sum or retrieve total. My current code returns nothing

1 Answers1

UPDATE

Linked