LINQ to SQL best prac

http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20Best%20Practice.aspx

LINQ to SQL Best Pratices
1. Always try to have only one call to SubmitChanges. This will not only ensure that all the database operation are performed in one go, but will also maintain the Transaction of the all the operations performed.


2. While retrieving data from the database make sure to explicitly mention the column names that you want to fetch instead of selecting all the data. This will reduce the amount of data flow and increase the performance of your application.
If I want only Name and City from a table having say 10 column use


var result = from t in db.Countries
select new
{
Name = t.Name,
ID = t.CountryID
}
Instead of
var result = from t in db.Countries
select t;


3. Make sure to set the Delay Loaded property for the column, which carry large amount of data, to False. This will ensure that data is not loaded into the memory till required.

4. If you are dealing with a very complicated query, it’s always advisable to write a store procedure for the same and call the same through LINQ. Idea behind is, when we go on to write such a complicated query using LINQ, we often land up write query which has poor performance and involves multiple call to database.


5. LINQ work in deferred way, meaning the connection to database will not be open till we start iterating through the result and will not close the connection till this iteration is complete.
Hence if you are iterating through the loop to get the result make sure you don’t perform other lengthy operation inside the loop.(As connection will remain open till the loop ends)


6. If it required to do some lengthy processing it always better to call .ToList() or .ToArray() method which query the data immediately and return the strong typed collection which can be further used.


7. Do not dump all your database table and Store Procedure into single class. As often there are number of Read only tables, tables that maintain the log information and not used extensively. These tables will unnecessary consume space in the memory and increase the identity management cost.


8. It’s always preferred to create two data context class. One that will have all the ready only data and other to store all the transactional data. By doing so we can increase the performance.
For the data context that has ready only table we can turn of the property ObjectTrackingEnabled. This will ensure that unnecessary Identity management is avoided.

DataClasses1DataContext datacontext = new DataClasses1DataContext();
datacontext.ObjectTrackingEnabled = false;


9. Turn off the Optimisting Concurrency unless you really need it. If application is desinged in a way to make sure that always the last update wills, there is no gain in doing an extra level of check.[Column(Storage="_Name", DbType="NVarChar(50) NOT NULL", CanBeNull=false, UpdateCheck=UpdateCheck.Never)]
public string Name
{
get
{
return this._Name;
}
set
{
if ((this._Name != value))
{
this.OnNameChanging(value);
this.SendPropertyChanging();
this._Name = value;
this.SendPropertyChanged("Name");
this.OnNameChanged();
}
}
}


With respect to point 8 one can always set the attribute UpdateCheck= UpdateCheck.Never for all the columns of Ready only table.


10. It’s always a good idea to constantly monitor the query generated from the LINQ query that we write. This not only help one understand the behavior of LINQ in a better way but also helps to optimize the query. Its more about a standard process that one needs to follow in the initial stage of learning. Afterward it becomes a habit and one starts writing more optimized form of query.


11. Use CompiledQuery.Compile to create a compiled version of query if you know that the query is going to get executed more than once. I say this because when query is generated for the first time, it takes more time than time required to generated normal query. There is a separate sections that deals in detail about the CompiledQuery.


12. Use DataLoadOption when you want to retrieve all the data associated with given table. i.e. If you have a table Student and its has a mapping with Subject table, it always good to use DataLoadOption to pre fetch all the data beforehand. This will avoid multiple calls to database.
Ex.
DataLoadOptions options = new DataLoadOptions();
options.LoadWith(s => s.Subject);


context.LoadOptions = options;


IEnumerable customers = context.Student.ToList();


13. Further if you are aware that you need only part of the related data use DataLoadOption.AssociateWith. using this generic method once can easily filter out and get the required data.
Ex.
DataLoadOptions option = new DataLoadOptions();
option.AssociateWith(s => s.Subject.Where(sb => sb.Id = 3));


14. As by now we already know that LINQ has some performance overhead. Keeping this is mind its always better to fetch only required amount of data. Real time scenario is, while implementing paging in GridView make sure to fetch data that is equal to page size. Take() and Skip() method comes very handy in achieving this.