CODEWITHSUNDEEP
c#.netentity-framework-4
Gromer
Gromer

Reputation: 9931

EF not executing where clause at DB level

Disclaimer: I changed the names of the service/entities/etc... to generic stuff for SO in Notepad. If you see an inconsistency with regards to class names, ignore them please, as it isn't the problem.

I'm working on a WCF service for a client and am having some issues with expressions I'm serializing. I am currently using Serialize.Linq to deal with the expression serialization. On top of that, I'm using my DataContract classes to create the expression on the client side, and converting it to an expression using my Entity class.

Assume these two classes:

  1. MyEntity (part of the DataContract namespace in the project)
  2. MyEntity (part of the Entities namespace in the project)

Both have identical properties, and I use AutoMapper to convert the entities I get via EF to my DataContract object, then send that object back to the client.

To deal with the expression conversion, I'm using an ExpressionVisitor class:

class MyExpressionVisitor : ExpressionVisitor
{
    public ParameterExpression ParameterExpression { get; private set; }

    public MyExpressionVisitor(ParameterExpression newParameterExp)
    {
        ParameterExpression = newParameterExp;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return ParameterExpression;
    }

    protected override Expression VisitMember(MemberExpression node)
    {
        if (node.Member.DeclaringType == typeof(DataContracts.MyEntity))
        {
            return Expression
                .MakeMemberAccess(this.Visit(node.Expression), typeof(Entities.MyEntity).GetMember(node.Member.Name).FirstOrDefault());
        }

        return base.VisitMember(node);
    }
}

This is how I'm calling my service:

Expression<Func<DataContracts.MyEntity, bool>> expression =
    fl.SomeNameField1 == "John Doe" || fl.SomeNameField2 == "John Doe";
var entities = manager.MyService.GetFilteredEntities(expression.ToExpressionNode());

This is my current service implementation (partial, omitting return lines, etc...) for GetFilteredEntities:

// Using Serialize.Linq for send expressions over WCF.
// query is an ExpressionNode from the Serialize.Linq library.
// This DOES NOT execute the where clause on the database.
var expression = query.ToExpression<Func<DataContracts.MyEntity, bool>>();
var visitor = new MyExpressionVisitor(Expression.Parameter(typeof(Entities.MyEntity), expression.Parameters[0].Name));
var entityExpression = Expression.Lambda<Func<Entities.MyEntity, bool>>(visitor.Visit(expression.Body), visitor.ParameterExpression);
var func = entityExpression.Compile();
var entities = this.Entities.MyEntities.Where(func);

All of this code works, but the Where isn't applied at the database level, it's applied against the in memory set of every row from my table. This takes a long time, as the table has 150k+ rows.

If I hard code the where I'm wanting in the service, it applies the where clause at the database level:

// This DOES execute the where clause on the database.
var temp1 = this.Entities.MyEntities.Where(fl => fl.SomeNameField1 == "John Doe" || fl.SomeNameField2 == "John Doe");

// This DOES execute the where clause on the database.
Func<Entities.MyEntity, bool> func2 = fl => fl.SomeNameField1 == "John Doe" || fl.SomeNameField2 == "John Doe";
var temp2 = this.Entities.MyEntities.Where(func2);

I know I could just write a bunch of different service opperations that would allow the user to filter by passing in names, ids, whatever, but this table has a ridiculous amount of columns (200+), and I have ZERO input on said database. It's a lot easier for other developers who may be using the client I'm writing to be able to create an expression using whatever data & columns they feel like, so I'd love to get this applying the where at the db level.

I'm almost certain I've included everything in this post that is relevant. I used SQL Server Profiler to check the queries EF was running, that's how I know which ones used the where. Please ask for more information where/if needed.

Thanks!

/walloftext

Upvotes: 3

Views: 1036

Answers (3)

qxn
qxn

Reputation: 17584

I don't think you should be compiling your expression before you use it in your Where clause. Doing so will invoke the extension method provided in System.Core by the System.Linq namespace. You want the method overload that takes an object of type Expression.

In other words, try removing this line:

var func = entityExpression.Compile();

Upvotes: 1

Piotr Zierhoffer
Piotr Zierhoffer

Reputation: 5149

Have you tried using LinqKit to create compound queries? It provides, with PredicateBuilder, an easy way to work with Expressions.

Take a look at this address, the source code is quite straight-forward, it did miracles in my case.

Upvotes: 0

sinelaw
sinelaw

Reputation: 16553

Perhaps the problem is with this line:

var func = entityExpression.Compile();

Since you are calling Where with a compiled delegate function, you are not calling the overload of Where that actually gets sent to the DB.

You need to pass an expression object to Where (i.e. call one of these)

Upvotes: 2

Related Questions