"outer apply" statement

[kulibin]

"outer apply" statement, generated by provider causes an error.

Database script:

Code: Select all

create table time_table
(
id_time_table bigserial primary key,
custom_period int not null default 0,
number_of_displayed_points int not null default 0,
priority int not null default 0,
start timestamp not null,
stop timestamp not null,
time_table_period int default 0
);

create table time_table_constraint
(
id_time_table_constraint bigserial primary key,
fk_time_table bigint not null references time_table on delete cascade,
day int not null default 0, -- check???
day_of_week int not null default 0,
month int not null default 0,
time timestamp not null
);

create table task_sequence
(
id_task_sequens bigserial primary key,
name text not null default ''
);

create table task_sequens_data
(
id_task_sequens_data bigint primary key references task_sequence on delete cascade,
current_operation int default 0,
fk_time_table bigint not null references time_table,
name text not null default ''
);

pg-log error message:

2008-10-31 16:15:29 EET ERROR: ошибка синтаксиса в или рядом "OUTER" на символе 2555
2008-10-31 16:15:29 EET КОМАНДА: SELECT
"Project1".id_task_sequens AS id_task_sequens,
"Project1".tmp AS tmp,
"Project1".id_task_sequens_data AS id_task_sequens_data,
"Project1".id_task_sequens_data1 AS id_task_sequens_data1,
"Project1".current_operation AS current_operation,
"Project1".fk_probe AS fk_probe,
"Project1"."name" AS "name",
"Project1".id_task_sequens_data2 AS id_task_sequens_data2,
"Project1".id_time_table AS id_time_table,
"Project1".custom_period AS custom_period,
"Project1".number_of_displayed_points AS number_of_displayed_points,
"Project1".priority AS priority,
"Project1"."start" AS "start",
"Project1".stop AS stop,
"Project1".time_table_period AS time_table_period,
"Project1".id_task_sequens_data3 AS id_task_sequens_data3,
"Project1"."C1" AS "C1",
"Project1"."C2" AS "C2",
"Project1"."C3" AS "C3",
"Project3"."C1" AS "C4",
"Project3".id_time_table_constraint AS id_time_table_constraint,
"Project3"."day" AS "day",
"Project3".day_of_week AS day_of_week,
"Project3"."month" AS "month",
"Project3"."time" AS "time",
"Project3".fk_time_table AS fk_time_table
FROM (SELECT
"Extent1".id_task_sequens AS id_task_sequens,
"Extent1".tmp AS tmp,
"Extent2".id_task_sequens_data AS id_task_sequens_data,
"Extent3".id_task_sequens_data AS id_task_sequens_data1,
"Extent3".current_operation AS current_operation,
"Extent3".fk_probe AS fk_probe,
"Extent3"."name" AS "name",
"Extent4".id_task_sequens_data AS id_task_sequens_data2,
"Extent5".id_time_table AS id_time_table,
"Extent5".custom_period AS custom_period,
"Extent5".number_of_displayed_points AS number_of_displayed_points,
"Extent5".priority AS priority,
"Extent5"."start" AS "start",
"Extent5".stop AS stop,
"Extent5".time_table_period AS time_table_period,
"Extent6".id_task_sequens_data AS id_task_sequens_data3,
1 AS "C1",
1 AS "C2",
1 AS "C3"
FROM public.task_sequence AS "Extent1"
LEFT OUTER JOIN public.task_sequens_data AS "Extent2" ON "Extent1".id_task_sequens = "Extent2".id_task_sequens_data
LEFT OUTER JOIN public.task_sequens_data AS "Extent3" ON "Extent2".id_task_sequens_data = "Extent3".id_task_sequens_data
LEFT OUTER JOIN public.task_sequens_data AS "Extent4" ON "Extent2".id_task_sequens_data = "Extent4".id_task_sequens_data
LEFT OUTER JOIN public.time_table AS "Extent5" ON "Extent4".fk_time_table = "Extent5".id_time_table
LEFT OUTER JOIN public.task_sequens_data AS "Extent6" ON "Extent2".id_task_sequens_data = "Extent6".id_task_sequens_data ) AS "Project1"
OUTER APPLY (SELECT
"Extent7".id_time_table_constraint AS id_time_table_constraint,
"Extent7".fk_time_table AS fk_time_table,
"Extent7"."day" AS "day",
"Extent7".day_of_week AS day_of_week,
"Extent7"."month" AS "month",
"Extent7"."time" AS "time",
1 AS "C1"
FROM public.time_table_constraint AS "Extent7"
LEFT OUTER JOIN (SELECT
"Extent8".id_task_sequens_data AS id_task_sequens_data,
"Extent8".fk_time_table AS fk_time_table
FROM public.task_sequens_data AS "Extent8"
WHERE "Project1".id_task_sequens_data = "Extent8".id_task_sequens_data ) AS "Project2" ON true = true
WHERE "Project2".fk_time_table = "Extent7".fk_time_table ) AS "Project3"
ORDER BY "Project1".id_task_sequens ASC, "Project1".id_task_sequens_data ASC, "Project1".id_task_sequens_data1 ASC, "Project1".id_task_sequens_data2 ASC, "Project1".id_time_table ASC, "Project1".id_task_sequens_data3 ASC, "Project3"."C1" ASC[/b]

