3

I had a very strange problem of removing fractional part of decimal while converting between decimal and decimal?

My Code (the data is fetched from database using EntityFramework):

var rs = from s in db.SalesDocHeader
         select new SalesTransaction
         {
             SalesDocHeaderId = s.id,
             TotalGrossAmount = s.total_gross ?? 0M
         };

Definition of SalesTransaction class:

public class SalesTransaction
{
    public int SalesDocHeaderId { get; set; }
    public decimal TotalGrossAmount { get; set; }       
}

Definition of SalesDocHeader class (generated by Entity Framework).

public partial class SalesDocHeader
 {
    public int id { get; set; }
    public Nullable<decimal> total_gross { get; set; }
 }

Whenever the above code is executed TotalGrossAmount is rounded to largest previous integer. So if s.total_gross = 32.9, TotalGrossAmount becomes 32, 64.3 becomes 64 and so on.

How can I prevent this problem?

Liam
  • 27,717
  • 28
  • 128
  • 190
iljon
  • 398
  • 2
  • 16
  • 1
    EF does nothing of the sort. What is the type and *precision* of the underlying field? If it is eg, `numeric(18,0)`, it means you can have 18 digits but no decimals. Saving any decimals to such a column will result in truncation. How are the values generated and where are they stored? Is there any code that modifies the values before saving them? – Panagiotis Kanavos Feb 04 '16 at 10:36
  • Are you sure s.total_gross == 32.9? What happens if you change SalesTransaction.TotalGrossAmount to a Nullable? – Liam Feb 04 '16 at 10:38
  • How do you know the value is truncated? Did you add a watch during debugging or is the number displayed truncated in the UI? Could you be using the wrong format string or truncatig when binding/displaying the value in the UI? – Panagiotis Kanavos Feb 04 '16 at 10:40
  • total_gross is represented in DB as money (MS SQL Server) and for sure it is not truncated. Value is properly fetched. Moreover, when I change decimal TotalGrossAmount to Nullable TotalGrossAmount it works fine. But I'm wonder what's the problem in the code above. – iljon Feb 04 '16 at 10:44
  • I'm sure s.total_gross = 32.9. if I change SalesTransaction.TotalGrossAmount to a Nullable it works fine. – iljon Feb 04 '16 at 10:45
  • Yes, during debugging I can see value is truncated. Also on the report which I produce, the value is also truncated. – iljon Feb 04 '16 at 10:47
  • You should improve your question, including all relevant details. As it is, the problem cannot be reproduced, and you will not get help. Please, strip the code to bare minimum necessary to reproduce the problem and show the model configuration, the XAML, and all relevant code. There mus be somethign that you're not showing which is creating the problem. Please, see this: http://stackoverflow.com/help/mcve – JotaBe Feb 05 '16 at 09:45

1 Answers1

0

By default EF maps all decimals to decimal(18,0) on the DB.

You must configure the model to hold the desired number of decimals. Please, see this: Decimal precision and scale in EF Code First

NOTE: remember that there are many things that happen on the C# side, i.e. before hitting the DB. So, if you don't have a good configuration for your model, you'll get this kind of problems. Your model should be correctly configured to match your DB.

Community
  • 1
  • 1
JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • It is not the case. total_gross is defined in DB as money type field and it is properly mapped by EF as decimal(19,4). Total_gross field is referenced in many places and everything works fine only in the case I mentioned above problem raises. That's strange. – iljon Feb 05 '16 at 07:41