Support for DbInExpression

[nlz242]

Hello,

I'm having some performance issues using Contains() on larger list.
After digging around, i found out that in Entity Framework 6.0, this has been adressed by adding DbInExpression. However, the provider has to support it. I dug around in my version of dotConnect (8.4.303.6) and found that the Provider's DbProviderManifest answers "false" to "SupportsInExpression()".

Here is the WorkItem for the changes in EF6 : http://entityframework.codeplex.com/workitem/245

So my question(s):
Are DbInExpression supported in more recent versions ? If not, are there plans to support it ?

This is a pretty big feature for us has we have data that comes from a third party source which is basically a list of IDs and we need to fetch all those items into a grid.
There are, of course, ways around this issues but most are not elegant.

EDIT: Installed 8.4.447 (trial) to look if "SupportsInExpression()" still answers false. It does.
Also found this interesting wiki page :
https://entityframework.codeplex.com/wi ... 0for%20EF6
Which has a paragraph about this :

Native support for Enumerable.Contains

EF6 introduces a new expression type, DbInExpression, which was added to address performance issues around use of Enumerable.Contains in LINQ queries. The DbProviderManifest class has a new virtual method, SupportsInExpression, which is called by EF to determine if a provider handles the new expression type. For compatibility with existing provider implementations the method returns false. To benefit from this improvement, an EF6 provider can add code to handle DbInExpression and override SupportsInExpression to return true. An instance of DbInExpression can be created by calling the DbExpressionBuilder.In method. A DbInExpression instance is composed of a DbExpression, usually representing a table column, and a list of DbConstantExpression to test for a match.

[Shalex]

We will investigate the question to find out the performance gains if the DbInExpression feature is implemented and notify you about the result.

As for the generated SQL, our provider always creates IN for comparisons with all EF versions (starting from EFv1):
(column = :p0) OR (column = :p1) OR ...
-->
column IN (:p0, :p1, ...)

[nlz242]

Yes, indeed the query are generated using IN clauses and the query themselves execute quickly. The query generation is what is taking very long.
In my test, if my list contains 8000 items, it takes more than 100s to generate.
500 items starts taking a few seconds, 2000 reaches the 10s mark. The growth isnt linear too. 4000 items takes 30s,

Of course, if i use short list (10, 20 items) it's super fast and not an issue, the problem really lies in the time it takes to generate the queries once there are many items, which is what is adressed by the new DbInExpression (instead of many Or expressions).

Thanks for looking into it, i can't wait to know what kind of gains you guys achieve on large lists!

[Shalex]

The performance of generating SQL for Enumerable.Contains in LINQ queries is improved by supporting a new expression type, DbInExpression, in Entity Framework 6. This feature is available in the new (8.4.457) build of dotConnect for Oracle: http://forums.devart.com/viewtopic.php?f=1&t=32142.

[nlz242]

Hello,

We tested the changes with great results. Generating the query with a .Contains using a list with more than 14 000 items is done in less than a second. Previously we were above 100 seconds for 8 000 items.

Thanks alot for adding this in!