none
SQL Server decimal mapping to C# .Net short RRS feed

  • Question

  • I have a SQL LocalDB (V11.0) database.  My Invoice table in SQL Server Management Studio contains a decimal field (column), Invoice_discount, as follows:

    However, when I compile this, C# maps the Invoice_discount field to a short:

    There doesn't seem to be any explanation for this and it has not happened with any of my other SQL tables.  Is there any way I can force C# to map the SQL decimal to a decimal rather than a short?

    While I can deal with this in code, that would be cumbersome.

    Many thanks


    Mike Whalley

    Wednesday, September 25, 2019 8:09 AM

Answers

  • Hello,

    Try annotating the column (if using code first contention) with [Column(Typename = "decimal(10,2")]

    https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types#data-annotations

    public class Blog
    {
        public int BlogId { get; set; }
        [Column(TypeName = "varchar(200)")]
        public string Url { get; set; }
        [Column(TypeName = "decimal(5, 2)")]
        public decimal Rating { get; set; }
    }

    And

    class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>(eb =>
            {
                eb.Property(b => b.Url).HasColumnType("varchar(200)");
                eb.Property(b => b.Rating).HasColumnType("decimal(5, 2)");
            });
        }
    }
    
    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public decimal Rating { get; set; }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Mike Whalley Wednesday, October 9, 2019 6:10 AM
    Wednesday, September 25, 2019 10:40 AM
    Moderator
  • Hello Mike,

    So I used a table that was used to answer another forum question and added the two columns highlighted.

    Created a class project and modeled the table using Entity Framework 6 code first from database.

    Here is the generated code for the table.

    namespace DecimalShortTest
    {
        using System;
        using System.ComponentModel.DataAnnotations;
    
        public partial class Table_1
        {
            public int id { get; set; }
    
            [StringLength(50)]
            public string FirstName { get; set; }
    
            [StringLength(50)]
            public string LastName { get; set; }
    
            public DateTime? DMLDate { get; set; }
    
            public decimal? Invoice_discount { get; set; }
    
            public decimal? Invoice_discount1 { get; set; }
        }
    }

    Here is the context

    namespace DecimalShortTest
    {
        using System.Data.Entity;
    
        public partial class ForumExampleContext : DbContext
        {
            public ForumExampleContext()
                : base("name=ForumExampleContextConnectionString")
            {
            }
    
            public virtual DbSet<Table_1> Table_1 { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Table_1>()
                    .Property(e => e.Invoice_discount)
                    .HasPrecision(5, 0);
    
                modelBuilder.Entity<Table_1>()
                    .Property(e => e.Invoice_discount1)
                    .HasPrecision(18, 0);
            }
        }
    }

    So in this case the fields were mapped and precision done properly on both. 

    With that code generation worked as expected, have no clue why your code did short rather than decimal.

    Perhaps comment out your code for Invoice_discount and replace with the code about changing the table name and see what happens.

    Other than that a slightly better way to perform conversions is using language extensions. The following takes your code and creates extensions.

    public static class LanguageExtensions
    {
        public static decimal ToDecimal(this short pValue)
        {
            return (decimal.Parse(pValue.ToString())) / 100;
        }
    
        public static short ToShort(this decimal pValue)
        {
            return short.Parse(((decimal)pValue * 100).ToString("N0"));
        }
    }

    Extremely simple usage examples.

    decimal decimalValue = 12.3M;
    short shortValue = decimalValue.ToShort();
    decimal otherDecimal = shortValue.ToDecimal();

    Closing notes

    Make sure you have targeted the right database and table as I've not seen Entity Framework do what is happening with you.

    If you are using Entity Framework Core I can do the same as above and even without doubt Entity Framework Core would map incorrectly.

    Edit

    I just created a model using Entity Framework Core 2.2.6 using a different database and in this case using SQL-Express while the first code samples were done against a full versions of SQL-Server.

    Table class

    public partial class Table1
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public decimal? InvoiceDiscount { get; set; }
        public decimal? InvoiceDiscount1 { get; set; }
    }

    DbContext

    public partial class ForumExampleContext : DbContext
    {
        public ForumExampleContext()
        {
        }
    
        public ForumExampleContext(DbContextOptions<ForumExampleContext> options)
            : base(options)
        {
        }
    
        public virtual DbSet<Table1> Table1 { get; set; }
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(
                    "Server=.\\SQLEXPRESS;Database=ForumExample;" + 
                    "Trusted_Connection=True;");
            }
        }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("ProductVersion", "2.2.6-servicing-10079");
    
            modelBuilder.Entity<Table1>(entity =>
            {
                entity.ToTable("Table_1");
    
                entity.Property(e => e.InvoiceDiscount)
                    .HasColumnName("Invoice_discount")
                    .HasColumnType("decimal(5, 0)");
    
                entity.Property(e => e.InvoiceDiscount1)
                    .HasColumnName("Invoice_discount1")
                    .HasColumnType("decimal(18, 0)");
            });
    
            modelBuilder.HasSequence<int>("seq_test").HasMin(1);
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, September 26, 2019 11:36 AM
    Moderator