[AndreyR]

Could you please send us a little test project or provide some steps to reproduce the error?

[kulibin]

have you received my mail with test project?

[AndreyR]

Yes, thanks. We are investigating the issue and will notify you about the results.

[AndreyR]

The problem is associated with the realization of LINQ to Entities. The LINQ query in C# code is transformed to Expression Tree by Microsoft code, and only after this transformation we generate provider-specific SQL query. The problem is that the Expression Tree in particular cases like the one you have described with DB script has not enough information to construct the appropriate SQL query. The new build throws NotSupportedException in such cases and no SQL generated at all.

Particularly in your case we recommend a slight modification of your DB script. Try to replace the one-to-one relationship in the "task_sequens_data" table with one-to-many relationship, like in the following script:

create table task_sequens_data
(
id_task_sequens_data bigserial primary key,
id_task_sequence bigint references task_sequence on delete cascade,
current_operation int default 0,
fk_time_table bigint not null references time_table,
name text not null default ''
);

[kulibin]

Your advice for dbscript modification doesn’t work, error still appears.
I tested this edm model with SQL Server 2005 and native MS provider, everything works OK. So I can’t understand why it is impossible to construct SQL query from Expression Tree using your provider. And how can I solve my problem?

[kulibin]

hi. look on this code:

Code: Select all

using (monitoringEntities db = new monitoringEntities())
            {
                //var q = db.task_sequence.Include("task_sequens_data.time_table.time_table_constraint");

                foreach (task_sequence t in db.task_sequence)
                {
                    t.task_sequens_dataReference.Load();
                    t.task_sequens_data.time_tableReference.Load();
                    t.task_sequens_data.time_table.time_table_constraint.Load();
                    Console.WriteLine(t.id_task_sequens);
                }
            }

it works...

[kulibin]

Any news on this problem? It is critical for one of our projects and we are waiting for fix.
Do you have any temporary solutions? Your previous advice didn't help, exception appears again.

[AndreyR]

The test project you sent works fine if to change the DB script according to our suggestion (after regenerating the EDM).
In case you have problems with other DB objects, try to play around with SQL.
Unfortunately, no fix can be provided because of problems described in previous post.

[mcw14]

Has there been any progress made with constructing the correct sql statement for oracle when "outer apply" is being generated for the sql server statement? My linq to entity query is below. This query uses MAX to find the Products that have the highest unit price in each category. The problem lies with the g.Max(p3 => p3.UnitPrice). I believe this is where the outer apply is being generated since the APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. Can this be translated in your provider to oracle syntax?

var query = from p in Context.Products
group p by p.Category.CategoryID into g
select new {
g.Key,
MostExpensiveProducts =
from p2 in g
where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
select p2
};

[AndreyR]

Unfortunately, there is no technical possibility to overcome this problem.
It is present not only in Oracle, but also in SQL Server 2000, see this link:
http://msdn.microsoft.com/en-us/library/bb896273.aspx
There was a discussion here also:
http://social.msdn.microsoft.com/Forums ... 82a2177346
We have contacted the Microsoft EF team and there was no positive answer as well.

[slaxman]

We are running into the same issue with EF4 and oracle 11g while using MAX. I understand that it stems from Microsoft's EF implementation. any ideas whether it's been fixed in EF5?

[Shalex]

Please refer to http://forums.devart.com/viewtopic.php?f=30&t=24769.