C#: ADO.NET Entity Data Model linking tables

You should have foreign keys defined on the corresponding tables in the database, then EF will automatically make the join.
From the scripts in your earlier questions, doesn't look like you have any FKs setup (totally missed that the last time, sorry).

Based on the structure, I would assume these are the ones you need:

alter table movielinkperson add foreign key (movieId) references movie(id);
alter table movielinkperson add foreign key (personId) references person(id);

alter table movielinkfile add foreign key (movieId) references movie(id);
alter table movielinkfile add foreign key (fileId) references [file](id);

alter table movie add foreign key (genreId) references genre(id);
alter table movie add foreign key (langId) references language(id);

Select allOpen in new window

Then refresh the model from the database, and you should have the connections.

Thanx I was thinking something similar on the way home

The original SQLite DB didn't have FK's

Will try tomorrow

I had  to manipulate the data in order to run your query
There were some entries in movielinkperson that didn't have an id in person  so I removed these and a number movies had genre of -1 so I created an other entry in genre

ON refreshing the EF data model I get the image bellow I also get these errors as neither movielinkperson & movielinkfile have primary keys

Severity	Code	Description	Project	File	Line	Suppression State
Warning		Error 6002: The table/view 'movie.dbo.movielinkperson' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.	Movie.DataModel	E:\VB\MovieDB\MovieDB\Movie.DataModel\MovieModel.edmx	1	

Select allOpen in new window

I'm guessing I add a primary key  like this, however is this necessary?

ALTER TABLE movielinkperson
ADD PRIMARY KEY (id);

Select allOpen in new window

A few different items on that:

There were some entries in movielinkperson that didn't have an id in person  so I removed these

Just from the name of the table, why would that happen?  Was that just a data error or would you legitimately ever have a record in that table that didn't / shouldn't link to a person?  I'm guessing just a mistake.
Typically you would just leave it as null in this case (which is perfectly fine on the FK), but as per another item below, I don't think this should be allowed to be nullable in this case.

I'm guessing I add a primary key  like this, however is this necessary?

It's not really necessary, EF will take a guess on it, but usually there is one that will fit the requirements, and usually a good idea on most tables.  In this case, if I'm getting the point of the table properly, I would say that's a good candidate for a composite PK on both movieID and personId.  You would not have a single person appear in the same movie list TWICE, right?

alter table movielinkperson add primary key (movieId, personId)

Select allOpen in new window

That will add the key in, depending on how you query will probably also improve performance, and will constrain the logic to prevent duplicate users in teh same movie.

neither movielinkperson & movielinkfile have primary keys

For movielinkfile - you already have a unique index on fileid.  Which I'm assuming is correct?  In practice I can't see why you would have a file associated with more than one movie.  If that is correct, than movielinkfile.fileid can be the primary key, which would take the place of (and provide the same functionality) as the unique constraint.

ON refreshing the EF data model I get the image bellow

The double properties is concerning.  You shouldn't have ended up in movielinkperson for example, with .movie AND .movie1 AND .movie2.
Or person AND person1.  Same with the majority of the tables.  Either the designer messed up, or something with the table structure isn't quite accurate.

Can you attach a revised script of the table / key create statements?
And if you can, the DataContext files generated?

The original SQlite  database is from an application  that is no longer maintained and website non reachable
The Data is derived from TMDB API

The ultimate object is to  learn Entity Framework, SQLite doesn't  have an ADO connector for VS 2019 hence converting it to MSSQL
I thought it would be a fairly simple  data set to learn on

To answer your questions

movielinkperson  I'm guessing during maintenance the original program hasn't cleaned up properly

This table dose what it says on the Tin
A movie has multiple people in    
An actor can be in multiple movies

I have the exact query at home but to get a list of "John Wayne" films you have to link the 3 tables together

For movielinkfile - you already have a unique index on fileid.  Which I'm assuming is correct?  In practice I can't see why you would have a file associated with more than one movie.

A movie could be associated with multiple files (The Alamo Part1.dvd  & The Alamo Part2.dvd )

On the original database file.path is unique so a file cannot be linked to multiple movies

The double properties is concerning.

I'm glad you picked that up I spotted it and thought it odd

The table structure bellow is from the SQLite file, with a few tweaks where column types differ, however looking at it again I can see considerable optimizations could be done like
name columns don't need to be text. The only text column is probably movie.plot
Other text columns might get long but I don't have this film so "title   varchar(1000) NOT NULL" will suffice

I'm wondering If I should start again and recreate the Database with better column types and adding primary keys to the  2 tables that don't have them I have a feeling this could be quicker and hopefully  resolve some of the issues with the EF data model?

This is what I used to create the database I'm sorry I don't know what "the DataContext files" are

CREATE TABLE language (
	id              INT NOT NULL  IDENTITY  PRIMARY KEY,
	name	TEXT NOT NULL DEFAULT 'Unknown'
);

CREATE TABLE person (
	id              INT NOT NULL  IDENTITY  PRIMARY KEY,
	"name"	TEXT NOT NULL,
	tmdb_id	INT,
	profile_path	TEXT
);

