Delete object without loading first in Linq2SQL

Question

I want to remove an object from the database using LINQ2SQL without prefetching (for performance reasons).

I emphasize that this can be done as follows ( Delete the LINQ to SQL record without first loading ):

public void Delete(int id) { var e = new TestEntity { Id = id }; _context.TestEntities.Attach(e, false); _context.TestEntities.DeleteOnSubmit(e); } 

however, when TestEntity contains a date-time like this:

 public class TestEntity { public int Id { get; set; } public String TestString { get; set; } public DateTime TestDate { get; set; } public int TestInt { get; set; } } 

I get the following error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 and 12/31/9999 11:59:59 PM.

which basically means its trying to check .net default datetime for SQL datetime

Is there a way to delete an object with a non-empty value without having to pre-select it?

* Note ive also tried to set the datetime value to dummy, in which case I get the following:

The row was not found or not modified.

Answer 1

“Row not found or changed” assumes that you have the columns that are set for validation. If you have a timestamp ( rowversion ) column, you can use this single value for optimistic concurrency checks, although you still won't know the values, of course. In some cases, you can completely disable concurrency checking (set UpdateCheck="Never" to all interesting columns in dbml).

However, perhaps a simpler option:

 _context.ExecuteCommand("delete from [TableName] where [PrimaryKey]={0}", id); 

or something like that. Not object oriented, but very efficient. Note that this does not execute in the same transaction as SubmitChanges , unless you manage the transaction yourself, and that it will not verify that exactly 1 row has been deleted. But it’s straight. Also note that the data context will not be aware of this change, so if your data context also has (say) pending updates for this row, it might get confused.

Answer 2

Try to change

 public DateTime TestDate { get; set; } 

to

 public DateTime? TestDate { get; set; } 

And set this field NULLable in the database table