LINQ deletion - Can delete one way, can't using Single enumerable

Question

This works:

var i = (from x in db.Test
    where x.Id == 1
    select x).First();

db.Test.DeleteOnSubmit(i);
db.SubmitChanges();

I get a cast error for this (int/string):

var i = db.Test.Single(x => x.Id == 1);
db.Test.DeleteOnSubmit(i);
db.SubmitChanges();

I was also able to make an update using Single sucesssfully on the same table. Any clues?

Update

Here's more of the error message:

Here's more:

[InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.] System.Data.Linq.SingleKeyManager2.TryCreateKeyFromValues(Object[] values, V& v) +134 System.Data.Linq.IdentityCache2.Find(Object[] keyValues) +57 System.Data.Linq.StandardIdentityManager.Find(MetaType type, Object[] keyValues) +51 System.Data.Linq.CommonDataServices.GetCachedObject(MetaType type, Object[] keyValues) +113 System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc, Object instance) +235 System.Data.Linq.ChangeProcessor.BuildEdgeMaps() +510 System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +137 System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +453 System.Data.Linq.DataContext.SubmitChanges() +38

Answer 1

What's the data type of your id field?

I just ran the following code against a dummy database that has data in it where my key field is of type int:

using (var db = new dbDataContext())
{
    var x = db.items.Single(i => i.fld_key == 1);
    db.items.DeleteOnSubmit(x);
    db.SubmitChanges();

    var y = (from i in db.items
             where i.fld_key == 2
             select i).First();
    db.items.DeleteOnSubmit(y);
    db.SubmitChanges();
}

I had no problems with either, and checking the database, items 1 and 2 were both deleted successfully.

Are you attempting to delete an item that doesn't exist in the second code block - as you deleted it with the first. This would mean that y is actually null and you're attempting to DeleteOnSubmit passing null which will give you an invalid cast as you can't cast null.

Try attempting to delete a different item in your second code block, I would imagine it would work in that case.

Change .Single to .SingleOrDefault and wrap your delete lines to check for null:

if(null != x)
{
    db.items.DeleteOnSubmit(x);
    db.SubmitChanges();
}

I would wager this will fix the problem you're seeing.

Edit Looking at the exception you've posted, I'm wondering if you changed the data type of your table to int after you generated your dbml. Reload the table into your dbml and see if that fixes the issue. Either that, or the issue is the other way around and you changed it to VarChar/NVarChar and haven't updated your dbml.

I would definitely take a look at your entities and make sure that the data types match those in your data table in your underlying database.

Answer 2

Have you changed the datatypes in your database lately? (Maybe those types currently differ from your generated classes)

Similarly, if you're using a stored procedure for deleting items, make sure the parameter types match up to your generated types.