Apr 27, 2014

A little less than 2 months ago I had some crazy ideas for interacting with a SQL database and C# as opposed to simply using ADO.Net or Microsoft's own Entity Framework. Not sure exactly as to how I was going to implement some of the features, I shelved it until I came up with a clean way to implement it.

With this project I had four goals:
1. Same or similar syntax to Entity Framework - meaning I should be able to simply drop in my framework in place of Entity Framework with little to no changes.
2. Performance should be equal to or better in both console and WebAPI applications - covering both scenarios of desktop applications and normal for today, WebAPI Services returning results and executing SQL server side and then returning results to a client.
3. Implement my own caching syntax that puts the effort of caching on the Framework, not the user of the Framework.
4. Provide an easy way to generate strongly typed classes akin to Microsoft's Entity Framwork.
This weekend I was able to achieve #1 and to some degree #2.

I was able to achieve an identical syntax to Entity Framework like in the snippet below:

In regards to performance, I wrote 2 tests. One that simply called a stored procedure with a single insert statement and another that returned several thousand rows. To give some what real results I directly referenced the framework in a console application and then wrote a WebAPI Service referencing the framework along with a wrapper function to call the WebAPI Service from a console application.

Without further adieu here are the results running it with 10 to 1000 iterations:
[bash]
Console App Tests
JC EF 10 Iterations with average of 0.00530009
MS EF 10 Iterations with average of 0.05189771
WebAPI Tests
JC EF 10 Iterations with average of 0.18459302
MS EF 10 Iterations with average of 0.12075582
Console App Tests
JC EF 100 Iterations with average of 0.000740188
MS EF 100 Iterations with average of 0.005783375
WebAPI Tests
JC EF 100 Iterations with average of 0.018184102
MS EF 100 Iterations with average of 0.011673686
Console App Tests
JC EF 1000 Iterations with average of 0.0002790646
MS EF 1000 Iterations with average of 0.001455153
WebAPI Tests
JC EF 1000 Iterations with average of 0.0017801566
MS EF 1000 Iterations with average of 0.0011440657
[/bash]
An interesting note is the WebAPI performance differences between the console application. Sadly, with a WebAPI Service my framework is nearly twice as slow, but in console applications (presumably WinForms and WPF as well) my framework was considerably faster.

So where does that leave the future of the framework? First off, I am going to investigate further on the performance discrepencies between the two approaches. Secondly, I am going to then add in caching support with the following syntax (assuming one would want to cache a query result for 3 hours):

More to come with my framework as it progresses over the next few weeks. As far as a release schedule, once all four of my main project requirements are completed I will release a pre-release version on NuGet. I don't plan on open-sourcing the framework, but that may change further down the road. One thing is for sure, it will be freely available through NuGet.