LINQ-to-SQL is a new programming model for data access layer in managed .net environment. you may also consider it as another ORM tool if you want to.LINQ-to-SQL is a LINQ-based framework that allows users to query database using object model build using designer tools available in visual studio 2008 or using command line tool called Sqlmetal.exe.
Generated database driven object model resides in LINQ-to-SQL class file ending with a .dbml extension.All data types that implement IQueryable or its generic version IQueryable can be employed as data sources in a LINQ query.
DataContext:
Another important thing is to know in LINQ-to-SQL world is Datacontext object. Simply put, DataContext object manages the database connection and holds connection strings. It is closely associated with underlying LINQ-to-SQL domain model and provides single point entry to LINQ-to-SQL data model.
You need an instance of datacontext everytime you need to query or update database. DataContext object is light-weight and extensible in nature. Usually, what I know is to create DataContext object for every unit of work. Of course, we can also maintain a global context class for all database calls. DataContext class is also extensible using partial classes for various scenarios like returning multiple result sets.
By now, we might understand the importance of DataContext class for database operations. So, Let's peek into few properties which're worth looking into
ObjectTrackingEnabled:
As already told, DataContext is self-contained box which maintains identity and tracking information for all the entities it holds. But question is do we need the tracking or identity information all the times? Nopes.. If you're dealing with read-only data or just retrieving data for display purposes then we don't need burden of tracking information which's quite useful for editable data. So, just switchoff tracking in DataContext object. How do we do that?
Just set ObjectTrackingEnabled property of DataContext object to false. This will avoid unnecessary identity management of entities and also makes Data Context object light weight.
using (NorthwindDataContext context = new NorthwindDataContext())
{
context.ObjectTrackingEnabled = false;
}
Optimistic Concurrency Control:
LINQ to SQL comes with out of the box Optimistic concurrency support mapping a binary type for all SQL Time stamp columns in database. In some cases like low volume environments where we don't care much about concurrency control or we may employ our own custom concurrency control independent of LINQ-to-SQL optimistic concurrency control techniques, then it would be a gain if you turn off Optimistic Concurrency property in mapping file (.dbml file) which does the invalidation of insert/update data.
Set UpdateCheck property of DataContext object to Enumeration value of Never to turn optimistic concurrency off in LINQ to SQL.
Here is an example of turning optimistic concurrency off implemented as attribute level
mapping:
[Column(Storage=“_city”, DbType=“string”,
UpdateCheck=UpdateCheck.Never)]
public string city
{
get
{ return this._city; }
}
Persistance Ignorance:
LINQ-to-SQL(LTS) allows to generate POCO (Plain old CLR objects) with an acceptable level of persistence ignorance by setting the DataContext class’s Access property value to Internal.
Generated SQL queries:
Wanna see SQL statements generated by LTS? There is a possibility that you may not be aware of additional columns or extra data that is retrieved behind the scenes. Use Data Context’s Log property to be able to see what SQL statements are being run by the Data Context. Quite useful to analyze SQL statements generated by LINQ-to-SQL.
using (NorthwindDataContext context = new NorthwindDataContext())
{
context.Log = Console.Out;
}
Similarly, to avoid logging of SQL statements in production environments, set DataContext.Log to null.
DataContext.CommandTimeOut:
The default value of the DataContext class's CommandTimeout is set to 30 seconds. Any database queries taking a longer time to complete than 30 seconds will throw a Timeout Exception.
One solution to this problem is to set the value of the CommandTimeout before each time a LINQ to SQL DataContext object is created and such a query is invoked. But the problem with this approach is, it will introduce code duplication. To do it efficiently, Fortunately enough, the Visual Studio 2008 auto generated DataContext subclasses provide an easy way to achieve this target using extensibility through partial methods.
Data filtering:
When we retrieve data with Load or LoadWith methods, we assume that we want to retrieve all the associated data those are bound with the primary key. But in most cases we likely need additional filtering to this. Here is where DataLoadOptions.AssociateWith generic method comes very handy. This method takes the criteria to load the data as a parameter and applies it to the query – so you get only the data that you need.
using (NorthwindDataContext context = new NorthwindDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.AssociateWith(x=> x.streets.Where(y => y.michiganave)); context.LoadOptions = options;
}
DataContext.Refresh:
To reload entity object, Data Context object provides Refresh method to invalidate the entity if it's changed.
Generated database driven object model resides in LINQ-to-SQL class file ending with a .dbml extension.All data types that implement IQueryable or its generic version IQueryable can be employed as data sources in a LINQ query.
DataContext:
Another important thing is to know in LINQ-to-SQL world is Datacontext object. Simply put, DataContext object manages the database connection and holds connection strings. It is closely associated with underlying LINQ-to-SQL domain model and provides single point entry to LINQ-to-SQL data model.
You need an instance of datacontext everytime you need to query or update database. DataContext object is light-weight and extensible in nature. Usually, what I know is to create DataContext object for every unit of work. Of course, we can also maintain a global context class for all database calls. DataContext class is also extensible using partial classes for various scenarios like returning multiple result sets.
By now, we might understand the importance of DataContext class for database operations. So, Let's peek into few properties which're worth looking into
ObjectTrackingEnabled:
As already told, DataContext is self-contained box which maintains identity and tracking information for all the entities it holds. But question is do we need the tracking or identity information all the times? Nopes.. If you're dealing with read-only data or just retrieving data for display purposes then we don't need burden of tracking information which's quite useful for editable data. So, just switchoff tracking in DataContext object. How do we do that?
Just set ObjectTrackingEnabled property of DataContext object to false. This will avoid unnecessary identity management of entities and also makes Data Context object light weight.
using (NorthwindDataContext context = new NorthwindDataContext())
{
context.ObjectTrackingEnabled = false;
}
Optimistic Concurrency Control:
LINQ to SQL comes with out of the box Optimistic concurrency support mapping a binary type for all SQL Time stamp columns in database. In some cases like low volume environments where we don't care much about concurrency control or we may employ our own custom concurrency control independent of LINQ-to-SQL optimistic concurrency control techniques, then it would be a gain if you turn off Optimistic Concurrency property in mapping file (.dbml file) which does the invalidation of insert/update data.
Set UpdateCheck property of DataContext object to Enumeration value of Never to turn optimistic concurrency off in LINQ to SQL.
Here is an example of turning optimistic concurrency off implemented as attribute level
mapping:
[Column(Storage=“_city”, DbType=“string”,
UpdateCheck=UpdateCheck.Never)]
public string city
{
get
{ return this._city; }
}
Persistance Ignorance:
LINQ-to-SQL(LTS) allows to generate POCO (Plain old CLR objects) with an acceptable level of persistence ignorance by setting the DataContext class’s Access property value to Internal.
Generated SQL queries:
Wanna see SQL statements generated by LTS? There is a possibility that you may not be aware of additional columns or extra data that is retrieved behind the scenes. Use Data Context’s Log property to be able to see what SQL statements are being run by the Data Context. Quite useful to analyze SQL statements generated by LINQ-to-SQL.
using (NorthwindDataContext context = new NorthwindDataContext())
{
context.Log = Console.Out;
}
Similarly, to avoid logging of SQL statements in production environments, set DataContext.Log to null.
DataContext.CommandTimeOut:
The default value of the DataContext class's CommandTimeout is set to 30 seconds. Any database queries taking a longer time to complete than 30 seconds will throw a Timeout Exception.
One solution to this problem is to set the value of the CommandTimeout before each time a LINQ to SQL DataContext object is created and such a query is invoked. But the problem with this approach is, it will introduce code duplication. To do it efficiently, Fortunately enough, the Visual Studio 2008 auto generated DataContext subclasses provide an easy way to achieve this target using extensibility through partial methods.
Data filtering:
When we retrieve data with Load or LoadWith methods, we assume that we want to retrieve all the associated data those are bound with the primary key. But in most cases we likely need additional filtering to this. Here is where DataLoadOptions.AssociateWith generic method comes very handy. This method takes the criteria to load the data as a parameter and applies it to the query – so you get only the data that you need.
using (NorthwindDataContext context = new NorthwindDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.AssociateWith
}
DataContext.Refresh:
To reload entity object, Data Context object provides Refresh method to invalidate the entity if it's changed.