Problem with FromCache in EF8 when use "contains" (translated to openJson)

[fenomeno83]

Hi.
I've upgraded to EF8 but I've problems when i use "List.contains(column)".
In EF8, "contains" is translated to "openjson" (vs "IN(...)" of the previous versions).
If some values in List that makes contains,change, fromcache of Z.entityframework.plus doesn't understand the changes and it doesn't make new query, but uses a cached query (for example, if ListA.Contains(column) is cached and I do a new query with ListB.Contains(column), it returns old cached ListA.Contains(column) results and doesn't make a new query for ListB.Contains(column)...maybe fromcache doesn't manage properly openjson?).
I've solved with UseCompatibilityLevel(120) in dbcontext configuration; in this manner it doesn't use openjson but "old" translation in "IN(...)", and fromcache, as usual, understand changes in List.
Can you solve that issue?

[fenomeno83]

Here some real example:

Consider the table Tbl with records:
Id Code
1 test1
2 test2
3 test3
4 test4

public async Task<List<string>> TestCache(List<int> input)
{
    var query = (await (from ec in _ncoxDb.Tbl
                    where input.Contains(ec.Id)
                    select ec.Code).FromCacheAsync(new MemoryCacheEntryOptions() { AbsoluteExpirationRelativeToNow = 
 TimeSpan.FromHours(8) }, "TestCache")).ToList();

return query;
}

First Call:

List<int> listA = new List<int>() { 1, 2 };
var retA = await TestCache(listA);

The query generated of EF < 8 or EF8 with UseCompatibilityLevel(120) is

SELECT [e].[Code]
FROM [Tbl] AS [e]
WHERE [e].[Id] IN (1, 2)

The query generated by EF 8 without UseCompatibilityLevel(120) is

SELECT [e].[Code]
FROM [Tbl] AS [e]
WHERE [e].[Id] IN (
  SELECT [i].[value]
  FROM OPENJSON(@__input_0) WITH ([value] int '$') AS [i]
)

In both cases result is

retA = ["test1","test2"];

Second call:

List<int> listB = new List<int>() { 3, 4 };
var retB = await TestCache(listB);

The query generated of EF < 8 or EF8 with UseCompatibilityLevel(120) is

 SELECT [e].[Code]
 FROM [Tbl] AS [e]
 WHERE [e].[Id] IN (3, 4)

and return

retB=["test3","test4"];

But in EF8 without UseCompatibilityLevel(120) uses previous cached version (with listA), so result is

retB=["test1","test2"];  //it is an error

[JonathanMagnan]

Hello @fenomeno83 ,

Thank you for reporting.

We haven't tested it yet, but indeed we understand very well how this new OPENJSON can cause an issue for this feature.

We will look at it.

Best Regards,

Jon

[JonathanMagnan]

Hello @fenomeno83 ,

Just to let you know that a fix has been merged in our master.

The fix will be available on next Tuesday 13.

Best Regards,

Jon

[JonathanMagnan]

Hello @fenomeno83 ,

A new version is now available. The latest release note now contains this fix: https://github.com/zzzprojects/EntityFramework-Plus/releases/tag/8.102.1.0

Let us know if everything works as expected.

Best Regards,

Jon