In Entity Framework Core, if I define these relationships, will it create a cyclic relationship betw-CodePudding

I have an event scheduling app with a SQL Server database. It is running as part of an ASP.NET web app using Entity Framework Core. It has the following tables/entities:

User: may join many events as a participant
Event: may have many users as participants. It's also created by a single user

This many-to-many relationship can be resolved using a join table: UserEvent.

I end up with this relationship diagram.

However, my problem is that I am not sure that it is okay if a single User is linked to a single Event twice, both as a participant and as a creator. The creator of the event would have their ID stored as a FK in the Event they created and they would have their ID stored as a FK in the UserEvent that registers them as a participant of the Event. The app assumes if they create the event, they are attending it.

Is there a better way to configure this relationship?

When I try to add the migration I get the following error:

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

Here is an example of my code:

User model:

public class User
{
    public User() {
        UserName = "Default";
    }

    public long ID { get; set; }
    public string UserName { get; set; }
    public virtual List<Event>? UserEvents { get; set; }
}

Event model:

public class Event
{
    public Event() 
    {
        EventName = "Default Event Name";
    }

    public long ID { get; set; }
    public long CreatorID { get; set; }
    public User Creator { get; set; }

    public virtual List<User> EventUsers { get; set; }

    public string EventName { get; set; }
}

Override of OnModelCreating method:

protected override void OnModelCreating(ModelBuilder builder) 
{
    base.OnModelCreating(builder);

    builder.Entity<User>(
            b => {
                b.Property(user => user.ID)
                    .IsRequired();

                b.Property(user => user.UserName)
                    .IsRequired();

                b.HasKey(user => user.ID);

                b.HasMany(user => user.UserEvents)
                 .WithMany(e => e.EventUsers)
                 .UsingEntity(join => join.ToTable("UserEvents"));

                b.ToTable("MyUsers");
            });

    builder.Entity<Event>(
            b => {
                b.Property(e => e.ID)
                    .IsRequired();

                b.Property(e => e.EventName)
                    .IsRequired();

                b.Property(e => e.CreatorID)
                    .IsRequired();

                b.HasKey(e => e.ID);

                b.HasMany(e => e.EventUsers)
                 .WithMany(user => user.UserEvents)
                 .UsingEntity(join => join.ToTable("UserEvents"));

                b.HasOne(e => e.Creator)
                 .WithMany();

                b.ToTable("Events");
            });
}

When I remove the CreatorID and Creator columns from the Event table, it migrates fine.

How can I reorganize my relationships to get allow events to have a Creator field and/or track which User created an Event?

@SNBS Deserves all credit for the answer. See their post below. I had to tweak their code to get it to compile.

To avoid the relationship detailed in the question it was necessary to add a BIT field to the UserEvent table to represent the user "Role" within that event.

To ensure that an event could not have more than one creator I had to add a custom SQL function and enforce it as a constraint on the UserEvent table.

This explanation assumes you're using Visual Studio.

Custom Function

If you are new to SQL Server like me you might wonder how to add the function. In visual studio within the SQL Server Object Explorer. Right click on: Programmability->Functions -> Add New Scalar Valued Function.

After writing the code in the new window make sure to click "Update Database" and check your code compiles. If it works you should be able to see you new function in the explorer.

CREATE FUNCTION CheckOneCreator (@EventId BIGINT)
RETURNS BIT
AS
BEGIN
-- Create and open a cursor
DECLARE Participants CURSOR
FOR
SELECT UserID
FROM UserEvents
WHERE EventID = @EventId;
OPEN Participants;

-- Creators count
DECLARE @CreatorsCount INT;
SELECT @CreatorsCount = 0;

-- Iterate over participants
DECLARE @ParticipantId BIGINT;

FETCH NEXT FROM Participants
INTO @ParticipantId;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Check whether the participant is the creator
DECLARE @IsCreator BIT;

SELECT @IsCreator = (
SELECT TOP 1 IsCreator
FROM UserEvents
WHERE UserID = @ParticipantId
AND EventID = @EventId
);

-- Increase creators count
IF @IsCreator = 1 SELECT @CreatorsCount = (@CreatorsCount   1);

FETCH NEXT FROM Participants
INTO @ParticipantId;
END

IF @CreatorsCount = 1 RETURN 1;
RETURN 0;
END

Add Constraint

To add this function as a constraint right click on your database in the SQL Object Explorer in Visual Studio and select New Object. A code window will open. Once you type the code in again ensure you click "Update Database" and see if your code compiles. If successful you will see the constraint added under the table.

ALTER TABLE [dbo].[UserEvents]
    ADD CONSTRAINT [SingleCreator] CHECK ([dbo].[CheckOneCreator]([EventID])=(1));

CodePudding user response：

I think it would be better if each user had a role (creator or general participant) in all events he takes part in. You may create a new column in table UserEvent and name it IsCreator (data type — bit, which is the SQL equivalent of .NET type bool). This column should contain 0 for general participants and 1 for creators.

UPDATE: a constraint is necessary to check that an event can have only one creator. Firstly, create a function in your database using this SQL code:

CREATE FUNCTION CheckOneCreator (@EventId BIGINT)
RETURNS BIT
AS
BEGIN
-- Create and open a cursor
DECLARE Participants CURSOR
FOR
SELECT UserId
FROM UserEvent
WHERE EventId = @EventId;
OPEN Participants;

-- Creators count
DECLARE @CreatorsCount INT;
SELECT @CreatorsCount = 0;

-- Iterate over participants
DECLARE @ParticipantId BIGINT;

FETCH NEXT FROM Participants
INTO @ParticipantId;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Check whether the participant is the creator
DECLARE @IsCreator BIT;

SELECT @IsCreator = (
SELECT TOP 1 IsCreator
FROM UserEvent
WHERE UserId = @ParticipantId
AND EventId = @EventId
);

-- Increase creators count
IF @IsCreator SELECT @CreatorsCount = (@CreatorsCount   1);

FETCH NEXT FROM Participants
INTO @ParticipantId;
END

IF @CreatorsCount != 1 RETURN 0;
ELSE RETURN 1;
END

And define a constraint for table UserEvent:

ALTER TABLE UserEvent
ADD CONSTRAINT CHECK (CheckOneCreator(EventId) = 1);