I'm having some issues with a REST API I'm trying to implement using ASP.NET. So basically the issue I'm having is with the following code :

public async Task<IActionResult> AddUserToTutoringSession(int userId, int tutoringSessionId)
{
    var user = await _context.Users.FindAsync(userId);
    var session =  _context.TutoringSessions.Include(session => session.Students).FirstOrDefault(s => s.Id == tutoringSessionId);

    if (user != null && session != null)
    {
        session.Students.Add(user);
        _context.Update(session);
        _context.SaveChanges();
       
        return Ok();
    }
    return BadRequest();
}

This is an HttpPut method where I'm trying to add a user to a tutoring session. A tutoring session (TutoringSession here) has a list of students (List in this case). The issue that I'm having is the following : let's say that I have two TutoringSessions A and B, if a User is in the list of A and I decide to add it to B, it vanishes from A but now exists in B.

If needed here is the code of both the TutoringSession and the User classes.

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Username { get; set; }
    [EmailAddress]
    [Required]
    public string Mail { get; set; }
    public string Password { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public string Role { get; set; }
}

public class TutoringSession
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int RelatedCourseId { get; set; }
    public String Subject { get; set; }
    public DateTime? StartTime { get; set; }
    public DateTime? EndTime { get; set; }
    
    public List<User> Students{ get;set; } 
    public User? Tutor { get; set; }
    public bool IsTutored { get; set; }
}

I've tried to modify it directly using this :

 _context.TutoringSessions.Include(session => session.Students).FirstOrDefault(s => s.Id == tutoringSessionId).Students.Add(user)

But as soon as I call SaveChanges(), it does it again.

I'm sure this is something really silly but it's been almost a day of try/fail and research and I can't find where my error lies, if you guys have any ideas, please let me know!

Edit :

My EF Core version is the 7.0.0

And for the tables here are the SQL scripts that Visual Studio made :

User :

CREATE TABLE [dbo].[User] (
    [Id]                INT            IDENTITY (1, 1) NOT NULL,
    [Username]          NVARCHAR (MAX) NOT NULL,
    [Mail]              NVARCHAR (MAX) NOT NULL,
    [Password]          NVARCHAR (MAX) NOT NULL,
    [Name]              NVARCHAR (MAX) NOT NULL,
    [Surname]           NVARCHAR (MAX) NOT NULL,
    [Role]              NVARCHAR (MAX) NOT NULL,
    [TutoringSessionId] INT            NULL,
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_User_TutoringSessions_TutoringSessionId] FOREIGN KEY ([TutoringSessionId]) REFERENCES [dbo].[TutoringSessions] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_User_TutoringSessionId]
    ON [dbo].[User]([TutoringSessionId] ASC);

TutoringSessions :

CREATE TABLE [dbo].[TutoringSessions] (
    [Id]              INT            IDENTITY (1, 1) NOT NULL,
    [relatedCourseId] INT            NOT NULL,
    [Subject]         NVARCHAR (MAX) NOT NULL,
    [StartTime]       DATETIME2 (7)  NULL,
    [EndTime]         DATETIME2 (7)  NULL,
    [TutorId]         INT            NULL,
    [IsTutored]       BIT            NOT NULL,
    CONSTRAINT [PK_TutoringSessions] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TutoringSessions_User_TutorId] FOREIGN KEY ([TutorId]) REFERENCES [dbo].[User] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_TutoringSessions_TutorId]
    ON [dbo].[TutoringSessions]([TutorId] ASC);

If you guys need anything more let me know ! :)

CodePudding user response：

The problem is the user can reference only one tutoring session, because in DB schema the relation between User and TutoringSessions is one to many. That means a tutoring sessions can have many user and a user can have one tutoring session.

In your case, you want a relation many to many, that to say a tutoring sessions can have many user and a user can have many tutoring session. To do this, you need to introduce a intermediate table like :

CREATE TABLE [dbo].[Attendee] (
    [TutoringSessionId]     INT            NOT NULL,
    [UserId]                INT            NOT NULL,
    CONSTRAINT [FK_Attendee_TutoringSession] FOREIGN KEY ([TutoringSessionId]) REFERENCES [dbo].[TutoringSession] ([Id]),
    CONSTRAINT [FK_Attendee_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id])
)

So when a user is register in a session, it's just a new row in the table Attendee and the precedent attendees are keep.

With EF Core 6, you can declare a many to many relation in many ways, but as there are several relation between user and tutoring session, only the fluent way will work :

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TutoringSession>()
        .HasMany(t => t.Users)
        .WithMany();
}

So EF Core will understand this relation is many to many and will generate the intermediate table on migration.