Home > .NET Framework > Linq to SQL Detached IQueryable / Disconnected Queries / Detached Criteria

Linq to SQL Detached IQueryable / Disconnected Queries / Detached Criteria

It has been a long time since I wrote something on technology. No, I am not becoming a non technical manager. It was just that I was lazy to write something up.

We are planning to standardize on LINQ’s IQueryable as the Query Object in our project. We had to choose between LINQ to SQL vs LINQ to Entities. Since LINQ to Entities / Entity Framework is still prerelease stuff we decided to check with LINQ to SQL first. A few issues which made us think before adopting LIINQ to SQL

  • No support for Disconnected Query Building (akin to NHibernate’s Detached Criteria)
  • No support for explicit detach of objects & object graphs from the DataContext (akin to NHibernate Detached entities / objects). You need to Serialize De-Serialize to detach an Entity Instance. Combine this with the WCF & LINQ Serialization issues with object graphs situation become further worse.
  • No support for attaching detached (detached by serialization / deserialization) object graphs.
  • IQueryable implementations & Expression classes are not serializable.
  • LINQ to SQL supports only Single Table Inheritance

But still LINQ to SQL query syntax is really cool. So we decided to use LINQ just for Querying and let our own framework handle the persistence mapping.

All of these limitations made me think of LINQ to SQL is just a cool Toy which cannot be used in production quality applications. In fact even some Microsoft folks have said something along these lines "LINQ to SQL supports rapid development of applications that query Microsoft SQL Server databases using objects that map directly to SQL Server schemas. LINQ to Entities supports more flexible mapping of objects to Microsoft SQL Server and other relational databases through extended ADO.NET Data Providers". From http://blogs.msdn.com/data/archive/2007/04/28/microsoft-s-data-access-strategy.aspx. LINQ to SQL is not for complex mapping scenarios. For complex Mapping scenarios use LINQ to Entities (it looks like L2E is going to bring in its own additional complexity).

How do we do disconnected LINQ queries which are going to be executed later by the LINQ to SQL provider / How do we simulate Detached Criteria using LINQ / Is there a Detached IQueryable implementation?

LINQ to SQL doesn’t support disconnected queries natively. As a workaround we were tempted to try IEnumerable.AsQueryable() which returns a EnumerableQuery on the client side (I know purists must be raising their eyebrows now that this should be done on the server side, in reality you cannot think all possible scenarios and let the client send in queries for scenarios that one hasn’t thought about). On the server side when we called DataContext.GetCommand(queryable) it started returning a SqlCommand with SELECT NULL AS [EMPTY] as CommandText.

Next we tried to create a disconnected query using a DataContext object initialized with an empty connection string on the client side. On the server side when we called DataContext.GetCommand(queryable) it started throwing an exception stating ‘The query contains references to items defined on a different data context’. Looks like LINQ to SQL wasn’t happy about a disconnected context no matter what you did.

