LINQ FAQ Part 2

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 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. For past some days I was running small sprint to complete this marathon I hope you will like this article.

I have collected around 400 FAQ questions and answers Videos in WCF, WPF, WWF, SharePoint, design patterns LINQ, Silverlight, design patterns, UML http://www.questpond.com 

Other LINQ FAQ

LINQ FAQ Part 1:- http://www.c-sharpcorner.com/UploadFile/shivprasadk/linq-faq-for-newbies/

Can you show a simple LINQ to SQL example?

So let's first start with a simple LINQ to SQL example and then we will try to understand how we can establish relationship in LINQ entities.

Step 1:- Define Entity classes using LINQ

When we design project using tiered approach like 3-tier or N-tier we need to create business classes and objects. For instance below is a simple class which defines a class which is mapped to a country table as shown below. You can see we how the class properties are mapped in 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 "System.Data.Linq.Mapping;" namespace to get attributes for mapping. Below is the code snippet which shows how the 'Table' attribute maps the class with the database table name 'Customer' and how 'Column' attributes helps mapping properties with 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 use the data context object of LINQ to fill your entity objects. Datacontext acts like a mediator between database objects and your LINQ entity mapped classes.


So the first thing is to create the object of datacontext and create a active connection using the SQL connection string.

DataContext objContext = new DataContext(strConnectionString);

The second thing 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 table collection it's time to browse through the table collection and display the record.

  foreach (clsCustomerEntity objCustomer in objTable)
        {
            Response.Write(objCustomer.CustomerName + "<br>");
        }
 
You can get the above source code which is 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 violate 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 of 1-1 and 1-many relationship example ?

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 customer and address has one-many relationship while address and phone has one-one relationship.


To define one-many relationship between customer and address classes we need to use 'EntitySet' attribute. To define one-one relationship between address and phone class we need to use 'EntityRef' attribute.

Note :- You need to define primary key attribute for every entity class or else the mapping relationship will not work.  

  So below is the class entity snippet for customer class which shows how it has used 'EntitySet' to define one-many relationship with address class. Association is defined using 'Association' attribute. 'Association' attribute has three important properties storage , thiskey and otherkey. 'storage' defines the name of private variable where the address object is stored, currently it is '_CustomerAddresses'. 'ThisKey' and 'OtherKey' defines which property will define the linkage , for this instance it is 'CustomerId'. In other words both 'Customer' class and 'Address' class will have 'CustomerId' property in common.'ThisKey' defines the name of property for customer class while 'OtherKey' define the property of addresses 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 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 relationship between address class and phone class we need to use the 'EntityRef' syntax. So below is the code snippet which defines the relationship using 'EntityRef'. All the other properties are 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 a 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;
            }
        }
    }
 
Phone class which was aggregated 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.

So the first step is to create the data context object with connection initialized.

DataContext objContext = new DataContext(strConnectionString);

Second step is fire the query. Please note we are just firing the query for customer class. LINQ engine ensures that all the child tables data is extracted and place as per relationship 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 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 3 tables customer, addresses and phone. There is one-many relationship between customer and addresses, while there is one-one relationship between address table and phones.

 

 
We have created three entities as per the table design i.e. 'ClsCustomerWithAddresses','ClsAddresses' and 'ClsPhone'. We have defined the relationships between them using 'EntitySet' and 'EntityRef'. 



To fill the entity objects with data from table is a 5 step process. As a first step the datacontext connection is created using the connection string, LINQ query is created and then we start browsing through customer, address and phones. 

 

Analyzing the LINQ SQL round trips
 
Ok, now that we have analyzed that it takes 5 steps to execute a LINQ query. So let's try to figure out on which step does the LINQ query actually fire SQL to the database. So what we will do is we will run the above LINQ code and analyze the same using SQL profiler.

Just so that we do not catch with lot 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 actual SQL takes place when the for each statement is iterated on the LINQ objects.
-> The second very stunning thing you will notice is that for every entity 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 lot of round trips.

How can we avoid the extra round trips ?

We can instruct LINQ engine to load all the objects using 'DataLoadOptions'. Below are the steps involved to enable 'DataLoadOptions'.

The first step is to create the data context class.

DataContext objContext = new DataContext(strConnectionString);
 
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 address in one SQL.

objDataLoadOption.LoadWith<clsCustomerWithAddresses>(clsCustomerWithAddresses => clsCustomerWithAddresses.Addresses);

Every address object has phone object , so we have also defined saying 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 the same to the data context object using '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 for the same.
 
       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 3 SQL for every object shown previously.
  
 

Source code
 
We have also attached a source code with this article. Run the project and see how profiler shows different SQL execution. You can first run the 'EntitySet' example and see how SQL profiler reacts for the same 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 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 . 

        [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 DataContext class
 
In order to execute stored procedures LINQ has provided 'ExecuteMethod' call function which belongs to 'DataContext' class. This function returns '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 the 'ExecuteMethod' should be a part of our DAL.

As said the function is purely protected you can only invoke the same by inheritance and not aggregation. I am really not sure why this compulsion is put by Microsoft , so in other words we need to create one more extra class which inherits from 'DataContext' and then put in the corresponding function calls for stored procedures. So below is the code snippet where we have inherited from 'DataContext' class and created a new DAL class called as 'ClsMyContext'.
 
public class clsMyContext : DataContext {}

Step 4:- Attribute using Function attribute
 
We have created 'GetCustomerAll' function which is attributed with 'Function' attribute from '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 stored procedure or UDF i.e. User defined function. If 'IsComposable' is false that means it's a stored procedure and in case it is true that means it's a user defined function.
 
   [Function(Name = "usp_SelectCustomer", IsComposable = false)]
    public ISingleResult<clsCustomerEntity> getCustomerAll() { }

Step 5:- Invoke Executemethod call
 
Ok now it's time to fill in the empty function 'GetCustomerAll'. Below is the code snippet of how to execute the 'ExecuteMethod' call. This invocation returns back 'IExecuteResult' object.
 
IExecuteResult objResult = this.ExecuteMethodCall(this, (MethodInfo)(MethodInfo.GetCurrentMethod()));

The object returned from 'IExecuteResult' has 'ReturnValue' property from which we can get 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 client
 
So at 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 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; 

 Source code
 
Download the One-Many and Many-One source code From top of this article :-
Download the LINQ code optimizing using 'DataLoadOption' From top of this article :-
Download the CRUD LINQ source code From top of this article :-
Download the Stored procedure code From top of this article:-