How to Loop through IEnumerable List and if there are any duplicates, sum a value each duplicate has and remove the duplicates in ASP.Net MVC C#

by

I have an inventory usage page that is populated with a list of items I’ve used over the course of a certain period of time (like a month or so). There’s a good amount of items that are duplicates and I am trying to basically put a button on the view page (an IEnumerable List) that will populate a pdf with a list of items and their totals. What I can’t figure out is how to loop through the list of items and if there is any duplicates, get the “AmtTaken” values and sum them together, then remove the duplicates where it only shows each item once but the “AmtTaken” totals are summed together.
I don’t have much code to go off of right now because I’m stuck and am trying to figure out the best way to go about doing this. It’s from a database table called “InventoryUsage”.

Here is my model:

   public partial class InventoryUsage
   {
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public int ID { get; set; }           
       [Display(Name = "Item Code")]
       public string Item_Code { get; set; }           
       [Display(Name = "Amt Taken")]
       public string Amt_Taken { get; set; }           
       [Display(Name = "Submitted?")]
       public Nullable<bool> Submitted { get; set; }
   }

So the duplicates I’m looking for is any with the same Item_Code and then if there is a duplicate, then sum the Amt_Taken values together. I also still want it to list all the items that don’t have duplicates. How I have it set currently is with all the items where “Submitted” == false. So with this model, the ID’s won’t be the same if there is a duplicate so that’s another thing that’s throwing me off.

Here is my view page:

 @model IEnumerable<MyApp.Models.InventoryUsage>

     <div class="wrapper">
         <table class="table">
             <tr>
                 <th>
                     Item/RM Code
                 </th>
                 <th>
                     Amt Taken
                 </th>
                 <th>
                     Submitted? (Y/N)
                 </th>
                 <th></th>
             </tr>

             @foreach (var item in Model)
             {
                 <tr>
                     <td>
                         @Html.DisplayFor(modelItem => item.Item_RM_Code)
                     </td>
                     <td>
                         @Html.DisplayFor(modelItem => item.Amt_Taken)
                     </td>
                     <td>
                         @if (item.Submitted_ == true)
                         {
                             <label class="lbl-yes">Yes</label>
                         }
                         else
                         {
                             <label class="lbl-no">No</label>
                         }
                     </td>
                     <td>
                         @Html.ActionLink("Generate List", "GetTotals", new { not sure what to put here yet }) |
                         @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
                         @Html.ActionLink("Delete", "Delete", new { id = item.ID })
                     </td>
                 </tr>
             }
         </table>
     </div>

And for my controller, is where I don’t have much code yet as I am totally stumped.

    public ActionResult Index()
    {
        var usage = from u in db.InventoryUsage
                    select u;

        return View(usage.Where(u => u.Submitted_ == false).ToList());
    }


    public ActionResult GetTotals()
    {
        var list = db.InventoryUsage.Where(x => x.Submitted == false).ToList();

        //Stuck on this part//

    }

Any advice or direction would be greatly appreciated!

  • 1) Why is Amt_Taken a string and not a numeric type like decimal? How do you sum the string values? 2) Do LINQ Remove duplicates and result item to have sum of quantities and/or how to get a SUM in Linq? answer your question?

    – 




  • @dbc when I first made the database table, I formatted it wrong. I should have added a “unit of measure” column, but decided to just keep it a text column so I could put (as an example: “10 lbs” or “10 gal” instead of having to do 2 different fields. It wasn’t until I started wanting to try to do this list function that I realized I messed up and can’t do that for this to work. So I am gonna have to add that new column for unit of measure and change the AmtTaken to numerical. But either way, as long as there is just a numerical value, it should still sum it right?

    – 

  • As long as you have some way to get a numerical value for each InventoryUsage, I believe you should be able to use the answer by dtb to how to get a SUM in Linq? to get your sum: db.InventoryUsage.GroupBy(x => x.Item_Code).Select(g => new { Item_Code = g.Key, Amt_Taken= g.Sum(x => GetValue(x.Amt_Taken)) }). Does that answer your question?

    – 




  • Also, those examples are confusing me and don’t see how I can make that apply to my example. They are using a generic list and matching them by ID whereas I cannot match mine by the ID.

    – 

  • See also: Best way to store units in database

    – 

Use GroupBy and sum the results:

IEnumerable<InventoryUsage> usages = ...;

var aggregatedUsages = usages.GroupBy( iu => iu.Item_Code )
    .Select( g => new
    {
        Item_Code = g.Key,
        Amt_Taken = g.Select( iu => Convert.ToInt32( iu.Amt_Taken ) )
            .Sum();
    } );

You didn’t specify on how to deal with mismatches in Submitted values for the same Item_Code. You can make that part of the GroupBy key if you want the amounts summed by item code & submitted/not submitted values:

usages.GroupBy( iu => new { iu.Item_Code, iu.Submitted } )

Then project those properties:

.Select( g => new
{
    Item_Code = g.Key.Item_Code,
    Submitted = g.Key.Submitted,
    Amt_Taken = ...,
} )

Leave a Comment