Topics

C#•3y ago

✅ EFCore Nullability and Circular References

I have 2 tables which have FK's to each other due to the relationship. I'm trying to add some seed data for a profile that needs to be created on initialisation.

EFUser(single - FK to most recent EFAlias) -> EFAlias(many - FK to EFUser)

EFAlias(many - FK to EFUser) -> EFUser(single - FK to most recent EFAlias)

EFUser(single - FK to most recent EFAlias) -> EFAlias(many - FK to EFUser)

EFAlias(many - FK to EFUser) -> EFUser(single - FK to most recent EFAlias)

I've tried two ways... 1. I'm getting an SQL 19 error which is a expected field that isn't provided (NULL). If I set the FK in EFAlias to Nullable with ?, then the field in the database isn't populated (which makes sense). However, with the above scenario if I ever lookup the seeded user from their EFAlias, FK to EFUser isn't populated. So it's not possible. 2. If I remove the Nullable statement in the property and provide the UserId and AliasId in the seed data, the migration fails due to Circular Reference (which again, makes sense) Is there a way I can both have each field populated and required (not nullable)? Prefixed tables with EF just to make it a little clearer. SEED: (2nd example - circular reference)

var adminAlias = new EFAlias
{
    Id = 1,
    EntityId = 1, 
    UserName = "IW4MAdmin",
    IpAddress = "0.0.0.0",
    Changed = DateTimeOffset.UtcNow
};

var adminEntity = new EFEntity
{
    Id = 1,
    CurrentAliasId = 1,
    ProfileIdentity = "0:UKN",
    Reputation = 0,
    Infractions = new List<EFInfraction>()
};

var adminAlias = new EFAlias
{
    Id = 1,
    EntityId = 1, 
    UserName = "IW4MAdmin",
    IpAddress = "0.0.0.0",
    Changed = DateTimeOffset.UtcNow
};

var adminEntity = new EFEntity
{
    Id = 1,
    CurrentAliasId = 1,
    ProfileIdentity = "0:UKN",
    Reputation = 0,
    Infractions = new List<EFInfraction>()
};

MODEL: (1st example - missing FK in EFAlias)

    public int? EntityId { get; set; }
    [ForeignKey(nameof(EntityId))] public EFEntity? Entity { get; set; } = null!;

    public int? EntityId { get; set; }
    [ForeignKey(nameof(EntityId))] public EFEntity? Entity { get; set; } = null!;

46 Replies

phaseshift•3y ago

You can't have a circular ref thing required if you want db constraints. But you didn't show your lookup code - that's probably the issue

AmosOP•3y ago

I can't build the migrations or database, I haven't wrote any lookup code yet. 😄 I'm not really sure how to go forward here since I need to know the User from the Alias. The migration works from example 1 and the database updates as expected, it's just the EFAlias FK for EFEntity isn't populated. 😦

AmosOP•3y ago

Here's the database entry example for Example 1.

AmosOP•3y ago

Example 2's migration doesn't build, obviously. 🙂

phaseshift•3y ago

Sorry, I don't really get your situation. If you're seeding data then just add it

AmosOP•3y ago

The issue is that I need the EFAlias (screenshotted above) to have the FK to EntityId. I can't, because it's circular.

phaseshift•3y ago

You can, just do it ?

AmosOP•3y ago

I legit can't. 😭

phaseshift•3y ago

Why?

AmosOP•3y ago

Unable to save changes because a circular dependency was detected in the data to be saved: 'EFAlias [Added] <-
CurrentAlias { 'CurrentAliasId' } EFEntity [Added] <-
Aliases Entity { 'EntityId' } EFAlias [Added]To show additional information call 'DbContextOptionsBuilder.EnableSensitiveDataLogging'.'.

Unable to save changes because a circular dependency was detected in the data to be saved: 'EFAlias [Added] <-
CurrentAlias { 'CurrentAliasId' } EFEntity [Added] <-
Aliases Entity { 'EntityId' } EFAlias [Added]To show additional information call 'DbContextOptionsBuilder.EnableSensitiveDataLogging'.'.

