Linq query deleting multiple rows instead of one. Very Simple Query

Question

This should be really simple but I think I'm having possible issues with my model. I have been working with linq over a year and I should have this simple remove easily done. Please help! It's removing both records from the database when I only want one deleted

I have a database table with these properties.

Email, EmployeeName, StoreId
jsch@m.com,Joe Schneider,9
jsch@m.com,Joe Schneider,8

I need to delete Joe Schneider with storeId 9

So I run this simple query and remove process.

        var temp2 = difference[i];
        var PersonToRemove = db.Permissions.SingleOrDefault(s => s.EmployeeName == temp2 && s.StoreId == Persons.StoreId);
        if (PersonToRemove.EmployeeName != null)
        {
            db.Permissions.Remove(PersonToRemove);
            db.SaveChanges();
        }

I am assuming one is going to say, hey your model is not right and don't put the name as a key, but I can't just be changing the model because other parts of the app are based on this model and would cause huge breaks. Could you give me advise how to edit the linq query to not delete both records?

model

  [Table("Permissions")]
    public class Permissions
    {
        [Key]
        public String EmployeeName { get; set; }

        public string Department { get; set; }
        public int? StoreId { get; set; }

        public String Email { get; set; }
    }

Answer 1

[Table("Permissions")]
public class Permissions
{
    [Key]

    public String EmployeeName { get; set; }
}

The problem is here you are defining a primary key which has no length constraint on it. (MaxLength). This leads to EF generate a column with NVARCHAR(MAX). As mentioned here VARCHAR(MAX) columns are not allowed to be primary key. So correct definition should be like below

[Table("Permissions")]
public class Permissions
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)] <--
    [MaxLength(255)] // <---
    public String EmployeeName { get; set; }
}

Edit: You need to recreate the database in order to associated tables initialized with correct settings. Edit 2 : Also you may need a DatabaseGenerated(DatabaseGeneratedOption.None) since its not identity column.

Answer 2

you can set Deleted state on individual entity like so:

var temp2 = difference[i];
var PersonToRemove = db.Permissions.SingleOrDefault(s => s.EmployeeName == temp2 && s.StoreId == Persons.StoreId);
if (PersonToRemove.EmployeeName != null)
{
    db.Entry(PersonToRemove).State = EntityState.Deleted; // do this instead
    db.SaveChanges();
}

EF should then figure out which entity you wanted to delete

UPD I am assuming you are using EF6 and DB-first approach. I am also assuming you've got your DB context class set up with default convention model builder. It seems EF's default object tracking based on Key will not work as your key is not unique (this is a bigger problem, but I understand you're already aware of that).

You might try circumvent that convention by adding custom model builder configuration like so:

class MyDbContext : DbContext { 
    public virtual DbSet<Permissions> Permissions {get;set;}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Permissions>().HasKey(p => new { p.EmployeeName, p.StoreId});
    }
}

since you didn't share your DbContext definition this is just a snippet but hopefully gives you some ideas to explore. this is the API reference: https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.modelconfiguration.entitytypeconfiguration-1?view=entity-framework-6.2.0