EF Core Power Tools - Sqlite data types different than those created in EF 6.0

[jonreis]

Describe what is not working as expected.

Hello Erik, I just found the EF Core Power Tools and the HandleBar support is awesome! I'm not sure I would be able to use EF Core with a database first approach without it. Thank you so much.

I have having a small problem with the data types that worked fine under EF6 using the Reverse Engineering tool with Sqlite.

Here is the table definition:

-- Script Date: 3/11/2018 1:21 PM - ErikEJ.SqlCeScripting version 3.5.2.75
CREATE TABLE [Schema] (
[SchemaId] INTEGER NOT NULL
, [SchemaVersion] nvarchar(16) NOT NULL
, [DatabaseVersion] nvarchar(16) NOT NULL
, [Expression] ntext NULL
, [Description] nvarchar(1024) NULL
, [ReadOnly] bit NOT NULL
, [ModifiedDateTime] datetime NOT NULL
, CONSTRAINT [sqlite_master_PK_Schema] PRIMARY KEY ([SchemaId])
);

In EF6, Readonly was translated into a bool, and ModifiedDateTime was translated into a DateTime.

 private bool _readOnly;
[DataMember]
public bool ReadOnly
{
  get { return _readOnly; }
  set
  {
    if (value != _readOnly)
    {
      _readOnly = value;
      OnPropertyChanged();
    }
  }
}

private System.DateTime _modifiedDateTime;
[DataMember]
public System.DateTime ModifiedDateTime
{
  get { return _modifiedDateTime; }
  set
  {
    if (value != _modifiedDateTime)
    {
      _modifiedDateTime = value;
      OnPropertyChanged();
    }
  }
}

In EF Core 2.1 (preview) /EF Power Tools 1.0.539 these get translated to strings.

    private string m_ReadOnly;
    public string ReadOnly
    {
       get { return m_ReadOnly; }
       set
       {
         if(m_ReadOnly != value)
         {
            m_ReadOnly = value;
            OnPropertyChanged();
         }
       }
    }
    private string m_ModifiedDateTime;
    public string ModifiedDateTime
    {
       get { return m_ModifiedDateTime; }
       set
       {
         if(m_ModifiedDateTime != value)
         {
            m_ModifiedDateTime = value;
            OnPropertyChanged();
         }
       }
    }

The ModelBuilder code looks like it has the correct types:

        modelBuilder.Entity<Schema>(entity =>
        {
            entity.ToTable("Schema");

            entity.Property(e => e.SchemaId).ValueGeneratedNever();

            entity.Property(e => e.DatabaseVersion)
                .IsRequired()
                .HasColumnType("nvarchar(16)");

            entity.Property(e => e.Description).HasColumnType("nvarchar(1024)");

            entity.Property(e => e.Expression).HasColumnType("ntext");

            entity.Property(e => e.ModifiedDateTime) // this is a datetime type
                .IsRequired()
                .HasColumnType("datetime");

            entity.Property(e => e.ReadOnly)  // this is a bool type
                .IsRequired()
                .HasColumnType("bit");

            entity.Property(e => e.SchemaVersion)
                .IsRequired()
                .HasColumnType("nvarchar(16)");
        });

Properties.hbs (partial)
+++++++++

