OUTER APPLY is not supported by MySQL

Question

I am using Entity Framework with MySQL. The following query results an error:

var foobar = ctx.ArticleBase.OfType<ActicleSpecial>().Include("CreatedBy.Image.Location").ToList();

Error: "OUTER APPLY is not supported by MySQL" I also get the "CROSS APPLY is not supported by MySQL" on a little different query.

I have the following datamodel:

Except the Image entity have Location entity (one to many relation) named Location relation and UserBase have Image relation instead of UserSpecial.

Why do I get this error? How to avoid this error? Is it possible?

Answer 1

If this error is coming from MySQL one of two things have happened:

1. You haven't configured the provider correctly.
2. There is a bug in your EF provider.

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

1. You haven't configured the provider correctly.
2. 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.

Answer 2

This is an Entity Framework internal architecture feature. Sometimes it generates queries not supported by providers other than SQL Server. More information is available here at MSDN.