C#: Updating database  using ADO.NET Entity Data Model

trevor1940
trevor1940 used Ask the Experts™
on
Hi
I'm attempting to update my local movie database from information from the movie database API (TMDB API)
This picture shows my Entity Framework diagram
MovieModel3.JPGthere are several issues I need to address

People with no tmdb_id or profile path ( Every person on TMDB should have an id but not all have a picture)

movielinkperson.job as few entries, for cast this is the  cast.Character and for crew it's job eg Director

Wrong person linked to a movie

TMDB Person in the database but not linked to a movie or not in the database

Searching TMDB by persons name is ambiguous for instance search for "John Wayne" returns 13 people  

I there for need to search for people linked to movies in the database and update accordingly

In the code below I'm struggling with the logic and with this line

// this is supposed to create a movielinkpersons object but isn't happy with code after &&
  var MLP = context.movielinkpersons.Where(m => m.movieId = Movie.id && x => x.personId == PersonExists.id);

        private void UpdateBtn_Click(object sender, EventArgs e)
        {
            TMDbClient client = new TMDbClient("2c8a02fa36fb5299dcd97bbc84609899");
            using (var context = new MoviesEntities())
            {
                var allMovies = context.movies.OrderBy(m => m.title).ToArray();
                // make a list so not processing a person twice
                List<string> FondPeople = new List<string>();
                foreach (var Movie in allMovies)
                {
                    // Make a list of people in this movie
                    List<Person> People = new List<Person>();
                    foreach(var movielinkperson in Movie.movielinkpersons)
                    {

                        Person NewPerson = new Person();
                        NewPerson.id = movielinkperson.personId;
                        NewPerson.name = movielinkperson.person.name;
                        NewPerson.profile_path = movielinkperson.person.profile_path;
                        if (movielinkperson.person.tmdb_id != null)
                        {
                            NewPerson.tmdb_id = (int)movielinkperson.person.tmdb_id;
                        }

                        People.Add(NewPerson);
                    }

                    // get the List of  people in this movie from TMDB
                    var TMDBMovie = client.GetMovieAsync((int)Movie.tmdb_id, TMDbLib.Objects.Movies.MovieMethods.Credits).Result;

                    // Deal with cast first then do similar for crew
                    foreach (var cast in TMDBMovie.Credits.Cast)
                    {
                        // Has a picture or set to a local default
                        string profile_path = cast.ProfilePath ?? "\\JohnWayneBig.png";

                        // potentialy multiple people could be in this movie with the same name
                        // but usure how ee to this

                        var PersonExists = People.Find(x => x.name == cast.Name);


                        if (PersonExists == null)
                        { 
                            // create a new person however they might by in the DB but not linked to this movie
                            var dbPerson = context.people.SingleOrDefault(p => p.tmdb_id == cast.Id);
                            if(dbPerson == null)
                            {
                                var NewPerson = new MoviesDataModel.person
                                {
                                    name = cast.Name,
                                    tmdb_id = cast.Id,
                                    profile_path = profile_path,
                                };
                                var MLP = new MoviesDataModel.movielinkperson
                                {
                                    movie = Movie,
                                    person = NewPerson,
                                    role = "Actor",
                                    job = cast.Character
                                };
                                context.people.Add(NewPerson);
                                Movie.movielinkpersons.Add(MLP);
                                context.SaveChanges();

                            } // end dbPerson == null)
                            else
                            {
                                // lind the person in the db to this movie
                                var MLP = new MoviesDataModel.movielinkperson
                                {
                                    movie = Movie,
                                    person = dbPerson,
                                    role = "Actor",
                                    job = cast.Character
                                };
                                Movie.movielinkpersons.Add(MLP);
                                context.SaveChanges();
                            }


                        }
                        else 
                        {
                            // Create a Person object
                            var Person = context.people.SingleOrDefault(p => p.id == PersonExists.id);

                            // this is supposed to create a movielinkpersons object but isn't happy with code after &&
                            var MLP = context.movielinkpersons.Where(m => m.movieId = Movie.id && x => x.personId == PersonExists.id);


                            if (Person.tmdb_id > 0)
                            {
                            // just add job    
                                MLP.job = cast.Character;
                            }
                            else // No tmdb_id
                            {
                                MLP.job = cast.Character;
                                Person.tmdb_id = cast.Id;
                                Person.profile_path = profile_path;
                            }
                                context.SaveChanges();

                        }
                    }

                }
            }
        }  // end Update Btn

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Haven't looked at all the code, but the first thing that I notice is:

var MLP = context.movielinkpersons.Where(m => m.movieId = Movie.id && x => x.personId == PersonExists.id);

should be:

var MLP = context.movielinkpersons.Where(m => m.movieId == Movie.id && x => x.personId == PersonExists.id);


»bp
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
As well as what Bill has pointed out, your lambda is incorrect. In your lamba, each movielinkperson is represented by m, so you don't need the x:

var MLP = context.movielinkpersons.Where(m => m.movieId == Movie.id && m.personId == PersonExists.id);

Open in new window

Author

Commented:
Hi

Thanx I'm now getting Error

Severity	Code	Description	Project	File	Line	Suppression State
Error	CS1061	'IQueryable<movielinkperson>' does not contain a definition for 'job' and no accessible extension method 'job' accepting a first argument of type 'IQueryable<movielinkperson>' could be found (are you missing a using directive or an assembly reference?)	

Open in new window


The Database table "movielinkperson" has a job column


                            // this is supposed to create a movielinkpersons object but isn't happy with code after &&
                            var MLP = context.movielinkpersons.Where(m => m.movieId == Movie.id && m.personId == PersonExists.id);


                            if (Person.tmdb_id > 0)
                            {
                            // just add job    
                                MLP.job = cast.Character;
                            }
                            else // No tmdb_id
                            {
                                MLP.job = cast.Character;
                                Person.tmdb_id = cast.Id;
                                Person.profile_path = profile_path;
                            }
                                context.SaveChanges();

Open in new window

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
In your code, MLP is a collection of movielinkpersons (more specifically, it's a IQueryable), but you're trying to set the job as if it's an individual movielinkperson.

To get just a single movielinkperson from your LINQ query, then you need the FirstOrDefault() method:

var MLP = context.movielinkpersons.Where(m => m.movieId == Movie.id && m.personId == PersonExists.id).FirstOrDefault();

Open in new window

Now instead of getting a IQueryable back, MLP with be a single movielinkperson object (or null if your query can't find anything), and you can set the job property on it.

Author

Commented:
Thanx very much
I need to get my head round link queries
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
No worries Trevor.

LINQ can be quite tricky to get your head around but once you do, you'll find it's pretty powerful stuff.

Good luck with your project.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial