Entity Framework and Firebird DB Dialect 1 - not generating compatible sql

c# entity-framework entity-framework-6 firebird firebird2.5

Question

I am trying to setup a project with EF on top of a firebird DB but it is generating extra quotes that the Firebird db is rejecting.

The db already exists and there is a matching record for this query there. Error is FbException: Dynamic SQL Error SQL error code = -104 Token unknown - line 2, column 4 .

However if I remove the quotes it generates in the sql the query runs.

Example

using (var context = new ContextManager())
{
     var accounts = context.Accounts.Where(x => x.OBJID == 1).ToList();
}

generates sql

SELECT 
"A"."OBJID" AS "OBJID"
FROM "ACCOUNT" AS "A"

Config file

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="FirebirdSql.Data.EntityFramework6.FbConnectionFactory, EntityFramework.Firebird" />
    <providers>
      <provider invariantName="FirebirdSql.Data.FirebirdClient" type="FirebirdSql.Data.EntityFramework6.FbProviderServices, EntityFramework.Firebird" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="FirebirdSql.Data.FirebirdClient" publicKeyToken="3750abcc3150b00c" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>

<system.data>
        <DbProviderFactories>
            <remove invariant="FirebirdSql.Data.FirebirdClient" />
            <add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient" description=".NET Framework Data Provider for Firebird" type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient" />
        </DbProviderFactories>
    </system.data></configuration>

Context class

public class ContextManager : DbContext
    {
        public ContextManager() : base(new FbConnection("database=xxxx.fdb;DataSource=localhost;user=xxx;password=xxxxx"),true)
        {

        }

        public ContextManager(string connString) : base(new FbConnection(connString), true)
        {
            //this.Configuration.LazyLoadingEnabled = false;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
        }

        public DbSet<Account> Accounts { get; set; }
    }

Account Model

[Table("ACCOUNT")]
    public class Account
        {
            [Key]
            public int OBJID { get; set; }
        }

Update - checked and saw dialect 1 was set so I updated the query string to denote it, the query did not change but the error did FbException: Dynamic SQL Error SQL error code = -104 Token unknown - line 2, column 4 .

enter image description here

1
2
3/28/2018 7:39:39 PM

Accepted Answer

The problem is that your database is dialect 1, and Entity Framework is quoting the object names. Unfortunately quoted object names are not supported in dialect 1, and are instead interpreted as strings. This leads to the token unknown errors, as the parser isn't expecting strings, but object names.

It has been a while since I did anything with entity framework (I normally program in Java), and I'm not sure if there is an option to disable quoting.

According to this ticket in the Firebird bug tracker, the Firebird Entity Framework support does not and will not support dialect 1: DNET-580.

So unless you upgrade your database to dialect 3, it looks like you will not be able to use Entity Framework. Be aware that upgrading from dialect 1 to dialect 3 may not be simple, especially not if your application(s) rely on dialect 1 specific syntax (eg double quotes for strings) or behaviour (floating point division for integers).

Workaround

A potential workaround is to explicitly specify your connection dialect as dialect 3 (connection property Dialect=3 for the Firebird ADO.net provider). This will allow you to query the database using dialect 3 syntax. Be aware there might be some differences in behavior, as dialect 3 has a number of different rules.

I'm a bit surprised though, because I tested a number of recent versions of the Firebird ADO.net provider and it defaults to dialect 3 unless explicitly set to 1, so this shouldn't be an issue at all.

Similar problems with dialect 3

This part of the answer assumes a dialect 3 database where the table is actually called ACCOUNTS, and not Accounts. This would not result in a token unknown, but in a table unknown error.

In this situation, the problem is that the default configuration derives names from objects, and it will quote names. Quoted names in dialect 3 are case sensitive (unquoted names are case insensitive but are stored (and compared) as uppercase).

What you can do is override the table name by annotating your object:

[Table("ACCOUNTS")]
public class Account
{
    [Key]
    public int OBJID { get; set; }
}

Another option you could use is the fluent API, but I have never used it myself, so I'm not entirely sure where you need to specify this (I think in your DbContext).

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  //Changing Database table name to Metadata
  modelBuilder.Entity<Account>()
      .ToTable("ACCOUNTS");
}

There might be other options to influence naming conventions.

See also Entity Framework Code First - Changing a Table Name

2
3/30/2018 2:44:26 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow