PredicateBuilder cannot fill in parameter in SQLite when building expression.

[agreentejada]

Came across this error on a side project with EF Core 6.0 for Sqlite.

I am attempting to query a search query based on two properties of an IdentityUser, the username and the email. This IdentityUser, called AppUser in code, has a many-many relationship with an Accounts entity.

            // string searchQuery is provided by the function args.
            var predicate = PredicateBuilder.New<AppUser>();
            searchQuery = searchQuery?.Trim().ToUpper();

            if (!string.IsNullOrWhiteSpace(searchQuery))
            {
                predicate = predicate.Or(X => X.UserName.Contains(searchQuery));
                predicate = predicate.Or(X => X.NormalizedEmail.Contains(searchQuery));

                // Utility method to remove all digits from a string.
                searchQuery = Utilities.NumbersOnly(searchQuery);
                if (!string.IsNullOrWhiteSpace(searchQuery))
                {
                    predicate = predicate.Or(X => X.PhoneNumber.Contains(searchQuery));
                }
            }
            else
            {
                predicate = predicate.And(X => true);
            }

            IQueryable<AppUser> users = _db.DbContext.Users.AsNoTracking().Include(X => X.Accounts);
            users = users.AsExpandableEFCore().Where(predicate);

            return await users.ToListAsync();

Interestingly, when I go to look at the Sqlite query, I can't find the searchQuery object.

[00:32:37 INF] Executed DbCommand (9ms) [Parameters=[@__searchQuery_0=''], CommandType='Text', CommandTimeout='30']
SELECT "t"."Id", "t"."AccessFailedCount", "t"."Activated", "t"."ConcurrencyStamp", "t"."Email", "t"."EmailConfirmed", "t"."LockoutEnabled", "t"."LockoutEnd", "t"."NormalizedEmail", "t"."NormalizedUserName", "t"."PasswordHash", "t"."PhoneNumber", "t"."PhoneNumberConfirmed", "t"."ResetPassword", "t"."SecurityStamp", "t"."TwoFactorEnabled", "t"."UserName", "t0"."Id", "t0"."Name", "t0"."AccountsId", "t0"."UsersId"
FROM (
    SELECT "u"."Id", "u"."AccessFailedCount", "u"."Activated", "u"."ConcurrencyStamp", "u"."Email", "u"."EmailConfirmed", "u"."LockoutEnabled", "u"."LockoutEnd", "u"."NormalizedEmail", "u"."NormalizedUserName", "u"."PasswordHash", "u"."PhoneNumber", "u"."PhoneNumberConfirmed", "u"."ResetPassword", "u"."SecurityStamp", "u"."TwoFactorEnabled", "u"."UserName"
    FROM "Users" AS "u"
    WHERE ((@__searchQuery_0 = '') OR (instr("u"."FullName", @__searchQuery_0) > 0)) OR ((@__searchQuery_0 = '') OR (instr("u"."NormalizedEmail", @__searchQuery_0) > 0))
    ORDER BY "u"."Id"
) AS "t"
LEFT JOIN (
    SELECT "a"."Id", "a"."Name", "u0"."AccountsId", "u0"."UsersId"
    FROM "UserstoAccounts" AS "u0"
    INNER JOIN "Accounts" AS "a" ON "u0"."AccountsId" = "a"."Id"
) AS "t0" ON "t"."Id" = "t0"."UsersId"
ORDER BY "t"."Id", "t0"."AccountsId", "t0"."UsersId"

The key fact from this debug log is that @__searchQuery_0='', even though I explicitly enter in an arg for searchQuery. From the debugger, this is what I get if I peek at the predicate object.

{X => (X.UserName.Contains(value(TaxDesktop.API.UsersController+<>c__DisplayClass7_0).searchQuery) OrElse X.NormalizedEmail.Contains(value(TaxDesktop.API.UsersController+<>c__DisplayClass7_0).searchQuery))}

and attempting to peek at predicate.Compile() shows an exception.

[sdanyliv]

1. AsExpandable Is not needed here, there is nothing to expand.
2. Do not reuse searchQuery for searching digits, assign to other variable.

[StefH]

Hello @agreentejada, has the answer provided by @sdanyliv enough detail?