QueryCache misses for dynamically created queries with ConstantExpresssions or nested MemberExpressions from closure

[Tasteful]

I got a lot of dupplicated entries in the MemoryCache that origin from EF and many of them contains the same sql query and is generated from the exact same code.

Example the following that generate an SQL query that is duplicated in cache more than 16 000 times.

When I checking the entity in the MemoryCache we can se a lot of repeated entries that is 14 338 bytes each but contains the same query.

The only "special" that I can remember that I have done is that the query is built with System.Linq.Expressions.Expression that is added to the query with .Where(expression).

What is the best approach to find the reason for this cache misses?

Further technical details

EF Core version: 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017

[Tasteful]

It seems that is the usage of System.Linq.Expressions.Expression that is bad in my usage case.

• Is there any reason to not use the string representation as the cache key?
• I can't find a way to change the sliding timeout for the cache entry per query, is that possible?

[Tasteful]

Is it the same when using ICollection<T>.Contains?

example

var values = new List<Guid>();
values.Add(Guid.NewGuid());
values.Add(Guid.NewGuid());
values.Add(Guid.NewGuid());
dbContext.Table1.Where(x => values.Contains(x.Id)).ToList();

For the contains it generate an SQL with the values in the statement, not as parameters. Will the cache handle that to replace the parameters?

[anpete]

@Tasteful Sorry you are hitting this. Can you post the complete LINQ query that causes the cache miss?

[Tasteful]

@anpete I will try to make a breakout of relevant code parts.

[smitpatel]

EF Core version: 1.1.1

Any reason you haven't upgraded to 1.1.2?
You filed issue in past about memory leak which was fixed in 1.1.2 #8063 😄

[anpete]

Oh yeah, definitely try that 😄

[Tasteful]

Was it the one that i found with Db.Set, then I already have that fix localy. Was it any other fixes in 1.1.2 regarding to query-cache?

It was found in production system and want to be able to produce same issue localy on the same version before upgrade and trying any fixes.

[anpete]

@Tasteful #7899 is the only other one I can see, but I suspect you are not hitting that.

[Tasteful]

@anpete Tested this with both the 1.1.1 and 1.1.2 release and both version have the same behavour

I have put a test-repo here https://github.com/Tasteful/bugs/blob/query-cache2 and if you check the https://github.com/Tasteful/bugs/blob/query-cache2/MemoryUsage/Program.cs and specific the method TestContains that is working, will produce the following expression tree

.Call System.Linq.Queryable.Where(
    .Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MemoryUsage.MyTable1]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MemoryUsage.MyTable1]),
    '(.Lambda #Lambda1<System.Func`2[MemoryUsage.MyTable1,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[MemoryUsage.MyTable1,System.Boolean]>(MemoryUsage.MyTable1 $item) {
    .Call (.Constant<MemoryUsage.Program+<>c__DisplayClass0_0>(MemoryUsage.Program+<>c__DisplayClass0_0).itemIds).Contains($item.Id)
}

and compare that to TestContainsFromExpression-method that create the same expression tree but is not working.

.Call System.Linq.Queryable.Where(
    .Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MemoryUsage.MyTable1]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MemoryUsage.MyTable1]),
    '(.Lambda #Lambda1<System.Func`2[MemoryUsage.MyTable1,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[MemoryUsage.MyTable1,System.Boolean]>(MemoryUsage.MyTable1 $item) {
    .Call (.Constant<MemoryUsage.Program+<>c__DisplayClass0_4>(MemoryUsage.Program+<>c__DisplayClass0_4).itemIds).Contains($item.Id)
}

If I not can add and use the Contains method dynamic I will get a limit on the max parameter count that sql server can handle for the items in the list.

Then we have method TextExpressionFunc that is working and TestExpressionConst that not is working,.

For all versions that not is working I found that we use the Expression.Constant and that is probably why I got the repeated query in cache of size 14 338 bytes.

I will continue with the larger SQL that was duplicated 16 000 times and see if I can make a simple replication there also.

[Tasteful]

If I remember correct the QueryCache is caching all the queries infinity, is that a big benefit to do that instead of have a sliding cache timeout to clear out queries that not is used frequently?

[anpete]

The first thing is that there should only be one entry for each query, so we will look at that (thanks for the repro).

We are using the ASP.NET MemoryCache, which does have some configuration options.

[Tasteful]

What I can see in https://github.com/aspnet/EntityFramework/blob/dev/src/EFCore/Query/Internal/CompiledQueryCache.cs is that no options are set when the item is adding to the cache. The old v1 implementation of that is removing 10% of cache entries when gc gen 2 is happening. This will be changed in v2 aspnet/Announcements#228 and if no expiration time is set the cache entries will probably live forever.

[anpete]

@Tasteful I guess we could set a value for SlidingExpiration as a mitigation for these kinds of bugs. I think you could also manually call the Compact method as a workaround.

[Tasteful]

For the one where the same SQL is stored multiple times the callstack is the following and result down into a stringbuilder. The string builder internal use an char arrayto keep track of the data. I'm not sure if this string builder is populated with data for each query or that the StringBuilder can be replaced with a string after it is populated to not keep track of all the chars.

The expression for that looks like

.Call System.Linq.Queryable.Where(
    .Call System.Linq.Queryable.Where(
        .Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MemoryTest.Application.Products.Data.VariantEntity]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[MemoryTest.Application.Products.Data.VariantEntity]),
        '(.Lambda #Lambda1<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity,System.Boolean]>)),
    '(.Lambda #Lambda2<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity,System.Boolean]>(MemoryTest.Application.Products.Data.VariantEntity $item)
{
    .Call System.Linq.Queryable.Count(
        .Call System.Linq.Queryable.Where(
            .Call ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).dbContext).Set()
            ,
            '(.Lambda #Lambda3<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity+FieldDataEntity,System.Boolean]>)),
        (.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).exp)
    > 0 || .Call System.Linq.Queryable.Count(
        .Call System.Linq.Queryable.Where(
            .Call ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).dbContext).Set()
            ,
            '(.Lambda #Lambda4<System.Func`2[MemoryTest.Application.Products.Data.BaseProductEntity+FieldDataEntity,System.Boolean]>))
        ,
        .Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).baseProductExp)
    > 0
}

.Lambda #Lambda2<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity,System.Boolean]>(MemoryTest.Application.Products.Data.VariantEntity $item)
{
    .Call System.Linq.Queryable.Count(
        .Call System.Linq.Queryable.Where(
            .Call ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).dbContext).Set()
            ,
            '(.Lambda #Lambda5<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity+FieldDataEntity,System.Boolean]>)),
        (.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).exp)
    > 0 || .Call System.Linq.Queryable.Count(
        .Call System.Linq.Queryable.Where(
            .Call ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).dbContext).Set()
            ,
            '(.Lambda #Lambda6<System.Func`2[MemoryTest.Application.Products.Data.BaseProductEntity+FieldDataEntity,System.Boolean]>))
        ,
        .Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).baseProductExp)
    > 0
}

.Lambda #Lambda3<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity+FieldDataEntity,System.Boolean]>(MemoryTest.Application.Products.Data.VariantEntity+FieldDataEntity $x)
{
    $x.OwnerId == $item.SystemId && $x.Id == ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).f).Id
    && $x.Culture == (.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).cultureName
}

.Lambda #Lambda4<System.Func`2[MemoryTest.Application.Products.Data.BaseProductEntity+FieldDataEntity,System.Boolean]>(MemoryTest.Application.Products.Data.BaseProductEntity+FieldDataEntity $x)
{
    ($x.Owner).SystemId == $item.BaseProductSystemId && $x.Id == ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).f).Id
    && $x.Culture == (.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).cultureName
}

.Lambda #Lambda5<System.Func`2[MemoryTest.Application.Products.Data.VariantEntity+FieldDataEntity,System.Boolean]>(MemoryTest.Application.Products.Data.VariantEntity+FieldDataEntity $x)
{
    $x.OwnerId == $item.SystemId && $x.Id == ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).f).Id
    && $x.Culture == (.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).cultureName
}

.Lambda #Lambda6<System.Func`2[MemoryTest.Application.Products.Data.BaseProductEntity+FieldDataEntity,System.Boolean]>(MemoryTest.Application.Products.Data.BaseProductEntity+FieldDataEntity $x)
{
    ($x.Owner).SystemId == $item.BaseProductSystemId && $x.Id == ((.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).f).Id
    && $x.Culture == (.Constant<MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1>(MemoryTest.Application.Products.Data.Querying.VariantFieldQueryExpressionInfoBuilder+<>c__DisplayClass3_1).CS$<>8__locals1).cultureName
}

and I can see that .Constant is used frequently in that expression also.

The inner part

.Lambda #Lambda1<System.Func`2[MemoryTest.FieldFramework.IFieldData,System.Boolean]>(MemoryTest.FieldFramework.IFieldData $x) {
    $x.TextValue == .Constant<MemoryTest.Application.FieldFramework.FieldTypes.TextFieldMetadata+TextField+<>c__DisplayClass2_0>(MemoryTest.Application.FieldFramework.FieldTypes.TextFieldMetadata+TextField+<>c__DisplayClass2_0).v .As
    System.String
}

is created from an method as below and added to the query with .Where(GetExpression("my value")).

private Expression<Func<IFieldData, bool>> GetExpression(object v)
{
    return x => x.TextValue == v as string;
}

@anpete