Cannot convert object of type 'Devart.Data.Oracle.OracleDate' to object of type 'System.Decimal'.

[Sulphy]

Hi,

LinqConnect version: 4.1.207.0
Entity Developer version: 5.0.66
Devart Data version: 5.0.661.0
Devart Oracle version: 7.6.202.0
Devart Oracle Linq version: 4.1.207.0

I'm just following up on post by another user:

http://forums.devart.com/viewtopic.php?f=31&t=24922

This is actually the same project as I've taken over development. I've attempted a fresh upgrade where the project was originally created using linqconnect250pro.exe.

The upgrade for the most part went smoothly bar a few niggly things but appears to work in our dev environment. However when I update web.config to tell the app to use our UAT database things become a bit strange.

On this segment of code:

Code: Select all

 return _db.ExecuteQuery<ABC_FLASH>(
                string.Format(sql,user)).ToList();

The SQL that is passed is 100% valid (tested) and I can't figure out why I get the below response:

Cannot convert object of type 'Devart.Data.Oracle.OracleDate' to object of type 'System.Decimal'.

Observations / Notes:
1. The original development under the older framework is fine on all DB instances i.e dev and UAT.
2. I don't think this is relevant but thought it worth highlighting. When the upgrade was done it was still hooked up to the dev database with a DB account that has full permissions. The UAT instance is a little more locked down only to the point that DB objects can not be modified (so only the usual CRUD operations are permitted).
3. The upgraded project functions when pointing to the DEV database, but not UAT.
4. Following on from point 3 it would suggest that the UAT database was the most likely root cause of the issue. I've compared the table structure from both dev and UAT and they are both identical. Just to reiterate that UAT works fine on the original project.
5. I ruled out any rogue data in UAT be clearing out the table, creating a new entry and found that the issue returned once data was availble in the table again.
6. I've checked over the model and the darkest depths of various config files with no luck.
7. In the off chance there was something going on with the data context model I used Entity Framework to recreate the model from scratch using the 'Update model from database' option. Again this had no visible impact as the same error persists post this action.

So I'm really at a loss as where to go now. Any ideas?

Kind regards and thanks in advance!

[MariiaI]

Please specify the following details:
- the SQL query you are performing;
- the definitions of the entity classes being used;
- the DDL scripts for the database tables that are involved in the query;
- versions of the Oracle Server, and Client (if the Direct mode is not used);

If possible, please send us a small test project so that we are able to investigate this issue in more details.

[Sulphy]

Hiya,

I've done some more testing and the behaviour differences between Dev and UAT was simply due to program logic in that I have admin rights in Dev but not UAT. Hence the logic will use linq to sql (as it's a simple request) for admins, but a custom built SQL statement is passed for all other users via the .ExecuteQuery method.

So the issue squarely resides with this .ExcecuteQuery statement.

I'll send over an example via email later today.


Thanks.

[MariiaI]

Please send us an example project via our contact form or to mariiaI*devart*com.

[Sulphy]

Hi Mariial,

I dropped an email yesterday to yourself and to the general support account, hopefully they came through ok?

I've done some more testing and think I've found a possible cause, but I don't know if I'm chasing something that might not exist.

So to recap quickly. There are a couple of SQL statements passed using the .ExecuteQuery method. Both calls, which are not related to each other, use one of table models i.e. ABC_FLASH i.e.

_db.ExecuteQuery<ABC_FLASH>(sql).ToList();

The first call includes a filter on one of the date fields. Where as the second call doesn't. In testing what I've found is that if I replace the SQL in the first call with the SQL from the second call, the code will actually work fine when both calls are made. The same is true if I reverse this action and put the SQL from the first call in to the second call. So essentially if the SQL on both calls are one of the same everything is fine.

What I've proven is that:

1) The SQL once again is fine.
2) There is nothing wrong with the coding in the second call as I can get it to run under specific conditions.

Given the error that is generated, the fact that this has only started to happen post upgrade and my observations. I'm now wondering if Linqconnect Pro / Entity Framework is creating a frame for the query passed in the first call. And that this frame persists right round to the second call. So could this be some sort of caching behaviour? This might go someway as to explaining why I'm seeing such strange behaviour. As I say, I'm probably barking up the wrong tree :O)

[MariiaI]

Thank you for the additional information. However it is not enough for reproducing the issue.
Please specify the details we have asked you in the previous reply:
- the SQL query you are performing ("sql");
- the definitions of the entity classes being used("ABC_FLASH");
- the DDL scripts for the database tables that are involved in the query;
- versions of the Oracle Server, and Client (if the Direct mode is not used);
- small code sample, etc.

"So could this be some sort of caching behaviour?"

Please try using this code

Code: Select all

YourDataContext ctx = new YourDataContext();
ctx.EntityCachingMode = EntityCachingMode.StrongReference;

Or try disabling the compiled query cache (please refer here for more information about disabling it: http://forums.devart.com/viewtopic.php?t=28070#p95476 ).

Please tell us about the results.

For more information about Entity Cache Modes and compiled query cache, please refer to
http://www.devart.com/linqconnect/docs/ ... ntity.html
http://www.devart.com/linqconnect/docs/ ... eries.html

[Sulphy]

Hi,

Thanks for the update. I've tried the solutions you listed and neither resolved the issue. I have however been able to re-produce the issue and have sent over the project file, DDL scripts and the other bits you asked for.


Look forward to hearing back from you.

Kind regards,

P

[MariiaI]

Thank you for the test project. We have reproduced this issue. We will investigate it and inform you about the results as soon as possible.

[MariiaI]

The bug related to re-materialization of objects of the same type when performing various SQL-queries via ExecuteQuery () method is fixed.
New build of LinqConnect 4.4.428 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/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=31&t=28831.

[Sulphy]

Hi Mariial,

Just a quickie to say that this latest version with the fix did the job and our project is working as expected once more. Many thanks for sorting out so quickly!.


Kind regards,

[MariiaI]

If you have any further questions regarding working with LinqConnect, feel free to contact us.

[Sulphy]

Hi again,

Just following up on this post as I have a similar issue with the same .ExecuteQuery method. I've noticed during dev that I had to re-order the fields in the SQL otherwise I kept getting:

Input string was not in a correct format

I can't see why the order of fields in the SQL would matter but I eventually found a combination that works. However now that I've gone to production I'm getting intermittent issues where the code will work sometimes and all of a sudden for a minute or two stop working and the error logs fill up with some entries citing the same error 'Input string was not in a correct format'.

I've concluded that it's still an issue with the ExecuteQuery method but getting it to happen 100% to time is difficult. I generally have to refresh my page multiple times to get it to reoccur.

Just wondering if this is a known bug?


Thanks,

[MariiaI]

Hi again,
Just following up on this post as I have a similar issue with the same .ExecuteQuery method. I've noticed during dev that I had to re-order the fields in the SQL otherwise I kept getting:
Input string was not in a correct format

We couldn't reproduce this issue with the latest build of LinqConnect 4.4.484.
Please specify the following details:
- the version of LinqConnect/dotConnect for Oracle you are working with at the moment;
- whether this error could be reproduced with the test project, which you have sent us on 23.01.2014 20:33;
- if possible, please send us the generated SQL queries (when query executes without this error and with this error) and the full stack trace of this exception.

Looking forward to your reply.