Finally! Entity Framework working in fully disconnected N-tier web app

Entity Framework was supposed to solve the problem of Linq to SQL, which requires endless hacks to make it work in n-tier world. Not only did Entity Framework solve none of the L2S problems, but also it made it even more difficult to use and hack it for n-tier scenarios. It’s somehow half way between a fully disconnected ORM and a fully connected ORM like Linq to SQL. Some useful features of Linq to SQL are gone – like automatic deferred loading. If you try to do simple select with join, insert, update, delete in a disconnected architecture, you will realize not only you need to make fundamental changes from the top layer to the very bottom layer, but also endless hacks in basic CRUD operations. I will show you in this article how I have  added custom CRUD functions on top of EF’s ObjectContext to make it finally work well in a fully disconnected N-tier web application (my open source Web 2.0 AJAX portal – Dropthings) and how I have produced a 100% unit testable fully n-tier compliant data access layerfollowing the repository pattern.

In .NET 4.0, most of the problems are solved, but not all. So, you should read this article even if you are coding in .NET 4.0. Moreover, there’s enough insight here to help you troubleshoot EF related problems.

You might think “Why bother using EF when Linq to SQL is doing good enough for me.” Linq to SQL is not going to get any innovation from Microsoft anymore. Entity Framework is the future of persistence layer in .NET framework. All the innovations are happening in EF world only, which is frustrating. There’s a big jump on EF 4.0. So, you should plan to migrate your L2S projects to EF soon.

Simple way to cache objects and collections for greater performance and scalability

Caching of frequently used data greatly increases the
scalability of your application since you can avoid repeated
queries on database, file system or to webservices. When objects
are cached, it can be retrieved from the cache which is lot faster
and more scalable than loading from database, file or web service.
However, implementing caching is tricky and monotonous when you
have to do it for many classes. Your data access layer gets a whole
lot of code that deals with caching objects and collection,
updating cache when objects change or get deleted, expire
collections when a contained object changes or gets deleted and so
on. The more code you write, the more maintenance overhead you add.
Here I will show you how you can make the caching a lot easier
using Linq to SQL and my library AspectF. It’s a
library that helps you get rid of thousands of lines of repeated
code from a medium sized project and eliminates plumbing (logging,
error handling, retrying etc) type code completely.

Here’s an example how caching significantly improves the
performance and scalabitlity of applications. Dropthings – my
open source Web 2.0 AJAX portal, without caching can only serve
about 11 request/sec with 10 concurrent users on a dual core 64 bit
PC. Here data is loaded from database as well as from external
sources. Avg page response time is 1.44 sec.

Load Test Without Cache

After implementing caching, it became significantly faster,
around 32 requests/sec. Page load time decreased
significantly as well to 0.41 sec only. During the
load test, CPU utilization was around 60%.

Load Test with in memory cache

It shows clearly the significant difference it can make to your
application. If you are suffering from poor page load performance
and high CPU or disk activity on your database and application
server, then caching Top 5 most frequently used objects in your
application will solve that problem right away. It’s a quick
win to make your application a lot faster than doing complex
re-engineering in your application.

Common approaches to caching objects and

Sometimes the caching can be simple, for example caching a
single object which does not belong to a collection and does not
have child collections that are cached separately. In such case,
you write simple code like this:

  • Is the object being requested already in cache?
    • Yes, then serve it from cache.
    • No, then load it from database and then cache it.

On the other hand, when you are dealing with cached collection
where each item in the collection is also cached separately, then
the caching logic is not so simple. For example, say you have
cached a User collection. But each User
object is also cached separately because you need to load
individual User objects frequently. Then the caching
logic gets more complicated:

  • Is the collection being requested already in cache?
    • Yes. Get the collection. For each object in the collection:
      • Is that object individually available in cache?
        • Yes, get the individual object from cache. Update it in the
        • No, discard the whole collection from cache. Go to next
    • No. Load the collection from source (eg database) and cache
      each item in the collection separately. Then cache the

You might be thinking why do we need to read each individual
item from cache and why do we need to cache each item in collection
separarely when the whole collection is already in cache? There are
two scenarios you need to address when you cache a collection and
individual items in that collection are also cached separately:

  • An individual item has been updated and the updated item is in
    cache. But the collection, which contains all those individual
    items, has not been refreshed. So, if you get the collection from
    cache and return as it is, you will get stale individual items
    inside that collection. This is why each item needs to be retrieved
    from cache separately.
  • An item in the collection may have been force expired in cache.
    For ex, something changed in the object or the object has been
    deleted. So, you expired it in cache so that on next retrieval it
    comes from database. If you load the collection from cache only,
    then the collection will contain the stale object.

If you are doing it the conventional way, you will be writing a
lot of repeated code in your data access layer. For example, say
you are loading a Page collection that belongs to a
user. If you want to cache the collection of Page for
a user as well as cache individual Page objects so
that each Page can be retrieved from Cache directly.
Then you need to write code like this:

