Problems to execute a DB function inside LINQ

Problems to execute a DB function inside LINQ

Postby vdefeo » Mon 10 Nov 2014 11:13

Hi,

I'm using dotConnect to access a database running on Oracle 11g. I have a db function (but the entity developer identified it as Stored Procedure) that I need to run inside a query. This function is already defined on Model.Store as the following image:

Image

In one case I'm needing to build a linq that represents the following SQL:

SELECT FU_DOMN_DC(a.field1) from table1 a

I tried to provide this function with the following code segment:

Code: Select all
[DbFunction("TPIModel.Store", "FU_DOMN_DC")]
public static string FU_DOMN_DC(string dominio, string {
    throw new PlatformNotSupportedException("Direct Calls are not supported");
}


AND

Code: Select all
[EdmFunction("TPIModel.Store", "FU_DOMN_DC")]
public static string FU_DOMN_DC(string dominio, string valor)
{
    throw new PlatformNotSupportedException("Direct Calls are not supported");
}


But the either approach raises an exception that devart can't translate the expression.

What am I doing wrong ?

Best regards.
vdefeo
 
Posts: 6
Joined: Mon 10 Nov 2014 10:47

Re: Problems to execute a DB function inside LINQ

Postby MariiaI » Wed 12 Nov 2014 07:21

This function is already defined on Model.Store as the following image

It is necessary to create the corresponding method for this stored procedure (function). To do this, please right-click the necessary stored function in the Model.Store and select Create Method option in the context menu.
For more information about working with stored procedures and functions please refer to:
1) http://www.devart.com/dotconnect/oracle/docs/?OracleRoutines.html
2) the corresponding section in the Entity Developer documentation:
- ORM Support-> Entity Framework-> Concepts-> Working with Methods
JIC: Entity Developer documentation is available with the installation package (the "Help" component should have been selected when installing).

If this information doesn't help, please send us the DDL script for the necessary stored function, so that we are able to find the solution for you in a shortest time.

But the either approach raises an exception that devart can't translate the expression.

Please also specify the full stack trace of the exception.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Problems to execute a DB function inside LINQ

Postby vdefeo » Wed 12 Nov 2014 11:24

Hi,

Thanks for answering, but it doesn't work.

When I try to convert the Stored procedure into a Method by clicking with the right button, I get the following message:

Cannot create method for a storage function 'FU_DOMN_DC' that can be composed. Only stored procedures may be mapped.


The LINQ query that I'm trying to run is:

Code: Select all
var r = (from t in table
         select TCPEntities.FU_DOMN_DC("estadoT", t.Ic_Estd_T)).Take(10).ToList();

The error message that I'm getting is:

The specified method 'System.String FU_DOMN_DC(System.String, System.String)' on the type 'TPIModel.TCPEntities' cannot be translated into a LINQ to Entities store expression.


The definition of the function on edml is:

Code: Select all
       
<Function Name="FU_DOMN_DC" IsComposable="true" ReturnType="VARCHAR2" BuiltIn="false" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="FU_DOMN_DC">
          <Parameter Name="DOMINIO" Type="VARCHAR2" Mode="In" />
          <Parameter Name="VALOR" Type="VARCHAR2" Mode="In" />
</Function>


The call stack is:
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction(Expression Expression)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.FindFunction(String namespaceName, String functionName, IList`1 argumentTypes, Boolean isGroupAggregateFunction, Expression Expression)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.TranslateFunctionCall(ExpressionConverter parent, MethodCallExpression call, DbFunctionAttribute functionAttribute)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateSet(Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
vdefeo
 
Posts: 6
Joined: Mon 10 Nov 2014 10:47

Re: Problems to execute a DB function inside LINQ

Postby MariiaI » Thu 13 Nov 2014 12:48

We have contacted you by e-mail.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Problems to execute a DB function inside LINQ

Postby vdefeo » Mon 17 Nov 2014 14:17

Hi MariiaI

I found the erro.

When I use the connection string generated by Entity Developer it works perfectly, the connection is like this:

Code: Select all
<add name="U_TPIEntitiesConnectionString" connectionString="metadata=res://*/DataModel1.csdl|res://*/DataModel1.ssdl|res://*/DataModel1.msl;provider=Devart.Data.Oracle;provider connection string=&quot...&quot;" providerName="System.Data.EntityClient" />



And by the configuration on DbContext, the devart component find this connection string on web.config and it works perfectly.

But I need to customize my connection string like this:

Code: Select all
<add name="U_TPIEntitiesConnectionString" connectionString="User Id={0};Password={1};Server={2};Port=1521;Direct=True;Sid={3};Persist Security Info=True" providerName="Devart.Data.Oracle" />



This kind of connection string works to access tables and execute queries, but don't work when I need to execute a Function on database, it appears that Devart need to know .csdl, .ssdl and .msdl files

By this way I added the metadata on this connection string, it became like this:

Code: Select all
<add name="U_TPIEntitiesConnectionString" connectionString="metadata=res://*/DataModel1.csdl|res://*/DataModel1.ssdl|res://*/DataModel1.msl;provider=Devart.Data.Oracle;provider connection string=&quot;User Id={0};Password={1};Server={2};Port=1521;Direct=True;Sid={3};Persist Security Info=True&quot;" providerName="System.Data.EntityClient" />


But when I format this string with the access information and try to create the connection I receive the following message.

Code:
Code: Select all
Devart.Data.Oracle.OracleConnection conexao = new Devart.Data.Oracle.OracleConnection(conn);



Message:
Additional information: Keyword not supported: 'metadata'.



And it avoid me create the connection.

Observation:
Class EntityConnectionStringBuilder allows configuration of metadata property but
Devart.Data.Oracle.OracleConnectionStringBuilder don't.

How would I create a connection in runtime and configure the metadata and provider information ?
vdefeo
 
Posts: 6
Joined: Mon 10 Nov 2014 10:47

Re: Problems to execute a DB function inside LINQ

Postby MariiaI » Tue 18 Nov 2014 10:50

Class EntityConnectionStringBuilder allows configuration of metadata property but
Devart.Data.Oracle.OracleConnectionStringBuilder don't.

How would I create a connection in runtime and configure the metadata and provider information ?
Please refer here:
http://forums.devart.com/viewtopic.php?f=2&t=27913#p94899
http://msdn.microsoft.com/en-us/library/bb738533.aspx

The metadata parameter is not supported in both OracleConnectionStringBuilder and OracleConnection. We are sending you a small test project by e-mail. Please check that the letter is not blocked by your mail filter. Please test it and tell us about the results.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Problems to execute a DB function inside LINQ

Postby vdefeo » Tue 18 Nov 2014 11:13

I replied via email
vdefeo
 
Posts: 6
Joined: Mon 10 Nov 2014 10:47

Re: Problems to execute a DB function inside LINQ

Postby MariiaI » Wed 19 Nov 2014 10:50

I replied via email

Unfortunately, we didn't receive response from you. Probably, your letter was blocked for some reason (e.g., the attachment, etc.). Please send us your letter again.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Problems to execute a DB function inside LINQ

Postby vdefeo » Wed 19 Nov 2014 15:13

I implemented exactly as the example sent but... I received an error:

An unhandled exception of type 'System.Data.Entity.Core.MetadataException' occurred in mscorlib.dll

Additional information: Schema specified is not valid. Errors:

DataModel1.ssdl(10,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(11,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(12,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(13,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(14,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(15,6) : error 0040: The Type DATE is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(16,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(17,6) : error 0040: The Type DATE is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(18,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(19,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(20,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(21,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(22,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

DataModel1.ssdl(23,6) : error 0040: The Type VARCHAR2 is not qualified with a
================================================================================

Connection string generated :

metadata=res://*/DataModel1.csdl|res://*/DataModel1.ssdl|res://*/DataModel1.msl;provider=Devart.Data.Oracle;provider connection string="User Id=***;Password=***;Server=***;Direct=True;Sid=***;Persist Security Info=True"


=================================================================================

Entity framework configuration on web.config:


Code: Select all
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Devart.Data.Oracle" type="Devart.Data.Oracle.Entity.OracleEntityProviderServices, Devart.Data.Oracle.Entity, Version=8.4.201.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
  </entityFramework>
vdefeo
 
Posts: 6
Joined: Mon 10 Nov 2014 10:47

Re: Problems to execute a DB function inside LINQ

Postby MariiaI » Thu 20 Nov 2014 08:39

DataModel1.ssdl(10,6) : error 0040: The Type VARCHAR2 is not qualified with a namespace or alias. Only primitive types can be used without qualification.

Please refer to these topics:
http://forums.devart.com/viewtopic.php?t=24678
http://stackoverflow.com/questions/15077985/using-code-first-and-database-first-entity-framework-with-devart

Does it help?
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Problems to execute a DB function inside LINQ

Postby vdefeo » Fri 21 Nov 2014 13:16

Thank you, problem solved!!
vdefeo
 
Posts: 6
Joined: Mon 10 Nov 2014 10:47

Re: Problems to execute a DB function inside LINQ

Postby MariiaI » Fri 21 Nov 2014 13:26

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle