This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi all, Linq query:
TotalAmount, PercentageAsFactor and the Absolute are all of type 'decimal'. Generated SQL query: SELECT SUM(((t2.TotalAmount * 0.10000000000000000) + 0.000000000000000000)) FROM [Reservations] [t0] INNER JOIN [ActionCode] [t1] ON [t0].[ActionCodeId] = [t1].[Id] INNER JOIN [Invoices] [t2] ON [t0].[Id] = [t2].[ReservationId] Error: [OverflowException: Conversion overflows.] System.Data.SqlClient.SqlBuffer.getDecimal() +2060386 System.Data.SqlClient.SqlBuffer.getValue() +5057210 System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) +111 System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) +109 System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +105 System.Data.SqlClient.SqlCommand.ExecuteScalar() +150 ..() +34 ..(IUnitOfWork , IDbCommand , ) +513 ..(IUnitOfWork , IDbCommand ) +125 ..() +299 ..(String , IdentifierExpression , Query ) +542 Mindscape.LightSpeed.UnitOfWork.Calculate(String calculation, IdentifierExpression attribute, Query query) +133 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) +992 Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +315 Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +43 System.Linq.Queryable.Sum(IQueryable`1 source) +163 Extra info: When I copy paste the query, the result set is just fine, so this goes wrong when trying to read the result set from SQL. http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/f3b4c193-c332-4a99-9c20-cd1c226ec37b http://connect.microsoft.com/VisualStudio/feedback/details/316993/overflowexception-when-querying-decimal-38-0-through-linq-to-sql Lightspeed version: 4.0.524.17831 Not sure how to approach this, besides moving to floating points. Can I truncate the result somehow? Cheers! |
|
|
Against my expectation, this will work: Query:
Resulted SQL: SELECT SUM(((t2.TotalAmount * 0.1) + 0)) FROM [Reservations] [t0] INNER JOIN [ActionCode] [t1] ON [t0].[ActionCodeId] = [t1].[Id] INNER JOIN [Invoices] [t2] ON [t0].[Id] = [t2].[ReservationId] So when the decimals aren't zero leading, it won't force SQL into overflowing the decimal. |
|
|
Looking at the internals of the SQL client, it looks like SQL Server supports a wider range than the .NET Decimal type (128 bits vs. 96 bits). As per the Connect bug report you linked, I think it might work if you reduce the precision of the SQL Server columns (the Connect discussion seems to imply DECIMAL(29) should be safe). (It's not to do with leading or trailing zeroes, by the way -- it's due to doing a GetDouble() vs. a GetDecimal() to extract the result from the underlying SqlDataReader.) |
|
|
Thanks for your wisdom! I generate my database from the Lightspeed model, is this something that needs to change in Lightspeed too (when creating the database from the model)? Just curious ;-). |
|
|
Hmm, we should already be generating Decimal fields as DECIMAL(24, 10) columns, which should be CLR-safe. Is that not what you're seeing? |
|
|
Hi Ivan, Nope, I see (24,18)s. A detail is that these were doubles before but I migrated it to decimal (by using update database from the model). I develop very Lightspeed model driven. Thanks, Dennis |
|
|
Whoops, sorry, misread my own code. DECIMAL(24, 18) is what we'd expect and what we believe is correct for CLR System.Decimal compatibility. I may have to rope in our SQL Server guru on this one. |
|
|
Hey Ivan, Let there be ninjas!, Seriously though, if I can help with something, provide information be sure to ask! Cheers, Dennis |
|
|
Hi Dennis, The issue here isn't specifically down to the column definitions themselves, but rather the resulting precision of the decimal put out by the SUM operation causing an overflow when it is being converted back into a decimal. The background to this as Ivan noted is that SqlDecimal supports a higher precision than the System.Decimal type. Any SqlDecimal with a precision higher than 29 will cause an overflow. Its a bit frustrating that there is a reasonable different between SqlDecimal and Decimal in the precision and SQL Server will by default return 38 precision if needed so this can easily lead to overflows. e.g. Consider the following decimal with a precision of 30: var d1 = SqlDecimal.Parse("12345.1234567890123456789012345"); d1.Value (which returns the System.Decimal equivalent) will throw an overflow exception. If you remove the last decimal place from that so it has a precision of 29 then you will not encounter the exception. So what is occurring with the query is the result from your SUM() is returning a value with a precision of >= 30 which leads to the overflow when the result is attempted to be converted back to a System.Decimal. The only control you are going to have over this is to truncate the value being multiplied against the column in the query - e.g. var totalDiscount = (from r in Repository.FindAll This will reduce the precision required for the result avoiding the overflow.
|
|
|
Bugger - Ivan has pointed out to me that I have put you slightly wrong. Decimal.Round is not going to work in this case because we dont have a translation for it (in my testing for this I was working with literals so that was working fine, but putting it around a column mean you are going to try and get this run server side..), so I guess that brings things back to either going back to using doubles as you were previously or executing the selection client side, although that is likely to have quite a negative performance impact on things so I wouldnt expect that will be a realistic option. The other thing you could look at is lowering the precision on the columns if that is an option (in particular the PercentageAsFactor) so that it doesn't expand the precision of the result in the SUM operation.
|
|
|
You guys are awesome for brainstorming this over! I can see how hard this problem is to solve completely and correctly in the general sense. For now I apparently (but not verified) force the LINQ executor into using getDouble on the SQL reader, which for my usage is 'good enough'. This works because of the double cast.
|
|