public List<Page> GetPagesOfUserOldSchool(Guid userGuid)
    ICache cache = Services.Get<ICache>();
    bool isCacheStale = false;
    string cacheKey = CacheSetup.CacheKeys.PagesOfUser(userGuid);
    var cachedPages = cache.Get(cacheKey) as List<Page>;
    if (cachedPages != null)
        var resultantPages = new List<Page>();
        // If each item in the collection is no longer in cache, invalidate the collection
        // and load again.
        foreach (Page cachedPage in cachedPages)
            var individualPageInCache = cache.Get(CacheSetup.CacheKeys.PageId(cachedPage.ID)) as Page;
            if (null == individualPageInCache)
                // Some item is missing in cache. So, the collection is stale.
                isCacheStale = true;

        cachedPages = resultantPages;

    if (isCacheStale)
        // Collection not cached. Need to load collection from database and then cache it.
        var pagesOfUser = _database.GetList<Page, Guid>(...);
        pagesOfUser.Each(page =>
            cache.Add(CacheSetup.CacheKeys.PageId(page.ID), page);
        cache.Add(cacheKey, pagesOfUser);
        return pagesOfUser;
        return cachedPages;

Imagine writing this kind of code over and over again for each
and every entity that you want to cache. This becomes a maintenace
nightmare as your project grows.

Here’s how you could do it using AspectF:

public List<Page> GetPagesOfUser(Guid userGuid)
    return AspectF.Define
        .CacheList<Page, List<Page>>(Services.Get<ICache>(), 
page => CacheSetup.CacheKeys.PageId(page.ID)) .Return<List<Page>>(() => _database.GetList<Page, Guid>(...).Select(p => p.Detach()).ToList()); }

Instead of 42 lines of code, you can do it in 5 lines!

Read my article Simple
way to cache objects and collections for greater performance and
on CodeProject and learn:

  • Caching Linq to SQL entities
  • Handling update and delete scenarios
  • Expiring dependent objects and collections in cache
  • Handling objects that’s cached with multiple keys
  • Avoid database query optimizations when you cache sets of

Enjoy. Don’t forget to vote for me!

Web 2.0 AJAX Portal using jQuery, ASP.NET 3.5, Silverlight, Linq to SQL, WF and Unity

– my open
Web 2.0 Ajax Portal has gone through a technology
overhauling. Previously it was built using ASP.NET AJAX, a little
bit of Workflow Foundation and Linq to SQL. Now Dropthings boasts
full jQuery front-end combined with ASP.NET AJAX
UpdatePanel, Silverlight widget, full
Workflow Foundation implementation on the business
layer, 100% Linq to SQL Compiled Queries on the
data access layer, Dependency Injection and Inversion of Control
(IoC) using Microsoft Enterprise Library 4.1 and
Unity. It also has a ASP.NET AJAX Web Test
framework that makes it real easy to write Web Tests that simulates
real user actions on AJAX web pages. This article will walk you
through the challenges in getting these new technologies to work in
an ASP.NET website and how performance, scalability, extensibility
and maintainability has significantly improved by the new
technologies. Dropthings has been licensed for commercial use by
prominent companies including BT Business, Intel, Microsoft IS,
Denmark Government portal for Citizens; Startups like Limead and
many more. So, this is serious stuff! There’s a very cool
open source implementation of Dropthings framework available at
University of Singapore


Dropthings AJAX Portal

I have published a new article on this on CodeProject:

Get the source code

Latest source code is hosted at Google code:

There’s a CodePlex site for documentation and issue

You will need Visual Studio 2008 Team Suite with Service Pack 1
and Silverlight 2 SDK in order to run all the projects. If you have
only Visual Studio 2008 Professional, then you will have to remove
the Dropthings.Test project.

New features introduced

Dropthings new release has the following features:

  • Template users – you can define a user who’s pages
    and widgets are used as a template for new users. Whatever you put
    in that template user’s pages, it will be copied for every
    new user. Thus this is an easier way to define the default pages
    and widgets for new users. Similarly you can do the same for a
    registered user. The template users can be defined in the
  • Widget-to-Widget communication – Widgets can send message
    to each other. Widgets can subscribe to an Event Broker and
    exchange messages using a Pub-Sub pattern.
  • WidgetZone – you can create any number of zones in any
    shape on the page. You can have widgets laid in horizontal layout,
    you can have zones on different places on the page and so on. With
    this zone model, you are no longer limited to the Page-Column model
    where you could only have N vertical columns.
  • Role based widgets – now widgets are mapped to roles so
    that you can allow different users to see different widget list
    using ManageWidgetPersmission.aspx.
  • Role based page setup – you can define page setup for
    different roles. For ex, Managers see different pages and widgets
    than Employees.
  • Widget maximize – you can maximize a widget to take full
    screen. Handy for widgets with lots of content.
  • Free form resize – you can freely resize widgets
  • Silverlight Widgets – You can now make widgets in

Why the technology overhauling