If I seed CurrentAliasId in EFEntity and EntityId in EFAlias that's circular and EFCore says no. 😄

phaseshift•3y ago

You said you could do it with nullable

AmosOP•3y ago

But then the field is null. I have to remove the circular entry though - which is why it's null to be explicit

phaseshift•3y ago

Does the field already exist? If it's new, how could the migration add data for it?

AmosOP•3y ago

No, this is a new database. I'm dropping the database and deleting the migration each time I try this out. It's all fresh data.

phaseshift•3y ago

Then it can't magically pick the right fk

AmosOP•3y ago

Yes, the problem being if the field is set explicitly it causes circular reference. Though, I need it set. I need to be able to lookup users by Aliases. But also lookup aliases by users. Fun problem. 😄

phaseshift•3y ago

I don't see the problem

AmosOP•3y ago

Then how do I solve it? 😄

phaseshift•3y ago

Just add the data 🤷‍♂️

AmosOP•3y ago

How? I've explained now that I can't due to circular. EFCore literally won't create the migration.

phaseshift•3y ago

The thing that's circular os optional. You can add it any time

AmosOP•3y ago

You keep saying I can without saying how. How do I avoid EFCore's migration restriction?

phaseshift•3y ago

You just assign it and save changes

AmosOP•3y ago

Manually?

phaseshift•3y ago

You said starting from scratch. What is this migration

AmosOP•3y ago

EFCore's migration dotnet ef migrations add Init --context SqliteDataContext

phaseshift•3y ago

You also said it's not a problem with optional fks

AmosOP•3y ago

Updating the field manually via accessing the DB and adding it isn't practical. D: they're examples to explain my problem. There not solutions. If they were solutions I wouldn't be here. 1. I can't add circular FK due to Migration failure of Circular Reference. 2. I don't want to null the FK since it needs to be populated.

phaseshift•3y ago

You don't have a choice. It must be nullable of it's circular

AmosOP•3y ago

Okay. Let's say I make it null. I still can't add the data since it's circular!

phaseshift•3y ago

You can. It's just a reference to another item

AmosOP•3y ago

One sec. Let me record gif of me showing this

AmosOP•3y ago

https://i.imgur.com/bIlEm35.gif

Imgur

AmosOP•3y ago

I highlight both circular references I go to EFAlias and show you the nullable state I then run the migration - it fails for circular oh, welp, gif broke half way apparently. I run the migration right after it freezes and the same error as the terminal currently has

phaseshift•3y ago

I can't see hardly anything on mobile.

AmosOP•3y ago

eh. You're just going to have to take my word for it. Even with the fields being nullable, the migration won't build due to being circular.

phaseshift•3y ago

Did you also remove the cascade delete behaviour?

AmosOP•3y ago

I'm not too sure what that is sorry

phaseshift•3y ago

I thought that should have been automatic with an optional fk but maybe not. There's something like 'OnDelete(...)' available in the model builder. Suggest looking into that

AmosOP•3y ago

Is that relevant to this problem though or just an observation?

phaseshift•3y ago

Yes it's relevant if it has been implicitly set up, because that blocks circular dependencies

AmosOP•3y ago

I suppose another point, if I'm finding it this hard to do circular reference, is this normal or is there some fundamental design issue?

phaseshift•3y ago

Think you're not familiar with dealing with db constraints? But yeah, I'm not db expert either but don't think circular refs are too common. But afaik, they're workable with optional fks and no cascade delete

AmosOP•3y ago

Alright, I'll take a look at that. I'll leave this open to see if anyone else has any ideas. Thanks for your time. 🙂

phaseshift•3y ago

It's easy to just do it on nosql because it doesn't scream at you if the target is missing

AmosOP•3y ago

Okay, so I decided to redo this structure. I'm getting told by a few people circular references are not a great way to go about it. 😄 I've created a new table which just has foreign keys to Entity and Alias. This gets rid of the circular references, just makes a little more awkward to access the data I need. It's a compromise, I guess. 🤷‍♂️

We are a programming server aimed at coders discussing everything related to C# (CSharp) and .NET.

54KMembers

View on Discord

Did you find this page helpful?