Devart Forums

coeamyd

Hi Shalex,

thanks for your input. I just verified this behavior with EF to SqlServer and you are absolutely right. EF always astonishes me in how lacking it is compared to LINQ-to-SQL. I guess, I'll have to live with the workaround for now. We are too far into the project to switch now.

Thanks,
Christoph

coeamyd

Hi everyone,

I have been using Linq-to-SQL for quite a while and now switched to dotConnect for Oracle using Entity Framework 4. I have several queries, where the user can select the field to sort on. I also apply a default sorting on several other columns. With Linq-to-SQL I was able to do something like this:

Code: Select all

// apply default sort
query = query.OrderBy(it => it.ColA).ThenBy(it => it.ColB);

switch (sortKey) {
    case "ColC": query = query.OrderBy(it => it.ColC); break;
    case "ColD": query = query.OrderBy(it => it.ColD); break;
}

This would result in the last Order clause being the first to sort by, and the default sort being applied afterwards, i.e. "ORDER BY ColC, ColA, ColB". With dotConnect for Oracle, however, only the last OrderBy seems to be taken into account. When I use the ObjectQuery.ToTraceString method, I only get ORDER BY ColC or something similar. ColA and ColB are simply ignored.

I know, the simple workaround is to simply add the default sorting to all the sort operations, but this results in much less maintainable code and a bunch of redundancy. So, I was wondering, whether this might be a bug, that might be fixed in the near future.

Thanks,
Christoph

coeamyd

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.

coeamyd

Thanks for the clarification. I had not set the DefaultValue attribute in the store part. Works like a charm now. Great work.

coeamyd

Hi Shalex,

I just installed the new dotConnect for Oracle build from friday, because I have been experiencing the same issues. After updating, I still get all columns I manually changed to Identity=true marked as changed. Do I have to update my complete model once, before the change takes effect, or has this behavior not been changed? I could not find any information about it in the release notes.

Thanks,
Christoph

coeamyd

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

coeamyd

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

coeamyd

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

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

Devart Forums

Search found 9 matches

Re: Only last Order clause used

Only last Order clause used

Composable function producing invalid SQL code