Performance, Scalability, Maintainability and Extensibility
– four key reasons for the overhauling. Each new technology
solved one of more of these problems.

First, jQuery was used to replace my personal hand-coded large
amount of Javascript code that offered the client side drag &
drop and other UI effects. jQuery already has a rich set of library
for Drag & Drop, Animations, Event handling, cross browser
javascript framework and so on. So, using jQuery means opening the
door to thousands of jQuery plugins to be offered on Dropthings.
This made Dropthings highly extensible on the client side.
Moreover, jQuery is very light. Unlike AJAX Control Toolkit jumbo
sized framework and heavy control extenders, jQuery is very lean.
So, total javascript size decreased significantly resulting in
improved page load time. In total, the jQuery framework, AJAX basic
framework, all my stuffs are total 395KB, sweet! Performance is
key; it makes or breaks a product.

Secondly, Linq to SQL queries are replaced with Compiled
Queries. Dropthings did not survive a load test when regular lambda
expressions were used to query database. I could only reach up to
12 Req/Sec using 20 concurrent users without burning up web server
CPU on a Quad Core DELL server.

Thirdly, Workflow Foundation is used to build operations that
require multiple Data Access Classes to perform together in a
single transaction. Instead of writing large functions with many
if…else conditions, for…loops, it’s better to
write them in a Workflow because you can visually see the flow of
execution and you can reuse Activities among different Workflows.
Best of all, architects can design workflows and developers can
fill-in code inside Activities. So, I could design a complex
operations in a workflow without writing the real code inside
Activities and then ask someone else to implement each Activity. It
is like handing over a design document to developers to implement
each unit module, only that here everything is strongly typed and
verified by compiler. If you strictly follow Single Responsibility
Principle for your Activities, which is a smart way of saying one
Activity does only one and very simple task, you end up with a
highly reusable and maintainable business layer and a very clean
code that’s easily extensible.

Fourthly, Unity
Dependency Injection (DI) framework is used to pave the path for
unit testing and dependency injection. It offers Inversion of
Control (IoC), which enables testing individual classes in
isolation. Moreover, it has a handy feature to control lifetime of
objects. Instead of creating instance of commonly used classes
several times within the same request, you can make instances
thread level, which means only one instance is created per thread
and subsequent calls reuse the same instance. Are these going over
your head? No worries, continue reading, I will explain later

Fifthly, enabling API for Silverlight widgets allows more
interactive widgets to be built using Silverlight. HTML and
Javascripts still have limitations on smooth graphics and
continuous transmission of data from web server. Silverlight solves
all of these problems.

Read the article for details on how all these improvements were
done and how all these hot techs play together in a very useful
open source project for enterprises.

Don’t forget to vote for me if you like it.

Memory Leak with delegates and workflow foundation

Recently after Load Testing my open source project Dropthings, I
encountered a lot of memory leak. I found lots of Workflow
Instances and Linq Entities were left in memory and never
collected. After profiling the web application using .NET Memory Profiler, it showed the real picture:


It shows you that instances of the several types are being
created but not being removed. You see the “New” column
has positive value, but the “Remove” column has 0. That
means new instances are being created, but not removed. Basically
the way you do Memory Profiling is, you take two snapshots. Say you
take one snapshot when you first visit your website. Then you do
some action on the website that results in allocation of objects.
Then you take another snapshot. When you compare both snapshots,
you can see how many instances of classes were created between
these two snapshots and how many were removed. If they are not
equal, then you have leak. Generally in web application many
objects are created on every page hit and the end of the request,
all those objects are supposed to be released. If they are not
released, then we have a problem. But that’s the scenario for
desktop applications because in a desktop application, objects can
remain in memory until app is closed. But you should know best from
the code which objects were supposed to go out of scope and get

For beginners, leak means objects are being allocated but not
being freed because someone is holding reference to the objects.
When objects leak, they remain in memory forever, until the process
(or app domain) is closed. So, if you have a leaky website, your
website is continuously taking up memory until it runs out of
memory on the web server and thus crash. So, memory leak is a bad
– it prevents you from running your product for long duration
and requires frequent restart of app pool.

So, the above screenshot shows Workflow and Linq related classes
are not being removed, and thus leaking. This means somewhere
workflow instances are not being released and thus all workflow
related objects are remaining. You can see the number is same 48
for all workflow related objects. This is a good indication that,
almost every instance of workflow is leaked because there were
total 48 workflows created and ran. Moreover it indicates we have a
leak from a top Workflow instance level, not in some specific
Activity or somewhere deep in the code.

As the workflows use Linq stuff, they held reference to the Linq
stuffs and thus the Linq stuffs leaked as well. Sometimes you might
be looking for why A is leaking. But you actually end up finding
that since B was holding reference to A and B was leaking and thus
A was leaking as well. This is sometimes tricky to figure out and
you spend a lot of time looking at the wrong direction.

Now let me show you the buggy code:

