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, Feb 10 '20 at 18:57
I think you can use GroupByRange() exmaple here https://learn.microsoft.com/en-us/dotnet/csharp/linq/group-query-results — Ankit, Feb 10 '20 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, Feb 10 '20 at 19:14

ocuenca · Answer 1 · 2020-02-10T19:42:52.497

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 '20 at 19:42

answered Feb 10 '20 at 19:08

ocuenca

38,548
11
89
102

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 Feb 10 '20 at 19:23
OrderDTO: ` public int Id { get; set; } public string OrderPlaced { get; set; } public decimal Total { get; set; }` – Konrad Dybkowski Feb 10 '20 at 19:23
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 https://go.microsoft.com/fwlink/?linkid=2101038 for more information. – Konrad Dybkowski Feb 10 '20 at 19:25
Is `OrderPlaced` a `DateTime`? Use also `FirstOrDefault` instead or `First` – ocuenca Feb 10 '20 at 19:28
It is a string. – Konrad Dybkowski Feb 10 '20 at 19:29
With FirstOrDefault: 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 https://go.microsoft.com/fwlink/?linkid=2101038 for more info. – Konrad Dybkowski Feb 10 '20 at 19:31
I strongly recommend you to use `DateTime` instead of `string` in case it's in your hands to change the schema. – ocuenca Feb 10 '20 at 19:32
Just to know if it's the `OrderBy`, call `.AsEnumerable()` before calling the `Select` – ocuenca Feb 10 '20 at 19:34
InvalidOperationException: Client side GroupBy is not supported. AspNetCore.Views_Order_GetOrders.ExecuteAsync() in GetOrders.cshtml + 13 @foreach (var item in Model) – Konrad Dybkowski Feb 10 '20 at 19:41
1

You need to call `ToList` before going to the view, that will force to execute the query before going to the view – ocuenca Feb 10 '20 at 19:42
https://pastebin.com/L6pfH195 Can you fix this? I tried ToList also. – Konrad Dybkowski Feb 10 '20 at 19:47
And what happened after calling `ToList()`?, `.AsEnumerable` is going to avoid any problem you could have in the `Select`, which I don't understand why is not translating your query, now the filtering and the grouping should be in the DB side and the projection in the client side, I'm running out of ideas, What version of EF are you using and for what DB provider? – ocuenca Feb 10 '20 at 19:54
EF Core 3.1 / Microsoft.EntityFrameworkCore.SqlServer 3.1.1 When I use to List(): System.InvalidOperationException: 'Client side GroupBy is not supported.' – Konrad Dybkowski Feb 10 '20 at 20:01
Query: https://pastebin.com/PsD0CEP3 Controller: https://pastebin.com/G3EDhY75 OrderDTO: https://pastebin.com/FTUtJKfv GetOrdersView: https://pastebin.com/vEe6Mt6v – Konrad Dybkowski Feb 10 '20 at 20:04
EF 3 made heavy changes for client evaluations, but I don't know why your Group can't be translated, I'm not seeing nothing special in the group, for now move the `AsEnumerable` before the grouping, trying to see why can't be translated to sql, https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client – ocuenca Feb 10 '20 at 20:12
A [post](https://stackoverflow.com/questions/58138556/client-side-groupby-is-not-supported) with the same issue than you, and yes, I guess the solution for now is going to be calling `AsEnumerable` before grouping. Maybe someone else knows a better solution – ocuenca Feb 10 '20 at 20:20
I have one more issue in other method. You say me to leave DateTime but in this method: https://pastebin.com/LAeYefTE when I compare string orderPlaced and string x.OrderDetails.OrderPlaced the query works. Now in database i have DateTime2 type and in parameter of this method DateTime. Is there way to check if this 2 dates are equal? What should I do? – Konrad Dybkowski Feb 10 '20 at 20:56

LINQ Expression for remove duplicates

1 Answers1