Data first many to many for EF Core

Question

I have a SQLite DB that I am trying to use with EF Core database first. It has a table of users, and a table of groups that users can belong to, and it has a mapping table because users can belong to multiple groups.

-- holds users
CREATE TABLE IF NOT EXISTS user (
  _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT,
);
-- holds groups users can belong to
CREATE TABLE IF NOT EXISTS group (
  _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT,
);
-- holds user group membership
CREATE TABLE IF NOT EXISTS map_group_user (
  group_id INTEGER,
  user_id INTEGER,
  UNIQUE (group_id,user_id) ON CONFLICT REPLACE,
  FOREIGN KEY(group_id) REFERENCES group(_id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES user(_id) ON DELETE CASCADE
);    

When I scaffold this up I get a warning from dotnet ef scaffold that it could not identify a primary key for map_group_user and it does not generate a model, and neither the User nor Group model contains any reference to the other (expected).

Answer 1

Try adding an explicit primary key to the map_group_user bridge table:

CREATE TABLE IF NOT EXISTS map_group_user (
    group_id INTEGER,
    user_id INTEGER,
    UNIQUE (group_id,user_id) ON CONFLICT REPLACE,
    FOREIGN KEY(group_id) REFERENCES "group"(_id) ON DELETE CASCADE,
    FOREIGN KEY(user_id) REFERENCES user(_id) ON DELETE CASCADE,
    PRIMARY KEY (group_id, user_id)
);

The logical primary key for the map_group_user table is the combination of group_id and user_id, each combination which should ideally appear only once.

By the way, please avoid naming your tables and columns using reserved SQL keywords, such as group. I don't know if this was giving you an error, but I have placed "group" in double quotes to escape it.