How to use relations in C#?

I am working on a C# Web API project, but I have some issues building the foreign key relationships.

I have these entity classes:

public class Car
{
    public Guid Id { get; set; }
    public string Type { get; set; }
    public string Licence { get; set; }
    public int BuiltYear { get; set; }
    public WorkCategory WorkCategory { get; set; }
    public string ProblemDescription { get; set; }
    public int FaultWeight { get; set; }
    public Guid ClientId { get; set; }

    public Client Client { get; set; }
}

public class Client
{
    public Client() { 
        Cars = new List<Car>();
        Works = new List<Work>();
    }

    public Guid Id { get; set; }
    public Guid CarId { get; set; }
    public Guid WorkId { get; set; }
    public string Name { get; set; }

    public ICollection<Car> Cars { get; set; }
    public ICollection<Work> Works { get; set; }
}

public class Work
{
    public Work() 
    {
        Status = Status.REGISTERED;
        RegisterDate = DateTime.Now;
    }

    public Guid Id { get; set; }
    public Guid ClientId { get; set; }
    public Guid CarId { get; set; }
    public float WorkHours { get; set; }
    public Status Status { get; set; }
    public DateTime RegisterDate { get; set; }

    public Client Client { get; set; }
    public Car Car { get; set; }
}

And I created the following DbContext:

public class AppDbContext : DbContext
{
    public DbSet<Client> Clients { get; set; }
    public DbSet<Car> Cars { get; set; }
    public DbSet<Work> Works { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Client>(entity =>
        {
            entity.ToTable("Client");
            entity.HasMany(client => client.Cars)
                .WithOne(car => car.Client)
                .HasForeignKey(car => car.ClientId)
                .HasConstraintName("FK_Client_Car");
        });

        modelBuilder.Entity<Car>(entity =>
        {
            entity.ToTable("Car");
            entity.HasOne(car => car.Client)
                .WithMany(client => client.Cars)
                .HasForeignKey(car => car.ClientId)
                .HasConstraintName("FK_Car_Client");
        });

        modelBuilder.Entity<Work>(entity =>
        {
            entity.ToTable("Work");
            entity.HasOne(w => w.Client)
                .WithMany(c => c.Works)
                .HasForeignKey(w => w.ClientId)
                .HasConstraintName("FK_Work_Client");

            entity.HasOne(w => w.Car)
                .WithOne()
                .HasForeignKey<Work>(w => w.CarId)
                .HasConstraintName("FK_Work_Car");
        });

        base.OnModelCreating(modelBuilder);
    }
}

The migration runs successfully, but when I run the
update-database command, I got this error:

Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

CREATE TABLE [Work] (  
    [Id] uniqueidentifier NOT NULL,  
    [ClientId] uniqueidentifier NOT NULL,  
    [CarId] uniqueidentifier NOT NULL,  
    [WorkHours] real NOT NULL,  
    [Status] int NOT NULL,  
    [RegisterDate] datetime2 NOT NULL,  
    CONSTRAINT [PK_Work] PRIMARY KEY ([Id]),  
    CONSTRAINT [FK_Work_Car] FOREIGN KEY ([CarId]) REFERENCES [Car] ([Id]) ON DELETE CASCADE,  
    CONSTRAINT [FK_Work_Client] FOREIGN KEY ([ClientId]) REFERENCES [Client] ([Id]) ON DELETE CASCADE   );  

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

   CREATE TABLE [Work] (  
          [Id] uniqueidentifier NOT NULL,  
          [ClientId] uniqueidentifier NOT NULL,  
          [CarId] uniqueidentifier NOT NULL,  
          [WorkHours] real NOT NULL,  
          [Status] int NOT NULL,  
          [RegisterDate] datetime2 NOT NULL,  
          CONSTRAINT [PK_Work] PRIMARY KEY ([Id]),  
          CONSTRAINT [FK_Work_Car] FOREIGN KEY ([CarId]) REFERENCES [Car] ([Id]) ON DELETE CASCADE,  
          CONSTRAINT [FK_Work_Client] FOREIGN KEY ([ClientId]) REFERENCES [Client] ([Id]) ON DELETE CASCADE

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Work_Client' on table 'Work' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint or index. See previous errors.

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

ClientConnectionId:d20f9306-ee45-4959-bb40-3469f04c6a0b Error Number:1785,State:0,Class:16 Introducing FOREIGN KEY constraint 'FK_Work_Client' on table 'Work' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

Your issue is that you can get from WORK to CLIENT two different ways.

You can go directly using the FK from WORK to CLIENT and you can go indirectly from WORK to CAR to CLIENT by following those FKs.

The real issue is that these two paths can lead to inconsistent results. The database engine has detected this potential inconsistency and it's telling you that it's not possible to define things this way.

Notice that the error is pointing out specifically that the issue is cascading deletes or updates. You can get around the error by taking out the cascade specification and handling the cascading manually in your code. That's also a dicey proposition because it's more code, so more to build, test and maintain. Alternatively, you could change your model so that the only way to get from WORK to CLIENT is through CAR. That might not fit your business rules, though. For example, if your client can have work without having a car. Take another look at your business rules and decide if this is actually the structure you want or if another structure makes more sense, for example having an intersection table that sits in the middle of CLIENT, WORK and CAR which then joins all three together as a single record. This may be a great idea or a terrible idea, it depends on what your business rules require.