First let's start with the IQueryable. Since this interface has been split into two, it's now possible to implement the IQueryable part just once and re-use it for any provider. I'll implement a class called DynaQuery<T> that implements IQueryable <T> and all the rest.
public class DynaQuery<T> : IQueryable<T>, IQueryable, IEnumerable<T>, IEnumerable, IOrderedQueryable<T>, IOrderedQueryable
{
DynaQueryProvider provider;
Expression expression;
public DynaQuery(DynaQueryProvider provider)
{
if (provider == null)
{
throw new ArgumentNullException("provider");
}
this.provider = provider;
this.expression = Expression.Constant(this);
}
public DynaQuery(DynaQueryProvider provider, Expression expression)
{
if (provider == null)
{
throw new ArgumentNullException("provider");
}
if (expression == null)
{
throw new ArgumentNullException("expression");
}
if (!typeof(IQueryable<T>).IsAssignableFrom(expression.Type))
{
throw new ArgumentOutOfRangeException("expression");
}
this.provider = provider;
this.expression = expression;
}
Expression IQueryable.Expression
{
get { return this.expression; }
}
Type IQueryable.ElementType
{
get { return typeof(T); }
}
IQueryProvider IQueryable.Provider
{
get { return this.provider; }
}
public IEnumerator<T> GetEnumerator()
{
return ((IEnumerable<T>)this.provider.Execute(this.expression)).GetEnumerator();
}
IEnumerator IEnumerable.GetEnumerator()
{
return ((IEnumerable)this.provider.Execute(this.expression)).GetEnumerator();
}
public override string ToString()
{
return this.provider.GetQueryText(this.expression);
}
}
As you can see now, the IQueryable implementation is straightforward. This little object really does just hold onto an expression tree and a provider instance. The provider is where it really gets juicy.
Okay, now I need some provider to show you. I've implemented an abstract base class called DynaQueryProvider that DynaQuery<T> referred to above. A real provider can just derive from this class and implement the Execute method.
public abstract class DynaQueryProvider : IQueryProvider
{
protected DynaQueryProvider()
{
}
IQueryable<S> IQueryProvider.CreateQuery<S>(Expression expression)
{
return new DynaQuery<S>(this, expression);
}
IQueryable IQueryProvider.CreateQuery(Expression expression)
{
Type elementType = TypeSystem.GetElementType(expression.Type);
try
{
return (IQueryable)Activator.CreateInstance(typeof(DynaQuery<>).MakeGenericType(elementType), new object[] { this, expression });
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
}
S IQueryProvider.Execute<S>(Expression expression)
{
return (S)this.Execute(expression);
}
object IQueryProvider.Execute(Expression expression)
{
return this.Execute(expression);
}
public abstract string GetQueryText(Expression expression);
public abstract object Execute(Expression expression);
}
I've implemented the IQueryProvider interface on my base class DynaQueryProvider. The CreateQuery methods create new instances of DynaQuery<T> and the Execute methods forward execution to this great new and not-yet-implemented Execute method.
I suppose you can think of this as boilerplate code you have to write just to get started building a LINQ IQueryable provider. The real action happens inside the Execute method. That's where your provider has the opportunity to make sense of the query by examining the expression tree.
And that's what I'll start showing next time.
In the below code for hepler class of the next implemantion
internal static class TypeSystem
{
internal static Type GetElementType(Type seqType)
{
Type ienum = FindIEnumerable(seqType);
if (ienum == null) return seqType;
return ienum.GetGenericArguments()[0];
}
private static Type FindIEnumerable(Type seqType)
{
if (seqType == null || seqType == typeof(string))
return null;
if (seqType.IsArray)
return typeof(IEnumerable<>).MakeGenericType(seqType.GetElementType());
if (seqType.IsGenericType)
{
foreach (Type arg in seqType.GetGenericArguments())
{
Type ienum = typeof(IEnumerable<>).MakeGenericType(arg);
if (ienum.IsAssignableFrom(seqType))
{
return ienum;
}
}
}
Type[] ifaces = seqType.GetInterfaces();
if (ifaces != null && ifaces.Length > 0)
{
foreach (Type iface in ifaces)
{
Type ienum = FindIEnumerable(iface);
if (ienum != null) return ienum;
}
}
if (seqType.BaseType != null && seqType.BaseType != typeof(object))
{
return FindIEnumerable(seqType.BaseType);
}
return null;
}
}
The Query Translator
The query translator is going to simply visit each node in the query's expression tree and translate the supported operations into text using a StringBuilder. For the sake of clarity assume there is a class called ExpressionVisitor that defines the base visitor pattern for Expression nodes.
internal class QueryTranslator : ExpressionVisitor
{
StringBuilder sb;
internal QueryTranslator()
{
}
internal string Translate(Expression expression)
{
this.sb = new StringBuilder();
this.Visit(expression);
return this.sb.ToString();
}
private static Expression StripQuotes(Expression e)
{
while (e.NodeType == ExpressionType.Quote)
{
e = ((UnaryExpression)e).Operand;
}
return e;
}
protected override Expression VisitMethodCall(MethodCallExpression m)
{
if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
{
sb.Append("SELECT * FROM (");
this.Visit(m.Arguments[0]);
sb.Append(") AS T WHERE ");
LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
this.Visit(lambda.Body);
return m;
}
throw new NotSupportedException(string.Format("The method '{0}' is not supported", m.Method.Name));
}
protected override Expression VisitUnary(UnaryExpression u)
{
switch (u.NodeType)
{
case ExpressionType.Not:
sb.Append(" NOT ");
this.Visit(u.Operand);
break;
default:
throw new NotSupportedException(string.Format("The unary operator '{0}' is not supported", u.NodeType));
}
return u;
}
protected override Expression VisitBinary(BinaryExpression b)
{
sb.Append("(");
this.Visit(b.Left);
switch (b.NodeType)
{
case ExpressionType.And:
sb.Append(" AND ");
break;
case ExpressionType.Or:
sb.Append(" OR");
break;
case ExpressionType.Equal:
sb.Append(" = ");
break;
case ExpressionType.NotEqual:
sb.Append(" <> ");
break;
case ExpressionType.LessThan:
sb.Append(" < ");
break;
case ExpressionType.LessThanOrEqual:
sb.Append(" <= ");
break;
case ExpressionType.GreaterThan:
sb.Append(" > ");
break;
case ExpressionType.GreaterThanOrEqual:
sb.Append(" >= ");
break;
default:
throw new NotSupportedException(string.Format("The binary operator '{0}' is not supported", b.NodeType));
}
this.Visit(b.Right);
sb.Append(")");
return b;
}
protected override Expression VisitConstant(ConstantExpression c)
{
IQueryable q = c.Value as IQueryable;
if (q != null)
{
// assume constant nodes w/ IQueryables are table references
sb.Append("SELECT * FROM ");
sb.Append(q.ElementType.Name);
}
else if (c.Value == null)
{
sb.Append("NULL");
}
else
{
switch (Type.GetTypeCode(c.Value.GetType()))
{
case TypeCode.Boolean:
sb.Append(((bool)c.Value) ? 1 : 0);
break;
case TypeCode.String:
sb.Append("'");
sb.Append(c.Value);
sb.Append("'");
break;
case TypeCode.Object:
throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value));
default:
sb.Append(c.Value);
break;
}
}
return c;
}
protected override Expression VisitMemberAccess(MemberExpression m)
{
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
{
sb.Append(m.Member.Name);
return m;
}
throw new NotSupportedException(string.Format("The member '{0}' is not supported", m.Member.Name));
}
}
public class DbDynaQueryProvider : DynaQueryProvider
{
DbConnection connection;
public DbDynaQueryProvider(DbConnection connection)
{
this.connection = connection;
}
public override string GetQueryText(Expression expression)
{
return this.Translate(expression);
}
public override object Execute(Expression expression)
{
DbCommand cmd = this.connection.CreateCommand();
cmd.CommandText = this.Translate(expression);
DbDataReader reader = cmd.ExecuteReader();
Type elementType = TypeSystem.GetElementType(expression.Type);
return Activator.CreateInstance(
typeof(ObjectReader<>).MakeGenericType(elementType),
BindingFlags.Instance | BindingFlags.NonPublic, null,
new object[] { reader },
null);
}
private string Translate(Expression expression)
{
return new QueryTranslator().Translate(expression);
}
}
internal class ObjectReader<T> : IEnumerable<T>, IEnumerable where T : class, new()
{
Enumerator enumerator;
internal ObjectReader(DbDataReader reader)
{
this.enumerator = new Enumerator(reader);
}
public IEnumerator<T> GetEnumerator()
{
Enumerator e = this.enumerator;
if (e == null)
{
throw new InvalidOperationException("Cannot enumerate more than once");
}
this.enumerator = null;
return e;
}
IEnumerator IEnumerable.GetEnumerator()
{
return this.GetEnumerator();
}
class Enumerator : IEnumerator<T>, IEnumerator, IDisposable
{
DbDataReader reader;
FieldInfo[] fields;
int[] fieldLookup;
T current;
internal Enumerator(DbDataReader reader)
{
this.reader = reader;
this.fields = typeof(T).GetFields();
}
public T Current
{
get { return this.current; }
}
object IEnumerator.Current
{
get { return this.current; }
}
public bool MoveNext()
{
if (this.reader.Read())
{
if (this.fieldLookup == null)
{
this.InitFieldLookup();
}
T instance = new T();
for (int i = 0, n = this.fields.Length; i < n; i++)
{
int index = this.fieldLookup[i];
if (index >= 0)
{
FieldInfo fi = this.fields[i];
if (this.reader.IsDBNull(index))
{
fi.SetValue(instance, null);
}
else
{
fi.SetValue(instance, this.reader.GetValue(index));
}
}
}
this.current = instance;
return true;
}
return false;
}
public void Reset()
{
}
public void Dispose()
{
this.reader.Dispose();
}
private void InitFieldLookup()
{
Dictionary<string, int> map = new Dictionary<string, int>(StringComparer.InvariantCultureIgnoreCase);
for (int i = 0, n = this.reader.FieldCount; i < n; i++)
{
map.Add(this.reader.GetName(i), i);
}
this.fieldLookup = new int[this.fields.Length];
for (int i = 0, n = this.fields.Length; i < n; i++)
{
int index;
if (map.TryGetValue(this.fields[i].Name, out index))
{
this.fieldLookup[i] = index;
}
else
{
this.fieldLookup[i] = -1;
}
}
}
}
}
Now that we have our provider we can try it out. Since I'm basically following the LINQ to SQL model I'll define a class for the Customers table, a 'Context' that holds onto the tables (root queries) and a little program that uses them.
public class Northwind
{
//public DynaQuery<Customers> Customers;
//public DynaQuery<Orders> Orders;
public DynaQuery<TFA_PRODHDR> TFA_PRODHDR;
public Northwind(DbConnection connection)
{
DynaQueryProvider provider = new DbDynaQueryProvider(connection);
//this.Customers = new DynaQuery<Customers>(provider);
//this.Orders = new DynaQuery<Orders>(provider);
this.TFA_PRODHDR = new DynaQuery<TFA_PRODHDR>(provider);
}
}
public class Customers
{
public string CustomerID;
public string ContactName;
public string Phone;
public string City;
public string Country;
}
public class Orders
{
public int OrderID;
public string CustomerID;
public DateTime OrderDate;
}
public class TFA_PRODHDR
{
public String LOCATIONCODE ;
public String SITECODE ;
public String TYPE ;
public String FINYR ;
public String PRODNO ;
public DateTime PRODDT ;
public String PROVPRODNO ;
public String DIVISION ;
public String WONO ;
public String APPROVED ;
public String PROVDOCTYPE ;
public Decimal APPRVLVL ;
public DateTime APPRVDT ;
public DateTime LMDT ;
public String LMBY ;
public String MACHINEID ;
public String IPADDRESS ;
public String MACHINECD ;
public String MCHITEMCD ;
public String ACCD ;
}
public class Program
{
public static void Main(string[] args)
{
string constr = @"Server=sql;Database=dbss;User Id =sa;Password =;Integrated Security=false";
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
Northwind db = new Northwind(con);
IQueryable<TFA_PRODHDR> query =
db.TFA_PRODHDR.Where(c => c.PRODNO == "00001/11-12");
Console.WriteLine("Query:\n{0}\n", query);
}
}
}
The main difference, from a user's perspective, is that, when you use IQueryable<T> (with a provider that supports things correctly), you can save a lot of resources.
For example, if you're working against a remote database, with many ORM systems, you have the option of fetching data from a table in two ways, one which returns IEnumerable<T>, and one which returns an IQueryable<T>. Say, for example, you have a Products table, and you want to get all of the products whose cost is >$25.
If you do:
IEnumerable<Product> products = myORM.GetProducts();
var productsOver25 = products.Where(p => p.Cost >= 25.00);
What happens here is the database loads all of the products, and passes them across the wire to your program. Your program then filters the data. In essence, the database does a " SELECT * FROM Products", and returns EVERY product to you.
With the right IQueryable<T> provider, on the other hand, you can do:
IQueryable<Product> products = myORM.GetQueryableProducts();
var productsOver25 = products.Where(p => p.Cost >= 25.00);
The code looks the same, but the difference here is that the SQL executed will be " SELECT * FROM Products WHERE Cost >= 25".
From your POV as a developer, this looks the same. However, from a performance standpoint, you may only return 2 records across the network instead of 20,000.
Posted By: Mr.Palash Paul
Excellent Post with understandable Guide for Beginners.
ReplyDeleteWaiting for more...........
Very interesting and good Explanation
ReplyDeleteASP NET Training
ASP NET Training
ASP NET Online Training
C-Sharp Training
Dot Net Training in Chennai
Online .Net Training
LINQ Training