Query cache makes query filter Func irrelevant after first execution

[joonatanu-softwerk]

Describe your issue

When query filter is defined for an entity, then the contents of the query filter is only used once. The query filter Func is executed, but it is not being used when SQL is generated.

Below is example code that uses Random in query filter Func. Whether the caching is enabled or disabled, the random number is always generated and printed out from the query filter Func. But when SQL is composed, it is always containing only the first generated number when caching is enabled.

Steps to reproduce

Following code defines query filter that uses Random to add a WHERE condition to a query. The Random is also printed out from the query filter to show that the query filter Func is being executed. The code prints out SQL text 3 times. On the top, it has flag to enable/disable caching.

When caching is enabled, it shows different random numbers generated for each SQL, but the SQL itself is still containing the old (first) number. When caching is disabled, freshly generated number is always used in SQL text.

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.Mapping;

var isCacheEnabled = true;

using var dbCtx = new MyDbCtx(new DataOptions(new ConnectionOptions()
{
    ConnectionString = "Data Source=:memory:;Version=3;New=True;",
    ProviderName = ProviderName.SQLite
}).UseDisableQueryCache(!isCacheEnabled));

dbCtx.CreateTable<User>();

var query = dbCtx.GetTable<User>().Select(x => x.Id);

Console.WriteLine(query);
Console.WriteLine(query);
Console.WriteLine(query);

var result = query.ToList();

Console.ReadLine();


public class MyDbCtx : DataConnection
{
    public MyDbCtx(DataOptions options) : base(options)
    {
        var builder = new FluentMappingBuilder();

        builder.Entity<User>().HasQueryFilter((q, ctx) =>
        {
            var r = Random.Shared.Next();
            Console.WriteLine(r);

            return q.Where(x => x.Name.Contains(r.ToString()));
        });

        builder.Build();

        AddMappingSchema(builder.MappingSchema);
    }
}

public record User
{
    public Guid Id { get; set; }

    public string Name { get; set; } = null!;
}

Environment details

Linq To DB version: 5.4.1

Database (with version): SQLite

ADO.NET Provider (with version): System.Data.SQLite.Core 1.0.118

Operating system: Windows 11

.NET Version: 8.0

[sdanyliv]

Should be fixed in upcoming V6 release. Anyway, I do not recommend to make query filter very dynamic. It will cause that translated query cache will not hit and queries will be translated again and again.

[joonatanu-softwerk]

Thanks!

I've stumbled upon this as i really have another potential bug to report. (I'm making a simplified example right now for ticket)
In general gist, i want to add associations on entities based on their interfaces by scanning through those entities and their interfaces and attributes. But it seems that when query filter is accessing data through association that is defined through expression (not with "ThisKey" and "OtherKey"), then it fails. (Something along the lines of that property not being association or not being column.) I'll make a separate ticket for that.

Is there a way to maybe only disable query cache inside a query filter only on those specific situations so i would not need to disable query caching througout the whole system?

[sdanyliv]

Can you show your real query filter?

[joonatanu-softwerk]

I don't have the full version that causes a bug.
The scenario is that i have Permission table that links to User table. And whenever i query any table (of course in real life i have attributes to turn this on/off) it should filter out the rows that i don't have permission to. The permission table doesn't have "millions" of foreign keys, but just a "free-floating" GUID for linking up.

I tried joining my entity in query filter against permissions, but it has some flaws.
1: #4496
2: After joining all modifications are impossible.

Then i tried using assembly scanning and then interface-based association defining approach. If i have, let's say "Post" entity and it implements "IHasPermissions", then it must define property of "List Permissions", but it will not have attributes defining the association. This is being done during DataConnection initialization through assembly scanning.

That almost worked, but then we have a requirement that it's not always the primary key that is used for permission linking. So i may not want to use "Post.Id" for linking with permissions, but "Post.PostType.Id" for example. (assuming we have PostType entity as well)
So IHasPermission also requires you to provide "static abstract Expression<Func<T, Guid>> PermissionIdGetter()" method. And this is where it eventually breaks. While i can select data with my queries through Post.Permissions now, then during the query filter phase, it is unable to properly process that expression func.

I'll have a simplified example soon. :)
EDIT: Here's that other ticket: #4509