{{#each properties}}
{{spaces 8}}private {{property-type}} m_{{property-name}};
{{#each property-annotations}}
{{spaces 8}}{{{property-annotation}}}
{{/each}}
{{spaces 8}}public {{property-type}} {{property-name}}
        {
           get { return m_{{property-name}}; }
           set
           {
             if(m_{{property-name}} != value)
             {
                m_{{property-name}} = value;
                OnPropertyChanged();
             }
           }
        }
{{/each}}

Am I doing something wrong, to get strings instead of the proper data types?

Another, very minor issue that doesn't matter is that the DbSet name in EF6 is Schemata, and in EF Core 2.1, it is Schemas

Further technical details

Toolbox/Power Tools version: 1.0.539

Database engine: SQlite

Visual Studio or SSMS version: 15.6.0

[jonreis]

Another difference between EF6 reverse engineering is that I have a table named Segment, and another one called Field. A field has a navigation property to Segment. Instead of the name being Field.Segment as it was in EF6, it called is Field.SegmentNameNavigation

    private Segment m_SegmentNameNavigation;
    public Segment SegmentNameNavigation
    {
       get { return m_SegmentNameNavigation; }
       set
       {
         if(m_SegmentNameNavigation != value)
         {
            m_SegmentNameNavigation = value;
            OnPropertyChanged();
         }
       }
    }

This is the only navigation property that seems to get named differently than EF6.

The modelbuilder code generated for this entity is:

        modelBuilder.Entity<Segment>(entity =>
        {
            entity.HasKey(e => e.Name);

            entity.ToTable("Segment");

            entity.Property(e => e.Name)
                .HasColumnType("nvarchar(3)")
                .ValueGeneratedNever();

            entity.Property(e => e.Description).HasColumnType("nvarchar(1024)");
        });

[bricelam]

Could be made better by dotnet/efcore#8824

[ErikEJ]

@jonreis Could I ask you to seperate your issues to keep the conversation scoped to a single issue , As @bricelam kindly mentioned, it is a known issue. In EF 6, the System.Data.Sqlite ADO.NET provider was used, it it was better at guessing datatype - also note that in reality, SQLite on has 4 "data types" - https://www.sqlite.org/datatype3.html - none of which are "bit"

Regarding the Schemata vs Schemas issue - are you using Pluralization? It is not the same between EF6 and EF Core Power Tools, I assume that could be the cause.

[ErikEJ]

If have created #656

[jonreis]

@ErikEJ sorry, yes I should have logged it as a different issue. Thanks for moving it.

I had Pluralization on. The table name is Schema to Schemas, instead of Schemata. I actually prefer Schemas, over Schemata, I just pointed it out as an aside.

[ErikEJ]

There is really nothing for me to do regarding this, so closing

[jonreis]

@ErikEJ you are right about Sqlite only having 4 data types, however; to keep the sql the same between Sql Server and Sqlite, it would be nice to have it mapped correctly.

[ErikEJ]

I understand, but maybe you should consider another database engine than SQLite then (one that is more stringly typed) - where is SQLite used?

[jonreis]

It is used as a local look up database, and application configuration. It used to be on SqlServerCe and I moved it over to Sqlite because SqlServerCe was problematic. It is wells suited to what it is being used for. We use full SqlServer for our main product.

I can always change the database definitions to use only the 4 datatypes that Sqlite supports, or I can just manually fix the model. That is not a problem. I just thought you might like to know that what worked in EF6, no longer works with EFP.

[ErikEJ]

@jonreis Appreciate the info, thanks!

[ErikEJ]

Btw - how was SQLCE problematic - maybe I could help your there?

[jonreis]

Thanks for your help Erik. We have already made the transition to Sqlite and have been quite happy with it. Our product sped up quite a bit after moving to Sqlite especially the initial database connection which would pause a few seconds, I think due having encryption turned on.

It seems that SQLCE is not being developed anymore and Microsoft seems to be using Sqlite more these days, including in visual studio. Sounds like you think SqlServerCe is still a viable option for new development?

It has been a few years, but are a couple of issues my customers had with SqlCE.

http://laurenthinoul.com/how-to-solve-dbproviderfactories-section-can-only-appear-once-per-config-file-error/

http://social.technet.microsoft.com/Forums/sqlserver/en-US/1b21bb10-1fa3-4954-87d2-f3fa61e8256a/sqlce-opens-slow-under-a-limited-user-account-in-windows-xp?forum=sqlce

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb6a01a7-0bfd-41e3-b4c8-34581c5ccaa3/sql-compact-4-locks-up-30seconds-when-accessing-encrypted-database-on-windows-xp-when-running-in?forum=sqlce

[ErikEJ]

I have blogged about this here: http://erikej.blogspot.dk/2013/08/faq-why-is-opening-my-sql-server.html

But you are right, SQLCE is deprecated, it is hard to find a perfect solution to most problems.

[jonreis]

Just wondering, in SQLite/SQL Server Compact Toolbox, you nicely handle the data types. For instance my bit field is shown as a checkbox. So it is nice to keep the "virtual" data types in Sqlite even if they get mapped to a different underlying type.

I am assuming from dotnet/efcore#8824, that is is an issue with .NET Core Sqlite provider, and not something with the EFP. Is that correct? I cannot imagine users would want to have to translate the datetime, bit, int, and others manually.

[ErikEJ]

Yes, this is due to the way EF Core implements reverse engineering for SQLite