Loss of Precision from C# to SQL Server

Question

This is an issue I am facing which is causing loss of precision when storing in SQL Server database from C# Entity Framework.

SQL Server Data Type is decimal(20, 15)
In C# Property is defined as public decimal AssignedGrossBudget { get; set; }
in C# value in variable (AssignedGrossBudget) is 34.09090909090909
But in SQL Server table it is 34.090000000000000

What could be wrong? (I am using Entity Framework db.SaveChanges(); and SQLBulkCopy to store data from c# to SQL Server)

I want to store 34.09090909090909 instead of 34.090000000000000.

I checked by directly inserting in the table and it works.

Pls use the Profiler to capture exact statement generated by EF, and post textdata. — dean, Apr 01 '14 at 11:30
Probably you need to configure precision for EF: http://stackoverflow.com/questions/3504660/decimal-precision-and-scale-in-ef-code-first — Uriil, Apr 01 '14 at 11:33
Is it the SaveChanges or the SqlBulkCopy that's introducing the problem? You won't use both for the same operation, so it should be obvious which one is to blame -- no need for your question to mention both. — Joe White, Apr 01 '14 at 11:46
Joe: Problem was with both, They are not in same operation though. SQL Bulk copy used to upload data from Excel to SQL Server (using EPPlus) while EF is used for other UI action. — user2739418, Apr 02 '14 at 09:15

score 2 · Answer 1 · answered Apr 01 '14 at 12:12

Thanks Urril and Remus.

I make changes in Entity Framework as below:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<StationMapping>().Property(x => x.AssignedGrossBudget).HasPrecision(35, 15);

        }

And for SQL BulkCopy I added Data Type as per Remus suggestion.

SpotLookupTable.Columns.Add(new DataColumn("GrossBudget",typeof(decimal)));

Its is working now and there is no loss (or Negligible).

Cheers

score 1 · Accepted Answer · answered Apr 01 '14 at 11:29

A simple example shows that no such loss of precision occurs with correctly written code:

    static void Main(string[] args)
    {
        decimal d = 34.09090909090909M;
        Console.WriteLine(d);

        SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
        scsb.IntegratedSecurity = true;
        scsb.DataSource = @".\sql2012";

        using (SqlConnection conn = new SqlConnection(scsb.ConnectionString)) {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand(
               "select cast(@d as DECIMAL(20,15))", conn))
            {
                cmd.Parameters.AddWithValue("@d", d);
                decimal rd = (decimal) cmd.ExecuteScalar();
                Console.WriteLine(rd);
            }
        }
    }

Therefore I must conclude the problem is with your code, which is not posted.

Loss of Precision from C# to SQL Server

2 Answers2