Efcore: How to properly setup a one to one relationship in case of circular dependency?

[embryologist]

This is a question rather than an issue. I have opened a question in SO and can't get an answer for it.

My idea is to upload all files in one table and store their Ids in the related tables. at the same time, I want to maintain a referential integrity between those related records for example,

```c#
public class FileUpload : BaseEntity {
[Key, DatabaseGenerated (DatabaseGeneratedOption.None)]
public long RecordId { get; set; }
public string UserId { get; set; }
public ApplicationUser User { get; set; }
public long? RecordKey { get; set; }
public long? Length { get; set; }
public string PhysicalPath { get; set; }
public string Name { get; set; }
public OffsetDateTime? LastModified { get; set; }
public LocalDateTime? ValidUntil { get; set; }
public FileCategory FileCategory { get; set; }

    }

`UserAvatarId` is just a property that is related to the `File Upload `Table `RecordId`. (one to one relationship)

The related entity is,

```c#
        public class ApplicationUser : IdentityUser {
            [Required, StringLength (50)]
            [Display (Name = "First Name")]
            public string FirstName { get; set; }
            public long? UserAvatarId { get; set; }
            public virtual ICollection<FileUpload> UploadedFiles { get; set; }
    }

my fluent API model builder is

```c#
builder.Entity ()
.HasMany (c => c.UploadedFiles)
.WithOne (c => c.User)
.OnDelete (DeleteBehavior.Cascade);

builder.Entity<FileUpload> ()
        .HasOne (c => c.User)
        .WithOne ()
        .OnDelete (DeleteBehavior.SetNull);

```

The problem here now is that,

If I delete a user from ApplicationUser, all related records in FileUpload are not deleted, UserId is set to null.

if I try to delete a record from FileUpload that has a related record in ApplicationUser (PK-Fk) on UserAvatarId-RecordId, I get an error with regards to FK constraints.

Desired behavior:

What I am looking for is,

if I am deleting a record from ApplicationUser should cascade delete all related ones in FileUpload.

if I am deleting a record from FileUpload that is in use in ApplicationUser, UserAvatarId in ApplicationUser should be set to null.

what is the correct config for the fluent API here in this case?

Further technical details

EF Core version: (2.1.3)
Database Provider: (Microsoft.EntityFrameworkCore.SqlServer)
Operating macOS:
IDE: (Visual Studio 2018 1.25)

[divega]

@embryologist at first glance the configuration code you posted seems insufficient to configure the 1:1 relationship between FileUpload and ApplicationUser. E.g. it is missing mentioning that UserAvatarId is the FK pointing to FileUpload.RecordId.

I haven't tested your model, but I think in absence of this information, and since the name of the FK does not match any convention, I think it is possible that EF Core is creating a separate non-nullable FK property in shadow state, which would make the relationship required. Can you try adding the missing information in the configuration of the relationships?

[embryologist]

@divega , thank you for your response.
You are right, so I modified the fluent API to:

```c#
builder.Entity()
.HasOne(c => c.User)
.WithOne(c => c.UserAvatar)
.HasForeignKey(c => c.UserAvatarId)
.OnDelete(DeleteBehavior.SetNull);

now with this config. I am able to achieve the one of the behaviours that I am looking for,

>  if I am deleting a record from FileUpload that is in use in ApplicationUser, UserAvatarId in ApplicationUser should be set to null.

now if I am trying to add the one to many relationship config. between `ApplicationUser` and `FileUpload`, to achieve the second behavior,

> if I am deleting a record from ApplicationUser should cascade delete all related ones in FileUpload.

I get multiple cascade path exception from SQL server,

> Introducing FOREIGN KEY constraint 'FK_FileUploads_AspNetUsers_UserId' on table 'FileUploads' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I am config. the fluent API as follows,

```c#
builder.Entity<ApplicationUser>()
             .HasMany(c => c.UploadedFiles)
             .WithOne()
             .HasForeignKey(c => c.UserId)
             .OnDelete(DeleteBehavior.Cascade);

currently, I am being forced to set the column value to null instead, (this is NOT what I am looking for)

c# builder.Entity<ApplicationUser>() .HasMany(c => c.UploadedFiles) .WithOne() .HasForeignKey(c => c.UserId) .OnDelete(DeleteBehavior.SetNull);

I am attaching an Image to show the table build,

is there a workaround it?

Thank you again.

[ajcvickers]

@embryologist Is it your intention to create two relationships? That is, a one-to-one and a one-to-many? If so, then these two relationships must use different navigation properties and foreign keys. That is, multiple relationships cannot share the same navigations.

[embryologist]

@ajcvickers , yes my intention is to create two relationships,
The one - one relationship is between (UserAvatarId <-> RecordId)
The one - many relationship is between (UserId <-> UserId). so by this design, I have more than one FK. How can I change the navigation prop though?

[ajcvickers]

@embryologist
```C#
protected override void OnModelCreating(ModelBuilder builder)
{
builder
.Entity()
.HasMany(c => c.UploadedFiles)
.WithOne()
.HasForeignKey(e => e.UserId)
.OnDelete(DeleteBehavior.Cascade);

builder
    .Entity<FileUpload>()
    .HasOne(c => c.User)
    .WithOne()
    .HasForeignKey<ApplicationUser>(e => e.UserAvatarId);

}
```
Notes:

• Uploaded files can be accessed through user.UploadedFiles, but there is no inverse navigation property.
• The user avatar can be accessed through file.User, but there is no inverse navigation property.
• You can add inverse navigations if you want, but they will need to be different properties on the entities--i.e. User cannot also be used as the inverse for UploadedFiles.
• Cascade deletes should work as you specified.
• SQL Server is pretty limited in what it can handle with regard to delete behaviors so I removed the SetNull specification. This means that as long as the ApplicationUser entity is loaded into the context, then when the related FileUpload is deleted the UserAvatarId will be set to null. However, if it is not loaded, then SQL Server won't do it for you--it will instead generate a referential integrity violation. This is a lmiitation in SQL Server.

[embryologist]

Thank you so much for the elaborate response.