LINQ Expression for remove duplicates

Question

How Can I remove duplicates in Order Placed column and only leave one row for each date?

I tried a few expressions but I have only first 4 orders still with the same date. I also tried GroupBy, OrderBy.

 public IEnumerable<Order> GetOrder(string userId)
 {
            var order = _appDbContext.Orders.Include(x => x.OrderDetails)
                                            .Include(x => x.Game)
                                            .Where(x => x.OrderDetails.UserId == userId);

            return order;
  }

View Code:

@model IEnumerable<Order>

<h2>Your orders</h2>
<table class="table table-bordered table-striped">
    <thead>
        <tr>
            <th class="text-center">Order Number:</th>
            <th class="text-center">Order Placed:</th>
            <th class="text-center">Total:</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td class="text-center">
                    <a asp-controller="Order" asp-action="OrderDetails"
                       asp-route-orderPlaced="@item.OrderDetails.OrderPlaced" class="btn btn-primary">@item.OrderId</a>
                </td>
                <td class="text-center">@item.OrderDetails.OrderPlaced</td>
                <td class="text-center">@(item.Price * item.Amount)</td>
            </tr>
        }
    </tbody>
</table>

If you want to group all orders of a date, you need to use group by. Also, since you are not grouping the other columns, you need to aggregate them either in CSV, Count or Sum etc. — Ankit
– Ankit, Commented Feb 10, 2020 at 18:57
I think you can use GroupByRange() exmaple here learn.microsoft.com/en-us/dotnet/csharp/linq/… — Ankit
– Ankit, Commented Feb 10, 2020 at 19:03
if you want eliminate duplicate order dates, how do you want Total and OrderNumber to be displayed? if you want them to be grouped on OrderDate, then you can only display total of all the orders placed on that date but you can not show OrderNumber in this case. — sam
– sam, Commented Feb 10, 2020 at 19:14

ocuenca · Accepted Answer · 2020-02-10 19:42:52Z

2

Yes, you can group by date:

var query = _appDbContext.Orders.Include(x => x.OrderDetails)
                         .Where(x => x.OrderDetails.UserId == userId)
                         .GroupBy(x=> x.OrderDetails.OrderPlaced)
                         .Select(g=> new OrderDTO{
                                       Id= g.OrderBy(e=>e.OrderId).FirstOrDefault().OrderId,
                                       OrderPlaced= g.Key,
                                       Total= g.Sum(e=> e.Price * e.Amount),
                                     }
                          )
                         .ToList();

OrderDTO is a custom class you need to define with all the properties you want to show in your view. I don't know how you are going to manage the ids of orders that share the date, I assumed is the first of the group because you mentioned OrderBy in the question.

public class OrderDTO{
  public int Id { get; set; } 
  public DateTime OrderPlaced { get; set; } 
  public decimal Total { get; set; }
}

edited Feb 10, 2020 at 19:42

answered Feb 10, 2020 at 19:08

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Konrad Dybkowski Over a year ago

It doesn't work :(

var query = _appDbContext.Orders.Include(x => x.OrderDetails)                          .Where(x => x.OrderDetails.UserId == userId)                          .GroupBy(x => x.OrderDetails.OrderPlaced)                          .Select(g => new OrderDTO                          {                              Id = g.OrderBy(e => e.OrderId).First().OrderId,                              OrderPlaced = g.Key,                              Total = g.Sum(e => e.Price * e.Amount),                          }                           );

Konrad Dybkowski Over a year ago

OrderDTO: ` public int Id { get; set; } public string OrderPlaced { get; set; } public decimal Total { get; set; }`

Konrad Dybkowski Over a year ago

Error Message: InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: (o.OrderPlaced), ElementSelector:(EntityShaperExpression: EntityType: Order ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .OrderBy(e => e.OrderId)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See go.microsoft.com/fwlink/?linkid=2101038 for more information.

ocuenca Over a year ago

Is OrderPlaced a DateTime? Use also FirstOrDefault instead or First

ocuenca Over a year ago

You need to call ToList before going to the view, that will force to execute the query before going to the view

|

Collectives™ on Stack Overflow

LINQ Expression for remove duplicates

1 Answer 1

17 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Linked

Related