Open hendriksteinhorst opened 6 years ago
In fact what is missing is the escape of People should be "People". I will resolve this
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.
sorry, didn't meant to close the issue
Can you put your class of people here?
And the SQL script of the database person table
It may be LINQPad that does not interpret this, but if running RAWSql on the database will work!
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);
@hendriksteinhorst You could test with version 2.1-RC1: https://www.nuget.org/packages/EntityFrameworkCore.FirebirdSQL/2.1.0-rc1-final
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 .
Thanks for the test, I'll simulate here!
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
can you provide a sample solution for me so that i can check this against my database?
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);
}
}
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.
Fix it for version 2.1.
@MaceWindu Thanks! I did not look at the dialect!
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
Error details
If you are seeing an exception, include the full exceptions details (message and stack trace).
Further technical details
Firebird version: 2.5 dialect 1 EntityFrameworkCore.FirebirdSql version: 2.0.11.6 Asp.net core 2.0