CREATE TABLE movielinkperson (
	movieId	INT NOT NULL,
	personId	INT NOT NULL,
	role	TEXT NOT NULL DEFAULT 'actor'
);
CREATE TABLE movielinkfile (
	movieId	INT NOT NULL,
	fileId	INT NOT NULL UNIQUE
);
CREATE TABLE movie (
	id              INT NOT NULL  IDENTITY  PRIMARY KEY,
	title	TEXT NOT NULL,
	tagline	TEXT,
	plot	TEXT,
	rating	FLOAT DEFAULT 0,
	year	INTEGER DEFAULT 1885,
	runtime	INTEGER DEFAULT 0,
	watched	BIT DEFAULT 0,
	updatedOn	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	genreId	INTEGER NOT NULL,
	type	TEXT DEFAULT 'sd',
	langId	INTEGER DEFAULT 25,
	certification	TEXT,
	tmdbId	TEXT,
	imdbId	TEXT
);
CREATE TABLE genre (
	id              INT NOT NULL  IDENTITY  PRIMARY KEY,
	"name"	TEXT NOT NULL DEFAULT 'Unknown'
);

CREATE TABLE "file" (
	id INT NOT NULL  IDENTITY  PRIMARY KEY,
	path	TEXT NOT NULL 
);

Select allOpen in new window

If it's just for learning purposes, I think you're fine, as you've already got a lot of data in it.

On the link questions above (files and movies), I understand the points you were making, I was asking slightly different items.

For movielinkfile, understand "The Alamo" would have multiple files (avi1, avi2).  BUT, would you ever have "Generic Header Image.png" that was linked to both "The Alamo" AND "El Dorado", or would that be two seperate file entries?

For movielinkperson, would you ever have two entries for the same person for the same movie:
IE: ("The Alamo", "John Wayne", "Actor") AND ("The Alamo", "John Wayne", "producer")?
Or are you handling that a different way?


Answers to the above make a big difference in the keys and how it should be linked.

Using the table script you send, AND adding in the foreign keys, AND making the primary keys based on  my above assumption, this is the EF model I get.  
Notice one key thing - for the file table, IF the file is specific to one movie, BECAUSE movielinkfile only has two columns in it which are both the keys, the EF model hides the entire movielinkfile table from you and shows you only a link between "movie" and "file".

And sorry for not being clear, by "DataCOntext" files I meant the files generated for the Entity Framework model.  Typically that would be a variety of .edmx, .cs and .tt files.

For movielinkperson, would you ever have two entries for the same person for the same movie:

Yes for The Alamo  John Wayne,  is both Actor and director

For movielinkfile, understand "The Alamo" would have multiple files (avi1, avi2).

These are the actual movie file eg avi,mp4,mkv etc

the Images are handled differently the original APP  stored movie files  in a local folder named movie.id.jpg & movie.id.backdrop .jpg
There are no generic images

I added profile_path to person this gets their picture from the  internet I use a PHP script to up date this

Using the table script you send, AND adding in the foreign keys, AND making the primary keys based on  my above

As you've got this working can I see your structure so I can delete and recreate my model?

Or do you still need me   to send my "DataCOntext" files?

I decided to rebuild the tables based on Scott P suggestions the TMDB API & the data

I did this mainly because having slept I forgot where I was

New Table Structure

CREATE TABLE language (
	id INT NOT NULL  IDENTITY  PRIMARY KEY,
	name varchar(max) NOT NULL DEFAULT 'Unknown'
);

CREATE TABLE person (
	id INT NOT NULL  IDENTITY  PRIMARY KEY,
	"name"	TEXT NOT NULL,
	tmdb_id	INT,
	profile_path varchar(max) NULL
);

CREATE TABLE movielinkperson (
    id INT NOT NULL  IDENTITY  PRIMARY KEY,
	movieId	INT NOT NULL,
	personId INT NOT NULL,
	role varchar(50) NOT NULL DEFAULT 'actor'
);
CREATE TABLE movielinkfile (
	movieId	INT NOT NULL,
	fileId	INT NOT NULL UNIQUE
);
CREATE TABLE movie (
	id              INT NOT NULL  IDENTITY  PRIMARY KEY,
	title	varchar(1000) NOT NULL,
	tagline	varchar(1000) NULL,
	plot	varchar(max) NULL,
      rating  decimal(9, 2) NULL,
      year    smallint NULL DEFAULT 1885,
      runtime_mins smallint NULL DEFAULT 0,
      watched      bit NOT NULL DEFAULT 0,
      updatedOn   datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      genreId    INT NOT NULL,
      type      varchar(30) NULL DEFAULT 'sd',
      langId      INT NULL DEFAULT 25,
      certification varchar(6) NULL,
	tmdb_id	INT,
	imdbId varchar(20) NULL,
	poster_path varchar(max) NULL,
	backdrop_path varchar(max) NULL 
);
CREATE TABLE genre (
	id INT NOT NULL  IDENTITY  PRIMARY KEY,
	"name"	TEXT NOT NULL DEFAULT 'Unknown'
);

CREATE TABLE "file" (
	id INT NOT NULL  IDENTITY  PRIMARY KEY,
	path varchar(max) NULL
);

alter table movielinkperson add foreign key (movieId) references movie(id);
alter table movielinkperson add foreign key (personId) references person(id);

alter table movielinkfile add primary key (fileId)
alter table movielinkfile add foreign key (movieId) references movie(id);
alter table movielinkfile add foreign key (fileId) references [file](id);

alter table movie add foreign key (genreId) references genre(id);
alter table movie add foreign key (langId) references language(id);

Select allOpen in new window

I was then able to build the EF model


However on attempting to load the data I got this error

(1 row affected)
Msg 547, Level 16, State 0, Line 24613
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__movielink__movie__37A5467C". The conflict occurred in database "Movies", table "dbo.movie", column 'id'.
The statement has been terminated.

Select allOpen in new window

I solved this by changing the order in which the data was inserted so movie before movielinkperson & movielinkfile

Thanx for your help

Now I need to work out how to use the EF model