LinqToEntity-Query depends on provider???

LinqToEntity-Query depends on provider???

Postby 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?f=1&t=20160&p=65791&hilit=convert+int+to+string#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
cew3
 
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: LinqToEntity-Query depends on provider???

Postby 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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

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

Re: LinqToEntity-Query depends on provider???

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

Re: LinqToEntity-Query depends on provider???

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

Re: LinqToEntity-Query depends on provider???

Postby 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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

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

Re: LinqToEntity-Query depends on provider???

Postby 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: 7705
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

Postby 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: 7705
Joined: Thu 14 Aug 2008 12:44

Re: LinqToEntity-Query depends on provider???

Postby 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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle