generated query has double quotes at the table alias and the column name

[hendriksteinhorst]

trafficstars

The issue

I'm trying to use EF core with Firebird 2.5 Database. Scaffolding the context works fine. If i try to run a query I get an error saying that the token is unknown. Logging the generated SQL query shows that there are unecessary double quotes, which leads to an SQL Exception.

e.g. SELECT "p"."ID", "p"."name", "p"."age" FROM People as "p" but it should be "SELECT p.ID, p.name, p.age from People p"

Steps to reproduce

1. Create new Project (in my case asp.net core 2.0 webapi)
2. Scaffold context of existing FireBird 2.5 (Dialiect1) Database
3. Query data

Error details

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
Stack trace:
FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 11
. ---> Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 11

Further technical details

Firebird version: 2.5 dialect 1 EntityFrameworkCore.FirebirdSql version: 2.0.11.6 Asp.net core 2.0

[ralmsdeveloper]

In fact what is missing is the escape of People should be "People". I will resolve this

[hendriksteinhorst]

I'm not sure if I understand you correctly. Wrapping the "People" in double quotes gives the same error when executing the query with LinqPad.

SELECT "p"."ID", "p"."Name", "p"."Vorname"
FROM "Personen" AS "p"

produces the following error:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 11
.

This is the dot between "p" and "ID". Removing all double quotes works for me.

[hendriksteinhorst]

sorry, didn't meant to close the issue

[ralmsdeveloper]

Can you put your class of people here?

And the SQL script of the database person table

[ralmsdeveloper]

It may be LINQPad that does not interpret this, but if running RAWSql on the database will work!

[hendriksteinhorst]

Hi, sorry for the late response. This is my class of People

public partial class People
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Givenname { get; set; }
    }

this is my context

public class TestContext : DbContext
    {
        public TestContext(DbContextOptions contextOptions) : base(contextOptions)
        {
        }

        public virtual DbSet<People> People { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<People>(entity =>
            {
                entity.ToTable("PEOPLE");

                entity.Property(e => e.Id).HasColumnName("ID");

                entity.Property(e => e.Givenname)
                    .HasColumnName("GIVENNAME")
                    .HasColumnType("VARCHAR(60.00000000000000)");

                entity.Property(e => e.Name)
                    .HasColumnName("NAME")
                    .HasColumnType("VARCHAR(60.00000000000000)");
            });
        }
    }

and my Table

CREATE TABLE PEOPLE (
    ID         INTEGER NOT NULL,
    NAME       VARCHAR(60),
    GIVENNAME  VARCHAR(60)
);

ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY (ID);

[ralmsdeveloper]

@hendriksteinhorst You could test with version 2.1-RC1: https://www.nuget.org/packages/EntityFrameworkCore.FirebirdSQL/2.1.0-rc1-final

[hendriksteinhorst]

with version 2.1-RC1 there are double quotes around "People", but the error still persists:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (50ms) [Parameters=[], CommandType='Text', Comm andTimeout='30'] SELECT "p"."ID", "p"."GIVENNAME", "p"."NAME" FROM "PEOPLE" AS "p" FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 11 . ---> Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 11 .

[ralmsdeveloper]

Thanks for the test, I'll simulate here!

[ralmsdeveloper]

I did the repro here and had success with version 2.1-RC1

CREATE TABLE "PEOPLE" (
    "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
    "NAME" VARCHAR(60),
    "GIVENNAME" VARCHAR(60),
    CONSTRAINT "PK_PEOPLE" PRIMARY KEY ("ID")
);

INSERT INTO "PEOPLE" ("GIVENNAME", "NAME")
VALUES (@p0, @p1)
RETURNING "ID";

Parameters:
Name:@p0	Type:Array	Used Value:Test
Name:@p1	Type:Array	Used Value:Ralms

SELECT "p"."ID", "p"."GIVENNAME", "p"."NAME"
FROM "PEOPLE" AS "p"
WHERE "p"."ID" > 0

[hendriksteinhorst]

can you provide a sample solution for me so that i can check this against my database?

[ralmsdeveloper]

public class Issue28Context : DbContext
{
    public virtual DbSet<People> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = @"User=SYSDBA;Password=masterkey;Database=..\..\..\Issue28.fdb;DataSource=localhost;Port=3050;";

        optionsBuilder
            .UseFirebird(connectionString)
            .ConfigureWarnings(c => c.Log(CoreEventId.IncludeIgnoredWarning));

        var loggerFactory = new LoggerFactory()
            .AddConsole()
            .AddDebug();

        optionsBuilder.UseLoggerFactory(loggerFactory);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<People>(entity =>
        {
            entity.ToTable("PEOPLE");

            entity.Property(e => e.Id).HasColumnName("ID");

            entity.Property(e => e.Givenname)
                .HasColumnName("GIVENNAME")
                .HasColumnType("VARCHAR(60)");

            entity.Property(e => e.Name)
                .HasColumnName("NAME")
                .HasColumnType("VARCHAR(60)");
        });
}

[Fact]
public void ReproIssue28()
{
    using (var ctx = new Issue28Context())
    {
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();
        ctx.People.Add(new People
        {
            Givenname = "Test",
            Name = "Ralms"
        });
        ctx.SaveChanges();

        var peoples = ctx
            .People
            .AsNoTracking()
            .Where(p => p.Id > 0)
            .ToList();

        Assert.Single(peoples);
    }
}

[MaceWindu]

Sorry to butt-in, but quoted identifiers supported only for Dialect 3, and @hendriksteinhorst says he use dialect 1. Also quoted identifiers made them case sensitive. So quotation should be configurable.

[ralmsdeveloper]

Fix it for version 2.1.

@MaceWindu Thanks! I did not look at the dialect!