To match the data types of columns in the database and in the class

Question

Question: what types of columns should I use in the database and in the class for the correct operation of the application?

Project: https://github.com/jhon65496/TypeDataCnsl01

Used by SQLite

There is a problem with the columns IndexValue, Discount. The goal is to get data from the database and perform calculations.

If:

• in the database, the columns IndexValue, Discount are of type DECIMAL(10, 5),;
• in the Index class, the fields IndexValue, Discount are of type string.

then there are no problems and I get the data.

If

• in the database, the columns IndexValue, Discount are of type DECIMAL(10, 5);
• in the Index class, the fields IndexValue, Discount are of type decimal.

I get an error when debugging.

Error:

The recipient of the call has created an exception

The error occurs on the line

var items = indexesRepository.Items.toArray();

in the Load Data() method in the `IndexesViewModel' class.

public class IndexesViewModel
{   
    // ...
    // Code
    // ...
    
    public void LoadData()
    {         
        var items = indexesRepository.Items.ToArray();
        Indexes = new ObservableCollection<Index>(items);
    }
}

Is used:

• Console .NET Framework;
• System.Data.SQLite.

Code

Index class:

[Table("Indexes")]
public class Index 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    // decimal
    // float        
    public decimal IndexValue { get; set; }
    public decimal Discount { get; set; }        
}

DbContextIndexes:

public class DbContextIndexes : DbContext
{
    static SQLiteConnection sqliteConnection;
    
    public DbContextIndexes() : base("DefaultConnection")
    {
    }

    public DbSet<Index> Indexes { get; set; }
}

IndexesRepository:

class IndexesRepository 
{
    private readonly DbSet<Index> _Set;
    DbContextIndexes _db;

    public IndexesRepository(DbContextIndexes db)
    {
        _db = db;
        _Set = _db.Set<Index>();
    }

    private IQueryable<Index> items;

    public virtual IQueryable<Index> Items
    {
        get
        {
            items = _Set;
            return items;
        }
        set
        {
            items = value;
        }
    }
}

IndexesViewModel:

public class IndexesViewModel
{   
    IndexesRepository indexesRepository;
    
    DbContextIndexes DataContextApp;

    public IndexesViewModel()
    {
        this.DataContextApp = new DbContextIndexes();
        indexesRepository   = new IndexesRepository(this.DataContextApp);

        LoadData();
    }

    private ObservableCollection<Index> indexes;

    public ObservableCollection<Index> Indexes
    {
        get { return indexes; }
        set 
        {
            indexes = value;                
        }
    }

    public void LoadData()
    {         
        var items = indexesRepository.Items.ToArray(); // <== Тут ошибка !!!
        Indexes = new ObservableCollection<Index>(items);
    }
}

SQL indexes:

CREATE TABLE "Indexes" 
(
    "Id"    INTEGER UNIQUE,
    "Name"  VARCHAR NOT NULL,
    "IndexValue"    DECIMAL(10, 5),
    "Discount"  DECIMAL(10, 5),
    "Description"   TEXT,
    PRIMARY KEY("Id" AUTOINCREMENT)
);

packages.config:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.4.4" targetFramework="net48" />
  <package id="Stub.System.Data.SQLite.Core.NetFramework" version="1.0.118.0" targetFramework="net48" />
  <package id="System.Data.SQLite" version="1.0.118.0" targetFramework="net48" />
  <package id="System.Data.SQLite.Core" version="1.0.118.0" targetFramework="net48" />
  <package id="System.Data.SQLite.EF6" version="1.0.118.0" targetFramework="net48" />
  <package id="System.Data.SQLite.Linq" version="1.0.118.0" targetFramework="net48" />
</packages>

App.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
    <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=dbAppIndexes.db" providerName="System.Data.SQLite" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
  </startup>
  <entityFramework>
    <providers>
        <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <!--<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />-->
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    <remove invariant="System.Data.SQLite" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /></DbProviderFactories>
  </system.data>
</configuration>

Screenshot #1:

Screenshot #2:

---

Update-1

It's working.
Replaced the comma , with a period ..

What is the point?

Can I store data in a database only with a semicolon?
can I not store data in a database ?
Which types should I choose to store the IndexValue and Discount fields.

---

Update-2. Added: `CultureInfo'.

Result: it doesn't work

Does not work.
Project: https://github.com/jhon65496/TypeDataCnsl01/tree/CultureInfo01

I downloaded the Sqlite application from the official website.
I created a SQLLite database using an SQL query.
I have created an application.
In the application, I did not create the code used by CultureInfo .

Added in Main() in program.cs:

var cultureInfo = new CultureInfo("ru-RU");
CultureInfo.DefaultThreadCurrentCulture = cultureInfo;
CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;

Full code

class Program
{
    static void Main(string[] args)
    {
        var cultureInfo = new CultureInfo("ru-RU");
        CultureInfo.DefaultThreadCurrentCulture = cultureInfo;
        CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;

        IndexesViewModel indexesViewModel = new IndexesViewModel();
    }
}

Error text

Message = The recipient of the call has created an exception.

Internal exception 1:
FormatException: The input string had an incorrect format.

The full text of the error

