Entity Framework Feature Suggestions

Improve the performance of the Contains() operator

Currently, using the Contains() LINQ operator in EF queries causes a significant performance hit. See http://stackoverflow.com/questions/7897630/why-does-the-contains-operator-degrade-entity-frameworks-performance-so-drama for documentation and discussion of this problem. Please improve the performance of this operator.

361 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Michael CarrMichael Carr shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    6 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Diego VegaAdminDiego Vega (Development Lead, DataFx) commented  ·   ·  Flag as inappropriate

        @Allon: you are probably right about using TVPs as a solution, but given all the things we are committed to do in the EF6 release it is hard to think we will introduce a major change like that in the short term. If you are interested in contributing you can start a discussion in our CodePlex site.

      • Allon GuralnekAllon Guralnek commented  ·   ·  Flag as inappropriate

        Instead of translating .Contains() to SQL's IN syntax, it should send the list as a table-valued parameter and perform a join. This would mean you could have an unlimited number of items in the .Contains() while not affecting query generation time, reducing the parsing time of SQL server (no huge lists to parse), and even possibly improving the query performance.

        You don't need full-blown TVP support with metadata just to implement this improvement, since this is only for native SQL types.

      • DaveDave commented  ·   ·  Flag as inappropriate

        I've just encountered this. My users have access to depots. In views screens they can filter by any selected subset of these. On a clients site where a user has access to 100 it takes 4 seconds to build the query and 0.1 to execute it. e.g
        var query = from fb in FITTERBOOKINGS
        where fb.BOOKINGDATE == theDate && fb.DRIVER != string.Empty
        && selectedDepotList.Contains(fb.DRIVERS.DEPOT)
        select fb;

      • Roy McDonoughRoy McDonough commented  ·   ·  Flag as inappropriate

        Performance hit is crippling. The more "contains" clauses, the slower the performance. L2E takes 6-8 seconds to translate into a SQL query which runs in 70ms. Solutions such as intermediate tables just to workaround this shortcoming are unpalatable.
        Thank you

      Feedback and Knowledge Base