Code - Highly efficient LINQ to SQL data layer using C# and SQL Server 2008 – Part 2

In the previous post we created the database objects in SQL Server as per our requirement. In this article we will be working only in Visual Studio. We will also do performance testing at the end of this post to see the effect of our approach.

1 - Create a new Class Library project and add a new LINQ to SQL Classes item as follows:

Add a connection to database and add all the entities and stored procedure into DBML designer. Eventually your DBML designer should look like this:

Now let’s pause here and let me explain few things before we move forward.

If you notice I have created a view called DomainEntView, this view is in fact not meant to be used by LINQ to SQL to fetch data, rather, this view will be used as a return type for one of the stored procedure later in the article. This is one of the techniques I use to speed up data retrieval especially when fetching data from related tables. Now if you look into the definition of stored procedure GetAllData or GetDataByID, you will notice that there is a column called LinkedData with return type of XML, this is the data from LinkedTable.


By using this technique you will have free hand to optimize your SQL statements since SQL statements are enclosed in a stored procedure and you may know already that stored procedures are interpreted only once by SQL Server, unlike dynamic SQL.


So far the concern of cluttered DBML file is concerned with the possibility of many tables in database, you can organize your tables in database according to self-defined schemas instead of letting them go in default “dbo” schema and then create a separate DBML file for each schema.


You can create schemas for a group of tables by using following SQL statement:

CREATE SCHEMA Employee

And then transfer a table to newly created schema as follows:

ALTER SCHEMA Employee TRANSFER dbo.LinkedTable

Let’s get back to where we left off.

2 – Now change the return type of the Stored Procedure to appropriate entities. E.g. change the return type of GetAllData and GetDataByID to DomainEntView as follows:

3 – Remember that GetAllData and GetDataByID returns one of the columns called LinkedData in form of XML which contains data from linked table. Let’s put the XML data returned from this column into strongly typed objects.

Note: All the entities in LINQ to SQL are defined as partial classes, which means we can extend the functionality of DomainEntView by adding a new partial class (make sure this partial class belongs to the same namespace as of the DomainEntView’s auto generated partial class) in project and code it like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;

public partial class DomainEntView
{
    private List _LinkedTables;
    public List LinkedTables
    {
        get
        {
            _LinkedTables = GetLinkedTables();
            return _LinkedTables;
        }
    }

    private List GetLinkedTables()
    {
        List linkedTables = new List();

        XDocument doc;

        if (!string.IsNullOrEmpty(this.LinkedData))
        {

            doc = XDocument.Parse(this.LinkedData, LoadOptions.None);

            linkedTables = (from item in doc.Descendants("LinkedTable")
                            select new LinkedTable()
                            {
                                ID = (int)item.Element("ID"),
                                MainTableID = (int)item.Element("MainTableID"),
                                Address = (string)item.Element("Address"),
                                EmailAddress = (string)item.Element("EmailAddress").ToString(),
                                Phone = (string)item.Element("Phone").ToString(),
                            }).ToList();
        }

        return linkedTables;
    }
}

4 – Create a test project and try to access data for MainTable and LinkedTable by LINQ to SQL traditional method and through our approach.

Alright, so all the leg-work is done let’s do some performance testing now.

I created a windows form application to test the performance difference between the two data access methods. I created two test functions to test each data access method’s performance.

Following is the code for our test methods:

Test for Traditional Approach

private void btnGetDataWithoutSP_Click(object sender, EventArgs e)
        {
            int count = 0;
            Stopwatch sw = Stopwatch.StartNew();

            tbResult.Text += Environment.NewLine + 
                             "Getting all data from MainTable Without SP." + 
                             Environment.NewLine;

            sw.Start();

            SampleDBDataContext dc = new SampleDBDataContext();

            //Following line will return only an IQueryable object
            var tabledata = dc.MainTables.ToList();
            //LINQ to SQL uses lazy loading mechanism, hence data won't 
            //be fetched until we invoke an operation on data.
            count = tabledata.Count;

            foreach (var item in tabledata)
            {
                var test = item.LinkedTables.Count;

            }

            sw.Stop();

            tbResult.Text += string.Format("Time used: {0} ms", sw.Elapsed.TotalMilliseconds);
            tbResult.Text += Environment.NewLine;
            tbResult.Text += string.Format("Time used: {0} sec", (sw.Elapsed.TotalMilliseconds / 1000));
        }

Test for Our Approach

        
 private void btnGetAllDataWithSPAndView_Click(object sender, EventArgs e)
        {
            int count = 0;
            Stopwatch sw = Stopwatch.StartNew();

            tbResult.Text += Environment.NewLine + 
                             "Getting all data from MainTable and LinkedTable through SP and utilizing DomainEntView." 
                             + Environment.NewLine;

            sw.Start();

            SampleDBDataContext dc = new SampleDBDataContext();
            //Following line will return only an IQueryable object
            var entView = dc.GetAllData().ToList(); 
            //LINQ to SQL uses lazy loading mechanism, hence data won't 
            //be fetched until we invoke an operation on data.
            count = entView.Count();

            foreach (var item in entView)
            {
                count = item.LinkedTables.Count;
            }

            sw.Stop();

            tbResult.Text += string.Format
                            ("Time used: {0} ms", sw.Elapsed.TotalMilliseconds);
            tbResult.Text += Environment.NewLine;
            tbResult.Text += string.Format
                             ("Time used: {0} sec", (sw.Elapsed.TotalMilliseconds / 1000));
        }

Results:

This test worth a thousand dollars. Isn’t it? Difference is clear!

Now let’s recap the short comings of LINQ to SQL and see how many concerns we can address now?

1. Data context or DBML file may become cluttered for a big database.


• You can use Schemas in DB to organize tables and based on schemas you can create a separate DataContext for each one of them and then create a wrapper on top of different DataContext to have a single point of access to all DataContexts.


2. Entities are directly mapped to SQL Server schemas, hence; mapping a single entity to multiple tables is not possible. 


• You can create dummy views and set those as a return type of stored procedures or you can even return only XML from your stored procedure and can have just one dummy view with only one column of type string and then populate entities on client side by inspecting XML. Similarly to update data in multiple tables all at once, could be achieved by creating a stored procedure which would take DomainEntView as a parameter in serialized XML format and then parsed this XML in SQL to update tables.


3. Data fetched by dynamic SQL statement is slower if you query on entities directly and try to fetch related data for objects in hand.


• You can use dummy views in conjunction with stored procedures and parse entities on client side if needed. This way you will get your data through a stored procedure, instead of issuing consecutive dynamic SQL statements to SQL Server. You can also use table entities as return type of your stored procedures for simple data fetch routines.

Conclusion:

You can save thousands of dollars on a mid-size project by using LINQ to SQL in a smart way.