ManualWorkflowSchedulerService manualScheduler = 
workflowRuntime.GetService<ManualWorkflowSchedulerService>(); WorkflowInstance instance = workflowRuntime.CreateWorkflow(workflowType, properties); instance.Start(); EventHandler<WorkflowCompletedEventArgs> completedHandler = null; completedHandler = delegate(object o, WorkflowCompletedEventArgs e) { if (e.WorkflowInstance.InstanceId == instance.InstanceId) // 1. instance { workflowRuntime.WorkflowCompleted -= completedHandler; // 2. terminatedhandler // copy the output parameters in the specified properties dictionary Dictionary<string,object>.Enumerator enumerator =
e.OutputParameters.GetEnumerator(); while( enumerator.MoveNext() ) { KeyValuePair<string,object> pair = enumerator.Current; if( properties.ContainsKey(pair.Key) ) { properties[pair.Key] = pair.Value; } } } }; Exception x = null; EventHandler<WorkflowTerminatedEventArgs> terminatedHandler = null; terminatedHandler = delegate(object o, WorkflowTerminatedEventArgs e) { if (e.WorkflowInstance.InstanceId == instance.InstanceId) // 3. instance { workflowRuntime.WorkflowTerminated -= terminatedHandler; // 4. completeHandler Debug.WriteLine( e.Exception ); x = e.Exception; } }; workflowRuntime.WorkflowCompleted += completedHandler; workflowRuntime.WorkflowTerminated += terminatedHandler; manualScheduler.RunWorkflow(instance.InstanceId);

Can you spot the code where it leaked?

I have numbered the lines in comment where the leak is
happening. Here the delegate is acting like a closure
and those who are from Javascript background know closure is evil.
They leak memory unless very carefully written. Here the
delegate keeps a reference to the
instance object. So, if somehow delegate
is not released, the instance will remain in memory
forever and thus leak. Now can you find a situation when the
delegate will not be released?

Say the workflow completed. It will fire the completeHandler. But the
completeHandler will not release the
terminateHandler. Thus the
terminateHandler remains in memory and it also holds
reference to the instance. So, we have a leaky
delegate leaking whatever it is holding onto outside
it’s scope. Here the only thing outside the scope if the
instance, which it is tried to access from the parent

Since the workflow instance is not released, all the properties
the workflow and all the activities inside it are holding onto
remains in memory. Most of the workflows and activities expose
public properties which are Linq Entities. Thus the Linq Entities
remain in memory. Now Linq Entities keep a reference to the
DataContext from where it is produced. Thus we have
DataContext remaining in memory. Moreover,
DataContext keeps reference to many internal objects
and metadata cacahe, so they remain in memory as well.

So, the correct code is:

ManualWorkflowSchedulerService manualScheduler = 
workflowRuntime.GetService<ManualWorkflowSchedulerService>(); WorkflowInstance instance = workflowRuntime.CreateWorkflow(workflowType, properties); instance.Start(); var instanceId = instance.InstanceId; EventHandler<WorkflowCompletedEventArgs> completedHandler = null; completedHandler = delegate(object o, WorkflowCompletedEventArgs e) { if (e.WorkflowInstance.InstanceId == instanceId) // 1. instanceId is a Guid { // copy the output parameters in the specified properties dictionary Dictionary<string,object>.Enumerator enumerator =
e.OutputParameters.GetEnumerator(); while( enumerator.MoveNext() ) { KeyValuePair<string,object> pair = enumerator.Current; if( properties.ContainsKey(pair.Key) ) { properties[pair.Key] = pair.Value; } } } }; Exception x = null; EventHandler<WorkflowTerminatedEventArgs> terminatedHandler = null; terminatedHandler = delegate(object o, WorkflowTerminatedEventArgs e) { if (e.WorkflowInstance.InstanceId == instanceId) // 2. instanceId is a Guid { x = e.Exception; Debug.WriteLine(e.Exception); } }; workflowRuntime.WorkflowCompleted += completedHandler; workflowRuntime.WorkflowTerminated += terminatedHandler; manualScheduler.RunWorkflow(instance.InstanceId); // 3. Both delegates are now released
workflowRuntime.WorkflowTerminated -= terminatedHandler; workflowRuntime.WorkflowCompleted -= completedHandler;

There are two changes – in both delegates, the
instanceId variable is passed, instead of the
instance. Since instanceId is a Guid,
which is a struct type data type, not a class, there’s no
issue of referencing. Structs are copied, not referenced. So, they
don’t leak memory. Secondly, both delegates are
released at the end of the workflow execution, thus releasing both

In Dropthings, I am using the famous CallWorkflow Activity by John Flanders, which
is widely used to execute one Workflow from another synchronously.
There’s a CallWorkflowService class which is
responsible for synchronously executing another workflow and that
has similar memory leak problem. The original code of the service
is as following:

public class CallWorkflowService : WorkflowRuntimeService
    #region Methods

    public void StartWorkflow(Type workflowType,Dictionary<string,object> inparms, 
Guid caller,IComparable qn) { WorkflowRuntime wr = this.Runtime; WorkflowInstance wi = wr.CreateWorkflow(workflowType,inparms); wi.Start(); ManualWorkflowSchedulerService ss =
wr.GetService<ManualWorkflowSchedulerService>(); if (ss != null) ss.RunWorkflow(wi.InstanceId); EventHandler<WorkflowCompletedEventArgs> d = null; d = delegate(object o, WorkflowCompletedEventArgs e) { if (e.WorkflowInstance.InstanceId ==wi.InstanceId) { wr.WorkflowCompleted -= d; WorkflowInstance c = wr.GetWorkflow(caller); c.EnqueueItem(qn, e.OutputParameters, null, null); } }; EventHandler<WorkflowTerminatedEventArgs> te = null; te = delegate(object o, WorkflowTerminatedEventArgs e) { if (e.WorkflowInstance.InstanceId == wi.InstanceId) { wr.WorkflowTerminated -= te; WorkflowInstance c = wr.GetWorkflow(caller); c.EnqueueItem(qn, new Exception("Called Workflow Terminated",
e.Exception), null, null); } }; wr.WorkflowCompleted += d; wr.WorkflowTerminated += te; } #endregion Methods }

As you see, it has that same delegate holding reference to
instance object problem. Moreover, there’s some queue stuff
there, which requires the caller and qn
parameter passed to the StartWorkflow function. So,
not a straight forward fix.

I tried to rewrite the whole CallWorkflowService so
that it does not require two delegates to be created per Workflow.
Then I took the delegates out. Thus there’s no chance of
closure holding reference to unwanted objects. The result looks
like this:

public class CallWorkflowService : WorkflowRuntimeService
    #region Fields

    private EventHandler<WorkflowCompletedEventArgs> _CompletedHandler = null;
    private EventHandler<WorkflowTerminatedEventArgs> _TerminatedHandler = null;
    private Dictionary<Guid, WorkflowInfo> _WorkflowQueue = 
new Dictionary<Guid, WorkflowInfo>(); #endregion Fields #region Methods public void StartWorkflow(Type workflowType,Dictionary<string,object> inparms,
Guid caller,IComparable qn) { WorkflowRuntime wr = this.Runtime; WorkflowInstance wi = wr.CreateWorkflow(workflowType,inparms); wi.Start(); var instanceId = wi.InstanceId; _WorkflowQueue[instanceId] = new WorkflowInfo { Caller = caller, qn = qn }; ManualWorkflowSchedulerService ss =
wr.GetService<ManualWorkflowSchedulerService>(); if (ss != null) ss.RunWorkflow(wi.InstanceId); } protected override void OnStarted() { base.OnStarted(); if (null == _CompletedHandler) { _CompletedHandler = delegate(object o, WorkflowCompletedEventArgs e) { var instanceId = e.WorkflowInstance.InstanceId; if (_WorkflowQueue.ContainsKey(instanceId)) { WorkflowInfo wf = _WorkflowQueue[instanceId]; WorkflowInstance c = this.Runtime.GetWorkflow(wf.Caller); c.EnqueueItem(wf.qn, e.OutputParameters, null, null); _WorkflowQueue.Remove(instanceId); } }; this.Runtime.WorkflowCompleted += _CompletedHandler; } if (null == _TerminatedHandler) { _TerminatedHandler = delegate(object o, WorkflowTerminatedEventArgs e) { var instanceId = e.WorkflowInstance.InstanceId; if (_WorkflowQueue.ContainsKey(instanceId)) { WorkflowInfo wf = _WorkflowQueue[instanceId]; WorkflowInstance c = this.Runtime.GetWorkflow(wf.Caller); c.EnqueueItem(wf.qn,
new Exception("Called Workflow Terminated", e.Exception),
null, null); _WorkflowQueue.Remove(instanceId); } }; this.Runtime.WorkflowTerminated += _TerminatedHandler; } } protected override void OnStopped() { _WorkflowQueue.Clear(); base.OnStopped(); } #endregion Methods #region Nested Types private struct WorkflowInfo { #region Fields public Guid Caller; public IComparable qn; #endregion Fields } #endregion Nested Types }

