LINQ help for Dynamic Data Website

Question

Hi All,

I have Dynamic dataWebsite which uses a SQL SP to do update operations..I have a problem here, my delete functionality is also a update(setting IsDeleted=1) operations. I am currently using LINQ query and calling datacontext.SubmitChanges() for deleting. The problem is, the update LINQ query(that sets IsDeleted=1) when i call SubmitChanges() is also calling the update SP which is meant for only Update Operations. Is there any option to fire my LINQ query directly to DB instead of calling update SP?

Employee ild = (from emp in _dataContext.Employee
                                     where emp.IN_ID == int.Parse(ID)
                                     select emp).FirstOrDefault();
ild.IsDeleted=1;
_dataContext.Submitchanges();

The above code always call UpdateSP that is conigured to Update operation

Thanks in advance!

Regards, babu

Answer 1

I'm not 100% sure that I follow the idea here.

generally to delete the record you would say:

_dataContext.Employee.remove(ild);
_dataContext.Submitchanges();

But it seems like you wanted to just update the record to read any Enployee that has a setting IsDeleted = 1 as a deleted record. By running the code you current have there are are generateing an UPDATE statement and so the UpdateSP will fire.

Is there a reason you can't use the .remove() method and ophysically delete the entry?

Answer 2

In this case, could you use a delete stored proc which will get called just like your update proc. The delete proc doesn't need to actually perform a DELETE FROM table query, but instead could do an Update on the underlying table setting the IsDeleted flag as appropriate:

CREATE PROCEDURE
  @Id int
AS
  UPDATE dbo.Employee 
  SET IsDeleted = 1 
  WHERE Id = @Id

You would then map this function to the delete behavior in LINQ to SQL just as you did the Update method. With this option, your client code would do a simple Remove on the table rather than dealing with the IsDeleted flag:

_dataContext.Employee.Remove(ild);
_dataContext.SubmitChanges();

In your model, I would argue that you shouldn't expose IsDeleted at all. That is a database implementation detail. When using soft deletes, you should abstract away your physical table and expose the functionality through views or table value functions.

As an alternative to the soft delete option, you could consider including a Tombstone table mimicing your transactional table. On a delete operation, use a stored proc or trigger to move the record from the transactional table to the tombstone table. With that, you could eliminate the IsDeleted flag from the database and elminate the need to include the filtering on all access (including reporting).