C#•2y ago

✅ ForeignKey Entity Framework and Identity

I've got the following classes: "Account", "Post", "Tag" and "Image". The "Post" class contains the following:

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid? PostId { get; set; }

        public List<Tag> Tags { get; set; }

        public List<Image> AttachedImages { get; set; }

        [ForeignKey("Account")]
        public Guid? OriginalPosterId { get; set; }
        public Account? OriginalPoster { get; set; }

        [ForeignKey("Account")]
        public List<Guid> PostParticipantsIds { get; set; }
        public virtual ICollection<Account> PostParticipants { get; set; }

        [ForeignKey("Account")]
        public List<Guid> UsersAttendingIds { get; set; }
        public virtual ICollection<Account> UsersAttending { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid? PostId { get; set; }

        public List<Tag> Tags { get; set; }

        public List<Image> AttachedImages { get; set; }

        [ForeignKey("Account")]
        public Guid? OriginalPosterId { get; set; }
        public Account? OriginalPoster { get; set; }

        [ForeignKey("Account")]
        public List<Guid> PostParticipantsIds { get; set; }
        public virtual ICollection<Account> PostParticipants { get; set; }

        [ForeignKey("Account")]
        public List<Guid> UsersAttendingIds { get; set; }
        public virtual ICollection<Account> UsersAttending { get; set; }

I can't seem to quite understand how this type of mapping works. Why can't the Lists simply reference the correct AccountId? I see no reason there could not be different fields referencing the same "Id" for different purposes. What am I doing wrong?

12 Replies

populus•2y ago

Here's the latest error message among many.

System.InvalidOperationException: 'There are multiple navigations in entity type 'Post' which are pointing to same set of properties 'Account' using a [ForeignKey] attribute: 'PostParticipantsIds', 'UsersAttendingIds'.'

System.InvalidOperationException: 'There are multiple navigations in entity type 'Post' which are pointing to same set of properties 'Account' using a [ForeignKey] attribute: 'PostParticipantsIds', 'UsersAttendingIds'.'

Angius•2y ago

Well, this won't work, unless you have a table of GUIDs

        [ForeignKey("Account")]
        public List<Guid> PostParticipantsIds { get; set; }

        [ForeignKey("Account")]
        public List<Guid> PostParticipantsIds { get; set; }

In general, lists of IDs won't work It's because that's how many-to-one relationships in databases work Databases, in general, have no concept of a list So if you want a relationship where one post can have one category, and one category can have many posts, it is the post that needs a foreign key to the category The category cannot have a list of foreign keys to posts

populus•2y ago

I was hoping I could declare a List<Guid> and have the DB populate it with every associated AccountId one-by-one as they become relevant. "it is the POST that needs a FK..." - My attempt at this was to create these List<Guid>. What is your recommended course of action?

Angius•2y ago

To set it up properly Also, get rid of those virtuals while you're at it Lazy loading is the devil

populus•2y ago

Maybe I can create a "Participator" class and a "Attending" class. Both with "List<Account>" fields.

Angius•2y ago

class Account
{
    public required Guid Id { get; set; }
    public List<Post> Participates { get; set; } = new();
    public List<Post> AttendingPosts{ get; set; } = new();
}
class Post
{
    public required Guid Id { get; set; }
    public List<Tag> Tags { get; set; } = new();
    public List<Image> Images { get; set; } = new();
    public required Account OriginalPoster { get; set; }
    public Guid OriginalPosterId { get; set; }
    public List<Account> Participants { get; set; } = new();
    public List<Account> AttendingUsers { get; set; } = new();
}
class Tag
{
    public required Guid Id { get; set; }
    public List<Post> Posts { get; set; } = new();
}
class Image
{
    public required Guid Id { get; set; }
    public required Post Post { get; set; }
}

class Account
{
    public required Guid Id { get; set; }
    public List<Post> Participates { get; set; } = new();
    public List<Post> AttendingPosts{ get; set; } = new();
}
class Post
{
    public required Guid Id { get; set; }
    public List<Tag> Tags { get; set; } = new();
    public List<Image> Images { get; set; } = new();
    public required Account OriginalPoster { get; set; }
    public Guid OriginalPosterId { get; set; }
    public List<Account> Participants { get; set; } = new();
    public List<Account> AttendingUsers { get; set; } = new();
}
class Tag
{
    public required Guid Id { get; set; }
    public List<Post> Posts { get; set; } = new();
}
class Image
{
    public required Guid Id { get; set; }
    public required Post Post { get; set; }
}

Something like this

Angius•2y ago

Those coloured pairs will create many-to-many relationships and necessary pivot tables

Angius•2y ago

Since there are two many-to-many relationships between accounts and posts, you'll need to configure them a bit

builder.Entity<Post>(cfg => {
    cfg.HasMany(p => p.Participants)
        .WithMany(a => a.Participates);
    cfg.HasMany(p => p.AttendingUsers)
        .WithMany(a => a.AttendingPosts);
});

builder.Entity<Post>(cfg => {
    cfg.HasMany(p => p.Participants)
        .WithMany(a => a.Participates);
    cfg.HasMany(p => p.AttendingUsers)
        .WithMany(a => a.AttendingPosts);
});

Configuring the post with this should just about do the trick

populus•2y ago

This utilizes "Fluent API"? Searching in NuGet and wondering whether it's "Henk Mollema" or "Jeremy Skinner" who made it?

Angius•2y ago

Yes, it is fluent config No clue who made it

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

populus•2y ago

Thanks ZZ

Gaming

Programming

✅ ForeignKey Entity Framework and Identity