How can I avoid duplicate rows in foreign table with EF Code First

c# entity-framework entity-framework-6 sql-server

Question

In my application, Movies are stored in a DB. These Movies have a Name and a Director.

It is well known that any person with the same Name as an other person is the same person. I would like to implement this constraint in the application.

My model is:

public class MovieContext: DbContext
{
    public MovieContext(): base("name=testDB") { }
    public DbSet<Movie> Movies { get; set; }
    public DbSet<Director> Revenues { get; set; }
}

public class Movie
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Director Director { get; set; }
}

public class Director
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

And my application is

static void Main()
{

        Director directorA = new Director()
        {
            Name = "A"
        };


        Movie movie1 = new Movie()
        {
            Name = "foo",
            Director = directorA
        };

        Movie movie2 = new Movie()
        {
            Name = "bar",
            Director = directorA
        };
    using (var context = new MovieContext())
    {
        context.Movies.Add(movie1);
        context.SaveChanges();
    }
    using (var context = new MovieContext())
    {
        context.Movies.Add(movie2);
        context.SaveChanges();
    }
}      

(I am disposing of the context between the two inserts to simulate two different runs of the program).

Running this creates two rows in the Directors table:

Id  Name
1   A
2   A

Since director 1 and director 2 have the same name, they are the same person, and I would therefore prefer to have a single row.

I have tried to use an index for this, and modified my Director model:

public class Director
{
    [Key]
    public int Id { get; set; }
    [Index(IsUnique =true), StringLength(30)]
    public string Name { get; set; }
}

However, this throws at runtime, since EF tries to insert the same row twice. This is clearly not the correct way to do it.

What is the correct way to make sure identical foreign objects are referred to with the same ID when inserting data with Entity Framework?

1
2
2/11/2019 4:34:31 PM

Accepted Answer

The correct way would be that in the second operation you find the director object from the context context.Directors.FirstOrDefault (d => d.Name == "A") and use the returned object, than trying to re-use the one you created in advance.

1
2/12/2019 6:46:22 AM

Popular Answer

I think what you're describing is just updating an entry. If a director is already created under a specific name and a new director is created with the same name it should restrict creation since a director with that name already exists but if the table has other columns like "email" then this field should be updated if the director already exits?

If this is the case why don't you just make Name the primary key of the table?

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string Name{ get; set; }

Why have the id?



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow