Assigning a new user to the movie in the database

Question

I wrote an application that works as follows:

1. User create account and log in.
2. User paste link to the film from the Filmweb.pl database in the appropriate field.
3. The application parses the link and the received results such as: directing, description, cover etc. are added to the application database.
4. And so on: user can paste a link to another film (and if the title has not been added before), the parsing process will repeat.

What I described above works, but not as I want. Suppose I have users named Jarek and Damian. Each of them will paste a movie with the same title into the parser, eg. "Obecność" (eng. "The Conjuring"). Currently, in the database will happen something like this:

but I would like to have something like that:

because it makes no sense to repeat all the data concerning the film, since they are the same, just add a new user to the field UserName assigned to a specific film. The only question is how to do it?

The whole code of my application is available here: https://bitbucket.org/DamianOS_MP5/filmwebparser-old-version

I read somewhere that I need to create a many-to-many relationship. I add a property to the Film class:

public List<FilmUser> FilmUserList { get; set; }

and to the FilmUser class property and constructor:

public List<Film> FilmList { get; set; }

public FilmUser(string name)
{
    IdentityUser user = new IdentityUser(name);
}

Then I change methods, for example AddFilm from:

public string AddFilm(Film film)
{
    if (film.Title == null)
        return "W podanym linku nie wykryto filmu.";
    else if (!_context.Films.Any(t => t.Title == film.Title && t.UserName == film.UserName))
    {
        _context.Add(film);
        return string.Empty;
    }
    else
        return "Podany film znajduje się już w bazie.";
}

to:

public string AddFilm(Film film)
{
    if (film.Title == null)
        return "W podanym linku nie wykryto filmu.";
    else if (!_context.Films.Any(t => t.Title == film.Title))
    {
        film.FilmUserList.Add(new FilmUser(film.UserName));
        _context.Add(film);
        return string.Empty;
    }
    else
    {
        if (!_context.Films.Any(t => t.FilmUserList.Contains(new FilmUser(film.UserName))))
        {
            film.FilmUserList.Add(new FilmUser(film.UserName));
            _context.Update(film);
            return string.Empty;
        }
        else
            return "Podany film znajduje się już w bazie.";
    }
}

So the next step is to create a new migration. And here is the error:

Unable to determine the relationship represented by navigation property 'Film.FilmUserList' of type 'List'. Either manually configure the relationship, or ignore this property from the model.

Using the example https://learn.microsoft.com/en-us/ef/core/modeling/relationships#many-to-many I wrote:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Film>()
        .HasKey(s => new { s.??? });
    base.OnModelCreating(builder);
}

but what to put in place of question marks? I made a mistake somewhere, but I don't know where. I write about it in details, because I am trying to understand it.

Answer 1

Why not remove username from the film table. Instead, create a seperate link table between the user and the film table looking something like:

UserFilm

user_id | film_id
    1   |   203
    3   |   203
    3   |   208
    1   |   209

This way any user can have many films and a 2 users can share the same film. This is what's needed in terms of your SQL database to support the sort of code you mention and the many to many relationships.

I'm happy to take a look at your code at lunch if you like the look of this solution. Though im sure others will assist you further before then.

Good luck