How to delete an entity by ID in Entity Framework

It may seem unnatural to delete Entities in Entity Framework. In the days of stored procedures I used to just pass an ID where as now you need to get the entity before deleting it. It seems inefficient - you're basically making two database hits when surely you can do it in one database call, right? Well it is difficult but entirely possible.

Since you do not have the entity you will need you will effectively be updating a detached entity which is a technique in itself. The trick here is that we are making a fake entity that shares the Entity Key (EF version of a primary key) with the one that needs deleting. Then we mark it as deleted and SaveChanges.

public void DeleteByID(object id)
{
    //make fake entity and set the ID to the id passed in
    var car = new Car();
    car.ID = id;

    using (var context = new MyDataContext())
    {
        //add this entity to object set
        context.CreateObjectSet<Car>().Attach(car);
        //mark it as deleted
        context.ObjectStateManager.ChangeObjectState(car, EntityState.Deleted);
        //save changed - effectively saying delete the entity with this ID
        context.SaveChanges();
    }
}

It is worth noting that I am following my own twist on the Repository pattern for data access so I have an "EntityRepository" that manages everything EF related. This is where I will make my generic "DeleteByID" method. My EntityRepository uses generics to specify the type of entities that are dealt with so this has to be completely reusable by any entity set (TEntity) or object context (TContext).

How to Delete by ID in a generic way

public void DeleteByID(object id)
{
    var item = new TEntity();
    var itemType = item.GetType();

    using (var context = new TContext())
    {
        //get the entity container - e.g. MyDataContext - this will have details of all its entities
        var entityContainer = context.MetadataWorkspace.GetEntityContainer(context.DefaultContainerName, DataSpace.CSpace);
        //get the name of the entity set that the type T belongs to - e.g. "Cars"
        var entitySetName = entityContainer.BaseEntitySets.First(b => b.ElementType.Name == itemType.Name).Name;
        //finding the entity key of this entity - e.g. Car.ID
        var primaryKey = context.CreateEntityKey(entitySetName, item).EntityKeyValues[0];
        //using Reflection to get and then set the property that acts as the entity's key
        itemType.GetProperty(primaryKey.Key).SetValue(item, id, null);
        //add this entity to object set
        context.CreateObjectSet<TEntity>().Attach(item);
        //mark it as deleted
        context.ObjectStateManager.ChangeObjectState(item, System.Data.EntityState.Deleted);
        //save changed - effectively saying delete the entity with this ID
        context.SaveChanges();
    }
}

These both translate to the following SQL:

delete from [dbo].[Cars]
where  ([ID] = 123)

And there it is - a reusable delete by ID function with no SELECT!

LINQ to SQL - Null value cannot be assigned to a member with type System.Int64 which is a non-nullable value type

I am attempting an InsertOnSubmit and it is failing since "The null value cannot be assigned to a member with type System.Int64 which is a non-nullable value type" This LINQ to SQL is driving me crazy. Please help.

The table in the SQL database has a MessageID column which is a BigInt with primary key (NOT NULL IDENTITY 1 1) no Default.

The field looks like this in the designer file to the dbml:

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_MessageId", AutoSync=AutoSync.OnInsert, DbType="BigInt NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] 
public long MessageId 
{ 
    get 
    { 
        return this._MessageId; 
    } 
    set 
    { 
        if ((this._MessageId != value)) 
        { 
            this.OnMessageIdChanging(value); 
            this.SendPropertyChanging(); 
            this._MessageId = value; 
            this.SendPropertyChanged("MessageId"); 
            this.OnMessageIdChanged(); 
        } 
    } 
}

So the problem is that null cannot be assigned... right. But I'm not passing null! How can I be? I'm using a long (a value type so can't be null by nature).

One workaround I found was making this field nullable but this feels like a hack since it should never be null. Anyway this just moves the problem to the ID not AutoSyncing OnInsert. (AutoSync=OnInsert is set).

Update and solution

Well it took me a while, I even posted on stackoverflow to no avail but finally worked out the problem and wrote about it in this post about optimistic concurrency exceptions on insert although by this time it has manifested itself in a new way since I'm using Entity Framework.