CROSS APPLY is not supported by Oracle

CROSS APPLY is not supported by Oracle

Postby i.kopcanski » Fri 27 Aug 2010 09:04

Hello,
I use Entity Framework 3.5 with .dotConnect for Oracle 5.70.152.0.
I get the exception saying "CROSS APPLY is not supported by Oracle."
The LINQ statement part causing this problem is the following:

.Include(s => s.v2_grid_def//full path to columns and picklists
.Include(gd => gd.v2_grid_columns
.Include(gc => gc.v2_templates_grid_columns
.Include(tgc => tgc.v2_columns
.Include(col => col.picklists)))))

.Include(s => s.v2_grid_def//full path to columns and their supported grid types
.Include(gd => gd.v2_grid_columns
.Include(gc => gc.v2_templates_grid_columns
.Include(tgc => tgc.v2_columns
.Include(col => col.v2_columns_grid_type)
.Include(cgt => cgt.v2_enum_grid_type)
))))

Can you help us with that?
i.kopcanski
 
Posts: 7
Joined: Mon 09 Nov 2009 17:36

Postby Shalex » Mon 30 Aug 2010 12:27

Oracle doesn't have its equivalents for the SQL Server OUTER APPLY and CROSS APPLY constructions (this issue is described at http://msdn.microsoft.com/en-us/library/bb896273.aspx). Microsoft decided not to change the current behaviour. So, users who encounter the mentioned obstacles should re-construct their LINQ-queries.

The full list of Entity Framework support limitations is available at http://www.devart.com/dotconnect/oracle/docs/EF.html#limit.

Here is a description of possible ways of loading related objects: http://msdn.microsoft.com/en-us/library/bb896272.aspx.
Shalex
Devart Team
 
Posts: 5833
Joined: Thu 14 Aug 2008 12:44

CROSS APPLY is not supported by Oracle

Postby chrisadam12 » Tue 07 Sep 2010 04:45

If this error is coming from your MySQL EF provider, then one of two things have happened:

You haven't configured the provider correctly.
There is a limitation in your EF provider.
SQL is generated by the provider. You can configure server-version-specific SQL generation via the ProviderManifestToken attribute in EDMX. This is how you tell the provider not to use SQL features which older server versions don't support.

It's possible that some MySQL storage engines support SQL features which others do not. In this case, the provider would need to either use a common subset of features supported by most engines or use ProviderManifestToken to allow you to choose.

However, it's also possible that a buggy provider simply returns incorrect SQL. If this is the case then you must either find an update or avoid queries which touch the bug.

Update: Based on @Devart's answer, it seems that this is a limitation of the provider, which is designed in due to limitations of MySQL. The EF will produce an ADO.NET canonical command tree. It is the provider's responsibility to translate this to SQL. If the EF returns a cross/outer apply node in the CCT, it seems that Devart has not found a way to translate this to SQL which MySQL can handle. So either MySQL is just not up to the task of supporting all EF queries, or someone who is a MySQL expert (not me!) needs to show Devart how to produce MySQL-compatible SQL which can properly return rows for the cross/outer apply CCT nodes.



_______________________________________________



Want to get-on Google's first page and loads of traffic to your website? Hire a SEO specialist from Ocean Groups seo specialist
chrisadam12
 
Posts: 1
Joined: Tue 07 Sep 2010 04:35

Postby AndreyR » Tue 07 Sep 2010 09:41

Chrisadam12, thank you for the comment. You are correct in your conclusions about the MySQL limitations.
We have not found the way to provide OUTER APPLY support in MySQL.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle