Composable function producing invalid SQL code

coeamyd · Post by **coeamyd** » Fri 04 Nov 2011 15:16

Hi all,

I'm currently developing a web site using dotConnect for Oracle and the entity framework 4.1. The database contains a stored function used to retrieve translations. It takes an id and a locale parameter and returns a string. I have added a function mapping to the SSDL and manually created a method to call the function using linq as described here: http://msdn.microsoft.com/en-us/library/dd456847.aspx.

The namespace and everything seems to be in order, since I'm not getting any "Function can not be mapped to the database" exceptions. Instead, I get an Oracle exception complaining about invalid SQL. And if I check the trace log, I can indeed see, that my function calls have been mapped, but the function name is missing.

My LINQ code

Code: Select all

context.Texts.Select(t => EdmFuncs.GetTranslation(t.Id, "en-US"))

gets translated to

Code: Select all

SELECT ("Extent1".Id, :p__linq__0) AS C1 FROM SCHEMA.TEXTS "Extent1"

As you can see, the crucial "GetTranslation" function name is missing.

What am I doing wrong?

Thanks for your help!

Cheers,
Christoph

Shalex · Post by **Shalex** » Mon 07 Nov 2011 12:02

coeamyd wrote:The database contains a stored function used to retrieve translations. It takes an id and a locale parameter and returns a string.

We recommend using Entity Developer (the Devart Entity Model template, *.edml).
Please perform the following steps:
1. Drag the GetTranslation function on the designer surface.
2. Go to the Moder Explorer->Model.Store. Edit the properties of this function: set Concealed to true.
3. Drag the function from the Model.Store to the designer surface.
4. Change the Return Type of the generated method (Model Explorer->Methods->GetTranslation->Properties > Return Type) to String.

EF doesn't allow to create method of context basing on the function (not stored procedure) which returns result. "Consealed" is our workaround for functions.

coeamyd · Post by **coeamyd** » Mon 07 Nov 2011 12:38

Hi Shalex,

thanks for your input. I just tried this, and I do get a nice function implemented using ADO.NET calls. This is not what I wanted, though. I need to be able to use the function in queries, so it must be composable. This does not work with the workaround you proposed.

I'm actually currently trying to adapt the function to be a model-defined function in ESQL. After some playing around my ESQL seems to be ok now. I now get a different error from ORA, telling me that it can't find a column in an "Extent". I wanted to try this on a simpler query, since my original query is too complex for debugging. I'll get back to you, once I have more details on this.

Do you have any other ideas on how I might get this working?

Thanks,
Christoph

coeamyd · Post by **coeamyd** » Tue 08 Nov 2011 12:28

My efforts with trying to get things working with a model defined function and esql were fruitless. I managed to get the definitions right, but the resulting sql always led to ORA errors.

I tried switching to linqConnect, since it handles functions well. Unfortunately, this is currently not an option, since all our ID columns are NUMBER(15), which becomes Int64 in EF, but Double in linqConnect. I was unable to find a way to define a custom mapping like in the entity framework provider, so I would have to manually change all my number columns in the lqml file.

I'm currently considering going back to direct ADO.NET access, though I still hope you find a way to get the functions working in EF. I wonder why you managed to get the built-in functions made available through the OracleFunctions class, but custom functions don't work. Especially, since the tree-traversing seems to parse the call ok. It just does not output the function's name to the resulting sql code. The parameters get mapped just fine. Pleeeeeeease help me on this. I don't want to go back to manual ADO.NET

Thanks for your help,
Christoph

StanislavK · Post by **StanislavK** » Thu 10 Nov 2011 17:33

There is no possibility of using stored functions in the queries generated by Entity Framework, as the latter does not support stored functions.

As for LinqConnect, the next version (not the nearest build) of Entity Developer will support changing Oracle number mappings. I.e., it will be possible to specify that, e.g., Number(15) should be mapped to Int64 by default.

JIC: at the moment, you can try using auto-replace in the .lqml file as a workaround. For example, you can replace entries like
Type="System.Double" DbType="NUMBER(15)
with
Type="System.Int64" DbType="NUMBER(15)

coeamyd · Post by **coeamyd** » Sat 12 Nov 2011 09:50

Hi Stanislav,

I know, this does not seem to be a supported feature, although I have found several other EF articles describing how it's done. What I find strange is that the mechanism does work with the OracleFunctions class' methods, and that your expression tree parser does not throw an exception when it reaches my definition, but instead actually converts the call to a sql function call with all parameters correctly filled, and only the actual function name missing. Shouldn't this be considered a bug? And isn't it a veeeery small step to also emit the missing function name?

Thanks,
Christoph

Shalex · Post by **Shalex** » Mon 14 Nov 2011 15:27

EF doesn't allow to create method of context basing on the function (not stored procedure). You can use functions with the "Consealed" workaround (to ignore the function's return value).

coeamyd · Post by **coeamyd** » Tue 15 Nov 2011 16:13

I don't necessarily want it to be a function on the context. I just want a .NET-side proxy method that gets translated to my stored function. This is one of the scenarios the EdmFunction attribute is used for, and you even use it in your own code in the OracleFunctions class.

The basic infrastructure seems to be in place, since the function is correctly identified as a stored function, even including the parameters. It just does not get translated to SQL correctly. It works with the functions in the OracleFunctions class, just not with custom functions.

Shalex · Post by **Shalex** » Tue 22 Nov 2011 14:01

We are investigating the problem with incorrect translating of EdmFunction to SQL (the actual function name is missing). We will post here about the result as soon as possible.

Shalex · Post by **Shalex** » Thu 24 Nov 2011 09:19

The bug with generating SQL for EdmFunction is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Shalex · Post by **Shalex** » Thu 24 Nov 2011 16:04

Please try using the new (6.50.250) build of dotConnect for Oracle and notify us about the results.