LINQ FAQ Part3

Introduction and Goal

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.

 

Here's my small gift for all my .NET friends , a complete 400 pages FAQ Ebook which covers various .NET technologies like Azure , WCF , WWF , Silverlight , WPF , SharePoint and lot more http://www.questpond.com/SampleDotNetInterviewQuestionBook.zip  

 

Links to LINQ FAQ Part I and II 

 

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.

 

How can we handle concurrency in LINQ? 

 

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. 

 

What other features are provided by LINQ to fine tuning concurrency at field level?

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;
}
} 

What kind of error reporting options are provided by LINQ when concurrency conflict occurs?

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.
 



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); 

What are compiled queries?

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.

 

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.
 



Figure: - Query plan bypasses many steps
 

What are the different steps involved to write compiled LINQ queries ?

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>");
}

Can you explain LINQ In-memory commits and physical commits ?
 

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.  

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

Can you show a simple CRUD example using LINQ ?

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; 

How can we use XML files to map LINQ attributes to simple .NET classes?

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.
 

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>");
} 

How can we use XML files to map stored procedures with .NET classes?

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>

Source Code

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.