Using generated datamodel in unit tests with SQLite

[mtanneryd]

Hi!

We use SQLite in our unit tests and SQL Server when actually running our application. With some tweeking Reverse POCO works fine with SQLite but there is one little problem remaining. I want to use the same generated datamodel code both when running unit tests and when running the application. Unfortunatly that does not work at the moment since the generated configuration code includes statements that need to be different during runtime for SQLite and SQL Server.

In the method "public void Configure(EntityTypeBuilder builder)" in each class derived from "IEntityTypeConfiguration" there are statements like the following for all integer columns:

builder.Property(x => x.Id).HasColumnName(@"Id").HasColumnType("int")
    .IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();

Using the tweeking mentioned above I can get that changed to

builder.Property(x => x.Id).HasColumnName(@"Id").HasColumnType("integer")
    .IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();

BUT, I need the same code to configure these columns with type "int" when running the application and with type "integer" when ruinning unit tests. So, it needs to configure differently at runtime depending on the underlying db type.

My current solution to this is to modify your code so that it emits the following (an example):

if (Environment.GetEnvironmentVariable("UseSQLite") == "true")
{
    builder.Property(x => x.Id).HasColumnName(@"Id").HasColumnType("integer").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
}
else
}
    builder.Property(x => x.Id).HasColumnName(@"Id").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
}

When running unit tests I set the environment variable UseSQLite to the value true.

This works but it feels rather hackish and awkward. If you could add functionality that resolves this issue in a more fashionable way it would be greatly appreciated.

[sjh37]

How about modifying the code to remove the `.HasColumnType()" addition?

Delete the following:

if (Column.ExcludedHasColumnType.Contains(c.SqlPropertyType))
    excludedHasColumnType = string.Format(" // .HasColumnType(\"{0}{1}\") was excluded", c.SqlPropertyType, columnTypeParameters);
else
    sb.AppendFormat(".HasColumnType(\"{0}{1}\")", c.SqlPropertyType, columnTypeParameters);

There are two places, one for EF6 and one for EFCore.

[mtanneryd]

Yeah, that would work too. I guess I'm losing out on something when skipping the HasColumnType but I'm not sure exactly what.

EDIT: Got into some issues with datetime formatting having removed the HasColumnType. I'll look into it.

[sjh37]

I was thinking of adding a flag to skip generating the HasColumnType, and I would be very interested to hear about the DateTime issue. Could you include it for DateTime types, and exclude it for all others? Seems a bit hacky and I'd like to get to the bottom of exactly what the problem is.

[mtanneryd]

We use datetime (as opposed to datetime2) in several columns in our db. EF Core for SQL Server defaults to datetime2 unless we use HasColumnType to tell it to use datetime. Without specifying that we really wantthe datetime rather than the datetime2 EF Core fails to serialize/deserialize our datetime columns.

My current way around this was to add some types to the ExcludedHasColumnType:

public static readonly List<string> ExcludedHasColumnType = new List<string>
        {
            "user-defined",
            "int",
            "varchar(max)",
            "nvarchar(max)",
        };

int: due to the int/integer issue between sql server and sqlite
varchar/nvarchar: due to the lack of support for "max" in sqlite
datetime: due to the datetime2 default of ef core/sql server