Good morning. I have a table of Sales with following properties

public Guid SaleId { get; set; }  this is a Primary key
public Guid UserObjectId { get; set; } this is the identity of the user that created sale
public SaleStatus SaleStatus { get; set; }

   public enum SaleStatus  
    {
        Pending, //user can have only 1 pending sale at the time
        Success, 
        Failed, 
        Expired 
    };

I want to make sure that 1UNIQUE USER can have ONLY 1 SALE WITH A STATUS PENDING(There is a background job that checks pending sales and changes status to expired so the user can create pending sale again if the last one expired). I understand this can be achieved by controlling the sale creation, In the past I queried my database and I prevented the user from creating sale by quering:

public bool CanCreateSale(List<Sale> salesForUser)
    {
        return !salesForUser.Any() || !salesForUser.Any(x => x.SaleStatus == Data.Models.SaleStatus.Pending);
    }

There is nothing wrong with the solution but I was wondering if it is possible to prevent my database from saving the sale by adding some constraint in the database just to make sure someone will not add this by accident(Sale creation is a very important part of the application and will be widely used by different services).

I tried this but that prevents me from creating more than 1 sale with a pending status. I want to have option to add multiple sales with pending status BUT I want to make sure that there will be only one Sale with a pending status for 1 user(or unique UserObjectId(not saleid)):

  modelBuilder.Entity<Sale>()
               .HasIndex(e => new { e.SaleStatus })
                .IsUnique()
                .HasFilter("[SaleStatus] = 0");

Is it possible to achieve this using fluent api?

CodePudding user response：

You need to use composite constraint. Try in this way:

modelBuilder.Entity<Sale>()
   .HasIndex(e => new { e.SaleStatus, e.UserObjectId })
   .IsUnique()
   .HasFilter("[SaleStatus] = 0")