Table of contents
Introduction and goal
This FAQ article is dedicated to LINQ to SQL. In this article, 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. For the past several days, I have been running a small sprint to complete this marathon. I hope you will like this article.
Other LINQ FAQ
Can you show a simple LINQ to SQL example?
Let’s first start with a simple LINQ to SQL example and then try to understand how we can establish relationships in LINQ entities.
Step 1: Define Entity classes using LINQ
When we design a project using a tiered approach like a 3-tier or N-tier, we need to create business classes and objects. For instance, the code defines a class which is mapped to a Country table. You can see how the class properties are mapped in a one to one fashion with the table. These types of classes are termed as entity classes.
In LINQ, we need to first define these entity classes using attribute mappings. You need to import the System.Data.Linq.Mapping namespace to get attributes for mapping. Below is a code snippet which shows how the Table attribute maps the class with the database table Customer and how the Column attributes help in mapping properties with the table columns.
[Table(Name = "Customer")]
public class clsCustomerEntityWithProperties
{
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)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}
Below is a more sophisticated pictorial view of the entity classes mapping with the customer table structure:
Step 2: Use the DataContext to bind the table data with the entity objects
The second step is to use the data context object of LINQ to fill your entity objects. DataContext acts like a mediator between the database objects and your LINQ entity mapped classes.
So the first thing to do is to create an object of DataContext and create an active connection using the SQL connection string.
DataContext objContext = new DataContext(strConnectionString);
The next thing to do is to get the entity collection using the Table data type. This is done using the GetTable function of the DataContext.
Table<clsCustomerEntity> objTable = objContext.GetTable<clsCustomerEntity>();
Once we get all the data in the Table collection, it’s time to browse through the Table collection and display the records.
foreach (clsCustomerEntity objCustomer in objTable)
{
Response.Write(objCustomer.CustomerName + "<br>");
}
You can get the above code from the source code attached with this article.
Can we encapsulate the set and get properties for LINQ entities?
In the previous question, we had exposed the entity class properties as public properties, which violates the basic rule of encapsulation. You can define setter and getter functions which encapsulate the private properties.
[Table(Name = "Customer")]
public class clsCustomerEntityWithProperties
{
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)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}
Can we see a simple example for 1-1 and 1-many relationships?
LINQ helps you define relationships using EntitySet and EntityRef. To understand how we can define relationships using LINQ, let’s consider the below example where we have a customer who can have many addresses and every address will have phone details. In other words, the customer and address has a one-many relationship while the address and phone has a one-one relationship.
To define a one-many relationship between the customer and address classes, we need to use the EntitySet attribute. To define a one-one relationship between the address and phone classes, we need to use the EntityRef attribute.
Note: You need to define a primary key attribute for every entity class or else the mapping relationship will not work.
Below is the class entity snippet for the customer class which shows how it has used EntitySet to define a one-many relationship with the address class. The association is defined using the Association attribute. The Association attribute has three important properties: storage, thiskey, and otherkey. storage defines the name of the private variable where the address object is stored; currently, it is _CustomerAddresses. ThisKey and OtherKey define which properties will define the linkage; for this instance, it is CustomerId. In other words, both the customer class and the address class will have the CustomerId property in common. ThisKey defines the name of the property for the customer class while OtherKey defines the property of the address class.
[Table(Name = "Customer")]
public class clsCustomerWithAddresses
{
private EntitySet<clsAddresses> _CustomerAddresses;
[Association(Storage = "_CustomerAddresses",
ThisKey="CustomerId", OtherKey = "CustomerId")]
public EntitySet<clsAddresses> Addresses
{
set
{
_CustomerAddresses = value;
}
get
{
return _CustomerAddresses;
}
}
}
Below is the complete code snippet with other properties of the customer class:
[Table(Name = "Customer")]
public class clsCustomerWithAddresses
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;
private EntitySet<clsAddresses> _CustomerAddresses;
[Column(DbType="int",IsPrimaryKey=true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
[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;
}
}
[Association(Storage = "_CustomerAddresses",ThisKey="CustomerId", OtherKey = "CustomerId")]
public EntitySet<clsAddresses> Addresses
{
set
{
_CustomerAddresses = value;
}
get
{
return _CustomerAddresses;
}
}
}
To define the relationship between the address class and the phone class, we need to use the EntityRef syntax. Below is the code snippet which defines the relationship using EntityRef. All the other properties are the same except that we need to define the variable using EntityRef.
public class clsAddresses
{
private int _AddressId;
private EntityRef<clsPhone> _Phone;
[Column(DbType = "int", IsPrimaryKey = true)]
public int AddressId
{
set
{
_AddressId = value;
}
get
{
return _AddressId;
}
}
[Association(Storage = "_Phone",
ThisKey = "AddressId", OtherKey = "AddressId")]
public clsPhone Phone
{
set
{
_Phone.Entity = value;
}
get
{
return _Phone.Entity;
}
}
}
Below is the complete address class with other properties:
public class clsAddresses
{
private int _Customerid;
private int _AddressId;
private string _Address1;
private EntityRef<clsPhone> _Phone;
[Column(DbType="int")]
public int CustomerId
{
set
{
_Customerid = value;
}
get
{
return _Customerid;
}
}
[Column(DbType = "int", IsPrimaryKey = true)]
public int AddressId
{
set
{
_AddressId = value;
}
get
{
return _AddressId;
}
}
[Column(DbType = "nvarchar(50)")]
public string Address1
{
set
{
_Address1 = value;
}
get
{
return _Address1;
}
}
[Association(Storage = "_Phone",
ThisKey = "AddressId", OtherKey = "AddressId")]
public clsPhone Phone
{
set
{
_Phone.Entity = value;
}
get
{
return _Phone.Entity;
}
}
}
Here is the phone class which was aggregated with the address class:
[Table(Name = "Phone")]
public class clsPhone
{
private int _PhoneId;
private int _AddressId;
private string _MobilePhone;
private string _LandLine;
[Column(DbType = "int", IsPrimaryKey = true)]
public int PhoneId
{
set
{
_PhoneId = value;
}
get
{
return _PhoneId;
}
}
[Column(DbType = "int")]
public int AddressId
{
set
{
_PhoneId = value;
}
get
{
return _PhoneId;
}
}
[Column(DbType = "nvarchar")]
public string MobilePhone
{
set
{
_MobilePhone = value;
}
get
{
return _MobilePhone;
}
}
[Column(DbType = "nvarchar")]
public string LandLine
{
set
{
_LandLine = value;
}
get
{
return _LandLine;
}
}
}
Now finally, we need to consume this relationship in our ASPX client-behind code.
The first step is to create the DataContext object with the connection initialized.
DataContext objContext = new DataContext(strConnectionString);
The second step is to fire the query. Please note that we are just firing the query for the customer class. The LINQ engine ensures that all the child tables data is extracted and placed as per relationships defined in the entity classes.
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerWithAddresses>()
select objCustomer;
Finally, we loop through the customer, loop through the corresponding addresses object, and display phone details as per the phone object.
foreach (clsCustomerWithAddresses objCustomer in MyQuery)
{
Response.Write(objCustomer.CustomerName + "<br>");
foreach (clsAddresses objAddress in objCustomer.Addresses)
{
Response.Write("===Address:- " + objAddress.Address1 + "<br>");
Response.Write("========Mobile:- " + objAddress.Phone.MobilePhone + "<br>");
Response.Write("========LandLine:- " + objAddress.Phone.LandLine + "<br>");
}
}
The output looks something as shown below. Every customer has multiple addresses and every address has a phone object.
Can you explain how round trips happen in LINQ?
First, let’s try to understand how LINQ queries actually work and then we will see how round trips happen. Let’s consider the below database design where we have three tables: customer, addresses, and phone. There is a one-many relationship between customer and addresses, while there is one-one relationship between address and phones.
We have created three entities as per the table design: ClsCustomerWithAddresses, ClsAddresses, and ClsPhone. We have defined the relationships between them using EntitySet and EntityRef.
To fill the entity objects with data from the table is a five-step process. As a first step, the DataContext connection is created using the connection string, the LINQ query is created, and then we start browsing through customer, address, and phones.
Analyzing the LINQ SQL roundtrips
OK, now that we have analyzed that it takes five steps to execute a LINQ query, let’s try to figure out in which step the LINQ query actually fires the SQL to the database. What we will do is, we will run the above LINQ code and analyze it using SQL Profiler.
Just so that we do not catch with lots of SQL Server noise, we have only enabled RPC and SQL batch events.
Now when you run the query, you will find the below things:
- The execution of the actual SQL takes place when the
foreach statement is iterated on the LINQ objects. - The second very stunning thing you will notice is that for every entity, a separate query is fired to SQL Server. For instance, for customer, one query is fired, and then separate queries for address and phones are fired to flourish the entity object. In other words, a lot of round trips.
How can we avoid the extra roundtrips?
We can instruct the LINQ engine to load all the objects using DataLoadOptions. Below are the steps involved to enable DataLoadOptions.
The first step is to create the DataContext class:
DataContext objContext = new DataContext(strConnectionString);
The second step is to create the DataLoadOption object:
DataLoadOptions objDataLoadOption = new DataLoadOptions();
Using the LoadWith method, we need to define that we want to load customer with the address in one SQL.
objDataLoadOption.LoadWith<clsCustomerWithAddresses>(
clsCustomerWithAddresses => clsCustomerWithAddresses.Addresses);
Every address object has a phone object, so we have also defined that the phone objects should be loaded for every address object in one SQL.
objDataLoadOption.LoadWith<clsAddresses>(clsAddresses => clsAddresses.Phone);
Whatever load option you have defined, you need to set that to the DataContext object using the LoadOptions property.
objContext.LoadOptions = objDataLoadOption;
Finally, prepare you query.
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerWithAddresses>()
select objCustomer;
Start looping through the objects:
foreach (clsCustomerWithAddresses objCustomer in MyQuery)
{
Response.Write(objCustomer.CustomerName + "<br>");
foreach (clsAddresses objAddress in objCustomer.Addresses)
{
Response.Write("===Address:- " + objAddress.Address1 + "<br>");
Response.Write("========Mobile:- " + objAddress.Phone.MobilePhone + "<br>");
Response.Write("========LandLine:- " + objAddress.Phone.LandLine + "<br>");
}
}
Below is the complete source code:
DataContext objContext = new DataContext(strConnectionString);
DataLoadOptions objDataLoadOption = new DataLoadOptions();
objDataLoadOption.LoadWith<clsCustomerWithAddresses>(
clsCustomerWithAddresses => clsCustomerWithAddresses.Addresses);
objDataLoadOption.LoadWith<clsAddresses>(clsAddresses => clsAddresses.Phone);
objContext.LoadOptions = objDataLoadOption;
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerWithAddresses>()
select objCustomer;
foreach (clsCustomerWithAddresses objCustomer in MyQuery)
{
Response.Write(objCustomer.CustomerName + "<br>");
foreach (clsAddresses objAddress in objCustomer.Addresses)
{
Response.Write("===Address:- " + objAddress.Address1 + "<br>");
Response.Write("========Mobile:- " + objAddress.Phone.MobilePhone + "<br>");
Response.Write("========LandLine:- " + objAddress.Phone.LandLine + "<br>");
}
}
Abracadabra…. Now if you run the code, LINQ has executed only one SQL with proper joins as compared to three SQL for every object shown previously.
Source code
The source code for this is attached with this article. Run the project and see how the profiler shows a different SQL execution. You can first run the EntitySet example and see how SQL Profiler reacts and then run the example with DataLoadOptions. The SQL script is attached in a different file.
How can we execute Stored Procedures using LINQ?
Step 1: Create a Stored Procedure
Below is the Stored Procedure which we will be used to flourish the LINQ objects:
Create PROCEDURE dbo.usp_SelectCustomer
AS
Select CustomerId,CustomerCode,CustomerName from Customer
RETURN
Step 2: Create the LINQ entity
The above Stored Procedure returns CustomerId, CustomerCode, and CustomerName, so we need to prepare a LINQ entity as per the returning Stored Procedure data. In case you are not aware of LINQ entities, please read the basics at OneManyandOneOneLINQ.aspx.
[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)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}
Step 3: Inherit from the DataContext class
In order to execute Stored Procedures, LINQ has provided the ExecuteMethod call function which belongs to the DataContext class. This function returns an ISingleresult of an entity collection. The ExecuteMethod call function is a protected function and can only be invoked through inheritance. Methods and functions from which we call our Stored Procedures normally forms our DAL. In other words, ExecuteMethod should be a part of our DAL.
As said, the function is purely protected. You can only invoke it by inheritance and not aggregation. I am really not sure why this compulsion is put by Microsoft. In other words, we need to create an extra class which inherits from DataContext and then put in the corresponding function calls for the Stored Procedures. Below is the code snippet where we have inherited from the DataContext class and created a new DAL class called ClsMyContext.
public class clsMyContext : DataContext
{}
Step 4: Attribute using the Function attribute
We have created the GetCustomerAll function which is attributed with the Function attribute from the System.Data.Linq.Mapping namespace. The Function attribute has a name parameter which specifies the Stored Procedure name; currently, the Stored Procedure is usp_SelectCustomer as defined in the previous steps.
The IsComposable parameter defines whether this method call is for a Stored Procedure or a UDF (User Defined Function). If IsComposable is false, that means it’s a Stored Procedure, and if it is true, that means it’s a user defined function.
[Function(Name = "usp_SelectCustomer", IsComposable = false)]
public ISingleResult<clsCustomerEntity> getCustomerAll()
{
}
Step 5: Invoke the Executemethod call
Now it’s time to fill in the empty function GetCustomerAll. Below is how to execute the ExecuteMethod call. This invocation returns back an IExecuteResult object.
IExecuteResult objResult = this.ExecuteMethodCall(this,(MethodInfo)(MethodInfo.GetCurrentMethod()));
The object returned from IExecuteResult has a ReturnValue property from which we can get the results collection of ClsCustomerEntity type.
ISingleResult<clsCustomerEntity> objresults = (ISingleResult<clsCustomerEntity>) objResult.ReturnValue;
Below is the complete code snippet with the function:
[Function(Name = "usp_SelectCustomer", IsComposable = false)]
public ISingleResult<clsCustomerEntity> getCustomerAll()
{
IExecuteResult objResult =
this.ExecuteMethodCall(this,(MethodInfo)(MethodInfo.GetCurrentMethod()));
ISingleResult<clsCustomerEntity> objresults =
(ISingleResult<clsCustomerEntity>) objResult.ReturnValue;
return objresults;
}
Step 6: Finally we call the data context in the client
In the final step, we just create the context object, call our function, and loop through the object collection display data.
clsMyContext objContext = new clsMyContext(strConnectionString);
foreach(var row in objContext.getCustomerAll())
{
Response.Write(row.CustomerCode);
}
Can you explain LINQ in-memory commits and physical commits?
Entity objects form the base of 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 the final step is 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 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 the SubmitChanges() method. This method finally commits data to the physical database.
Let’s consider a customer table (customerid, customercode, and customername) and see how we can do 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 an entity of the 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 the 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 the entity object 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 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 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 the 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 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 a data context and selecting a LINQ object, both of them were explained in the previous section. To delete an object from in-memory, we need to call DeleteOnSubmit(), and to delete from the database, we need SubmitChanges().
objContext.GetTable<clsCustomerEntity>().DeleteOnSubmit(objCustomerData);
objContext.SubmitChanges();
Step 5: Self explanatory LINQ select and read
Now on to the final step, selecting and reading the LINQ object by criteria. Below is a 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;
For further reading do watch the below interview preparation videos and step by step video series.
