This is the third series in my LINQ FAQ series. In this series we will cover LINQ FAQ's related to concurrency handling, compiled queries, CRUD implementation and mapping simple .NET classes with XML file configuration. I love to write articles in FAQ format for the only purpose that they are to the point and you can get to know more by reading less.
LINQ FAQ part I for newbie's:- This is the first part of LINQ FAQ series which starts with what exactly LINQ is and then talks about different LINQ query formats like group by, order by, search with criteria etc. A must to read if you are a beginner in LINQ technologies.
LINQ FAQ Part II:-In this FAQ we will see a basic example of LINQ to SQL, how to define 1-1 and 1-many relationship using LINQ, how we can optimize LINQ queries, execution of stored procedures using LINQ and finally we will see a simple CRUD example using LINQ to SQL.
LINQ gives three ways by which we can handle concurrency conflicts. To handle concurrency conflicts we need to wrap the LINQ to SQL code in a 'TRY' block and catch the 'ChangeConflictException'. We can then loop through the 'ChangeConflicts' collection to specify how we want the conflict to be resolved.
catch (ChangeConflictException ex)
{
foreach (ObjectChangeConflict objchangeconf in objContext.ChangeConflicts)
{
objchangeconf.Resolve(RefreshMode.OverwriteCurrentValues);
}
}
There are 3 ways provided by LINQ system to handle concurrency conflicts:-
- KeepCurrentValues :- When this option is specified and concurrency conflicts happen LINQ keeps call the LINQ entity object values as it is and does not push the new values from the database in to the LINQ object.
- OverwriteCurrentValues :- When this option is specified the current LINQ object data is replaced with the database values.
- KeepChanges :- This is the most weird option but can be helpful in some cases. When we talk about classes it can have many properties. So properties which are changed are kept as it is but the properties which are not changed are fetched from the database and replaced.
We need to use the 'RefereshMode' to specify which options we need as shown in the below code snippet.
![1.jpg]()
One of the best options provided by LINQ concurrency system is control of concurrency behavior at field level. There are three options we can specify using the 'UpdateCheck' attribute:-
- Never: - Do not use this field while checking concurrency conflicts.
- Always: - This option specifies that always use this field to check concurrency conflicts.
- WhenChanged :- Only when the member value has changed then use this field to detect concurrency conflicts.
Below is the code snippet which show how we can use the 'UpdateCheck' attribute to control property / field level concurrency options as specified above. [Column(DbType = "nvarchar(50)",UpdateCheck=UpdateCheck.Never)]
public string CustomerCode
{
set
{
_CustomerCode = value;
}
get
{
return _CustomerCode;
}
} LINQ concurrency system lets you specify how you want the conflicts to be reported. LINQ system has given 2 ways to report conflicts:-
ContinueOnConflict :- This option says to the LINQ engine that continue even if there are conflicts and finally return all conflicts at the end of the process.
FailOnFirstConflict :- This option says stop as soon as the first conflict occurs and return all the conflicts at that moment. In other words LINQ engine does not continue ahead executing the code.
![2.jpg]()
Both these options can be provided as an input in 'SubmitChanges' method using the 'ConflictMode' enum. Below is the code snippet of how to specify conflict modes.
objContext.SubmitChanges(ConflictMode.ContinueOnConflict);
LINQ has provided something called as compiled LINQ queries. In compiled LINQ queries the plan is cached in a static class. As we all know that static class is global cache. So LINQ uses the query plan from the static class object rather than building the preparing the query plan from scratch.
![3.jpg]()
Figure: - LINQ Query Caching
In all there are 4 steps which need to be performed right from the time LINQ queries are built till they are fired. By using compiled LINQ queries the 4 steps are reduced to 2 steps.
![4.jpg]()
Figure: - Query plan bypasses many steps
The first thing is to import Data.Linq namespace.
Import namespace using System.Data.Linq;
The syntax to write compiled queries is a bit cryptic. So let us break those syntaxes in small pieces and then we will try to see how the complete syntax looks like. To execute a compiled function we need to write function to pointer. This function should be static so that LINQ engine can use the query plan stored in those static class objects.
Below is how we define the function it starts with 'public static' stating that this function is static. Then we use the 'Func' keyword to define the input parameters and output parameters. Below is how the parameter sequence needs to be defined:-
- The first parameter should be a data context. So we have defined the data type as 'DataContext'.
- Followed by 1 or many input parameters currently we have only one i.e. customer code so we have defined the second parameter data type as string.
- Once we are done with all input parameters we need to define the data type of the output. Currently we have defined the output data type as 'IQueryable'.
We have given a name to this delegate function as 'getCustomers'.
public static Func<DataContext, string, IQueryable<clsCustomerEntity>> getCustomers
We need to call method 'Compiled' of static class 'CompiledQuery' with the datacontext object and necessary define input parameters followed by the LINQ query. For the below snippet we have not specified the LINQ query to minimize complications.
CompiledQuery.Compile((DataContext db, string strCustCode)=> Your LINQ Query );
So now uniting the above two code snippets below is how the complete code snippet looks like.
public static Func<DataContext, string, IQueryable<clsCustomerEntity>> getCustomers= CompiledQuery.Compile((DataContext db, string strCustCode)=> Your LINQ Query );
We then need to wrap this static function in a static class. So we have taken the above defined function and wrapped that function in a static class 'clsCompiledQuery'.
public static class clsCompiledQuery
{
public static Func<DataContext, string, IQueryable<clsCustomerEntity>>
getCustomers = CompiledQuery.Compile((DataContext db, string strCustCode)
=> from objCustomer in db.GetTable<clsCustomerEntity>()
where objCustomer.CustomerCode == strCustCode
select objCustomer);
}
Consuming the compiled query is pretty simple; we just call the static function. Currently this function is returning data type as 'IEnumerable'. So we have to define an 'IEnumerable' customer entity which will be flourished through the 'getCustomers' delegate function. We can loop through the customer entity using 'clsCustomerEntity' class.
IQueryable<clsCustomerEntity> objCustomers = clsCompiledQuery.getCustomers(objContext, txtCustomerCode.Text);
foreach (clsCustomerEntity objCustomer in objCustomers)
{
Response.Write(objCustomer.CustomerName + "<br>");
}
Entity objects forms 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 its 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 for that we need to call 'SubmitChanges()' method. This method finally commits data in to the physical database.
![5.jpg]()
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 property.
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 do not 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 how 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 on 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;
LINQ had provided attribute based XML mapping. So you can have your pure .NET class like the 'clsCustomer' class shown below and you can define the LINQ mapping in a XML file. LINQ engine can then read the mapping from a XML file and apply the same to your simple .NET classes.
![6.jpg]()
public class clsCustomer
{
private int _intCustomerId;
private string _strCustomerName;
private string _strCustomerCode;
public int CustomerId
{
set
{
_intCustomerId = value;
}
get
{
return _intCustomerId;
}
}
public string CustomerName
{
set
{
_strCustomerName = value;
}
get
{
return _strCustomerName;
}
}
public string CustomerCode
{
set
{
_strCustomerCode = value;
}
get
{
return _strCustomerCode;
}
}}
We need to then create a simple XML file which defines the mapping with the class members.
<?xml version="1.0" encoding="utf-8"?>
<Database Name="TstServer" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Customer" Member="WebAppMappingXML.clsCustomer">
<Type Name="WebAppMappingXML.clsCustomer">
<Column Name="CustomerId" Member="CustomerId" />
<Column Name="CustomerName" Member="CustomerName" />
<Column Name="CustomerCode" Member="CustomerCode" />
</Type>
</Table>
</Database>
To bind the XML mapping with the simple .NET class we need to first create the XMLMappingSource object as shown in the below code snippet.
XmlMappingSource xms = XmlMappingSource.FromUrl(physicalPath + "Mapping.xml");
We need to pass the XMLMappingSource object to the datacontext class shown in the below code snippet.
DataContext objContext = new DataContext(strConn, xms);
Finally we can get the table and loop through the entity objects.
var query = from customer in objContext.GetTable<clsCustomer>()
select customer;
foreach (var item in query)
{
Response.Write(item.CustomerCode + "<br>");
} In case you have stored procedures in your project you can use 'Function' XML element to define your stored procedure name in the XML file. The client code does not change for binding the datacontext and XMLMappingsource object.
<?xml version="1.0" encoding="utf-8"?>
<Database Name="TstServer" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Customer" Member="WebAppMappingXML.clsCustomer">
<Type Name="WebAppMappingXML.clsCustomer">
<Column Name="CustomerId" Member="CustomerId" />
<Column Name="CustomerName" Member="CustomerName" />
<Column Name="CustomerCode" Member="CustomerCode" />
</Type>
</Table>
<Function Name="dbo.sp_getCustomerCode" Method="getCustomerByCode">
<Parameter Name="CustomerCode" Parameter="" />
<ElementType Name="clsCustomer" />
</Function>
</Database>
Download the Concurrency source code top of this article.
Download the Mapping XML top of this article.
Download the CRUD source code top of this article.
Download the Compiled query source code top of this article.