LINQ – InActivating Records As Opposed to Deleting Them

Whilst there are always arguments for being able to delete records from a database, I have always preferred to InActivate my records as opposed to deleting them thus maintaining the referential integrity, history etc. With that in mind I thought I would pen a really quick post about how to do this using LINQ and VB.NET hopefully writing as little code as possible.


Consider the following (incredibly simplistic) Countries database table:

Column Name Data Type Details
ID INT Primary Key, Identity
Active BIT Not Null, Default 1
Name NVARCHAR(50) Not Null

Using Visual Studio 2008 we create a standard ASP.NET 3.5 web project called Example and add a Linq To SQL class as shown below:


Using the Server Explorer we simply drag the Countries table onto the design surface of the Example.dbml file as shown below:


We save that file and modify the Default.aspx file to include a simple Gridview and accompanying LinqDataSource:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<head runat="server">
<title>JOATIT - Example</title>
<form id="frm" runat="server">
<asp:GridView ID="grvRecords" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" DataSourceID="ldsRecords">
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:CheckBoxField DataField="Active" HeaderText="Active"
SortExpression="Active" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True"
ShowHeader="True" />
<asp:LinqDataSource ID="ldsRecords" runat="server"
ContextTypeName="ExampleDataContext" EnableDelete="True" OrderBy="Name"

If we run this as it is the GridView will bind as shown below, but the Delete link will ACTUALLY delete the records from the database which is not what we want:


Instead what we want to is InActivate them when the Delete event is fired. To do this we simply open the Default.aspx.vb code-behind file and add the following custom event as shown below:

Protected Sub ldsRecords_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceDeleteEventArgs) Handles ldsRecords.Deleting
Dim _recordToBeDeleted As Country = CType(e.OriginalObject, Country)
Dim _db As New ExampleDataContext
Dim _specificRecord = (From _allRecords In _db.Countries _
Where _allRecords.ID = _recordToBeDeleted.ID _
Select _allRecords).Single
_specificRecord.Active = False
e.Cancel = True
End Sub

Now when we click the Delete link, our custom event fire and updates the record in question setting Active = 0, and cancels the Delete event.


More than happy to learn of a better way of doing this other than with a database trigger (which has its own advantages and disadvantages) so please, feedback (positive or negative) is always welcome.