LinqToEntity-Query depends on provider???

[cew3]

Hi there,

I have a big problem. For accessing data we use the Entity Framework which I thought it works like a DAL. Because of the abstraction it should be invisible for the layers using the EF which database system and provider is used for holding and accessing data.

After running into problems which linq queries trying to cast an int to string or getting the length of a lob column directly in the query, if found some hints targeting to SqlFunction methods. I used them (on MSSQL) and it worked.

No, switching from MSSQL to Oracle (our software supports both systems), the queries fail.
In http://forums.devart.com/viewtopic.php? ... ing#p65791 I found the hint to use Devart.Data.Oracle.Entity.OracleFunctions.

I can't believe it! I have to use provider dependant methods in layers on top of the DAL?
That can't be the solution! The provider itself has to convert queries the db can execute.

How can this problem be solved?

Best regards,
cew3

[Shalex]

The functions from the System.Data.Objects.SqlClient.SqlFunctions class are used only by .NET Framework Data Provider for SQL Server (System.Data.SqlClient).

dotConnect for Oracle (Devart.Data.Oracle) works with the functions from Devart.Data.Oracle.Entity.OracleFunctions. There is no way to use SqlFunctions with dotConnect for Oracle because the SqlFunctions class is specific to SQL Server only.

General functions for all EF-providers are available in the System.Data.Objects.EntityFunctions class.

I can't believe it! I have to use provider dependant methods in layers on top of the DAL?

You started to use provider dependant methods in layers on top of the DAL when employed System.Data.Objects.SqlClient.SqlFunctions in your code.

[cew3]

Hi Shalex,

You started to use provider dependant methods in layers on top of the DAL when employed System.Data.Objects.SqlClient.SqlFunctions in your code.

You're right. And because of this, I want to call the desired methods from a class, that is db system independant. Microsoft does not provide such a class. So your provider should parse the expression tree of the query and map the called methods to your own methods, provided in Devart.Data.Oracle.Entity.OracleFunctions.

I don't see another way to get rid of the db dependance. Do you?

Best regards,
cew3

[Shalex]

We will investigate the possibility of supporting SqlFunctions if the corresponding functionality is available in Oracle server as well. Please give us the following information (we will try to support your approach first):
1) the version of your Entity Framework
2) the list of SqlFunctions used in your project

[cew3]

Hi Shalex,

1) Currently we have to get it to work with EF 4.0
2) Used SqlFunctions:
- StringConvert
- DataLength

Best regards
cew3

[Shalex]

Thank you for the information.

Please tell us the datatypes of parameters which you pass to the StringConvert and DataLength methods.

[cew3]

Hi Shalex,

you can stop investigations for this topic. We'll use workarounds to fix the problem.

Other providers also don't support the requested functionality.
It's a bug in design caused by Microsoft. It's up to them to fix that problem by extending the set of native methods for L2E which have to be implemented by the providers for other DBs. Everything else breaks the software architecture.

So let me thank you for your work and the will to solve my problem.

Best regards
cew3

[Shalex]

We can partially support SqlFunctions in our EF providers because there are our customers who encountered a similar issue in their approaches as well.

If you want us to implement the needed functionality in the nearest future, please give us the following information:

tell us the datatypes of parameters which you pass to the StringConvert and DataLength methods

Looking forward to your reply.

[Shalex]

The Acos, Asin, Ascii, Atan, Atan2, Char, Cos, CurrentUser, DataLength, Exp, Rand, Sign, Sin, SoundCode, SquareRoot, StringConvert, Tan methods of the SqlFunctions class in LINQ to Entities are supported to be converted to Oracle-specific SQL statements in runtime. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New version of dotConnect for Oracle 8.2 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=28734.