"outer apply" statement

"outer apply" statement

Postby kulibin » Fri 31 Oct 2008 15:08

"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]
kulibin
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby AndreyR » Mon 03 Nov 2008 12:06

Could you please send us a little test project or provide some steps to reproduce the error?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby kulibin » Tue 04 Nov 2008 12:41

have you received my mail with test project?
kulibin
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby AndreyR » Tue 04 Nov 2008 13:59

Yes, thanks. We are investigating the issue and will notify you about the results.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Wed 05 Nov 2008 14:02

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 ''
);
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby kulibin » Thu 06 Nov 2008 11:00

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
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby kulibin » Fri 07 Nov 2008 09:23

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
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby kulibin » Fri 21 Nov 2008 08:52

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.
kulibin
 
Posts: 12
Joined: Thu 18 Sep 2008 06:33

Postby AndreyR » Fri 21 Nov 2008 13:32

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby mcw14 » Tue 28 Apr 2009 14:21

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
};
mcw14
 
Posts: 1
Joined: Tue 28 Apr 2009 13:31

Postby AndreyR » Wed 29 Apr 2009 07:41

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Re: "outer apply" statement

Postby slaxman » Tue 19 Feb 2013 19:46

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?
slaxman
 
Posts: 47
Joined: Wed 16 Sep 2009 20:09
Location: United States

Re: "outer apply" statement

Postby Shalex » Wed 20 Feb 2013 13:50

Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL