Entity Framework Core Prevent Duplicate Values, Add specific constraint

Question

I have a table of Sales with the 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 a unique user can have only 1 sale with a status of "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 querying:

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?

You can try this one: modelBuilder.Entity<Sale>() .HasIndex(e => new { e.SaleStatus, e.UserObjectId }) .IsUnique() .HasFilter("[SaleStatus] = 0"); — Yigit Yuksel
– Yigit Yuksel, Commented Aug 17, 2022 at 11:06
@YigitYuksel this works perfect, you sure you dont want to submit that as an answer? — Vladimir
– Vladimir, Commented Aug 17, 2022 at 12:52

Yigit Yuksel · Accepted Answer · 2022-08-17 12:58:32Z

3

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

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

answered Aug 17, 2022 at 12:58

Yigit Yuksel

1,1902 gold badges21 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Entity Framework Core Prevent Duplicate Values, Add specific constraint

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related