Random Dev Notes

February 25, 2010

WCF Data Service mangled my select statement…

Filed under: Development — Tags: , — Tom Brothers @ 1:49 am

I’ve been working on a project that uses WCF Data Services for data access on a DMZ server.  The application was designed to work directly with LINQ to VFP on the intranet but use WCF Data Services on the extranet/DMZ server.  During development I noticed that in some cases the queries over WCF Data Services didn’t exactly match the direct LINQ to VFP queries.  What I found was the Expression Tree for the queries that required an upper(alltrim()) (to utilize indexes) had included some extra null checking Expressions. 

Here is an example of the issue.

Example Query:

var list = (from d in context.List<Customer>()
            where d.CustomerId.Trim().ToUpper() == "ALFKI"
            select d).ToList();

When executing this query directly with LINQ to VFP you would get the following Expression:

image

This would result in the following query:

__Param__0__ = [ALFKI]
SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerId, t0.Phone ;
FROM Customers AS t0 ;
WHERE (UPPER(ALLTRIM(t0.CustomerId)) = __Param__0__)

The same query would result in the following when executed through WCF Data Services:

image

__Param__0__ = [ALFKI]
SELECT t0.City, t0.CompanyName, t0.ContactName, t0.Country, t0.CustomerId, t0.Phone ;
FROM Customers AS t0 ;
WHERE (ICASE((ICASE((t0.CustomerId IS NULL), NULL, ALLTRIM(t0.CustomerId)) IS NULL), NULL, UPPER(ICASE((t0.CustomerId IS NULL), NULL, ALLTRIM(t0.CustomerId)))) = __Param__0__)


The extra null checking makes sense given the fact that WCF Data Services can be used with Data Sources other than ORMs.  However, this really made a mess of the Select Statement created by LINQ to VFP.  More importantly the query was unable to utilize the index. 

Correcting this issue wasn’t that difficult once it had been identified.  I added the following Visitor class to remove the unnecessary null checking Expressions.

using System.Linq.Expressions;
using IQToolkit.Data.Common;
 
namespace LinqToVfp {
    public class ConditionalImmediateIfNullRemover : DbExpressionVisitor {
        public static Expression Remove(Expression expression) {
            return new ConditionalImmediateIfNullRemover().Visit(expression);
        }
 
        protected override Expression VisitConditional(ConditionalExpression c) {
            if (c.Test.NodeType == ExpressionType.Equal && c.IfTrue.NodeType == ExpressionType.Constant && ((ConstantExpression)c.IfTrue).Value == null) {
                MethodCallExpression methodCallExpression = c.IfFalse as MethodCallExpression;
 
                if (methodCallExpression != null) {
                    if (methodCallExpression.Object.NodeType == ExpressionType.Conditional) {
                        return this.Visit(base.VisitConditional(c));
                    }
 
                    BinaryExpression be = (BinaryExpression)c.Test;
                    ColumnExpression columnExpression = be.Left as ColumnExpression;
 
                    if (columnExpression == null) {
                        return c.IfFalse;
                    }
                    else {
                        return this.Visit(c.IfFalse);
                    }
 
                }
            }
 
            return base.VisitConditional(c);
        }
    }
}

* This change was made in v1.0.17.1

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: