//Wednesday 3 September 2008

Comparing Linq with SubSonic

After seeing Subsonic in action in the dashCommerce project (Open Source ASP.NET eCommerce application) I have used it in a couple of projects and I like it. It's easy to get up and running, has an intuitive object model and a handy Scaffold component for getting a data admin interface put together quickly. It stands up well compared to other .NET DALs I have used in the past e.g. NEO which I found less intuitive to configure and update.
Recently I've started looking at Linq to see how it compares to Subsonic (and other .NET DALs). From what I've seen so far it looks good though I find it less intuitive than SubSonic. The query syntax is good and is easy to understand for anyone familiar with SQL but I think the concepts of how and when the data is loaded is less obvious with Linq when compared with Subsonic.
SubSonic feels like an object model I would have written myself from scratch. This might just be a case of me getting used to Linq though. To start getting to grips with Linq I thought I would generate a data context for the dashCommerce project and try writing some queries that correspond to existing Subsonic methods.
Examples
Here are some examples of how some calls to the dashCommerce database look in Subsonic with a corresponding Linq version.
Fetch all products
Subsonic Call
ProductController pc = new ProductController();
ProductCollection pcol = pc.FetchAll();
Linq Equivalent
DashContext rc = new DashContext(connStr);
var q = from p in rc.Products               
        select p;
List<Product> productList = q.ToList();
Fetch specific product by id
Subsonic Call
ProductController pc = new ProductController();
ProductCollection pcol = pc.FetchByID(5);
Product p = pcol[0];
Linq Equivalent
DashContext rc = new DashContext(connStr);
var q = from p in rc.Products        
        where p.ProductId == 5
        select p;
List<Product> productList = q.ToList();
Product p = productList[0];
Search for products by name
Subsonic Call
ProductController pc = new ProductController();
Query q = new Query(Product.Schema.TableName);
q.AddWhere("Name", Comparison.Like, "%sensor%");
ProductCollection pcol = pc.FetchByQuery(q);
Linq Equivalent
DashContext rc = new DashContext(connStr);
var q = from p in rc.Products        
        where p.Name.Contains("sensor")
        select p;
List<Product> productList = q.ToList();
Performance comparison
Inspired by this forum post: http://dashcommerce.org/forums/p/656/2194.aspx I thought I'd try comparing the performance of Linq with that of Subsonic. This isn't a particularly scientific test but I put each of the calls described above in a loop with 1000 iterations to see if there were any major differences in performance.
Fetch all products
Linq:     00:00:04.1
Subsonic: 00:00:07.2
Fetch specific product by id
Linq:     00:00:00.09
Subsonic: 00:00:01.1
Search for products by name (LIKE query)
Linq:     00:00:04.2
Subsonic: 00:00:01.6
In the first two queries Linq outperformed Subsonic but in the case of the LIKE query it was slower. I am guessing this is something to do with the way Linq translates the query into SQL as the final SQL that is executed is the same.
I decided to try the final test again using a Linq Compiled Query as follows:
    public static class Queries
    {
        public static Func<RetrofitContext, string, IQueryable<Product>> SearchProductsByName
            = CompiledQuery.Compile((RetrofitContext rc, string name) => from p in rc.Products where p.Name.Contains(name) select p);     
    }

    RetrofitContext rc = new RetrofitContext(connStr);
    for (int i = 1; i < 1001; i++)
    {
       // Now use a CompiledQuery
       List<Product> productList = Queries.SearchProductsByName(rc, "sensor").ToList();
    }
This time Linq outperformed Subsonic (slightly) with a time of 00:00:01.3.
For reference the products table in this test contained 39 records, there were 3 records that matched the LIKE query (i.e. have the word sensor in the name). I was using my local SQL Server 2005 Express Edition (SP2).
Conclusion
In (my not particularly stringent) performance testing Linq seems to stack up well against Subsonic, my current DAL of choice. I will continue to investigate the features of Linq and post any hints, tips or gripes here. I'm also going to look at ASP.NET Dynamic Data to see whether this will allow me to replicate the simple data admin interface provided by the Subsonic Scaffold control.

SyntaxHighlighter