Entity FC 6 Casting issues in query

[Diego De la Fuente]

I have started testing entity framework core, to replace Ado.Net (running on Framework 4.x), using:

- FirebirdSql.EntityFrameworkCore.Firebird (9.1.1)

- <TargetFramework>net6.0</TargetFramework>

Server Versión: WI-V2.5.9.27139 Firebird 2.5 (Old Fb database with no Charset)

Trying with these changes in optionBuilder.

This is an example using a simple web Api. Not a real production environment

This line belongs to program.cs file

builder.Services.AddDbContext<ApplicationDbContext>(opt => opt.UseFirebird("name=DefaultConnection",

                                                                            p => p.WithExplicitStringLiteralTypes(false)
                                                                                   .WithExplicitParameterTypes(true)));

case 1

            var pacientes = await _context.MAESTRO_PACIENTES
               .Where(x => x.NOMBRE2.StartsWith(nombre))
               .OrderBy(o => o.NOMBRE2)
               .Take(10)
               .ToListAsync();

Sql syntax

Select 

    "m"."CODIGO",
    "m"."NOMBRE2",

    ...all the other fields...

FROM
    "MAESTRO_PACIENTES" AS "m"
WHERE
    (CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191)) = _UTF8 '')
    OR (
        (
            (
                UPPER("m"."NOMBRE") LIKE CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191)) || _UTF8 '%'
            )
            AND (
                LEFT(
                    UPPER("m"."NOMBRE"),
                    CHAR_LENGTH(CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191)))
                ) = CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191))
            )
        )
        OR (CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191)) = _UTF8 '')
    )

Plan
--------------------------------------------------------------------------------
PLAN (m NATURAL)

Reads from disk to cache = 30.640
Writes from cache to disk = 0
Fetches from cache = 3.599.077

****************************************************************************

Case 2

            var pacientes = await _context.MAESTRO_PACIENTES
                .Where(x => x.NOMBRE2.CompareTo(nombre) >= 0)
                .OrderBy(o => o.NOMBRE2)
                .Take(10)
                .ToListAsync();

Sql syntax

Select 

    "m"."CODIGO",
    "m"."NOMBRE2",

    ...all the other fields...

FROM

    "MAESTRO_PACIENTES" AS "m"
WHERE
    "m"."NOMBRE2" >= CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191))
ORDER BY
    "m"."NOMBRE2"

Plan

--------------------------------------------------------------------------------
PLAN (m ORDER MAESTRO_PACIENTES_IDX15 INDEX (MAESTRO_PACIENTES_IDX15))
Reads from disk to cache = 2.347
Writes from cache to disk = 0
Fetches from cache = 2.740

These are my doubts

1) Why I have all that big "where" sentence in case 1 ?

I would like to allow using "StartsWith". It is more understandable than the command

"CompareTo(nombre) >= 0"

I tried to add some options to avoid casting. But I has no effect.

From: https://github.com/FirebirdSQL/NETProvider/issues/857

builder.Services.AddDbContext<ApplicationDbContext>(opt => opt.UseFirebird("name=DefaultConnection", p => p.WithExplicitStringLiteralTypes(false)

                                                                                   .WithExplicitParameterTypes(true)));

I tried to create a new field "NOMBRE3" with UTF8 charset. No changes

 NOMBRE3                   VARCHAR(30) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI

Regards

Diego

[Jiří Činčura]

First of all Firebird 2.5 is unsupported version (both in general and more importantly in EF Core provider)

> _case 1_

> var pacientes = await _context.MAESTRO_PACIENTES

> .Where(x => x.NOMBRE2.*StartsWith*(nombre))

> .OrderBy(o => o.NOMBRE2)
> .Take(10)
> .ToListAsync();
>

> _Sql syntax_

> Select
> "m"."CODIGO",
> "m"."NOMBRE2",
> ...all the other fields...
> FROM
> "MAESTRO_PACIENTES" AS "m"
> WHERE
> (CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191)) = _UTF8 '')
> OR (
> (
> (
> UPPER("m"."NOMBRE") LIKE CAST('DE LA FUENTE DIEGO' AS
> VARCHAR(8191)) || _UTF8 '%'
> )
> AND (
> LEFT(
> UPPER("m"."NOMBRE"),
> CHAR_LENGTH(CAST('DE LA FUENTE DIEGO' AS
> VARCHAR(8191)))
> ) = CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191))
> )
> )
> OR (CAST('DE LA FUENTE DIEGO' AS VARCHAR(8191)) = _UTF8 '')
> )