System.Reflection.TargetInvocationException
  HResult=0x80131604
  Сообщение = Адресат вызова создал исключение.
  Источник = mscorlib
  Трассировка стека:
   в System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   в System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   в System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetTypedValueDefault(DbDataReader reader, Int32 ordinal)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
   в System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   в System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   в System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   в System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

  Изначально это исключение было создано в этом стеке вызовов: 
    [Внешний код]

Внутреннее исключение 1:
FormatException: Входная строка имела неверный формат.

---

Update-3

Format: Russia

---

Update-4.

Description: Prop: [NotMapped], public string FormattedIndexValue

Error:
Message = The recipient of the call has created an exception.
FormatException: The input string had an incorrect format.

Project: https://github.com/jhon65496/TypeDataCnsl01/tree/PropNotMapString01

I didn't quite understand the phrase:
Just note that in Linq queries you can't use it in things like Select and Where, etc. where it translates to SQL, use IndexValue. I'll do as I understand.

I have deleted unnecessary fields. Now the model looks like this;

Index.cs

[Table("Indexes")]
public class Index 
{
    public int Id { get; set; }
    public string Name { get; set; }

    public decimal IndexValue { get; set; }        
    private readonly CultureInfo _uiCultureInfo = new CultureInfo("ru-RU");
    [NotMapped]
    public string FormattedIndexValue
    {
        get => IndexValue.ToString(_uiCultureInfo);
        set => IndexValue = decimal.Parse(value, _uiCultureInfo);
    }
}

Table "Indexes"

CREATE TABLE "Indexes" (
    "Id"    INTEGER UNIQUE,
    "Name"  VARCHAR NOT NULL,
    "IndexValue"    DECIMAL(10, 5) NOT NULL,
    PRIMARY KEY("Id" AUTOINCREMENT)
);

Id	Name	IndexValue
1	Name-1	10,10

Testing Log
If the table has IndexValue = 10 - the application reads the data;
If the table has IndexValue = 10.10 - the application reads the data;
If the table has IndexValue = 10,10 - error!!!.

IndexesViewModel(I didn't make any changes)

public void LoadData()
{         
    var items = indexesRepository.Items.ToArray();
    Indexes = new ObservableCollection<Index>(items);
}

IndexesRepository(I didn't make any changes)

class IndexesRepository 
    {

        private readonly DbSet<Index> _Set;
        DbContextIndexes _db;

        public IndexesRepository(DbContextIndexes db)
        {
            _db = db;
            _Set = _db.Set<Index>();
        }

        private IQueryable<Index> items;
        public virtual IQueryable<Index> Items
        {
            get
            {
                items = _Set;
                return items;
            }
            set
            {
                items = value;
            }
        }

    }

Error description

System.Reflection.TargetInvocationException
  HResult=0x80131604
  Сообщение = Адресат вызова создал исключение.
  Источник = mscorlib
  Трассировка стека:
   в System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   в System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   в System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetTypedValueDefault(DbDataReader reader, Int32 ordinal)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
   в System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
   в System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   в System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   в System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   в System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)


  Изначально это исключение было создано в этом стеке вызовов: 
    [Внешний код]

Внутреннее исключение 1:
FormatException: Входная строка имела неверный формат.

Answer 1

The issue is likely going to be a difference in regional settings between the database server and the server or client where your .Net code is executing. By default .Net will be using the system region settings, which includes settings for number formats, particularly the "Digit Grouping Symbol" and the "Decimal symbol", plus to a lesser degree the "List separator".

Between countries, some use "." for a decimal separator and "," for a grouping separator, while other countries use "," for decimal, and "." for grouping, which makes things really flipping confusing when a server set up for one receives numbers formatted for the other. Internally a decimal is a decimal, the symbol settings affect how these are displayed as strings or parsed from strings. Since EF is effectively building SQL to run against the database, this becomes a problem because at the end of the day it will be turning your decimal values into a string, which the DB won't understand unless it's using the same number formatting.

The first option to solve the problem is ensure that both servers, (assuming an ASP.Net application) web server and db server are using the same regional culture settings, so expecting the same number format.

If that option is not practical, then you can update the web server's culture setting programatically on initialization using:

var cultureInfo = new CultureInfo("en-US");
CultureInfo.DefaultThreadCurrentCulture = cultureInfo;
CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;

If the web server is set/hosted in Russia or Eastern Europe an the database installation defaulted to US English number formatting.

For web applications themselves where clients may be using a system with different UI culture settings you will need to know what UI cultures each client is using when they log in or based on the request headers from the browser and use that to parse and format any decimal values coming in and out of the client browser.

Edit:

If you do the above the force the application thread to use "en-US" with the "." decimal separator, the issue is that if you later use ToString() or Parse on a string representation of the decimal it will be expecting the "." as the decimal separator, where users in your region might expect to use ",". To get around this you don't need to change the IndexValue / Discount to strings with a "ru-RU" culture in the formatting, but rather add an unmapped property to handle getting and setting the value as a formatted string:

public decimal IndexValue { get; set; }
private readonly CultureInfo _uiCultureInfo = new CultureInfo("ru-RU");
[NotMapped]
public string FormattedIndexValue
{
    get => IndexValue.ToString(_uiCultureInfo);
    set => IndexValue = decimal.Parse(value, _uiCultureInfo);
}

This tells EF that there isn't a column named FormattedIndexValue, it is like a computed app-only value. Just note that in Linq queries you cannot use it in things like Select and Where etc. where that translates to SQL, use IndexValue.