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
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
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
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
- 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
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:
- 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).
false if order by descending is used in query, otherwise
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
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:
bucket.ItemsToTake is null if there is no
Take in the query or less it will have numeric value.
Note that I have declared
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
string is the name of the property and
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.
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
Bucket.Items in the following way:
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
null), to check if the property is used in a query
bucket.Items[propertyKey].ReleationType contains the
enum operator (
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
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
Bucket.Items[key].Name) either gives the property name or user defined name representing the entity column(by
The final task is to override the
RemovItem. These are pretty simple as the fetched object is passed by user through
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
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.
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.
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...
This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.
You might also be interested in the following related blog posts
Designer Support for One-Way navigations in Entity Framework 4
How To: Silverlight grid hierarchy load on demand using MVVM and RIA services
Migrated from Community Server to DasBlog
Free software for you! WebsiteSpark let the mountain go to Microsoft instead.
Why VBA Still Makes Sense
Ruminations on Multi-Tenant Data Architectures
Telerik Announces Support for Microsoft Silverlight 3
DotNetNuke Tips and Tricks #12: Creating your own Authentication Provider
WPF Release History : Q2 2009 (version 2009.2.701)
Please login to rate or to leave a comment.