LINQ to SQL Generic Class for Insert and Delete operation

Question

Hi,

I have been writing same code for insert, update, delete with LINQ over and over again. I want to have some sort of generic function for Insert, Update, Delete operation. I read a post here like the following :

    public static void Insert<T>(T entity) where T : class
{
   using (OrcasDB database = new OrcasDB())
   {
      database.GetTable<T>().Add(entity);
      database.SubmitChanges();
   }
}

public static void Delete<T>(Expression<Func<T, bool>> predicate)
where T : class
{
   using (OrcasDB database = new OrcasDB())
   {
      T instance = (T) database.GetTable<T>().Where<T>(predicate).Single();
      database.GetTable<T>().Remove(instance);
      database.SubmitChanges();
   }
}

How to Use
// insert
Employee will = new Employee
{
   Username = "will.asrari",
   EmailAddress = "[email protected]",
   CanCode = true
};

LinqHelper.Insert<Employee>(will); 

// delete
LinqHelper.Delete(emp => emp.EmployeeId.Equals(3));

Yes, I would like to write something like in VB.NET. Is the code above good to follow? Can anyone show me any LINQ to SQL generic class for Insert, Delete, Update written in VB.NET?

Thank you.

Answer 1

We've taken a similar approach in our 3-tier application framework. We currently have roughly 80 entities and have used generics to create a very light-weight set of generic CRUD methods that satifsy those 80 entities and any number of entities.

The only suggestion I might make is to re-think your approach to creating a new database context for each insert, update and delete operation. The problem is that if you need to wrap multiple inserts, updates and/or deletes in a single transaction, you're going to need to use a TransactionScope object because each insert/update/delete is using it's own context object. Using TransactionScope is ok, but since you've got multiple connections, the transaction is going to get elevated to an MTC transaction, which is a hassle.

Can't help you with the VB code. IMO, learn and stick with C#.

Randy

Answer 2

Hi,

FYI, I managed to write a simple class to do the generic CUD operantion for LINQ to SQL.

'Class GenericCUD.vb

Imports System.Linq.Expressions Imports System.Data.Linq

Public Class GenericCUD

Public Shared Sub Insert(Of T As Class)(ByVal theEntity As T)
    Using db As New DemoDataContext()
        db.GetTable(Of T)().InsertOnSubmit(theEntity)
        db.SubmitChanges()
    End Using
End Sub


Public Shared Sub Update(Of T As Class)(ByVal originalEntity As T, ByVal newEntity As T)
    Using db As New DemoDataContext()
        db.GetTable(Of T)().Attach(newEntity, originalEntity)
        db.Refresh(RefreshMode.KeepCurrentValues, newEntity)
        db.SubmitChanges()
    End Using
End Sub

Public Shared Sub Delete(Of T As Class)(ByVal theEntity As T)
    Using db As New DemoDataContext()
        db.GetTable(Of T)().Attach(theEntity)
        db.GetTable(Of T).DeleteOnSubmit(theEntity)
        db.Refresh(RefreshMode.KeepCurrentValues, theEntity)
        db.SubmitChanges()
    End Using
End Sub

End Class

How to use the class :

   'Using Insert
    Dim ta As New TestAuthor
    ta.FirstName = TextBox1.Text
    ta.LastName = TextBox2.Text
    GenericCUD.Insert(ta)


   'Using Update
    Dim original As New TestAuthor
    original.Id = 3


    Dim newEntity As New TestAuthor
    newEntity.Id = original.Id
    newEntity.FirstName = TextBox1.Text
    newEntity.LastName = TextBox2.Text

    GenericCUD.Update(original, newEntity)


   'Using Delete
    Dim ta As New TestAuthor
    ta.Id = 7
    GenericCUD.Delete(ta)

I read a lot of post on many blogs. Here are a few that really helped me to make the GenericCUD work:

1. LINQ, Lambda, and Generics: Insert and Delete
2. LINQ to SQL CRUD
3. How to Make LINQ to SQL Check for Changes After Attach

So, What do you think about the GernericCUD class above? Please give me some comment because I want to improve it. Thank you.