After fixing the problem, another Memory Profile result showed
the leak is gone:


As you see, the numbers vary, which means there’s no
consistent leak. Moreover, looking at the types that remains in
memory, they look more like metadata than instances of
classes. So, they are basically cached instances of metadata,
not instances allocated during workflow execution which are
supposed to be freed. So, we solved the memory leak!

Now you know how to write anonymous delegates without leaking
memory and how to run workflow without leaking them. Basically, the
principle theory is – if you are referencing some outside
object from an anonymous delegate, make sure that
object is not holding reference to the delegate in
some way, may be directly or may be via some child objects of its
own. Because then you have a circular reference. If possible, do
not try to access objects e.g. instance inside an
anonymous delegate that is declared outside the delegate. Try
accessing instrinsic data types like int, string, DateTime, Guid
etc which are not reference type variables. So, instead of
referencing to an object, you should declare local variables e.g.
instanceId that gets the value of properties (e.g.
instance.InstanceId) from the object and then use
those local variables inside the anonymous delegate.

Linq to SQL solve Transaction deadlock and Query timeout problem using uncommitted reads

When your database tables start accumulating thousands of rows
and many users start working on the same table concurrently, SELECT
queries on the tables start producing lock contentions and
transaction deadlocks. This is a common problem in any high volume
website. As soon as you start getting several concurrent users
hitting your website that results in SELECT queries on some large
table like aspnet_users table that are also being updated
very frequently, you end up having one of these errors:

