Issue Details (XML | Word | Printable)

Key: DNET-427
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Damasio
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
.NET Data provider

Query error in Firebird

Created: 30/Apr/12 01:40 PM   Updated: 04/Nov/16 06:22 AM
Component/s: Entity Framework
Affects Version/s: 2.7.5
Fix Version/s: None

File Attachments: 1. File METADATA.SQL (3 kB)
2. Zip Archive (589 kB)

Environment: C# Entity Framework - Visual Studio .Net 4.0
Issue Links:

 Description  « Hide
"Dynamic SQL Error
SQL error code = -104
Token unknown - line 8, column 3

protected void LinqDataSourcePedidos_QueryCreated(object sender, QueryCreatedEventArgs e)
            var q = e.Query.Cast<PED>();
            var result = (from t in q
                          select new
                              PEDPOS_POSICAO = (from pedpos in t.PEDPOS
                                                  t.PED_ID == pedpos.PED_ID &&
                                                  pedpos.PEDPOS_ID ==
                                                select new
                              PED_ID = t.PED_ID
            e.Query = result;

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Damasio made changes - 30/Apr/12 01:46 PM
Field Original Value New Value
Priority Major [ 3 ] Critical [ 2 ]
Component/s Entity Framework support [ 10110 ]
Component/s ADO.NET Provider [ 10041 ]
Jiri Cincura added a comment - 19/May/12 12:44 PM
Without the model I can hardly do anything about it.

Jiri Cincura made changes - 19/May/12 12:44 PM
Status Open [ 1 ] In Progress [ 3 ]
Jiri Cincura made changes - 19/May/12 12:45 PM
Priority Critical [ 2 ] Major [ 3 ]
Damasio added a comment - 19/May/12 08:48 PM
okay. I will build a model to give you.

Damasio added a comment - 25/May/12 03:31 AM
Attached is the data.

The error occurs in the code example below:

public IQueryable SelectAllPED()
            Entities entities = new Entities();
            var result = (from t in entities.PED
                          select new
                              PED_ID = t.PED_ID,
                              PED_DATA = t.PED_DATA,
                              PED_HORA = t.PED_HORA,
                              PED_CLI_CPFCNPJ = t.PED_CLI_CPFCNPJ,
                              PED_CLI_NOME = t.PED_CLI_NOME,
                              PEDPOS_POSICAO = (from pp in t.PEDPOS
                                                select new
                                                }).OrderByDescending(o => o.PEDPOS_ID).FirstOrDefault().PEDPOS_POSICAO,

            return result;

Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 9, column 1\r\nOUTER

///////////////////////SQL generated////////////////////////
        "E"."PEDPOS_ID" AS "PEDPOS_ID",
        FROM "PEDPOS" AS "E"
        WHERE "B"."PED_ID" = "E"."PED_ID"
    ) AS "C"


Test also with Firebird 2.7.7

I hope to be helping

thank you very much

Damasio made changes - 25/May/12 03:31 AM
Attachment METADATA.SQL [ 12160 ]
Attachment [ 12161 ]
Jiri Cincura added a comment - 25/May/12 09:25 AM
OUTER APPLY (as well as CROSS APPLY) isn't supported by Firebird. And it's not easy to rewrite the query *in general* using JOINs esp. if you're inside code translating the query into SQL. More reading i.e. or .

Jiri Cincura made changes - 25/May/12 09:25 AM
Status In Progress [ 3 ] Closed [ 6 ]
Resolution Won't Fix [ 2 ]
Jiri Cincura added a comment - 25/May/12 11:40 AM
The bottom line is, that you have to rewrite your query.

Damasio added a comment - 25/May/12 12:53 PM

I tried several other ways to achieve the same result, but always with an error.

Only if you had not (OrderByDescending (o => o.PEDPOS_ID)), but I need it.

unfortunately I'm in trouble.

You any alternative to a linq command that does not generate the error?

Jiri Cincura added a comment - 25/May/12 02:20 PM
This is not a support forum. Sorry. Please ask somewhere else.

Damasio added a comment - 25/May/12 02:25 PM
Okay I'm sorry. But the error in question generated by the "entity" would not be a bug in the case?

Nick Coad added a comment - 04/Oct/16 04:16 AM
Can you explain why this is marked as "Won't Fix"? The Firebird provider is generating SQL that is not supported by Firebird, surely that is a bug that warrants looking at? Disappointed to see this still unfixed after 4 years.

Nick Coad added a comment - 04/Nov/16 02:38 AM
Jiri, your provider is generating invalid Firebird SQL, that's the real issue. We all understand that OUTER APPLY is not supported by Firebird, and the bug we're reporting is that your provider is generating a query that contains OUTER APPLY, despite this being unsupported on Firebird.

Jiri Cincura made changes - 04/Nov/16 06:22 AM
Link This issue block progress on DNET-711 [ DNET-711 ]