LinqToEntity-Query depends on provider???

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cew3
Posts: 20
Joined: Wed 15 Jul 2009 14:50

LinqToEntity-Query depends on provider???

Post by cew3 » Thu 09 Jan 2014 10:08

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
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

Post by Shalex » Fri 10 Jan 2014 09:36

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.
cew3 wrote: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
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: LinqToEntity-Query depends on provider???

Post by cew3 » Fri 10 Jan 2014 12:16

Hi Shalex,
Shalex wrote: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
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

Post by Shalex » Mon 13 Jan 2014 12:17

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
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: LinqToEntity-Query depends on provider???

Post by cew3 » Mon 13 Jan 2014 12:49

Hi Shalex,

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

Best regards
cew3

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

Re: LinqToEntity-Query depends on provider???

Post by Shalex » Mon 13 Jan 2014 16:59

Thank you for the information.

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

cew3
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: LinqToEntity-Query depends on provider???

Post by cew3 » Tue 14 Jan 2014 07:33

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
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

Post by Shalex » Tue 14 Jan 2014 08:25

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:
Shalex wrote:tell us the datatypes of parameters which you pass to the StringConvert and DataLength methods
Looking forward to your reply.

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

Re: LinqToEntity-Query depends on provider???

Post by Shalex » Wed 15 Jan 2014 14:57

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
Devart Team
Posts: 8083
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

Post by Shalex » Thu 16 Jan 2014 15:12

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.

Post Reply