1

I am having an issue with ODAC (Oracle Data Access Components), Entity Framework 4.3.1, and expression trees. We have a legacy database (don't we all?) that we are mapping in Entity Framework. The table has millions of records and over one hundred columns (sad face).

Here is an example query on an indexed column:

int myId = 2;
var matchingRecord = context.MyLargeTable.Where(v=>v.Id == myId).ToList(); //Super slow (5+ minutes, sometimes Out of Memory exception)


int myId = 2;
Expression<Func<bool>> myLambda = v => v.Id == myId; //Shouldn't this work now?
var matchingRecord = context.MyLargeTable.Where(myLambda).ToList(); //Still super slow (5+ minutes, sometimes Out of Memory exception)


var elementName = Expression.Parameter(typeof(LargeTable), "v");
var propertyName = Expression.Parameter(elementName, "Id");
var constantValue = Expression.Constant(myId);
var comparisonMethod = Expression.Call(
propertyName,
typeof(int).GetMethod("Equals", new[] { typeof(int) }),
constantValue
)
var finalTree = Expression.Lambda<Func<LargeTable, bool>>(comparisonMethod, elementName);
var matchingRecord = context.MyLargeTable.Where(finalTree).ToList(); //Super fast

I've read things like this that explain the different between Func<> and Expression> and how Expression> actually gets passed to the database for the query and that's why it is faster.

http://www.fascinatedwithsoftware.com/blog/post/2011/12/02/Falling-in-Love-with-LINQ-Part-7-Expressions-and-Funcs.aspx - Whole thing is good, but if in a rush, just read the section titled “Unintended Consequences” for the main takeaway

http://fascinatedwithsoftware.com/blog/post/2012/01/10/More-on-Expression-vs-Func-with-Entity-Framework.aspx

Why would you use Expression<Func<T>> rather than Func<T>? - No set of links is complete without a corresponding SO question

My question is this: Are people really sitting there constructing expression trees using Expression.* classes? Any query beyond simple comparisons get really complicated and is almost impossible to read. What am I missing about passing the Expression> to the database? Who do I go punch in the face for this manually constructed expression tree solution? Oracle? EF? What am I missing?

Community
  • 1
  • 1
IrishBoiler
  • 55
  • 7
  • I assume the first query is actually context.MyLargeTable.Where(v=>v.Id == myId).ToList() (Where is missing). If this is the case it should be an equivalent to the last one. From what you wrote it seems like the .Where is not translated correctly to SQL. Can you check what SQL queries are sent to the database? You should not need to create expression trees manually - at least not for common tasks. There are some corner cases where it's helpful (e.g. http://stackoverflow.com/questions/10402029/ef-object-comparison-with-generic-types) but in general you should not need to do this. – Pawel Aug 22 '12 at 00:12
  • @Pawel You are correct, I'm missing the .Where in the question. The SQL queries are correct when I pass an expression tree, but when I use a regular lambda, it seems to drop the Where clause part when going to Oracle – IrishBoiler Aug 22 '12 at 12:13
  • Would you be able to prepare a small repro? It will be hard to figure out where the problem is without stepping through the code. – Pawel Aug 22 '12 at 13:15

0 Answers0