Zumero Sqlite Sql Server Data Class Invalid Cast

Question

My team and I are building a mobile app in Xamarin Forms to allow our clients to have some rudimentary access to their data via mobile. We are using zumero to handle creating a local sqlite copy of the ms sql server database. In order to get Entity Framework Core to work on the device, I created the models and context by reverse engineering off the sqlite file that zumero synced through.

public partial class tblEmployeeSchedule : BaseModel, ICrewMember
{
    //...
    public string DtDate { get; set; }

public class EFDatabase : IDataStore
{
    public DataContext Context { get; set; }
    public EFDatabase(string filepath)
    {
        try
        {
            this.Context = new DataContext(filepath);

public partial class DataContext : DbContext
{
    public string ConnString { get; private set; }

    public DataContext(string filepath)
    {
        this.ConnString = filepath;
    }

    //...
    public virtual DbSet<tblEmployeeSchedule> tblEmployeeSchedule { get; set; }
    //...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //...
        modelBuilder.Entity<tblEmployeeSchedule>(entity =>
        {
            entity.HasKey(e => e.Oid);

            entity.Property(e => e.DtDate).HasColumnName("dt_date");
        //...

For the next phase of development, I added a WinForms project to the Visual Studio solution and tried to create a really simple application that has similar functionality to the mobile app, and I wanted to be able to reuse the data model if possible.

At first, I mistakenly thought I could simply override one line of code:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlite($"Data Source={ConnString}");

by replacing it with this:

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseSqlServer(ConnString);

var connString = $"Server={server}; Database={db}; User Id={username}; Password={password};";
Program.Database = new SqlServerDatabase(connString);

But when I try to run the program, it threw the following error:

InvalidCastException
Message: "Specified cast is not valid."
Source: "Microsoft.Data.SqlClient"

I'm guessing that the error is coming from the data model mismatch. When Zumero synced through the information, it created a column of type text in the SQLite file. I tried using the data model I had reverse engineered directly off the SQL Server, and using it to create a SQLite file, and the column type was datetime.

I also tried using the protected override void OnModelCreating(ModelBuilder modelBuilder) from the SQL Server reverse engineer, and that gave the following error message: "The property 'tblCrewSchedule.DtDate' is of type 'string' which is not supported by current database provider. Either change the property CLR type or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'."

I have been trying to think of ways that I can construct a code-first data model that will be able to be used by both sides, and I can't think of a solution. I thought maybe if Zumero knows of a way that I can create a model that the property is of type DateTime in C#, even though Zumero is creating a SQLite column of type text, maybe that would be a possible solution path? Anyway, I'd like to find a way to be able to use the same data model for both projects, if that's possible.

Is there any way to arrange Zumero, Sql Server, and/or my data model so that they can work together?

As a self-taught programmer, I'm really new to all of this, so any and all help is greatly appreciated! Thanks!!

Answer 1

Jeremy Sheeley looked over my code, discussed the question with me, and showed me a possible solution. He was very polite as he looked over the code of a self-taught coder. :-)

The question as asked is backwards of the correct answer, if I understand the answer correctly. Jeremy shared a code sample with me to help me to see how to construct the code that will map from models that were reverse engineered from SQL Server and allow EF to correctly use them with a SQLite data source. So, I was trying to go from my models that were engineered from the SQLite data source, but I need to reverse that and then apply value conversions in the data context.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
//...

public partial class SQLiteEFDatabase : EFDatabase
{
   //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        ConfigureValueConversions(modelBuilder);
    }

    //...

    private void ConfigureValueConversions(ModelBuilder modelBuilder)
    {
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            foreach (var property in entityType.GetProperties())
            {
                if (property.ClrType == typeof(Guid))
                {
                    property.SetValueConverter(new GuidToBytesConverter());
                }
            }

            foreach (var property in entityType.GetProperties())
            {
                if (property.ClrType == typeof(DateTime))
                {
                    property.SetValueConverter(new DateTimeToStringConverter());
                }
            }
        }
    }
}

Huge thanks to Jeremy for helping with this!!!