I'm using LLBLGen Pro runtime version 3.1.11.208 . I'm using Firebird 2.5, with the Firebird .NET client version 2.6. I'm also using .NET 4.0 .

When I issue a query like the following: ( I ran the query in LINQPad)

using ( IDataAccessAdapter da = DataAccessAdapterFactory.CreateDataAccessAdapter() )
{
    LinqMetaData md = new LinqMetaData(da);
    
    ( from b in md.BlogEntry
      from c in b.Comments
      select b ).Take(10).Dump(1);
}

I receive the error: Dynamic SQL ErrorSQL error code = -104Token unknown - line 1, column 2310 .

The SQL generated by the LINQ query is:

SELECT DISTINCT FIRST 10 "LPA_L1"."BlogEntryId", "LPA_L1"."DateInserted", "LPA_L1"."IsPublic", "LPA_L1"."LastTimeModified", "LPA_L1"."TimeInserted", "LPA_L1"."Title", "LPA_L1"."ViewPath" 
FROM ( "BlogEntry" "LPA_L1"  INNER JOIN "Comment" "LPA_L2"  ON  "LPA_L1"."BlogEntryId"="LPA_L2"."BlogEntryId")

If I run this query directly against Firebird outside of the .NET environment, I receive the same error. Unfortunately, the Firebird documentation doesn't seem to address the order of these elements in a select, but if I put the DISTINCT after the FIRST 10, then the query works.

So the following query works:

SELECT FIRST 10 DISTINCT "LPA_L1"."BlogEntryId", "LPA_L1"."DateInserted", "LPA_L1"."IsPublic", "LPA_L1"."LastTimeModified", "LPA_L1"."TimeInserted", "LPA_L1"."Title", "LPA_L1"."ViewPath" 
FROM ( "BlogEntry" "LPA_L1"  INNER JOIN "Comment" "LPA_L2"  ON  "LPA_L1"."BlogEntryId"="LPA_L2"."BlogEntryId")

I imagine this should happen with any table setup, but if I need to provide a sample please let me know and I'll work on creating one.

Thanks for your help Hopefully I'm not missing something obvious.

Hmmm... we test on FB 2.0 and IMHO Distinct should be before First to get things working, but we'll check it out. It's easy to fix, that's not the problem, it's how we could have missed this. I'll look into how the code was constructed in v2.6, as we rewrote the DQEs (the engines which generate the SQL) completely in v3.

In v2.6 we emitted DISTINCT after First x, so it's clearly a bug in FB's DQE for v3. I'll fix it and attach a new build to this thread.

Fixed. See attached dll.

Thanks so much, this fixes the issue.

LLBLGen == ProgrammerMood.Happy