How can I delete an object with navigation property with entity framework 5 code first?

Question

I'm using EF 5 code first and I have 2 related entities ApplicationPermission and Application:

public class ApplicationPermission
{
   public virtual Application Application { get; set; }

   public int Id { get; set; }
} 

public class Application 
{
   public string Name { get; set; }

   public int Id { get; set; }
}

with the following mapping for ApplicationPermission:

HasKey(x => x.Id);
HasRequired(x => x.Application).WithMany().Map(m => m.MapKey("ApplicationId")).WillCascadeOnDelete(false);

and for Application:

HasKey(x => x.Id);

I use the code below for deleting ApplicationPermission:

ApplicationPermission entity = new ApplicationPermission { Id = id };
DbContext.Set<ApplicationPermission>().Attach(entity);
DbContext.Set<ApplicationPermission>().Remove(entity);
DbContext.SaveChanges();

But I got an error on SaveChanges method:

Entities in 'CodeFirstContainer.ApplicationPermissions' participate in the 'ApplicationPermission_Application' relationship. 0 related 'ApplicationPermission_Application_Target' were found. 1 'ApplicationPermission_Application_Target' is expected.

How can I delete ApplicationPermission without loading Application to the dbcontext?

Answer 1

I believe it's not possible to delete an entity without having set required navigation properties when you use independent associations. You must load the Application from the database or - at least - know the foreign key value and attach an Application entity with that value, like so:

ApplicationPermission entity = new ApplicationPermission { Id = 1 };
entity.Application = new Application { Id = 5 };
DbContext.Set<ApplicationPermission>().Attach(entity); //attaches Application too
DbContext.Set<ApplicationPermission>().Remove(entity);
DbContext.SaveChanges();

The SQL command generated when you call SaveChanges is then:

exec sp_executesql N'delete [dbo].[ApplicationPermissions]
where (([Id] = @0) and ([ApplicationId] = @1))',N'@0 int,@1 int',@0=1,@1=5

As you can see the query for the delete does not only ask for the Id of the ApplicationPermission to delete but also (and) the foreign key value for ApplicationId. In order to succeed you must know and set this FK value by setting the related entity with the same primary key.

The problem does not occur when using foreign key associations:

public class ApplicationPermission
{
    public virtual Application Application { get; set; }
    public int ApplicationId { get; set; }

    public int Id { get; set; }
}

Mapping:

modelBuilder.Entity<ApplicationPermission>()
    .HasRequired(x => x.Application)
    .WithMany()
    .HasForeignKey(x => x.ApplicationId)
    .WillCascadeOnDelete(false);

You can then use your original code without setting the FK property ApplicationId to a correct value (will default to 0 then) and without setting the navigation property and deleting the entity will work. The SQL command doesn't care about the FK and just queries for the Id of the ApplicationPermission to delete:

exec sp_executesql N'delete [dbo].[ApplicationPermissions]
where ([Id] = @0)',N'@0 int',@0=1

I have no idea why the SQL commands are different between the two types of associations.