• Peforming CRUD with LINQ to SQL

    by Venkata Koppaka | Oct 28, 2010

    LINQ to SQL is an ORM(Object-relational mapping) tool introduced by Microsoft.

    In this post I will walk through how to perform basic CRUD(Create, Retrieve, Update, Delete) operations using LINQ to SQL.

    (NOTE: This post assumes you have basic knowledge of using LINQ to SQL like adding the DBML class and dragging tables on to a DBML designer)

    Lets get started -

    The table we are going use for this example is a simple Person Table. Here is the schema for the table -

     

    Create

    To perform a Create operation on a table using LINQ to SQL you need to create an instance of the class and populate the data into the instance. Once you are done call "InsertOnSubmit" and "SaveChanges" to insert the object.

    Here is the code sample for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 Person aPersonToInsert = new Person(); 
    4                 aPersonToInsert.Name = "Test User"; 
    5                 aPersonToInsert.Age = 26; 
    6                 aPersonToInsert.Email = "test@test.com"; 
    7                 context.Persons.InsertOnSubmit(aPersonToInsert); 
    8                 context.SubmitChanges(); 
    9             } 

    The above code will insert the object only after context.SubmitChanges() is called.

    Retrieve

    To perform a retrieve from a table using you can use one of the two syntax. The first one is a normal LINQ syntax which reads more like plain SQL. The second syntax is called "Lamda" syntax which is just a more convenient way to create a delegate.

    Here are the code samples for doing both.

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Normal Syntax 
    4                 Person person = (from aPerson in context.Persons 
    5                                  where aPerson.PersonId == 1 
    6                                  select aPerson).SingleOrDefault(); 
    7  
    8                 //Lambda Syntax 
    9                 Person personLamda = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    10                  
    11             } 

    The SingleOrDefault() method above gets a single entity that matches the criteria or returns NULL if nothing is found.

    Update

    To perform a Update on a database record using LINQ to SQL, you have to get the record using one of the two syntax shown above and make changes to record and then Submit the changes to the database using SubmitChanges().

    Here is the code sample for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Lambda Syntax 
    4                 Person person = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    5                 person.Name = "Updated User"; 
    6                 person.Age = 99; 
    7                 person.Email = "noone@someone.com"; 
    8  
    9                 context.SubmitChanges(); //this will save your changes to the database 
    10             } 

    Again please note unless you call SubmitChanges() none of your changes are saved to the database.

     

    Delete

     Delete operation using LINQ to SQL is similar to Update operation. You have to get the record, delete the record and Submit the changes to the database using SubmitChanges().

    Here is the code for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Lambda Syntax 
    4                 Person person = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    5  
    6                 context.Persons.DeleteOnSubmit(person); 
    7                 context.SubmitChanges(); //this will save your changes to the database 
    8             } 

    Again unless you call SubmitChanges() nothing is deleted from the database.

    So we are done performing a simple CRUD operations using LINQ to SQL.

    Hope you find it useful,

     Cheers,

    Venkata

    Go comment!