Please create a test-case for this. I don't see where the UPPER is coming from. But it's been a long time I saw that translator.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Diego De la Fuente]

Using Fb 3.x

Server Version Info

---------------------------------------------------------------------------
Server Version: LI-V3.0.7.33374 Firebird 3.0
Server Implementation: Firebird/Linux/AMD/Intel/x64
Service Version: 2

I don't see any "Upper" syntax here.

WHERE
    (
        CAST(@__nombre_0 AS VARCHAR(8191)) = CAST(_UTF8 '' AS VARCHAR(1) CHARACTER SET UTF8)

    )
    OR (
        (
            (

                "m"."NOMBRE" LIKE CAST(@__nombre_0 AS VARCHAR(8191)) || CAST(_UTF8 '%' AS VARCHAR(1) CHARACTER SET UTF8)
            )
            AND (
                LEFT(
                    "m"."NOMBRE",
                    CHAR_LENGTH(CAST(@__nombre_0 AS VARCHAR(8191)))
                ) = CAST(@__nombre_0 AS VARCHAR(8191))
            )
        )
        OR (
            CAST(@__nombre_0 AS VARCHAR(8191)) = CAST(_UTF8 '' AS VARCHAR(1) CHARACTER SET UTF8)
        )
    )
ORDER BY
    "m"."NOMBRE" ROWS (CAST(@__p_1 AS INTEGER))

Plan
--------------------------------------------------------------------------------
PLAN (m ORDER MAESTRO_PACIENTES_IDX1)

Reads from disk to cache = 1.215.903
Writes from cache to disk = 1
Fetches from cache = 4.140.314

-------------------------------------------------------------------------------

I have changes manually the syntax, with only this line (This is a manual query. not made by EFCore)

FROM
    "MAESTRO_PACIENTES" AS "m"
WHERE

    "m"."NOMBRE" LIKE 'DE LA FUENTE' || '%'
ORDER BY
    "m"."NOMBRE" ROWS (CAST(@__p_1 AS INTEGER))

Plan
PLAN (m ORDER MAESTRO_PACIENTES_IDX1)

Reads from disk to cache = 1.215.902
Writes from cache to disk = 1
Fetches from cache = 4.140.311

-------------------------------------------------------------------------------

With this sentence (This is a manual query. not made by EFCore)

FROM
    "MAESTRO_PACIENTES" AS "m"
WHERE

    "m"."NOMBRE" LIKE 'DE LA FUENTE DIEGO%'
ORDER BY
    "m"."NOMBRE" ROWS (CAST(@__p_1 AS INTEGER))

Plan
PLAN (m ORDER MAESTRO_PACIENTES_IDX1)

Reads from disk to cache = 7

Writes from cache to disk = 0

Fetches from cache = 44

-----------------------------------------------------------------------------------

With all of these tests I've manually changed to this syntax 

WHERE
    (
        CAST(@__nombre_0 AS VARCHAR(8191)) = CAST(_UTF8 '' AS VARCHAR(1) CHARACTER SET UTF8)

    )
    OR (
        (
            (

                "m"."NOMBRE" LIKE 'DE LA FUENTE%'
            )
            AND (
                LEFT(
                    "m"."NOMBRE",
                    CHAR_LENGTH(CAST(@__nombre_0 AS VARCHAR(8191)))
                ) = CAST(@__nombre_0 AS VARCHAR(8191))
            )
        )
        OR (
            CAST(@__nombre_0 AS VARCHAR(8191)) = CAST(_UTF8 '' AS VARCHAR(1) CHARACTER SET UTF8)
        )
    )
ORDER BY
    "m"."NOMBRE" ROWS (CAST(@__p_1 AS INTEGER))

Plan
PLAN (m ORDER MAESTRO_PACIENTES_IDX1 INDEX (MAESTRO_PACIENTES_IDX1))

Reads from disk to cache = 1.400

Writes from cache to disk = 0

Fetches from cache = 1.992