CRUD Operations using LINQ Entities

• Download source code - 2.16 MB

Table of Contents

• Introduction and Goal
• Still new to LINQ - Below are Some Real Quick Starters
• LINQ In-memory Commits and Physical Commits
• Step 1: Create the Entity Customer Class
• Step 2: Create using LINQ
• Create Data Context
• Set the Data for Insert
• Do an In-memory Update
• Do the Final Physical Commit
• The Final Create LINQ Code
• Step 3: Update using LINQ
• Create Data Context
• Select the Customer LINQ Object which we Want to Update
• Finally Set New Values and Update Data to Physical Database
• The Final Code of LINQ Update
• Step 4: Delete using LINQ
• DeleteOnSubmit
• Step 5: Self Explanatory LINQ Select and Read
• The LINQ CRUD Code

Introduction and Goal

This is a pretty simple tutorial dedicated to LINQ newbies who want to learn how to do CRUD operations using LINQ entities. I am sure many experienced LINQ players would criticize me for such a mild article. One of the biggest catches which I found is the in-memory update service by LINQ, which I think for any LINQ newbie is a must to understand.

So we will start with an understanding of the differences between in-memory updates and physical commits and then we will go deep into the code for each database operation. Again this article is one of those small sprints which I need to run so that I can complete the huge LINQ FAQ project.

Watch my 500 videos dedicated to Microsoft technologies here.

Still New to LINQ - Below are Some Real Quick Starters

• Are you a complete newbie? Read this.
• Do you want to define 1-* and *-1 using LINQ? Read this.
• Issues of multiple trips handled in this article? Read this.
• Do not know how to call stored procedures using LINQ? Read this.

LINQ In-memory Commits and Physical Commits

Entity objects form the base of LINQ technologies. So when any data is submitted to database, it goes through the LINQ objects. Database operations are done through ‘DataContext’ class. As said previously, entities form the base of LINQ, so all the data is sent to these entities first and then it's routed to the actual physical database. Due to this nature of working database commits is a two step process. The first step is in-memory and final step is physical commits.
In order to do in-memory operation ‘DataContext’ has provided ‘DeleteOnSubmit’ and ‘InsertOnSubmit’ methods. When we call these methods from the ‘DataContext’ class, they add and update data in the entity objects memory. Please note these methods do not change / add new data in the actual database.

Once we are done with the in-memory operations and we want to send all the updates to the database, we need to call ‘SubmitChanges()’ method. This method finally commits data into the physical database.

So let’s consider a customer table (customerid, customercode and customername) and see how we can do the in-memory and physical commit operations.

Step 1: Create the Entity Customer Class

So as a first step, we create the entity of customer class as shown in the below code snippet.

[Table(Name = "Customer")]
public class clsCustomerEntity
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;

[Column(DbType = "nvarchar(50)")]
public string CustomerCode
{
set
{
_CustomerCode = value;
}
get
{
return _CustomerCode;
}
}
[Column(DbType = "nvarchar(50)")]
public string CustomerName
{
set
{
_CustomerName = value;
}
get
{
return _CustomerName;
}
}
[Column(DbType = "int", IsPrimaryKey = true,IsDbGenerated=true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}

Step 2: Create using LINQ

Create Data Context

So the first thing is to create a ‘datacontext’ object using the connection string.

DataContext objContext = new DataContext(strConnectionString);

Set the Data for Insert

Once you create the connection using the ‘DataContext’ object, the next step is to create the customer entity object and set the data to the object properties.

clsCustomerEntity objCustomerData = new clsCustomerEntity();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;

Do an In-memory Update

We then do an in-memory update in entity objects itself using ‘InsertOnSubmit’ method.

objContext.GetTable<clsCustomerEntity>().InsertOnSubmit(objCustomerData);

Do the Final Physical Commit

Finally we do a physical commit to the actual database. Please note until we call ‘SubmitChanges()’, data is not finally committed to the database.

objContext.SubmitChanges();

The Final Create LINQ Code

Below is the final LINQ code put together:

DataContext objContext = new DataContext(strConnectionString);
clsCustomerEntity objCustomerData = new clsCustomerEntity();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.GetTable<clsCustomerEntity>().InsertOnSubmit(objCustomerData);
objContext.SubmitChanges();

Step 3: Update using LINQ

So let’s take the next database operation, i.e. update.

Create Data Context

As usual we first need to create a ‘datacontext’ object using the connection string as discussed in the create step:

DataContext objContext = new DataContext(strConnectionString);

Select the Customer LINQ Object Which we want to Update

Get the LINQ object using LINQ query which we want to update:

var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

Finally Set New Values and Update Data to Physical Database

Do the updates and call ‘SubmitChanges()’ to do the final update.

clsCustomerEntity objCustomerData = 
	(clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.SubmitChanges();

The Final Code of LINQ Update

Below is what the final LINQ update query looks like.

DataContext objContext = new DataContext(strConnectionString);
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;
clsCustomerEntity objCustomerData = 
	(clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.SubmitChanges();

Step 4: Delete using LINQ

Let’s take the next database operation delete.

DeleteOnSubmit

We will not be going through the previous steps like creating data context and selecting LINQ object. Both of them are explained in the previous section. To delete the object from in-memory, we need to call ‘DeleteOnSubmit()’ and to delete from final database, we need use ‘SubmitChanges()’.

objContext.GetTable<clsCustomerEntity>().DeleteOnSubmit(objCustomerData);
objContext.SubmitChanges();

Step 5: Self Explanatory LINQ Select and Read

Now in the final step, selecting and reading the LINQ object by criteria. Below is the code snippet which shows how to fire the LINQ query and set the object value to the ASP.NET UI.

DataContext objContext = new DataContext(strConnectionString);

var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

clsCustomerEntity objCustomerData = 
	(clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
txtCustomerCode.Text = objCustomerData.CustomerCode;
txtCustomerName.Text = objCustomerData.CustomerName;

The LINQ CRUD Code

You can download the complete CRUD source code from here.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 4 of 4 (Total in Forum: 4) (Refresh) FirstPrevNext

Does it work with MySQL S. Kolic 23:58 30 Aug '09   Hi, Thanks for the nice article. I was wondering if it is possible to use this concept with an MySQL database? I want to build my models database-independent and in my data access layer I want to choose the database type. If it is possible to use this concept with MySQL please send me a note. Thanks in advance. Best regards, Sead Sign In·View Thread·PermaLink Update without reselect back the record Jason Law 20:48 21 Jul '09   Is there a way to update the data without re-select back the record? Something like we do in stored procedure, but of course I want to avoid using stored procedure. Thank you. Jason Law Sign In·View Thread·PermaLink Good One Abhijit Jana 13:20 14 Jul '09   Shivprasad, Excelent contribution Got my 5 cheers, Abhijit CodeProject MVP Web Site:abhijitjana.net My Latest Article : IIS Remote Debugging Sign In·View Thread·PermaLink It's nice Md. Marufuzzaman 8:05 11 Jul '09   You wrote a nice article.... Md. Marufuzzaman Sign In·View Thread·PermaLink

Last Visit: 12:44 7 Nov '09     Last Update: 12:44 7 Nov '09 1