2732

remove a condition from ef expression tree

Question:

I'm using entity framework to handle the database in a MVC application: I created a search engine for the application using several fields from start form, and setting privileges that comes from the user role, so the routine that create the select statement is growing, so sometimes I have this situation. before i make a selection:

<ol><li>

var orders = db.Orders.Where( ord => ord.Channel == 1 || ord.Channel == 2);

after in the source code, in some cases, type of users, filter combinations, and so on, I have to change this filter or remove this condition

</li> <li>

orders = db.Orders.Where( ord => ord.Channel == 1 );

this is just a simple example, because doing this way i loose the other filters I have done in the Linq expression tree, and cause i have request to add features very often, it's very difficult to reorganize all the code to test all the prerequisite before to add conditions to the expression tree so i would like to know if is possible to remove a statement in the linq expression tree after has been added and before the tree is parsed and converted in a sql query

</li> </ol>

luca

Answer1:

Assuming I understand your question correctly, I believe the simplest possible approach is to defer the creation of the query until you have collected all the data needed to know what the query should look like, i.e. in this case it would mean you build the query only after you already know whether the condition actually needs to be applied.

If it is not possible for you to defer the creation of the query (it seems to me that is what you are saying), but you can still anticipate which conditions may need to be removed at a later point, I would consider introducing a Boolean sentinel in the query, e.g. based on your query snippet:

var orders = db.Orders.Where( ord => (isFirstConditionRelevant && ord.Channel == 1) || (isSecondConditionRelvant && ord.Channel == 2));

The variables 'isFirstConditionRelevant' and 'isSecondConditionRelevant' would be initially be set to true and would be captured by the query expression, but you could set to false at a later point (e.g. just before executing the query) if you needed the corresponding condition to have no effect. Notice that with this approach the SQL translation of the query will still contain the condition, but it will also contain a parameter for each sentinel that will short circuit the Boolean logic when the query is executed by the server.

Note also that any constants in your conditions (e.g. the '1' in 'ord.Channel == 1') will be translated to constant in the SQL. I would suggest using variables as this will introduce parameters in the SQL query that could increase the chances that the query plan can be reused on the server.

Another approach that could be useful is to take advantage of support for references to expression variables in the query, e.g. you could pass a variable of type Expression> to the Where clause and at a later point replace the value of the variable to the right predicate. If I remember correctly, LINQ to Entities supports using expression variables as predicate conditions over nested collections by applying the AsQueryable operator to the collection in the query. Otherwise you can take advantage of the support for AsExpandable in provided by the LINQ kit. You will find more information about this, as well as examples of using references to expression variables in the LINQ Kit home page: <a href="http://www.albahari.com/nutshell/linqkit.aspx" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx</a>.

The fourth and most complicated approach I would consider is to use my own visitor (e.g. derived from System.Linq.Expressions.ExpressionVisitor) to rewrite the LINQ expression tree (in this case to remove the condition from the predicate) before the query is executed. I don't have an expression visitor that does exactly that handy. Instead, I can provide a few pointers to articles that solve different parts of the problem:

<ol><li>This thread in StackOverflow describes how to use an expression visitor to do some query rewriting: <a href="https://stackoverflow.com/questions/11164009/using-a-linq-expressionvisitor-to-replace-primitive-parameters-with-property-ref" rel="nofollow">Using a LINQ ExpressionVisitor to replace primitive parameters with property references in a lambda expression</a>. </li> <li>This great blog post by Alex James describes how to write an intercepting query provider that will call your expression rewriting visitor just be fore the query is executed: <a href="http://blogs.msdn.com/b/alexj/archive/2010/03/01/tip-55-how-to-extend-an-iqueryable-by-wrapping-it.aspx" rel="nofollow">http://blogs.msdn.com/b/alexj/archive/2010/03/01/tip-55-how-to-extend-an-iqueryable-by-wrapping-it.aspx</a>.</li> <li>The following article describes a library that can be used to rewrite expressions based on rules: <a href="http://www.codeproject.com/Articles/24454/Modifying-LINQ-Expressions-with-Rewrite-Rules" rel="nofollow">http://www.codeproject.com/Articles/24454/Modifying-LINQ-Expressions-with-Rewrite-Rules</a>. </li> </ol>

Hope this helps!

Recommend

  • How can I access the content of something created with programmatically?
  • Mongoose - go through object
  • Mongoose - can't access object properties?
  • Which list item is the most common
  • A Question about the .NET garbage collector when cyclic references exist
  • NewGlobalRef of a weak reference still prevent a object from garbage collected
  • AndThen executes before completable finished
  • chrome PDF viewer can't download file
  • How to add an object in my collection by only using add method? [closed]
  • How to remove last utf8 char of a python string
  • Firefox extension testing and developing - I'm confused
  • Time taken for Hadoop job to execute
  • Create registry key in 32-bit hive on x64 PC using Installshield 2012 LE - Avoid redirection
  • Manually Timing out a C# Thread
  • Multicolored edittext hint
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • Redux Form - Not able to type anything in input
  • presentShareDialogWithParams posts to FB wall, but callback handler results say error
  • Create DicomImage from scratch using Dcmtk
  • How to write order and limit within cakephp joins array
  • How can I sort a a table with VBA with given text condition?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • OpenGL 3.3 on Mac OSX El Capitan with LWJGL
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • How to do unit test for HttpContext.Current.Server.MapPath
  • All Classes Conforming to Protocol Inherit Default Implementation
  • Installing Hadoop, Java Exception about illegal characters at index 7?
  • MySQL WHERE-condition in procedure ignored
  • Convert array of 8 bytes to signed long in C++
  • Display Images one by one with next and previous functionality
  • How to make Safari send if-modified-since header?
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • How to pass list parameters for each object using Spring MVC?
  • SQL merge duplicate rows and join values that are different
  • -fvisibility=hidden not passed by compiler for Debug builds
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • How does Linux kernel interrupt the application?
  • How can I remove ASP.NET Designer.cs files?
  • JaxB to read class hierarchy
  • Net Present Value in Excel for Grouped Recurring CF