Hi,

I find a situation that Firebird's generated code don't run because the
CROSS APPLY is used. The issue is generated because I need to create a
query inside the where clause. I'll show the code that runs and part of the
code that brings the issue,

ITENS_ENTRADA.Where(w
=> (w.PRODUTOS.ESTOQUES.FirstOrDefault().DATA_PRIMEIRA_COMPRA >= _from)

This code gets the Items (ITENS_ENTRADA) of an order geting only the
products (PRODUTOS) that has the first date is greater than _from date.
This date is in Stocks (ESTOQUE) table in field First Buy
(DATA_PRIMEIRA_COMPRA).

This generator and correct code isÇ

SELECT
"GroupBy1"."A1" AS "C1"
FROM ( SELECT
COUNT("Filter2"."A1") AS "A1"
FROM ( SELECT
1 AS "A1"
FROM ( SELECT
(SELECT FIRST (1)
"Extent2"."DATA_PRIMEIRA_COMPRA" AS "DATA_PRIMEIRA_COMPRA"
FROM "ESTOQUES" AS "Extent2"
WHERE "Extent1"."LIVRO" = "Extent2"."LIVRO") AS "C1"
FROM "ITENSENTRADA" AS "Extent1"
) AS "Project2"
WHERE "Project2"."C1" >= @p__linq__0
) AS "Filter2"
) AS "GroupBy1"

Now I need only the Date from Store (FILIAL) number 1. So the code I need
is this:

ITENS_ENTRADA.Where(w => (w.PRODUTOS.ESTOQUES.Where(es => es.FILIAL ==
1).FirstOrDefault().DATA_PRIMEIRA_COMPRA >= _from)

The generted code that don't works is this:

SELECT
"GroupBy1"."A1" AS "C1"
FROM ( SELECT
COUNT("Filter2"."A1") AS "A1"
FROM ( SELECT
1 AS "A1"
FROM "ITENSENTRADA" AS "Extent1"
CROSS APPLY (SELECT FIRST (1)
"Extent2"."DATA_PRIMEIRA_COMPRA" AS "DATA_PRIMEIRA_COMPRA"
FROM "ESTOQUES" AS "Extent2"
WHERE ("Extent1"."LIVRO" = "Extent2"."LIVRO") AND ("Extent2"."FILIAL" =
@p__linq__0) ) AS "Limit1"
WHERE "Limit1"."DATA_PRIMEIRA_COMPRA" >= @p__linq__1
) AS "Filter2"
) AS "GroupBy1"

Well I hope this explanation is enough to explain the issue and I wish to
see the provider better each time.

Thanks,

Marco Aurélio Castro
Diretor
McSoft


[Non-text portions of this message have been removed]