We decided to use InterLinq‘s ExpressionConverter (actually it’s a slightly modified version of InterLinq’s ExpressionConverter inspired by Matt Waren‘s ExpressionVisitor) to rewrite the IQueryable references in the expression tree with the IQueryable created on the server side . We started getting another exception that Parameter ‘e’ not in scope. It looked like Matt’s code didn’t use the parameters collection returned by visit call inside the VisitLambda handler. Once that was fixed we were good to go.

    public class CloningExpressionVisitor
    {
        private readonly IQueryBinder queryBinder;
        private Dictionary<int, object> m_convertedObjects = new Dictionary<int, object>();
        private readonly Expression expressionToConvert;

        public CloningExpressionVisitor(Expression expression, IQueryBinder queryBinder)
        {
            this.queryBinder = queryBinder;
            expressionToConvert = expression;
        }

        public object Visit()
        {
            m_convertedObjects = new Dictionary<int, object>();
            return VisitResult(expressionToConvert);
        }

        /// <summary>
        /// Returns the value of the <see cref="Expression"/>.
        /// </summary>
        /// <param name="expression"><see cref="Expression"/> to visit.</param>
        /// <returns>Returns the value of the <see cref="Expression"/>.</returns>
        protected virtual object VisitResult(Expression expression)
        {
            if (expression == null)
            {
                return null;
            }
            if (m_convertedObjects.ContainsKey(expression.GetHashCode()))
            {
                return m_convertedObjects[expression.GetHashCode()];
            }

            object foundObject = null;

            if (expression is ConstantExpression)
            {
                foundObject = GetResultConstantExpression((ConstantExpression)expression);
            }
            else if (expression is MethodCallExpression)
            {
                foundObject = GetResultMethodCallExpression((MethodCallExpression)expression);
            }
            else
            {
                throw new NotImplementedException();
            }
            m_convertedObjects[expression.GetHashCode()] = foundObject;
            return foundObject;
        }

        protected virtual object GetResultConstantExpression(ConstantExpression expression)
        {
            object value = expression.Value;
            if (value == null)
            {
                return null;
            }
            else if (value is IQueryable)
            {
                return queryBinder.GetQueryBoundToNewProvider((value as IQueryable).ElementType);
            }
            return value;
        }

        protected virtual object GetResultMethodCallExpression( MethodCallExpression expression ) {
            return InvokeMethodCall( expression );
        }

        protected object InvokeMethodCall( MethodCallExpression ex ) {
            if( ex.Method.DeclaringType == typeof( Queryable ) ) {
                var args = new List<object>();
                for( int i = 0; i < ex.Arguments.Count; i++ ) {
                    Expression currentArg = ex.Arguments[i];
                    if( typeof( Expression ).IsAssignableFrom( currentArg.Type ) ) {
                        args.Add( ( (UnaryExpression) Visit( currentArg ) ).Operand );
                    }
                    else {
                        args.Add( VisitResult( currentArg ) );
                    }
                }
                return ex.Method.Invoke( ex.Object, args.ToArray() );
            }

            // If the method is not of DeclaringType "Queryable", it mustn't be invoked.
            // Without this check, we were able to delete files from the server disk
            // using System.IO.File.Delete( ... )!
            throw new SecurityException( string.Format( "Could not call method '{0}' of type '{1}'. Type must be Queryable.", ex.Method.Name, ex.Method.DeclaringType.Name ) );
        }

        public virtual Expression Visit(Expression exp)
        {
            if (exp == null)
                return exp;
            
            if (m_convertedObjects.ContainsKey(exp.GetHashCode()))
            {
                return (Expression)m_convertedObjects[exp.GetHashCode()];
            }

            Expression returnValue = null;

            switch (exp.NodeType)
            {
                case ExpressionType.Negate:
                case ExpressionType.NegateChecked:
                case ExpressionType.Not:
                case ExpressionType.Convert:
                case ExpressionType.ConvertChecked:
                case ExpressionType.ArrayLength:
                case ExpressionType.Quote:
                case ExpressionType.TypeAs:
                case ExpressionType.UnaryPlus:
                    returnValue = VisitUnary((UnaryExpression)exp);
                    break;
                case ExpressionType.Add:
                case ExpressionType.AddChecked:
                case ExpressionType.Subtract:
                case ExpressionType.SubtractChecked:
                case ExpressionType.Multiply:
                case ExpressionType.MultiplyChecked:
                case ExpressionType.Divide:
                case ExpressionType.Modulo:
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                case ExpressionType.Or:
                case ExpressionType.OrElse:
                case ExpressionType.LessThan:
                case ExpressionType.LessThanOrEqual:
                case ExpressionType.GreaterThan:
                case ExpressionType.GreaterThanOrEqual:
                case ExpressionType.Equal:
                case ExpressionType.NotEqual:
                case ExpressionType.Coalesce:
                case ExpressionType.ArrayIndex:
                case ExpressionType.RightShift:
                case ExpressionType.LeftShift:
                case ExpressionType.ExclusiveOr:
                case ExpressionType.Power:
                    returnValue = VisitBinary((BinaryExpression)exp);
                    break;
                case ExpressionType.TypeIs:
                    returnValue = VisitTypeIs((TypeBinaryExpression)exp);
                    break;
                case ExpressionType.Conditional:
                    returnValue = VisitConditional((ConditionalExpression)exp);
                    break;
                case ExpressionType.Constant:
                    returnValue = VisitConstant((ConstantExpression)exp);
                    break;
                case ExpressionType.Parameter:
                    returnValue = VisitParameter((ParameterExpression)exp);
                    break;
                case ExpressionType.MemberAccess:
                    returnValue = VisitMemberAccess((MemberExpression)exp);
                    break;
                case ExpressionType.Call:
                    returnValue = VisitMethodCall((MethodCallExpression)exp);
                    break;
                case ExpressionType.Lambda:
                    returnValue = VisitLambda((LambdaExpression)exp);
                    break;
                case ExpressionType.New:
                    returnValue = VisitNew((NewExpression)exp);
                    break;
                case ExpressionType.NewArrayInit:
                case ExpressionType.NewArrayBounds:
                    returnValue = VisitNewArray((NewArrayExpression)exp);
                    break;
                case ExpressionType.Invoke:
                    returnValue = VisitInvocation((InvocationExpression)exp);
                    break;
                case ExpressionType.MemberInit:
                    returnValue = VisitMemberInit((MemberInitExpression)exp);
                    break;
                case ExpressionType.ListInit:
                    returnValue = VisitListInit((ListInitExpression)exp);
                    break;
                default:
                    throw new Exception(string.Format("Unhandled expression type: '{0}'", exp.NodeType));
            }

            m_convertedObjects.Add(exp.GetHashCode(), returnValue);
            return returnValue;
        }

        protected virtual MemberBinding VisitBinding(MemberBinding binding)
        {
            switch (binding.BindingType)
            {
                case MemberBindingType.Assignment:
                    return this.VisitMemberAssignment((MemberAssignment)binding);
                case MemberBindingType.MemberBinding:
                    return this.VisitMemberMemberBinding((MemberMemberBinding)binding);
                case MemberBindingType.ListBinding:
                    return this.VisitMemberListBinding((MemberListBinding)binding);
                default:
                    throw new Exception(string.Format("Unhandled binding type '{0}'", binding.BindingType));
            }
        }

        protected virtual ElementInit VisitElementInitializer(ElementInit initializer)
        {
            ReadOnlyCollection<Expression> arguments = this.VisitExpressionList(initializer.Arguments);
            return Expression.ElementInit(initializer.AddMethod, arguments);
        }

        protected virtual Expression VisitUnary(UnaryExpression u)
        {
            Expression operand = this.Visit(u.Operand);
            return Expression.MakeUnary(u.NodeType, operand, u.Type, u.Method);
        }

        protected virtual Expression VisitBinary(BinaryExpression b)
        {
            Expression left = this.Visit(b.Left);
            Expression right = this.Visit(b.Right);
            Expression conversion = this.Visit(b.Conversion);
            if (b.NodeType == ExpressionType.Coalesce && b.Conversion != null)
                return Expression.Coalesce(left, right, conversion as LambdaExpression);
            else
                return Expression.MakeBinary(b.NodeType, left, right, b.IsLiftedToNull, b.Method);

        }

        protected virtual Expression VisitTypeIs(TypeBinaryExpression b)
        {
            Expression expr = this.Visit(b.Expression);
            return Expression.TypeIs(expr, b.TypeOperand);
        }

        protected virtual Expression VisitConstant(ConstantExpression expression)
        {
            if (expression.Value == null)
            {
                return null;
            }
            return Expression.Constant(expression.Value);
        }

        protected virtual Expression VisitConditional(ConditionalExpression c)
        {
            Expression test = this.Visit(c.Test);
            Expression ifTrue = this.Visit(c.IfTrue);
            Expression ifFalse = this.Visit(c.IfFalse);
            return Expression.Condition(test, ifTrue, ifFalse);
        }

        protected virtual Expression VisitParameter(ParameterExpression p)
        {
            return Expression.Parameter(p.Type, p.Name);
        }

        protected virtual Expression VisitMemberAccess(MemberExpression m)
        {
            Expression exp = this.Visit(m.Expression);
            return Expression.MakeMemberAccess(exp, m.Member);
        }

        protected virtual Expression VisitMethodCall(MethodCallExpression m)
        {
            
            Expression obj = this.Visit(m.Object);
            IEnumerable<Expression> args = this.VisitExpressionList(m.Arguments);
            //switch (m.Method.Name)
            //{
            //    case "Where":
            //        args = new List<Expression>() { Expression.Constant(queryable, queryable.GetType()), this.Visit(m.Arguments[1]) };
            //        break;
            //}
            return Expression.Call(obj, m.Method, args);
        }

        protected virtual ReadOnlyCollection<Expression> VisitExpressionList(ReadOnlyCollection<Expression> original)
        {
            List<Expression> list = null;
            for (int i = 0, n = original.Count; i < n; i++)
            {
                Expression p = this.Visit(original[i]);
                if (list != null)
                {
                    list.Add(p);
                }
                else if (p != original[i])
                {
                    list = new List<Expression>(n);
                    for (int j = 0; j < i; j++)
                    {
                        list.Add(original[j]);
                    }
                    list.Add(p);
                }
            }
            if (list != null)
            {
                return list.AsReadOnly();
            }
            return original;
        }

        protected virtual MemberAssignment VisitMemberAssignment(MemberAssignment assignment)
        {
            Expression e = this.Visit(assignment.Expression);
            return Expression.Bind(assignment.Member, e);
        }

        protected virtual MemberMemberBinding VisitMemberMemberBinding(MemberMemberBinding binding)
        {
            IEnumerable<MemberBinding> bindings = this.VisitBindingList(binding.Bindings);
            return Expression.MemberBind(binding.Member, bindings);
        }

        protected virtual MemberListBinding VisitMemberListBinding(MemberListBinding binding)
        {
            IEnumerable<ElementInit> initializers = this.VisitElementInitializerList(binding.Initializers);
            return Expression.ListBind(binding.Member, initializers);
        }

        protected virtual IEnumerable<MemberBinding> VisitBindingList(ReadOnlyCollection<MemberBinding> original)
        {
            List<MemberBinding> list = null;
            for (int i = 0, n = original.Count; i < n; i++)
            {
                MemberBinding b = this.VisitBinding(original[i]);
                if (list != null)
                {
                    list.Add(b);
                }
                else if (b != original[i])
                {
                    list = new List<MemberBinding>(n);
                    for (int j = 0; j < i; j++)
                    {
                        list.Add(original[j]);
                    }
                    list.Add(b);
                }
            }
            if (list != null)
                return list;
            return original;
        }

        protected virtual IEnumerable<ElementInit> VisitElementInitializerList(ReadOnlyCollection<ElementInit> original)
        {
            List<ElementInit> list = null;
            for (int i = 0, n = original.Count; i < n; i++)
            {
                ElementInit init = this.VisitElementInitializer(original[i]);
                if (list != null)
                {
                    list.Add(init);
                }
                else if (init != original[i])
                {
                    list = new List<ElementInit>(n);
                    for (int j = 0; j < i; j++)
                    {
                        list.Add(original[j]);
                    }
                    list.Add(init);
                }
            }
            if (list != null)
                return list;
            return original;
        }

        protected virtual Expression VisitLambda(LambdaExpression lambda)
        {
            Expression body = this.Visit(lambda.Body);
            IEnumerable<ParameterExpression> parameters = VisitCollection<ParameterExpression>(lambda.Parameters);
            return Expression.Lambda(lambda.Type, body, parameters);
        }

        public IEnumerable<T> VisitCollection<T>(IEnumerable enumerable) where T : Expression
        {
            if (enumerable == null)
            {
                return null;
            }

            var returnValues = new List<T>();
            foreach (Expression expression in enumerable)
            {
                returnValues.Add((T)Visit(expression));
            }
            return returnValues;
        }

        protected virtual NewExpression VisitNew(NewExpression nex)
        {
            IEnumerable<Expression> args = this.VisitExpressionList(nex.Arguments);
            if (nex.Members != null)
                return Expression.New(nex.Constructor, args, nex.Members);
            else
                return Expression.New(nex.Constructor, args);
        }

        protected virtual Expression VisitMemberInit(MemberInitExpression init)
        {
            NewExpression n = this.VisitNew(init.NewExpression);
            IEnumerable<MemberBinding> bindings = this.VisitBindingList(init.Bindings);
            return Expression.MemberInit(n, bindings);
        }

        protected virtual Expression VisitListInit(ListInitExpression init)
        {
            NewExpression n = this.VisitNew(init.NewExpression);
            IEnumerable<ElementInit> initializers = this.VisitElementInitializerList(init.Initializers);
            return Expression.ListInit(n, initializers);
        }

        protected virtual Expression VisitNewArray(NewArrayExpression na)
        {
            IEnumerable<Expression> exprs = this.VisitExpressionList(na.Expressions);

            if (na.NodeType == ExpressionType.NewArrayInit)
            {
                return Expression.NewArrayInit(na.Type.GetElementType(), exprs);
            }
            else
            {
                return Expression.NewArrayBounds(na.Type.GetElementType(), exprs);
            }

        }

        protected virtual Expression VisitInvocation(InvocationExpression iv)
        {
            IEnumerable<Expression> args = this.VisitExpressionList(iv.Arguments);
            Expression expr = this.Visit(iv.Expression);
            return Expression.Invoke(expr, args);
        }

       }

    public class LinqToSqlBinder : IQueryBinder
    {
        private readonly DataContext dataContext;

        public LinqToSqlBinder(DataContext dataContext)
        {
            this.dataContext = dataContext;
        }

        #region IQueryBinder Members

        public IQueryable GetQueryBoundToNewProvider(Type type)
        {
            return dataContext.GetTable(type);
        }

        #endregion
    }

public interface IQueryBinder { IQueryable GetQueryBoundToNewProvider(Type type); }

public class LinqToObjectsBinder : IQueryBinder { private readonly IEnumerable objectSource; public LinqToObjectsBinder(IEnumerable objectSource) { this.objectSource = objectSource; } #region IQueryBinder Members public IQueryable GetQueryBoundToNewProvider(Type type) { return objectSource.AsQueryable(); } #endregion }

Advertisements
Categories: .NET Framework
  1. February 16, 2013 at 4:58 am

    Any updates? 🙂
    I’ve spent my day trying to create Compiled queries detached from the DB context and I still have no working solution, Did you ever try anything like that? Thanks!

  2. February 18, 2013 at 8:14 am

    Compiled Queries are such a pain even when the queries are connected to the provider. I haven’t had any success when we tried to tune the performance. Good luck to you, may be you can manage to crack it :-).

    Regards,
    Sendhil

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: