ASP.NET Entity Framework insert if not exists

Question

Hey guys and girls :)

I'm currently trying to implement a c#/asp.net code-first entityframework database app (on azure) which would hold a list of movies. Every time i ask the app for a movie it does the following:

query database
if not found in database, query TMDB
add to database (without duplicates)

i have a working query, i have a working TMDB parser, i can add things to the database. The problem arises when i try to have no duplicates in my relations.

My setup:

Movies

public partial class Movies
    {
        public Movies() { this.Genres = new HashSet<Genres>(); /* [...] */ }

        [Key]
        public int Movie_ID { get; set; }
        // [...]
        public virtual ICollection<Genres> Genres { get; set; }
    }

Genres

public partial class Genres
{
    public Genres() { }

    [Key]
    public int Genre_ID { get; set; }
    public string Genre_Name { get; set; }
}

Database Access Class

==> here is where my issues arise <==

Movies daMov = new Movies();
// [...]
foreach ( string genre in movieGenres )
        {
            Genres daGenre = await dataBaseContext.Genres.Where(
                        m =>
                            m.Genre_Name == genre
                        ).FirstOrDefaultAsync( );       // query genre from db

            // doesn't exist - firstOrDefault() returns default
            if ( daGenre == null || object.Equals( daGenre, default( Genres ) ) )               
            {
                daGenre = new Genres(); // create new in db
                daGenre.Genre_Name = genre;
                daMov.Genres.Add( daGenre ); // works fine, just that it creates a new genre
            }
            else
            {
                // here i've been googling around for some time and found the following 
                // (all with the same error)

                //db.Genres.Attach( daGenre ); // don't create new??
                //daMov.Genres.Add( daGenre ); // should work?
                //db.Entry( daGenre ).State = EntityState.Modified; // no it doesn't
            }
        }
// [...]
db.Movies.Add( daMov );
await db.SaveChangesAsync(); // DBHelper.cs:line 140

and that's the (cleaned up) error message my ASP.NET WebAPI throws:

ExceptionMessage: 
An error occurred while saving entities that do not expose foreign key properties for their relationships. 
The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. 
Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.,
ExceptionType: System.Data.Entity.Infrastructure.DbUpdateException,

StackTrace:    
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at dotNETproject1.WebAPI.App_Code.DBHelper.<queryIMDB>d__0.MoveNext() in App_Code\DBHelper.cs: line 140

InnerException: 
    ExceptionMessage: Store update, insert, or delete statement affected an unexpected number of rows (0). 
    Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.,
    ExceptionType: System.Data.Entity.Core.OptimisticConcurrencyException,

Copyright © 1990-2014 IMDb.com, Inc.... do you have their written permission to be scraping their site? Until then, you might just be wasting your time [well, until they decide to block your scraper anyway] :) — Nathan
– Nathan, Commented Dec 1, 2014 at 13:36

Alex Art. · Accepted Answer · 2014-12-02 08:59:27Z

2

First of all you need to add public virtual ICollection<Movies> Movies{ get; set; } to your Genres class to insure many to many relationship.

EDIT

Also I see that you have both db and dataBaseContext and this is wrong. You have to work with the same db context

I think something like that should work:

Movies daMov = new Movies();
// [...]
foreach ( string genre in movieGenres )
        {
            Genres daGenre = await db.Genres.FirstOrDefaultAsync(
                   m => m.Genre_Name == genre);

            // doesn't exist - firstOrDefault() returns default
            if ( daGenre == null || object.Equals(daGenre, default(Genres)))               
            {
                daGenre = new Genres(); // create new in db
                daGenre.Genre_Name = genre;
            }
            daMov.Genres.Add( daGenre );
            db.Movies.Add(daMov);
            await db.SaveChangesAsync();

        }

edited Dec 2, 2014 at 8:59

answered Dec 1, 2014 at 13:22

Alex Art.

8,7813 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Asmodiel Over a year ago

not quite :( ExceptionMessage: "Self referencing loop detected with type 'dotNETproject1.WebAPI.Movies'. Path 'Genres[0].Movies'."

M Kenyon II Over a year ago

Asmodiel, are you still dealing with the loop issue? When do you get that error? .Net is more forgiving of relational loops than Serializers. If you're doing a Json or Javascript serialization, you can decorate the property with [ScriptIgnore] or [JsonIgnore].

Shoter Over a year ago

This is not insert if not exists behaviour. This is Select and insert behaviour. It will not work in highly concurrent environment.

Collectives™ on Stack Overflow

ASP.NET Entity Framework insert if not exists

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related