How can I restrict the column to accept only value equal to First or Second column using EF core?-CodePudding

I have a table with that contain HostId, GuestId, and WinnerId column. I want to restrict the WinnderId column to accept the value from HostId and GuestId column. How can I achive this using Fluent Api or Attributes.

public class Game
{
    public long Id { get; set; }

    [Required]
    public long TournamentId { get; set; }
    public Tournament Tournament { get; set; }

    public long HostId { get; set; }

    public long GuestId { get; set; }

    public loong WinnerId{ get; set; }
}

CodePudding user response：

From a data integrity perspective anything you do will not be enforceable. Host, Guest, and Winner would have FKs back to a User/Client or whatever table but nothing would enforce that the Client reference to winner would have to match the Host or Guest. (I.e. nothing would stop the data from changing a Host or Guest ID after assigning a Winner, etc.)

Two options:

1- Change the data structure slightly:

public enum Winner
{
    None = 0,
    Host,
    Guest
}

public class Game
{
    public long Id { get; set; }

    [Required]
    public long TournamentId { get; set; }
    public Tournament Tournament { get; set; }

    public long HostId { get; set; }

    public long GuestId { get; set; }

    public Winner Winner { get; set; } = Winner.None;
}

This way you can assign a game with a Host and Guest then to assign the winner you just nominate and store a value indicating whether the host or guest won.

2- Use a more DDD approach to making updates:

public class Game
{
    public long Id { get; set; }

    [Required]
    public long TournamentId { get; set; }
    public Tournament Tournament { get; set; }

    public long HostId { get; internal set; }

    public long GuestId { get; internal set; }

    public long WinnerId { get; internal set; }

    public void AssignWinner(long winnerId)
    {
        if(winnerId != HostId && winnerId != GuestId)
            throw new ArgumentException("The winner was not the host or guest.");
        WinnerId = winnerId;
    }
}

This could be done a number of ways, either passing the ID, or passing an enumeration like the first example and assigning the WinnerId based on the Host or Guest ID. Basically use deliberate actions on the domain to perform updates. This is a common approach to help ensure that updates to entities/data are always validated and "complete" when made rather than allowing updates piecemeal through property setters. This still doesn't protect the schema from allowing a WinnerID to point at something other than the Host or Guest, where-as the first structure change does.

CodePudding user response：

If your database supports table constraints, you can add a constraint specifying WinnerId Is Null Or WinnerId = HostId Or WinnerId = GuestId. If your entity class inherits from IValidatableObject you can implement the same rule in the Validate method.