Tag: linq

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.

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%.

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
collections

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
        collection.
      • No, discard the whole collection from cache. Go to next
        step:
  • No. Load the collection from source (eg database) and cache
    each item in the collection separately. Then cache the
    collection.

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;
            }
            else
            {
                resultantPages.Add(individualPageInCache);
            }
        }

        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 =>
        {
            page.Detach();
            cache.Add(CacheSetup.CacheKeys.PageId(page.ID), page);
        });
        cache.Add(cacheKey, pagesOfUser);
        return pagesOfUser;
    }
    else
    {
        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>(), 
                     CacheSetup.CacheKeys.PagesOfUser(userGuid), 
                     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
scalability 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
  data

Enjoy. Don’t forget to vote for me!

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
roundtrip?

You can call this function using DeleteByPK(10,
dataContext);

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
int.

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
entity.

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

Then the primary key field is decorated with a 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:

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:

The code is available here:

http://code.msdn.microsoft.com/DeleteEntitiesLinq

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();

There’s
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 of
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
supported.”

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
query:

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
this and then continue.

So, here’s the delegate representation of the above
function:

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
queries.

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
function.

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
CompiledQuery.Compile<>,
the generic types must match and be in exactly the same order as
the Func<>
declaration.

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

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:

Calling the query is quite simple:

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

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
LoadWith<>
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
queries.