Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Problems to execute a DB function inside LINQ

https://forums.devart.com/viewtopic.php?f=1&t=30732

Page 1 of 1

Problems to execute a DB function inside LINQ

Posted: Mon 10 Nov 2014 11:13
by vdefeo
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.

Re: Problems to execute a DB function inside LINQ

Posted: Wed 12 Nov 2014 07:21
by MariiaI
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 ... tines.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.

Re: Problems to execute a DB function inside LINQ

Posted: Wed 12 Nov 2014 11:24
by vdefeo
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)

Re: Problems to execute a DB function inside LINQ

Posted: Thu 13 Nov 2014 12:48
by MariiaI
We have contacted you by e-mail.

Re: Problems to execute a DB function inside LINQ

Posted: Mon 17 Nov 2014 14:17
by vdefeo
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..."" 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="User Id={0};Password={1};Server={2};Port=1521;Direct=True;Sid={3};Persist Security Info=True"" 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 ?

Re: Problems to execute a DB function inside LINQ

Posted: Tue 18 Nov 2014 10:50
by MariiaI
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? ... 913#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.

Re: Problems to execute a DB function inside LINQ

Posted: Tue 18 Nov 2014 11:13
by vdefeo
I replied via email

Re: Problems to execute a DB function inside LINQ

Posted: Wed 19 Nov 2014 10:50
by MariiaI
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.

Re: Problems to execute a DB function inside LINQ

Posted: Wed 19 Nov 2014 15:13
by vdefeo
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>

Re: Problems to execute a DB function inside LINQ

Posted: Thu 20 Nov 2014 08:39
by MariiaI
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/1507 ... ith-devart

Does it help?

Re: Problems to execute a DB function inside LINQ

Posted: Fri 21 Nov 2014 13:16
by vdefeo
Thank you, problem solved!!

Re: Problems to execute a DB function inside LINQ

Posted: Fri 21 Nov 2014 13:26
by MariiaI
Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/