Transaction (Process ID ##) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim.
Rerun the transaction.


Timeout Expired. The Timeout Period Elapsed Prior To Completion
Of The Operation Or The Server Is Not Responding.

The solution to these problems are – use proper index on
the table and use transaction isolation level Read
or WITH (NOLOCK) in your SELECT queries. So,
if you had a query like this:

SELECT * FORM aspnet_users
where ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

You should end up having any of the above errors under high
load. There are two ways to solve this:

SELECT * FROM aspnet_Users
WHERE ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

Or use the WITH (NOLOCK):

WHERE ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

The reason for the errors are that since aspnet_users is
a high read and high write table, during read, the table is
partially locked and during write, it is also locked. So, when the
locks overlap on each other from several queries and especially
when there’s a query that’s trying to read a large
number of rows and thus locking large number of rows, some of the
queries either timeout or produce deadlocks.

Linq to Sql does not produce queries with the WITH
option nor does it use READ UNCOMMITTED. So, if
you are using Linq to SQL queries, you are going to end up with any
of these problems on production pretty soon when your site becomes
highly popular.

For example, here’s a very simple query:

using (var db = new DropthingsDataContext()) { var user = db.aspnet_Users.First(); var pages = user.Pages.ToList(); }

DropthingsDataContext is a DataContext built from Dropthings database.

When you attach SQL Profiler, you get this:

You see none of the queries have READ UNCOMMITTED or WITH


The fix is to do this:

using (var db = new DropthingsDataContext2()) { db.Connection.Open(); db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"); var user = db.aspnet_Users.First(); var pages = user.Pages.ToList(); }

This will result in the following profiler output

As you see, both queries execute within the same connection and
the isolation level is set before the queries execute. So, both
queries enjoy the isolation level.

Now there’s a catch, the connection does not close. This
seems to be a bug in the DataContext that when it is disposed, it
does not dispose the connection it is holding onto.

In order to solve this, I have made a child class of the
DropthingsDataContext named DropthingsDataContext2
which overrides the Dispose method and closes the

   class DropthingsDataContext2 : DropthingsDataContext, IDisposable { public new void Dispose() { if (base.Connection != null) if (base.Connection.State != System.Data.ConnectionState.Closed) { base.Connection.Close(); base.Connection.Dispose(); } base.Dispose(); } }

This solved the connection problem.

There you have it, no more transaction deadlock or lock
contention from Linq to SQL queries. But remember, this is only to
eliminate such problems when your database already has the right
indexes. If you do not have the proper index, then you will end up
having lock contention and query timeouts anyway.

There’s one more catch, READ UNCOMMITTED will return rows
from transactions that have not completed yet. So, you might be
reading rows from transactions that will rollback. Since
that’s generally an exceptional scenario, you are more or
less safe with uncommitted read, but not for financial applications
where transaction rollback is a common scenario. In such case, go
for committed read or repeatable read.

There’s another way you can achieve the same, which seems
to work, that is using .NET Transactions. Here’s the code

using (var transaction = new TransactionScope( TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted, Timeout = TimeSpan.FromSeconds(30) })) { using (var db = new DropthingsDataContext()) { var user = db.aspnet_Users.First(); var pages = user.Pages.ToList(); transaction.Complete(); } }

Profiler shows a transaction begins and ends:

The downside is it wraps your calls in a transaction. So, you

are unnecessarily creating transactions even for SELECT operations.
When you do this hundred times per second on a web application,
it’s a significant over head.

Some really good examples of deadlocks are given in this

I highly recommend it.


Linq to SQL: Delete an entity using Primary Key only

Linq to Sql does not come with a function like .Delete(ID) which allows you to
delete an entity using it’s primary key. You have to first
get the object that you want to delete and then call .DeleteOnSubmit(obj) to queue
it for delete. Then you have to call DataContext.SubmitChanges() to
play the delete queries on database. So, how to delete object
without getting them from database and avoid database

Delete an object without getting it - Linq to Sql

You can call this function using DeleteByPK(10,

First type is the entity type and second one is the type of the
primary key. If your object’s primary key is a Guid field, specify
Guid instead of

How it works:

  • It figures out the table name and the primary key field name
    from the entity
  • Then it uses the table name and primary key field name to build
    a DELETE query

Figuring out the table name and primary key field name is a bit
hard. There’s some reflection involved. The GetTableDef()
returns the table name and primary key field name for an

Every Linq Entity class is decorated with a Table attribute that has the
table name:

Lint entity declaration

Then the primary key field is decorated with a Column attribute with
IsPrimaryKey =

Primary Key field has Column attribute with IsPrimaryKey = true

So, using reflection we can figure out the table name and the
primary key property and the field name.

Here’s the code that does it:

Using reflection find the Table attribute and the Column attribute

Before you scream “Reflection is SLOW!!!!” the
definition is cached. So, reflection is used only once per
appDomain per entity. Subsequent call is just a dictionary lookup
away, which is as fast as it can get.

You can also delete a collection of object without ever getting
any one of them. The the following function to delete a whole bunch
of objects:

Delete a list of objects using Linq to SQL

The code is available here:

kick it on

Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

If you are using Linq to SQL, instead of writing regular Linq
Queries, you should be using
Compiled Queries
. if you are building an ASP.NET web
application that’s going to get thousands of hits per hour,
the execution overhead of Linq queries is going to consume too much
CPU and make your site slow. There’s a runtime cost
associated with each and every Linq Query you write. The queries
are parsed and converted to a nice SQL Statement on *every* hit.
It’s not done at compile time because there’s no way to
figure out what you might be sending as the parameters in the
queries during runtime. So, if you have common Linq to Sql
statements like the following one throughout your growing web
application, you are soon going to have scalability nightmares:

var query = from widget in dc.Widgets
where widget.ID == id && widget.PageID == pageId
select widget;

var widget = query.SingleOrDefault();

a nice blog post by JD Conley
that shows how evil Linq to Sql
queries are:


You see how many times SqlVisitor.Visit is called to
convert a Linq Query to its SQL representation? The runtime cost to
convert a Linq query to its SQL Command representation is just way
too high.

Rico Mariani has a very informative performance comparison
regular Linq queries vs Compiled Linq queries performance:


Compiled Query wins on every case.

So, now you know about the benefits of compiled queries. If you
are building ASP.NET web application that is going to get high
traffic and you have a lot of Linq to Sql queries throughout your
project, you have to go for compiled queries. Compiled Queries are
built for this specific scenario.

In this article, I will show you some steps to convert regular
Linq to Sql queries to their Compiled representation and how to
avoid the dreaded exception “Compiled queries across
DataContexts with different LoadOptions not

Here are some step by step instruction on converting a Linq to
Sql query to its compiled form:

First we need to find out all the external decision factors in a
query. It mostly means parameters in the WHERE clause. Say, we are
trying to get a user from aspnet_users table using
Username and Application ID:

Here, we have two external decision factor – one is the
Username and another is the Application ID. So, first think this
way, if you were to wrap this query in a function that will just
return this query as it is, what would you do? You would create a
function that takes the DataContext (dc named here),
then two parameters named userName and applicationID, right?

So, be it. We create one function that returns just this

Converting a LInq Query to a function

Next step is to replace this function with a Func<> representation
that returns the query. This is the hard part. If you haven’t
dealt with Func<> and Lambda
expression before, then I suggest you read this
and then continue.

So, here’s the delegate representation of the above

Creating a delegate out of Linq Query

Couple of things to note here. I have declared the delegate as
static readonly
because a compiled query is declared only once and reused by all
threads. If you don’t declare Compiled Queries as static,
then you don’t get the performance gain because compiling
queries everytime when needed is even worse than regular Linq

Then there’s the complex Func> thing. Basically the
generic Func<> is declared to
have three parameters from the GetQuery function and a return
type of IQueryable.
Here the parameter types are specified so that the delegate is
created strongly typed. Func<> allows up to 4
parameters and 1 return type.

Next comes the real business, compiling the query. Now that we
have the query in delegate form, we can pass this to CompiledQuery.Compile function
which compiles the delegate and returns a handle to us. Instead of
directly assigning the lambda expression to the func, we will pass
the expression through the CompiledQuery.Compile

Converting a Linq Query to Compiled Query

Here’s where head starts to spin. This is so hard to read
and maintain. Bear with me. I just wrapped the lambda expression on
the right side inside the CompiledQuery.Compile function.
Basically that’s the only change. Also, when you call
the generic types must match and be in exactly the same order as
the Func<>

Fortunately, calling a compiled query is as simple as calling a

Running Compiled Query

There you have it, a lot faster Linq Query execution. The hard
work of converting all your queries into Compiled Query pays off
when you see the performance difference.

Now, there are some challenges to Compiled Queries. Most common
one is, what do you do when you have more than 4 parameters to
supply to a Compiled Query? You can’t declare a Func<> with more than 4
types. Solution is to use a struct to encapsulate all the
parameters. Here’s an example:

Using struct in compiled query as parameter

Calling the query is quite simple:

Calling compiled query with struct parameter

Now to the dreaded challenge of using LoadOptions with Compiled
Query. You will notice that the following code results in an

Using DataLoadOptions with Compiled Query

The above DataLoadOption runs perfectly
when you use regular Linq Queries. But it does not work with
compiled queries. When you run this code and the query hits the
second time, it produces an exception:

Compiled queries across DataContexts with different
LoadOptions not supported

A compiled query remembers the DataLoadOption once its called.
It does not allow executing the same compiled query with a
different DataLoadOption again. Although
you are creating the same DataLoadOption with the same
calls, it still produces exception because it remembers the exact
instance that was used when the compiled query was called for the
first time. Since next call creates a new instance of DataLoadOptions, it does not
match and the exception is thrown. You can read details about the
problem in
this forum post

The solution is to use a static DataLoadOption. You cannot
create a local DataLoadOption instance and use
in compiled queries. It needs to be static. Here’s how you
can do it:


Basically the idea is to construct a static instance of DataLoadOptions using a static
function. As writing function for every single DataLoadOptions combination is
painful, I created a static delegate here and executed it right on
the declaration line. This is in interesting way to declare a
variable that requires more than one statement to prepare it.

Using this option is very simple:


Now you can use DataLoadOptions with compiled

kick it on

My first book – Building a Web 2.0 Portal with ASP.NET 3.5

My first book “Building a Web 2.0 Portal with ASP.NET 3.5” from
O’Reilly is published and available in the stores. This book
explains in detail the architecture design, development, test,
deployment, performance and scalability challenges of my open
source web portal Dropthings is a prototype of a web
portal similar to iGoogle or Pageflakes. But this portal is developed using
recently released brand new technologies like ASP.NET 3.5, C# 3.0,
Linq to Sql, Linq to XML, and Windows Workflow foundation. It makes
heavy use of ASP.NET AJAX 1.0. Throughout my career I have built
several state-of-the-art personal, educational, enterprise and mass consumer web
. This book collects my experience in building all of
those portals.

O’Reilly Website:


Disclaimer: This book does not show you how to build Pageflakes.
Dropthings is entirely different in terms of architecture,
implementation and the technologies involved.

You learn how to:

  • Implement a highly decoupled architecture following the popular
    n-tier, widget-based application model
  • Provide drag-and-drop functionality, and use ASP.NET 3.5 to
    build the server-side part of the web layer
  • Use LINQ to build the data access layer, and Windows Workflow
    Foundation to build the business layer as a collection of
  • Build client-side widgets using JavaScript for faster
    performance and better caching
  • Get maximum performance out of the ASP.NET AJAX Framework for
    faster, more dynamic, and scalable sites
  • Build a custom web service call handler to overcome
    shortcomings in ASP.NET AJAX 1.0 for asynchronous, transactional,
    cache-friendly web services
  • Overcome JavaScript performance problems, and help the user
    interface load faster and be more responsive
  • Solve various scalability and security problems as your site
    grows from hundreds to millions of users
  • Deploy and run a high-volume production site while solving
    software, hardware, hosting, and Internet infrastructure

If you’re ready to build state-of-the art, high-volume web
applications that can withstand millions of hits per day, this book
has exactly what you need.

Linq to SQL: How to Attach object to a different data context

After upgrading to Visual Studio 2008 RTM, you will have trouble
updating Linq to SQL Classes which are read from one data context
and then updated into another data context. You will get this
exception during update:

System.NotSupportedException: An attempt has been made to
Attach or Add an entity that is not new, perhaps having been loaded
from another DataContext. This is not supported.

Here’s a typical example taken from a “”>
Forum post:

   1: public static void UpdateEmployee(Employee employee)
   2: {
   3:   using (HRDataContext dataContext =new HRDataContext())
   4:   {
   5:     //Get original employee
   6:     Employee originalEmployee = dataContext.Employees.Single(
   8:     //attach to datacontext
   9:     dataContext.Employees.Attach(employee, originalEmployee);
  11:     //save changes
  12:     dataContext.SubmitChanges();
  14:   }
  15: }

When you call the Attach function, you will get the exception
mentioned above.

Here’s a way to do this. First, create a partial class that adds
a Detach method to Employee class. This method will
detach the object from it’s data context and detach associated

   1: public partial class Employee
   2: {
   3:   public void Detach()
   4:   {
   5:     this.PropertyChanged = null; this.PropertyChanging = null;
   7:     // Assuming there's a foreign
key from Employee to Boss
   8:     this.Boss = default(EntityRef<Boss>);
   9:     // Similarly set child objects to default as well
  11:     this.Subordinates = default(EntitySet<Subordinate>);
  12:   }
  13: }
Now during update, call Detach before attaching the

object to a DataContext.

   1: public static void UpdateEmployee(Employee employee)
   2: {
   3:     using (HRDataContext dataContext = new HRDataContext())
   4:     {
   5:         //attach to datacontext
   6:         employee.Detach();
   9:         //save changes
  12:     }
  13: }
This'll work. It assumes the employee object already has its

primary key populated.

You might see during update, it’s generating a bloated UPDATE
statement where each and every property is appearing on the WHERE
clause. In that case, set UpdateCheck to Never for
all properties of Employee class from the Object Relational