All replies

  • Hello,

    Try annotating the column (if using code first contention) with [Column(Typename = "decimal(10,2")]

    https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types#data-annotations

    public class Blog
    {
        public int BlogId { get; set; }
        [Column(TypeName = "varchar(200)")]
        public string Url { get; set; }
        [Column(TypeName = "decimal(5, 2)")]
        public decimal Rating { get; set; }
    }

    And

    class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>(eb =>
            {
                eb.Property(b => b.Url).HasColumnType("varchar(200)");
                eb.Property(b => b.Rating).HasColumnType("decimal(5, 2)");
            });
        }
    }
    
    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public decimal Rating { get; set; }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Mike Whalley Wednesday, October 9, 2019 6:10 AM
    Wednesday, September 25, 2019 10:40 AM
    Moderator
  • Hi Mike Whalley, 

    Thank you for posting here.

    For your question, you find that C# maps the Invoice_discount field to a short.

    According to SQL Server Data Type Mappings, C# always maps decimal data type in SQLServer to Decimal data type.

    Is decimal(5,2) type converted to the smallint type before mapping?

        declare @decimal decimal(5,2)=5.12
        declare @smallint smallint
        set @smallint=@decimal
        print(@smallint)
        /*
        5
        */
    

    Please check your code.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 26, 2019 7:38 AM
    Moderator
  • Thank you both, @KarenPayne and @XinyuZhao.  To answer you both (and forgive me if my terminology is not precise - I am not a professional coder):

    1.  I am using database first, then compiling with Entity Framework, so I have no control over the mapping, and the mapping of decimal to short seems inexplicable.  The classes created in my C# desktop application (not ASP.Net) are automatically generated and cannot be amended.

    2.  So it is not possible to adopt Karen's suggestion, although the idea of an over-ride after the application generates the classes from the database may be the answer.  I am not sure, however, how to do this, and at what point the over-ride method should be called (e.g. on first initialisation of MainWindow.xaml.cs or only when the particular WPF Window that uses the relevant class is initialised.  Can you help with an over-ride for the Invoice_discount field and tell me how and where to implement it in my code?

    As I said, it is puzzling as to why the mapping produces a short from a database decimal, when this is all automatic under EF and is not code driven.

    BTW, my code solution for converting from decimal to short is somewhat tortuous:

    newInvoice.Invoice_discount = short.Parse(((decimal)value * 100).ToString("N0"));

    and every time I use it I have to convert back:

    decimal discount = (decimal.Parse(newInvoice.Invoice_discount.ToString())) / 100;
    Painful!


    Mike Whalley

    Thursday, September 26, 2019 8:58 AM
  • Hello Mike,

    So I used a table that was used to answer another forum question and added the two columns highlighted.

    Created a class project and modeled the table using Entity Framework 6 code first from database.

    Here is the generated code for the table.

    namespace DecimalShortTest
    {
        using System;
        using System.ComponentModel.DataAnnotations;
    
        public partial class Table_1
        {
            public int id { get; set; }
    
            [StringLength(50)]
            public string FirstName { get; set; }
    
            [StringLength(50)]
            public string LastName { get; set; }
    
            public DateTime? DMLDate { get; set; }
    
            public decimal? Invoice_discount { get; set; }
    
            public decimal? Invoice_discount1 { get; set; }
        }
    }

    Here is the context

    namespace DecimalShortTest
    {
        using System.Data.Entity;
    
        public partial class ForumExampleContext : DbContext
        {
            public ForumExampleContext()
                : base("name=ForumExampleContextConnectionString")
            {
            }
    
            public virtual DbSet<Table_1> Table_1 { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Table_1>()
                    .Property(e => e.Invoice_discount)
                    .HasPrecision(5, 0);
    
                modelBuilder.Entity<Table_1>()
                    .Property(e => e.Invoice_discount1)
                    .HasPrecision(18, 0);
            }
        }
    }

    So in this case the fields were mapped and precision done properly on both. 

    With that code generation worked as expected, have no clue why your code did short rather than decimal.

    Perhaps comment out your code for Invoice_discount and replace with the code about changing the table name and see what happens.

    Other than that a slightly better way to perform conversions is using language extensions. The following takes your code and creates extensions.

    public static class LanguageExtensions
    {
        public static decimal ToDecimal(this short pValue)
        {
            return (decimal.Parse(pValue.ToString())) / 100;
        }
    
        public static short ToShort(this decimal pValue)
        {
            return short.Parse(((decimal)pValue * 100).ToString("N0"));
        }
    }

    Extremely simple usage examples.

    decimal decimalValue = 12.3M;
    short shortValue = decimalValue.ToShort();
    decimal otherDecimal = shortValue.ToDecimal();

    Closing notes

    Make sure you have targeted the right database and table as I've not seen Entity Framework do what is happening with you.

    If you are using Entity Framework Core I can do the same as above and even without doubt Entity Framework Core would map incorrectly.

    Edit

    I just created a model using Entity Framework Core 2.2.6 using a different database and in this case using SQL-Express while the first code samples were done against a full versions of SQL-Server.

    Table class

    public partial class Table1
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public decimal? InvoiceDiscount { get; set; }
        public decimal? InvoiceDiscount1 { get; set; }
    }

    DbContext

    public partial class ForumExampleContext : DbContext
    {
        public ForumExampleContext()
        {
        }
    
        public ForumExampleContext(DbContextOptions<ForumExampleContext> options)
            : base(options)
        {
        }
    
        public virtual DbSet<Table1> Table1 { get; set; }
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(
                    "Server=.\\SQLEXPRESS;Database=ForumExample;" + 
                    "Trusted_Connection=True;");
            }
        }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("ProductVersion", "2.2.6-servicing-10079");
    
            modelBuilder.Entity<Table1>(entity =>
            {
                entity.ToTable("Table_1");
    
                entity.Property(e => e.InvoiceDiscount)
                    .HasColumnName("Invoice_discount")
                    .HasColumnType("decimal(5, 0)");
    
                entity.Property(e => e.InvoiceDiscount1)
                    .HasColumnName("Invoice_discount1")
                    .HasColumnType("decimal(18, 0)");
            });
    
            modelBuilder.HasSequence<int>("seq_test").HasMin(1);
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, September 26, 2019 11:36 AM
    Moderator
  • Hello Mike,

    Checking in to see if there is any progress. If not another option is to use SSMS (SQL-Server Management Studio) to generate scripts for your database without data and post the script on Microsoft OneDrive (login is the same as this forum) and provide a link back here for me to scaffold the database and see if the same happens.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, September 28, 2019 11:35 AM
    Moderator
  • Hello Karen

    Apologies – I overlooked the notification of your post on Thursday.  For that, many thanks – you have spent some time on this and I am grateful for your work and help.

    I have a concern about applying an override as I have existing users and datafiles.  My logic is (and happy to be corrected as I always struggle with the more complex aspects of SQL, ADO.NET and EF):  the datafile (.mdf and .ldf) which contains the data generated by the application is created by the application, and its table field assignments will therefore follow and mimic the classes created by the application.  The existing datafiles will therefore expect a short for the Invoice_discount field.  If I apply and call an override to convert the field to decimal, accessing or writing to an existing datafile will generate a type mismatch and fail, making the data inaccessible.

    If that is correct, my only option is to use the LanguageExtension you have proposed (and for which thanks again), as this does not interfere with the data drawn from and written to the datafile, which will remain as a short. (BTW, I have discovered that it is necessary to use the string format ToString(“F0”) rather than “N0” as the latter causes a run-time error if the decimal contains delimiters such as commas.)

    I would love to create a script of the tables for you but have been unable to work out how to do this from SSMS – happy to have further guidance from you if you are willing to pursue this.

    Thanks again.


    Mike Whalley

    Sunday, September 29, 2019 1:59 PM
  • Hello Mike,

    First off I will be gone today so if you can step through the steps below I will look at them when I get home.

    If you don't have SSMS it's an essential tool and free from Microsoft, download it here.

    Once installed, on startup you will be prompted to connect to a server be it a localDb, non-server based or server based database.

    Once connected follow the steps on this page and write (by default) the script to a file. Logon to Microsoft OneDrive with the user name and password used here, drag the script file to the bottom of the OneDrive page and it will upload the file. Then hover as per below, click then click and present the link here.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, September 29, 2019 2:16 PM
    Moderator
  • Karen

    I have been using SSMS for some time and all my database design and development is done in SSMS.  Hence database first rather than code first.  The .edmx model is then generated from the SSMS database with ADO.Net and EF.  I am using LocalDb.

    However, there were no links in your message as seems to have been intended, and I still need your guidance as to how to create and export a script of the database tables (excluding the data) from SSMS.  I am able to do the One Drive sharing once I have the file created to share with you, but the link for the user to use is also missing.

    Thanks again


    Mike Whalley

    Sunday, September 29, 2019 9:34 PM
  • Here is the generate script link, will check back tomorrow.

    https://docs.microsoft.com/en-us/sql/ssms/scripting/generate-scripts-sql-server-management-studio?view=sql-server-2017

    I would also recommend not using a .edmx approach, instead code first database first without a .edmx as you have greater control over every aspect of the context and model. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, September 29, 2019 11:56 PM
    Moderator
  • Karen

    Here is my script file.

    I have scripted all of the tables in the database, but the first run (using "Script entire database ...") failed so I had to adopt the "Select specific database objects" option, but with all tables selected.  I hope this works for you.

    My connectionString, btw, is:

    <add name="PMMEntities" connectionString="metadata=res://*/PMMData.csdl|res://*/PMMData.ssdl|res://*/PMMData.msl;provider=System.Data.SqlClient;provider connection string=&quot;

          data source=(localdb)\v11.0; AttachDBFilename=|DataDirectory|\PMM.mdf; initial catalog=PMM;Integrated security=True;MultipleActiveResultSets=True; App=EntityFramework&quot;"

          providerName="System.Data.EntityClient" />


    I take your point about code first, but I adopted database first because of the ease of using SSMS and then ADO.Net to migrate the database into my application.  I am not knowledgeable enough to change this now, and have always been terrified of making changes to data table columns (names or type assignments) or adding new columns to tables because of a Microsoft warning (I cannot now find the note on this) that an existing datafile would not be readable if any such changes were made.

    Edit:  Attempting to save an entry to the Invoices database table, I have realised that the datafile is created on the SSMS database design (not the app mappings) and therefore expects a decimal for the Invoice_discount field, but the app data is producing a short.  This leads to a DbEntityValidationException error, meaning that I cannot write any data to that table from the app.  This also means that the only solution is to take up your suggestion of an override, to force the app to map the Invoice_discount field to decimal.  Do you agree?

    Edit 2: I have managed to use another Visual Studio Solution to import the Invoice table using ADO.Net and it has mapped the Invoice_discount field/column correctly to decimal in the application.  This suggests that the mapping in my main application was just an inexplicable glitch.  To fix this, I can only suggest deleting the .edmx file from my original Solution and then recreating it by creating a new ADO.Net Entity Data Model and then updating the model from the original database to see if it maps the Invoice table correctly this time.  My only concern is the possible loss of access to my data due to the change in the model - can you reassure me about this?


    Mike Whalley



    Monday, September 30, 2019 10:31 AM
  • Karen

    I haven't had any response from you and wonder if you are still on the case.  I am about to delete the .edmx file from my VS solution and then recreate the ADO.Net/EF model from the database, in the hope that this will correct the incorrect mapping, and that it won't cause data loss.  I will let you know the result.  Thanks for your time on this.

    If you are aware of the MS note on how to protect a datafile when changes are made to the underlying tables in the database design, can you provide a link?  It contained sample code for importing the data under the old model and then saving it on the basis of the new script/schema to avoid data loss.


    Mike Whalley

    Tuesday, October 8, 2019 5:56 PM
  • Karen

    I haven't had any response from you and wonder if you are still on the case.  I am about to delete the .edmx file from my VS solution and then recreate the ADO.Net/EF model from the database, in the hope that this will correct the incorrect mapping, and that it won't cause data loss.  I will let you know the result.  Thanks for your time on this.

    If you are aware of the MS note on how to protect a datafile when changes are made to the underlying tables in the database design, can you provide a link?  It contained sample code for importing the data under the old model and then saving it on the basis of the new script/schema to avoid data loss.


    Mike Whalley

    I didn't see your reply with the script, just saw it. Here is what was generated for me for Invoices.

    using System;
    using System.Collections.Generic;
    
    namespace EntityTesting.Models
    {
        public partial class Invoices
        {
            public Invoices()
            {
                CreditNotes = new HashSet<CreditNotes>();
                Timesheets = new HashSet<Timesheets>();
                Transactions = new HashSet<Transactions>();
            }
    
            public int InvoiceId { get; set; }
            public DateTime InvoiceDate { get; set; }
            public string InvoiceNumber { get; set; }
            public decimal? TotalServices { get; set; }
            public decimal? TotalGoods { get; set; }
            public decimal? TotalDisbursements { get; set; }
            public decimal? InvoiceDiscount { get; set; }
            public bool? VatApplied { get; set; }
            public int? VatrateId { get; set; }
            public string ServicesDescription { get; set; }
            public string GoodsDescription { get; set; }
            public bool? Paid { get; set; }
            public int? ProjectId { get; set; }
            public string Status { get; set; }
            public int? BusinessId { get; set; }
            public DateTime? DueDate { get; set; }
            public DateTime? PaidDate { get; set; }
            public int? CategoryId { get; set; }
            public int? SupplierId { get; set; }
            public bool? Creditor { get; set; }
            public int? CustomerId { get; set; }
    
            public virtual Businesses Business { get; set; }
            public virtual Projects Project { get; set; }
            public virtual ICollection<CreditNotes> CreditNotes { get; set; }
            public virtual ICollection<Timesheets> Timesheets { get; set; }
            public virtual ICollection<Transactions> Transactions { get; set; }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, October 8, 2019 7:38 PM
    Moderator
  • Karen

    Thank you again.  Which seems to confirm that my problem was a one off glitch, and I need to rebuild the model from the database, which I will now try (trusting it doesn't lead to a loss of data).

    I was interested to see that your model generated

    public decimal? InvoiceDiscount { get; set; }

    from the table column [Invoice_discount], whereas I get

    public Nullable<short> Invoice_discount { get; set; }

    Assume the conversion of the column name is just the way and means by which you have created the model. In any event, wish me luck with the regeneration of the model.

    If I need more help with my SQL database design and implementation, are you able to consult?


    Mike Whalley

    Tuesday, October 8, 2019 8:28 PM
  • My bad, I'm so use to use Entity Framework Core that is what I did which is why I don't have an underscore. Here is the Entity Framework 6 class.

    namespace ClassLibrary1
    {
        using System;
        using System.Collections.Generic;
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
    
        public partial class Invoice
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage(
                "Microsoft.Usage", 
                "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public Invoice()
            {
                CreditNotes = new HashSet<CreditNote>();
                Timesheets = new HashSet<Timesheet>();
                Transactions = new HashSet<Transaction>();
            }
    
            public int InvoiceID { get; set; }
    
            [Column(TypeName = "date")]
            public DateTime Invoice_date { get; set; }
    
            [StringLength(30)]
            public string Invoice_number { get; set; }
    
            [Column(TypeName = "money")]
            public decimal? Total_services { get; set; }
    
            [Column(TypeName = "money")]
            public decimal? Total_goods { get; set; }
    
            [Column(TypeName = "money")]
            public decimal? Total_disbursements { get; set; }
    
            public decimal? Invoice_discount { get; set; }
    
            public bool? VAT_applied { get; set; }
    
            public int? VATrateID { get; set; }
    
            [StringLength(500)]
            public string Services_description { get; set; }
    
            [StringLength(500)]
            public string Goods_description { get; set; }
    
            public bool? Paid { get; set; }
    
            public int? ProjectID { get; set; }
    
            [StringLength(20)]
            public string Status { get; set; }
    
            public int? BusinessID { get; set; }
    
            [Column(TypeName = "date")]
            public DateTime? Due_date { get; set; }
    
            [Column(TypeName = "date")]
            public DateTime? Paid_date { get; set; }
    
            public int? CategoryID { get; set; }
    
            public int? SupplierID { get; set; }
    
            public bool? Creditor { get; set; }
    
            public int? CustomerID { get; set; }
    
            public virtual Business Business { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage(
                "Microsoft.Usage", 
                "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<CreditNote> CreditNotes { get; set; }
    
            public virtual Project Project { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage(
                "Microsoft.Usage", 
                "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Timesheet> Timesheets { get; set; }
    
            [System.Diagnostics.CodeAnalysis.SuppressMessage(
                "Microsoft.Usage", 
                "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<Transaction> Transactions { get; set; }
        }
    }
    

    In regards to loss of data, first do a backup in SSMS then proceed.

    In regards to more help, simply start a new question as this one needs closure.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, October 8, 2019 9:20 PM
    Moderator
  • Karen

    Thanks again for all your help.  I have marked as answers your two initial responses to close off this thread.

    I hope I am successful in re-initialising the data tables.


    Mike Whalley

    Wednesday, October 9, 2019 6:11 AM
  • Karen

    Thanks again for all your help.  I have marked as answers your two initial responses to close off this thread.

    I hope I am successful in re-initialising the data tables.


    Mike Whalley

    See if this is helpful, inspect invoices in regards to column mapping.

    https://1drv.ms/u/s!AtGAgKKpqdWjjV8K0qZAfoAWOf7x?e=ou8wQi


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, October 9, 2019 1:31 PM
    Moderator
  • Karen

    Thank you.  I have been successful: I simply deleted the Invoices table from the .edmx model and then updated the model from the database to reinstate it - the [Invoice_discount] field/column now maps correctly to decimal.  No damage done to datafile and I have also, by this process, discovered that I can change the model by adding columns to the tables in the SSMS database and update the model from the database again without affecting the existing data.  Win win.  

    Many thanks for helping me to reach this conclusion.  Couldn't be more pleased!


    Mike Whalley

    Wednesday, October 9, 2019 4:04 PM
  • Karen

    I spoke too soon - I have discovered that the column changes I have made work on the development machine without loss of data, but users lose access to their data and the app crashes.

    I have posted a new question on this.  I don't know if you are still monitoring this thread, but I'd be grateful if you would take a look at:

    https://social.msdn.microsoft.com/Forums/en-US/75f5898e-e579-4d20-97f6-4e384391a7aa/how-do-i-add-columns-to-sqllocaldb-schema-modelled-using-ef6-database-first-without-users-losing?forum=csharpgeneral

    Mike


    Mike Whalley

    Tuesday, October 22, 2019 10:18 AM