locked
Group data month by month in LINQ, viewmodel, or view? RRS feed

  • Question

  • User1778730866 posted

    I have a LINQ query that returns a list of fish caught but I would like to group the catches of each species by month. I have tried grouping by date and grouping by species but it results in a list of totals by month, or the totals for each fish. What do I need to add to my query to make this work, or have I missed something I should have done in my viewmodel CaughtByMonth? My aim is to have a line chart with a line for each species for the year, which can be shown/hidden as required.

                var SpeciesByMonth = (from p in db.FishingCatchReport
                             join e in db.FishCaught
                             on p.sessionsid equals e.sessionsid
                             where p.Location != null && p.CatchReportCustomerID == custid
    
                             select new CaughtByMonth
                             {
                                 Qty = e.Qty.Value,
                                 Species = e.Fish,
                                 DateCaught = p.Date,
    
                             }).ToArray();


     

    Sunday, December 23, 2018 10:12 PM

Answers

All replies

  • User1120430333 posted

    You don't have a Grouping operator, which would be data.

    https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 24, 2018 12:04 AM
  • User1778730866 posted

    Thanks. There was no group in my code snippet because what I tried didn't work. You link helped me and I also found an answer elsewhere which was immediately understandable by how it was written and formatted. My error was that I was thinking I needed to group by one column, then the next.

    I believe doing the statement this was gives me the information how I want it, but I probably need to sort it into Year, then month order to make it more straightforward when pushing the data to a line chart.

    var query = (from t in Transactions
                 group t by new {t.MaterialID, t.ProductID}
                 into grp
                        select new
                        {
                            grp.Key.MaterialID,
                            grp.Key.ProductID,
                            Quantity = grp.Sum(t => t.Quantity)
                        }).ToList();

    https://stackoverflow.com/questions/847066/group-by-multiple-columns

    Thanks for your help.

    Sunday, December 30, 2018 5:29 PM
  • User1778730866 posted

    Not that it is really any good for what I want though. I was expecting the data to be in tabular format, which would be easy to put into a dynamic multi line chart, but with the way the data ends up it requires more processing in the view just to sort through the data, so this is not really a good solution.

    I'll try with SQL instead.

    Thursday, January 3, 2019 11:19 PM