author/Mehfuz Hossain  from/dotnetslackers.com

Mehfuz shows how to create a custom LINQ provider using the open source project LINQExtender.

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.

感谢原创者的辛勤劳动，希望对您有所帮助，转载请注明原出处。

您可能对 [Linq] 的这些文章也感兴趣： LINQ体验(15)——LINQ to SQL语句之用户定义函数 LINQ体验(18)——LINQ to SQL语句之视图和继承支持 LINQ体验系列文章 Creating custom LINQ provider using LinqExtender LINQ体验(14)——LINQ to SQL语句之存储过程 打造自己的LINQ Provider（上）：Expression Tree揭秘 Linq系列：基础与本质（Part II） LINQ体验(7)——LINQ语句之Group By/Having和Exists/In/Any/All/Contains Presentation: Beth Massi on Conquering XML with LINQ in VB9 深入浅出学Linq：通过Linq to SQL看Linq

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:

SqlQuery<Book> queryContext = new SqlQuery<Book>(); 
SqlQuery<Book> queryContext = new SqlQuery<Book>();

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:

class Book : QueryObjectBase 
    { 
        [LinqVisible, Identity] 
        public int? Id { get; set; } 
        [LinqVisible] 
        public string Author { get; set; } 
        [LinqVisible] 
        public string Title { get; set; } 
        [LinqVisible] 
        public string ISBN { get; set; } 
        [LinqVisible] 
        public DateTime? LastUpdated {get; set;} 
 
        public override bool IsNew 
        { 
            get 
            { 
                return Id == null; 
            } 
        } 
    } 
class Book : QueryObjectBase
    {
        [LinqVisible, Identity]
        public int? Id { get; set; }
        [LinqVisible]
        public string Author { get; set; }
        [LinqVisible]
        public string Title { get; set; }
        [LinqVisible]
        public string ISBN { get; set; }
        [LinqVisible]
        public DateTime? LastUpdated {get; set;}

        public override bool IsNew
        {
            get
            {
                return Id == null;
            }
        }
    }

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.

[LinqVisible(false)] 
Public string SomeProperty {get;set;} 
[LinqVisible(false)]
Public string SomeProperty {get;set;}

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

public class IdentityAttribute : UniqueIdentifierAttribute { } 
public class IdentityAttribute : UniqueIdentifierAttribute { }

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:
protected virtual void Process(LinqExtender.Interface.IModify<T> items, Bucket bucket) 
protected virtual void AddItem(Bucket item) 
protected virtual void RemoveItem(Bucket item) 
protected virtual void Process(LinqExtender.Interface.IModify<T> items, Bucket bucket)
protected virtual void AddItem(Bucket item)
protected virtual void RemoveItem(Bucket item)

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:

public class SqlQuery<T> : Query<T> where T : QueryObjectBase 
{ 
 
.... 
.... 
 
} 
public class SqlQuery<T> : Query<T> where T : QueryObjectBase
{

....
....

}

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

StringBuilder orderByBuilder = new StringBuilder(); 
string orderbyField = bucket.UniqueItems != null ? bucket.UniqueItems[0] : string.Empty;   
if (bucket.OrderByClause != null) 
{ 
    orderByBuilder.Append("ORDER BY "); 
    orderByBuilder.Append("\r\n"); 
    orderByBuilder.Append(bucket.OrderByClause.FieldName + " " +   
    (bucket.OrderByClause.IsAscending ? "asc" : "desc")); 
} 
StringBuilder orderByBuilder = new StringBuilder();
string orderbyField = bucket.UniqueItems != null ? bucket.UniqueItems[0] : string.Empty;
if (bucket.OrderByClause != null)
{
    orderByBuilder.Append("ORDER BY ");
    orderByBuilder.Append("\r\n");
    orderByBuilder.Append(bucket.OrderByClause.FieldName + " " +
    (bucket.OrderByClause.IsAscending ? "asc" : "desc"));
}

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.

StringBuilder builder = new StringBuilder(); 
StringBuilder builder = new StringBuilder();

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:

if (bucket.ItemsToSkip > 0 && bucket.ItemsToTake == null) 
{ 
    throw new   
      ApplicationException("Skip with value > 0 but with no Take, is not supported"); 
} 
if (bucket.ItemsToSkip > 0 && bucket.ItemsToTake == null)
{
    throw new
      ApplicationException("Skip with value > 0 but with no Take, is not supported");
}

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:

if (bucket.ItemsToTake != null) 
{ 
// 1.Build Select statement using WITH 
} 
else 
{ 
// 2.General select statement followed by order by , if any 
} 
// 3. Run query and fill IModify<Items> collection. 
if (bucket.ItemsToTake != null)
{
// 1.Build Select statement using WITH
}
else
{
// 2.General select statement followed by order by , if any
}
// 3. Run query and fill IModify<Items> collection.

1. Building the Select statement using WITH

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

WITH FilteredList(Id,Author,Title,ISBN,LastUpdated, [RowNumber]) AS( 
SELECT Id,Author,Title,ISBN,LastUpdated, Row_number()OVER(ORDER BY   
LastUpdated asc) as [RowNumber] FROM Book  where book.author = “Dan Brown” 
)Select * from FilteredList WHERE [Rownumber] Between (3) and (7) 
WITH FilteredList(Id,Author,Title,ISBN,LastUpdated, [RowNumber]) AS(
SELECT Id,Author,Title,ISBN,LastUpdated, Row_number()OVER(ORDER BY
LastUpdated asc) as [RowNumber] FROM Book  where book.author = “Dan Brown”
)Select * from FilteredList WHERE [Rownumber] Between (3) and (7)

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:

string[] names = bucket.Items.Select(item => item.Value.Name).ToArray(); 
string fields = string.Join(",", names); 
string[] names = bucket.Items.Select(item => item.Value.Name).ToArray();
string fields = string.Join(",", names);

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

builder.Append("WITH FilteredList(" + fields + ", [RowNumber]) AS("); 
builder.Append("\r\n"); 
builder.Append("SELECT " + fields + ", Row_number()"); 
builder.Append("WITH FilteredList(" + fields + ", [RowNumber]) AS(");
builder.Append("\r\n");
builder.Append("SELECT " + fields + ", Row_number()");

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.

if (orderByBuilder.Length > 0) 
{ 
  builder.Append("OVER(" + orderByBuilder.ToString() + ")"); 
} 
else 
{ 
  if (bucket.UniqueItems == null) 
  { 
      throw new ApplicationException("There should be at least one Unique identifier property in order to limit items"); 
  } 
 
  builder.Append("OVER( Order By " + orderbyField + " asc)"); 
} 
if (orderByBuilder.Length > 0)
{
  builder.Append("OVER(" + orderByBuilder.ToString() + ")");
}
else
{
  if (bucket.UniqueItems == null)
  {
      throw new ApplicationException("There should be at least one Unique identifier property in order to limit items");
  }

  builder.Append("OVER( Order By " + orderbyField + " asc)");
}

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.

builder.Append(" as [RowNumber] FROM " + bucket.Name + " "); 
builder.Append(" as [RowNumber] FROM " + bucket.Name + " ");

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

CreateWhereClauseIfPossible(bucket, builder); 
builder.Append("\r\n"); 
builder.Append(")"); 
builder.Append("Select * from FilteredList WHERE [Rownumber] Between (" + (bucket.ItemsToSkip + 1) + ") and (" + (bucket.ItemsToSkip +   
 
bucket.ItemsToTake) + ")"); 
CreateWhereClauseIfPossible(bucket, builder);
builder.Append("\r\n");
builder.Append(")");
builder.Append("Select * from FilteredList WHERE [Rownumber] Between (" + (bucket.ItemsToSkip + 1) + ") and (" + (bucket.ItemsToSkip +

bucket.ItemsToTake) + ")");

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:

foreach (string propertyKey in bucket.Items.Keys) 
{ 
    string value = Convert.ToString( bucket.Items[propertyKey].Value); 
    value = value.Replace("'", "''"); // incase user use ‘ in query 
 
    if (!insert) 
    { 
          [part_1] // build the query with key value and operator 
    } 
    else 
    { 
        if (!bucket.Items[propertyKey].Unique) 
        { 
            builder.Append("'" + value + "',"); 
        } 
      } 
    } 
 
foreach (string propertyKey in bucket.Items.Keys)
{
    string value = Convert.ToString( bucket.Items[propertyKey].Value);
    value = value.Replace("'", "''"); // incase user use ‘ in query

    if (!insert)
    {
          [part_1] // build the query with key value and operator
    }
    else
    {
        if (!bucket.Items[propertyKey].Unique)
        {
            builder.Append("'" + value + "',");
        }
      }
    }

if (bucket.Items[propertyKey].Value != null) 
{ 
    builder.Append(bucket.Items[propertyKey].Name + GetEquavalentSqlOperator(bucket.Items[propertyKey].ReleationType)  + "'" + value   
 
"); 
} 
        if (bucket.Items[propertyKey].Value != null)
        {
            builder.Append(bucket.Items[propertyKey].Name + GetEquavalentSqlOperator(bucket.Items[propertyKey].ReleationType)  + "'" + value

+ "' AND ");
        }

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.

builder.Append("SELECT * FROM " + bucket.Name + " "); 
CreateWhereClauseIfPossible(bucket, builder); 
 
if (bucket.OrderByClause != null) 
{ 
    builder.Append(orderByBuilder.ToString()); 
} 
builder.Append("SELECT * FROM " + bucket.Name + " ");
CreateWhereClauseIfPossible(bucket, builder);

if (bucket.OrderByClause != null)
{
    builder.Append(orderByBuilder.ToString());
}

3. Run query and Fill IModify collection.

This is done through the following call:

FillObject(builder.ToString(), items, bucket.Items); 
FillObject(builder.ToString(), items, bucket.Items);

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

DatabaseContext context = new DatabaseContext() 
IDataReader reader = context.ExecuteReader(CommandType.Text, sql); 
DatabaseContext context = new DatabaseContext()
IDataReader reader = context.ExecuteReader(CommandType.Text, sql);

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

while (reader.Read()) 
{ 
      T item = Activator.CreateInstance(typeof(T)) as T; 
 
        foreach (string key in bItems.Keys) 
        { 
          item.FillProperty(key , reader[bItems[key].Name]); 
        } 
        items.Add(item); 
                // extract data 
} 
 
reader.Close(); 
while (reader.Read())
{
      T item = Activator.CreateInstance(typeof(T)) as T;

        foreach (string key in bItems.Keys)
        {
          item.FillProperty(key , reader[bItems[key].Name]);
        }
        items.Add(item);
                // extract data
}

reader.Close();

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:

StringBuilder builder = new StringBuilder(); 
 
builder.Append("INSERT INTO " + item.Name); // item == Bucket 
builder.Append("\r\n"); 
builder.Append("VALUES"); 
builder.Append("("); 
 
BuildClause(item, builder, true); // shown in section 1 
 
builder.Append(")"); 
 
ExecuteOnly(builder.ToString()); 
 
StringBuilder builder = new StringBuilder();

builder.Append("INSERT INTO " + item.Name); // item == Bucket
builder.Append("\r\n");
builder.Append("VALUES");
builder.Append("(");

BuildClause(item, builder, true); // shown in section 1

builder.Append(")");

ExecuteOnly(builder.ToString());


For the Delete statement, the code looks like:

StringBuilder builder = new   
 
ringBuilder(); 
 
builder.Append("DELETE FROM " + item.Name); // item == Bucket 
builder.Append("\r\n"); 
builder.Append("WHERE"); 
builder.Append(" "); 
 
BuildClause(item, builder, false); // shown in section 1 
 
ExecuteOnly(builder.ToString()); 
  StringBuilder builder = new

StringBuilder();

  builder.Append("DELETE FROM " + item.Name); // item == Bucket
  builder.Append("\r\n");
  builder.Append("WHERE");
  builder.Append(" ");

  BuildClause(item, builder, false); // shown in section 1

  ExecuteOnly(builder.ToString());

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:

<configuration> 
  <configSections> 
    <section name="customDataConfig" type="OpenLinqToSql.Configuration.OpenLinqDataProviderConfiguration, OpenLinqToSql" /> 
  </configSections> 
 
  <customDataConfig  provider="System.Data.SqlClient "  c/> 
   
  <!-- For SqlCe , it looks like --> 
   
  <customDataConfig  provider="System.Data.SqlServerCe"  c/> 
 
</configuration> 
<configuration>
  <configSections>
    <section name="customDataConfig" type="OpenLinqToSql.Configuration.OpenLinqDataProviderConfiguration, OpenLinqToSql" />
  </configSections>

  <customDataConfig  provider="System.Data.SqlClient "  c/>
 
  <!-- For SqlCe , it looks like -->
 
  <customDataConfig  provider="System.Data.SqlServerCe"  c/>

</configuration>

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.