Entity framework with Firebird throws dynamic SQL error

Question

I've got stuck with FbException

SQL error code = -104

Token unknown - line 2, column 4

.

when trying to run this code

var result = from x in _context.Bunts
                         select x;

I've checked the query which was produced by EF

SELECT 
"A"."BUNTCODE" AS "BUNTCODE", 
"A"."BUNTNAME" AS "BUNTNAME", 
"A"."BUNTDIAM" AS "BUNTDIAM"
FROM "BUNTS" AS "A"

So server thinks that something is wrong with dot after "A" statement. But this query runs just fine in IBExpert on the same machine. How to fix this problem?

I'm using:

Firebird server v2.1.6.18547

EntityFramework v6.0.0.0

EntityFramework.Firebird v4.5.2.0

FirebirdSql.Data.FirebirdClient 4.5.2.0

Answer 1

The error suggests you are connecting using dialect 1. Dialect 1 is the old dialect of Interbase 5 and earlier and should be considered deprecated (although unfortunately 15 years on it is still supported by Firebird...).

In dialect 1 it is not possible to quote object names, and double quotes are used for strings (instead of single quotes in dialect 3 and the SQL standard). When your query is parsed in dialect 1, Firebird sees "A" as a string constant, and the following dot (.) is not expected by the parser.

Switching to dialect 3 should fix this, however if you do that, make sure that your database itself is also dialect 3, otherwise you might get other unexpected behavior like certain datatypes not working, or errors, etc.