In my previous article - LINQ provider basics - I have explained how LINQ to Entity work. I used examples mostly from my LINQ.Flickr project. Although creating a provider is fun, there are some repetitive tasks along the way, like expression processing and data extraction. Therefore things could be much easier with a common framework that takes care of complexes and monotonous tasks, while developers are presented with a simple model, by which they can get going with their providers without any expression overhead.

LinqExtender exposes such model, which lets the developer focus only on the application logic - not on the query internals - while creating custom home made providers. It sits between the core LINQ framework and a custom provider.

The stack looks similar to the one shown in figure 1.

Figure 1: Stack

Just a little bit of extension

Creating a custom provider on LinqExtender is easier than anything else on the planet. As I said, it takes care of all the complex expression processing. All it takes is some overridden methods, where appropriate logics need to be placed.

LinqExtender is not only for providers that work on an external service. It can also be used to create home-made LINQToSql providers. Though the original LINQToSql provider that comes with the core LINQ framework is enough to start with, it is desirable to create a custom LINQToSql provider that performs the simplest task to serve the purpose. Anyway, in the LinqExtender project I have created one provider called OpenLinqToSql, which I use to exercise the extender itself. Though simple, it lets you insert, update, and query objects and even provides server side paging with CTE (Common Table Expression).

Therefore, to give a better example, I will demonstrate how OpenLinqToSql was made on top of LinqExtender. OpenLinqToSql extends the capability of LinqExtender and it supports both standard and compact (SQLCE) database. I would also like to add that the purpose of this article is to get acquainted with LinqExtender, not to create a LinqToSql provider. But the example was chosen to show the usefulness of the extender while creating a data intensive provider.

The typical stack diagram of OpenLinqToSql is similar to the following.

Figure 2: OpenLinqToSql stack

Creating the custom provider (OpenLinqToSql)

First let's create a query object on which the query will be performed. You create it by inheriting from QueryObjectBase and overriding the IsNew property, which is used to track if the object is newly added to the collection or not, and to track the object during the add/update of the query object. As we are creating a new LinqToSql provider, it needs to be able to contain any query object. Therefore, it must be of type T. As our provider is named SqlQuery, a general way of declaring it is like so:

In this case, Book will be a query object that is the replica of a database entity. In the future, I will make a tool that will generate the objects representing the entities in the database, though hand coding the entity is not that difficult as well. For the time being, let's do it in this way.

The Book entity has ID, Author, Title, LastUpdated, ISBN columns in database and the object representation looks like so:

Note that the LinqVisibleAttribute is in the LinqExtender.Attribute namespace. This is to enable a property to be able to do processing in LinqExtender. Also, it has a public property called UseInQuery, which by default it is set to true. In any case, if we don't want to include a property in a query, we can turn it off like in the following code snippet. In this case, it will still be visible by the extender.

IdentityAttriubute is defined in OpenLinqToSql, and inherits from LinqExtender.Attribute.UniqueAttribute. It is declared in the following way:

Finally, to differentiate valued and non-valued field, that all the non-string property in the Book class are defined as Nullable. Later I will show why.

Before moving to the details of the SqlQuery class, I need to mention that creating a Query provider with LinqExtender requires three simple steps:

• Create the query class by inheriting from QueryObjectBase.
• Create the provider class by inheriting from Query, where T is the child of the QueryObjectBase class.
• Override the following methods:

Note that all of these methods are protected, which means that they are called only by the extender framework to process a request.

Now, let's dig into the provider. First, the SqlQuery class is created. As I said earlier, the query object for the SQL provider is underministic, which means that I can port it to different tables in database. Therefore, unlike the external API (Flickr), for which I know the possible objects to query on or get result from - it is not the case for user defined database objects.

Therefore, the Query provider declaration is slightly different from that of known object types:

• SqlQuery
• Custom Attribute classes.

Let's move to the overriding of the Query methods.

According to step 3, we first override the Process method. In its body, we will generate the SQL query, based on the value that is passed with the bucket object (which is filled by the extender against the query expression). Then, we run the query against the database with a DataContext class. Finally, we build the T object and add it to the IModiy<T> items collection.

First, we need to know if any order by clause is provided in the query. If not, then we will perform an order by on a unique field. (This is a requirement when we are building a WITH statement that has the Over clause, but not for normal select statement).

• IsAscending: false if order by descending is used in query, otherwise true
• FieldName: the field name on which the order by is used. It can be, for example, order by book.id descending / order by "id" descending.

In the code, bucket.UniqueItems returns the array of property names on which UniqueAttribute or a child class is used.

Next, we have to build the select query, based on the expression items.

Let's examine the portion of the if block that will generate the SQL with a WITH statement if Take is provided in the query block. Note that when Skip > 0 but no take is provided, we need to show an exception as well (as currently it is not supported). There we need to provide the following exception:

Now, bucket.ItemsToTake is null if there is no Take in the query or less it will have numeric value. Note that I have declared itemToTake as Nullable, so that a user can distinguish between valued and not valued state of the property.

The whole if-else logic for processing SQL looks like the following:

1. Building the Select statement using WITH

A typical WITH clause generated by this logic looks like so:

The first task is to get the list of property names for the T type (e.g. Book). One way to do it is by using Reflection to extract the names out of it. An easier way is to use bucket.Items to get the names, which basically is a IDictionary<string,BucketItem>, where string is the name of the property and BucketItem contains the extended information about the property and how it is used in the query expression.

Getting the property names the easy way is done like in the following code:

Then, we properly format the string with the fields that we just got.

Earliar, we have built the orderByBuilder StringBuilder, which we used here to create the OVER clause. Here, two things are possible: If any orderby is used in query, then do the orderby using the mentioned property or object value. Otherwise, by the default, use the unique property of the object.

Next, we have to append the entity that the query targets. Here, bucket.Name will give name of the object - or the user-defined name if OriginalNameAttribute is provided - that maps to the entity name.

Finally, we have to build the WHERE clause and append the final stuff of WITH. That is, select between items.

Here, CreateWhereClauseIfPossible is used to build the Where clause, which internally calls BuildClause. It basically builds the clause based on the query expressions. For that I haven't used any black arts; just iterated over Bucket.Items in the following way:

Here, bucket.Items[propertyKey].Name is the name of the property or user-defined name. Earlier in the article I have talked about declaring the properties of a query object other than string as Nullable. This is where it comes useful with if (bucket.Items[propertyKey].Value != null), to check if the property is used in a query expression. Finally, bucket.Items[propertyKey].ReleationType contains the enum operator (Equal, LessThan,etc.) that is used against the property for filling the values in the where clause of the expression. GetEquavalentSqlOperator contains some switch statements that return SQL string operator values based on the enum type.

2. General Select Statement

This is pretty simple, in contrast to the WITH statement construction.

3. Run query and Fill IModify collection.

This is done through the following call:

Inside the method we create a db context and then we execute the query:

For each row, we create a new T type object and call its FillProperty to populate each property. Here bItems.Keys (Bucket.Items.Keys) gives a list of property names and bItems[key].Name (Bucket.Items[key].Name) either gives the property name or user defined name representing the entity column(by OriginalNameAttribute).

The final task is to override the 3.b -> AddItem and 3.c - > RemovItem. These are pretty simple as the fetched object is passed by user through context.Add and context.Remove calls. All is needed is to generate the Insert and Delete statement based on T property values.

For the Insert statement, the code looks like:

For the Delete statement, the code looks like:

Going back to BuildClause, we checked whether the query was an insert or not. We did it because the same routine is reused for insert, delete and select, which generates slightly different SQL for insert statements.

That's it, we are ready to roll. The DataContext class requires a simple config entry, so in the app/web.config file we need to have the following lines:

For parsing the configuration, I have created an OpenLinqDataProviderConfiguration class, which loads the settings in the constructor of DataContext, which I left to you to explore.

Summary

We have created a sort of custom LinqToSql provider, without using any expression processing and Reflection. We also showed how LinqExtender proves to be useful in this case. It can be used for external source based providers (e.g. Flickr) in the same way. You can take a live preview of that at www.codeplex.com/linqflickr.

Also, don't forget to check out www.codeplex.com/linqextender to download OpenLinqToSql for a more in-depth look of the LinqExtender in action.

About Mehfuz Hossain

Passionate about cutting edge technologies and a .net enthusiast. He played role in variety of products starting from University automation to web 2.0 start-page (www.pageflakes.com). Currently, working at Telerik Inc (www.telerik.com), the premium rad control provider for asp.net and winforms. He i... View complete profile