Table of contents
Introduction and goal
This is the third series in my LINQ FAQ series. In this series, we will cover LINQ FAQs 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 a lot more: http://www.questpond.com/SampleDotNetInterviewQuestionBook.zip.
Links to LINQ FAQ Parts I and II
- LINQ FAQ Part I for newbies: This is the first part of the LINQ FAQ series which starts with what exactly LINQ is and then talks about the different LINQ query formats like group by, order by, search with criteria, etc. A must read if you are a beginner in LINQ technologies: LINQNewbie.aspx.
- 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 relationships 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: LINQFAQPart2.aspx.
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 three ways provided by LINQ to handle concurrency conflicts:
KeepCurrentValues: When this option is specified and concurrency conflicts happen, LINQ calls the LINQ entity object values as it is and does not push the new values from the database into 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, they can have many properties. Properties which are changed are kept as is but properties which are not changed are fetched from the database and replaced.
We need to use 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 the 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, use this field to detect concurrency conflicts.
Below is the code snippet which shows 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 conflicts occur?
The LINQ concurrency system lets you specify how you want conflicts to be reported. The LINQ system has two ways to report conflicts:
ContinueOnConflict: This option tells the LINQ engine to 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, the LINQ engine does not continue ahead executing the code.
Both these options can be provided as an input in the 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 compiled LINQ queries. In compiled LINQ queries, the plan is cached in a static class. As we all know, a static class is a global cache. So LINQ uses the query plan from the static class object rather than building and preparing the query plan from scratch.
Figure: LINQ query caching
In all, there are four steps which need to be performed right from the time LINQ queries are built till they are fired. By using compiled LINQ queries, these four steps are reduced to two steps.
Figure: Query plan bypasses many steps
What are the different steps involved in writing compiled LINQ queries?
The first thing is to import the Data.Linq namespace.
Import namespace
using System.Data.Linq;
The syntax to write compiled queries is a bit cryptic. So let us break that syntax in small pieces and 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 the 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 one 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 named this delegate function getCustomers.
public static Func<DataContext, string, IQueryable<clsCustomerEntity>> getCustomers
We need to call the method Compiled of the static class CompiledQuery with the DataContext object and the necessary defined 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 );
Combining 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. 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 the 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 form the base for LINQ technologies. So when any data is submitted to the database, it goes through the LINQ objects. Database operations are done through the DataContext class. As said previously, entities form the base of LINQ, so all data is sent to these entities first and then 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 then physical commits. In order to do in-memory operations, DataContext has provided the DeleteOnSubmit and InsertOnSubmit methods. When we call these methods from the DataContext class, they add and update data in the entity object's 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 the 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.
Can you show a simple CRUD example using LINQ?
Step 1: Create the entity customer class
As a first step, we create the entity of customer class as shown in the code snippet below:
[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
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 using the 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
Let’s take the next database operation, i.e., update.
Create the 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 the 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 the 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 for the 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 a data context and selecting a 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 the final database, we need 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 provides 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 an XML file. The LINQ engine can then read the mapping from an XML file and apply that 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.
="1.0"="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?
If you have Stored Procedures in your project, you can use the 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 objects.
="1.0"="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>
For further reading do watch the below interview preparation videos and step by step video series.
