Remove entity without loading first in Linq2SQL

Question

I am wanting to remove an entity from a database using LINQ2SQL without having to pre-fetch it (for performance reasons).

I undersand that this can be done as follows(Delete a LINQ to SQL record without loading it first ):

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 datetime similar to:

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 AM and 12/31/9999 11:59:59 PM.

which basically means its trying to validate .net default datetime against a SQL datetime

Is there a way of deleting an entity with a non-nullable without needing to prefetch it?

*Note ive also tried setting the datetime value to a dummy one, in which case i get the following:

Row not found or changed.

Marc Gravell · Accepted Answer · 2012-03-19 08:30:53Z

4

The "Row not found or changed" suggests that you have those columns set for validation. If you have a timestamp (rowversion) column, you could use that single value for optimistic concurrency checks, although you still won't know the value, of course. In some cases, you could disable concurrency checks completely (set UpdateCheck="Never" on all the interesting columns in the dbml).

However, perhaps a simpler option is:

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

or something along those lines. Not object oriented, but very effective. Note that this doesn't run in the same transaction as SubmitChanges unless you manage the transaction yourself, and that it won't validate that exactly 1 row was deleted. But it is direct. Also note that the data-context won't know about this change, so if your data-context also has (say) pending updates for that row it could get confused.

answered Mar 19, 2012 at 8:30

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marc Gravell Over a year ago

@Luke beware: that will mean you don't get concurrency checks on your updates, too.

undefined Over a year ago

that should be fine I'm actually only using it for a performance test anyway and validating state aftewards

zabulus · Accepted Answer · 2012-03-19 08:34:16Z

0

Try to change

public DateTime TestDate { get; set; }

to

public DateTime? TestDate { get; set; }

And set this field NULLable in your database table

answered Mar 19, 2012 at 8:34

zabulus

2,5433 gold badges17 silver badges29 bronze badges

2 Comments

undefined Over a year ago

I want to do this without setting this as nullable. I shouldnt have to change my model to make it fast to delete

zabulus Over a year ago

OK, you haven't noticed about that. What about changing default value of the field in datamodel or orm layer?

Collectives™ on Stack Overflow

Remove entity without loading first in Linq2SQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related