LinqToEntity-Query depends on provider???
LinqToEntity-Query depends on provider???
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
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
Re: LinqToEntity-Query depends on provider???
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.
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.
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 wrote:I can't believe it! I have to use provider dependant methods in layers on top of the DAL?
Re: LinqToEntity-Query depends on provider???
Hi Shalex,
I don't see another way to get rid of the db dependance. Do you?
Best regards,
cew3
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.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.
I don't see another way to get rid of the db dependance. Do you?
Best regards,
cew3
Re: LinqToEntity-Query depends on provider???
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
1) the version of your Entity Framework
2) the list of SqlFunctions used in your project
Re: LinqToEntity-Query depends on provider???
Hi Shalex,
1) Currently we have to get it to work with EF 4.0
2) Used SqlFunctions:
- StringConvert
- DataLength
Best regards
cew3
1) Currently we have to get it to work with EF 4.0
2) Used SqlFunctions:
- StringConvert
- DataLength
Best regards
cew3
Re: LinqToEntity-Query depends on provider???
Thank you for the information.
Please tell us the datatypes of parameters which you pass to the StringConvert and DataLength methods.
Please tell us the datatypes of parameters which you pass to the StringConvert and DataLength methods.
Re: LinqToEntity-Query depends on provider???
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
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
Re: LinqToEntity-Query depends on provider???
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:
If you want us to implement the needed functionality in the nearest future, please give us the following information:
Looking forward to your reply.Shalex wrote:tell us the datatypes of parameters which you pass to the StringConvert and DataLength methods
Re: LinqToEntity-Query depends on provider???
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.
Re: LinqToEntity-Query depends on provider???
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.
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.