I want to write a function that get product distinct customers names I tried with this function. I think the problem is in the where clause. please tell me if there is any other way to accomplish this

public string[] GetProductDistinctCustomers(int productId)
{
    return _context.Orders
        .Include(o => o.OrderDetails)
        .Where(o => o.OrderDetails.Exists(od => od.ProductId == productId))
        .Select(o => o.User.UserName)
        .Distinct()
        .ToArray();
}

but I get InvalidOperationException.

Classes :

public class Order
{
    public int OrderId { get; set; }
    public List<OrderDetail> OrderDetails { get; set; }
    public string UserId { get; set; }
    public AppUser User { get; set; }
}

public class OrderDetail
{
    public int OrderDetailId { get; set; }
    public int Amount { get; set; }      
    public int ProductId { get; set; }
    public Product Product { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; }
}

public class AppUser : IdentityUser
{
    
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

I appreciate any hint you can give to me

Exception:

InvalidOperationException: The LINQ expression
'DbSet() .Where(o => MaterializeCollectionNavigation( Navigation: Order.OrderDetails, subquery: DbSet() .Where(o0 => EF.Property<Nullable>(o, "OrderId") != null && object.Equals( objA: (object)EF.Property<Nullable>(o, "OrderId"), objB: (object)EF.Property<Nullable>(o0, "OrderId"))).Exists(od => od.ProductId == __productId_0))'
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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

CodePudding user response：

Entity Framework does not know method Exists. Replace it with Any:

public string[] GetProductDistinctCustomers(int productId)
{
    return _context.Orders
        .Include(o => o.OrderDetails)
        .Where(o => o.OrderDetails.Any(od => od.ProductId == productId))
        .Select(o => o.User.UserName)
        .Distinct()
        .ToArray();
}