Should this library be creating tables in a migration?
Tyler-V opened this issue · 5 comments
Tyler-V commented
I just noticed in my last EF Core migration it picked up these table properties not sure why but didn't see any documentation if this is required
I have multiple contexts that are registering UseQueryableValues
Of the 9 contexts, when I generate migrations - one of them (ConfigurationContext) - is creating tables that the others are not
Startup.cs
builder.Services.AddDbContext<AccumulationContext>(options =>
options.UseSqlServer(appSettings.ConnectionStrings.Accumulation,
sqlServerOptionsBuilder => { sqlServerOptionsBuilder.UseQueryableValues(); }));
builder.Services.AddDbContext<ConfigurationContext>(options =>
options.UseSqlServer(appSettings.ConnectionStrings.Configuration,
sqlServerOptionsBuilder => { sqlServerOptionsBuilder.UseQueryableValues(); }));
builder.Services.AddDbContext<DispenseCaptureContext>(options =>
options.UseSqlServer(appSettings.ConnectionStrings.DispenseCapture,
sqlServerOptionsBuilder => { sqlServerOptionsBuilder.UseQueryableValues(); }));
etc.
ConfigurationContextModelSnapshot.cs
protected override void BuildModel(ModelBuilder modelBuilder)
{
#pragma warning disable 612, 618
modelBuilder
.HasAnnotation("ProductVersion", "6.0.4")
.HasAnnotation("Relational:MaxIdentifierLength", 128);
SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder, 1L, 1);
modelBuilder.Entity("BlazarTech.QueryableValues.QueryableValuesEntity", b =>
{
b.Property<bool?>("Bool0")
.HasColumnType("bit");
b.Property<bool?>("Bool1")
.HasColumnType("bit");
b.Property<bool?>("Bool2")
.HasColumnType("bit");
b.Property<bool?>("Bool3")
.HasColumnType("bit");
b.Property<bool?>("Bool4")
.HasColumnType("bit");
b.Property<bool?>("Bool5")
.HasColumnType("bit");
b.Property<bool?>("Bool6")
.HasColumnType("bit");
b.Property<bool?>("Bool7")
.HasColumnType("bit");
b.Property<bool?>("Bool8")
.HasColumnType("bit");
b.Property<bool?>("Bool9")
.HasColumnType("bit");
b.Property<byte?>("Byte0")
.HasColumnType("tinyint");
b.Property<byte?>("Byte1")
.HasColumnType("tinyint");
b.Property<byte?>("Byte2")
.HasColumnType("tinyint");
b.Property<byte?>("Byte3")
.HasColumnType("tinyint");
b.Property<byte?>("Byte4")
.HasColumnType("tinyint");
b.Property<byte?>("Byte5")
.HasColumnType("tinyint");
b.Property<byte?>("Byte6")
.HasColumnType("tinyint");
b.Property<byte?>("Byte7")
.HasColumnType("tinyint");
b.Property<byte?>("Byte8")
.HasColumnType("tinyint");
b.Property<byte?>("Byte9")
.HasColumnType("tinyint");
b.Property<string>("Char0")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char1")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char2")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char3")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char4")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char5")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char6")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char7")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char8")
.HasColumnType("nvarchar(1)");
b.Property<string>("Char9")
.HasColumnType("nvarchar(1)");
b.Property<DateTime?>("DateTime0")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime1")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime2")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime3")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime4")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime5")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime6")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime7")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime8")
.HasColumnType("datetime2");
b.Property<DateTime?>("DateTime9")
.HasColumnType("datetime2");
b.Property<DateTimeOffset?>("DateTimeOffset0")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset1")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset2")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset3")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset4")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset5")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset6")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset7")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset8")
.HasColumnType("datetimeoffset");
b.Property<DateTimeOffset?>("DateTimeOffset9")
.HasColumnType("datetimeoffset");
b.Property<decimal?>("Decimal0")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal1")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal2")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal3")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal4")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal5")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal6")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal7")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal8")
.HasColumnType("decimal(18,6)");
b.Property<decimal?>("Decimal9")
.HasColumnType("decimal(18,6)");
b.Property<double?>("Double0")
.HasColumnType("float");
b.Property<double?>("Double1")
.HasColumnType("float");
b.Property<double?>("Double2")
.HasColumnType("float");
b.Property<double?>("Double3")
.HasColumnType("float");
b.Property<double?>("Double4")
.HasColumnType("float");
b.Property<double?>("Double5")
.HasColumnType("float");
b.Property<double?>("Double6")
.HasColumnType("float");
b.Property<double?>("Double7")
.HasColumnType("float");
b.Property<double?>("Double8")
.HasColumnType("float");
b.Property<double?>("Double9")
.HasColumnType("float");
b.Property<float?>("Float0")
.HasColumnType("real");
b.Property<float?>("Float1")
.HasColumnType("real");
b.Property<float?>("Float2")
.HasColumnType("real");
b.Property<float?>("Float3")
.HasColumnType("real");
b.Property<float?>("Float4")
.HasColumnType("real");
b.Property<float?>("Float5")
.HasColumnType("real");
b.Property<float?>("Float6")
.HasColumnType("real");
b.Property<float?>("Float7")
.HasColumnType("real");
b.Property<float?>("Float8")
.HasColumnType("real");
b.Property<float?>("Float9")
.HasColumnType("real");
b.Property<Guid?>("Guid0")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid1")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid2")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid3")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid4")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid5")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid6")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid7")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid8")
.HasColumnType("uniqueidentifier");
b.Property<Guid?>("Guid9")
.HasColumnType("uniqueidentifier");
b.Property<int?>("Int0")
.HasColumnType("int");
b.Property<int?>("Int1")
.HasColumnType("int");
b.Property<int?>("Int2")
.HasColumnType("int");
b.Property<int?>("Int3")
.HasColumnType("int");
b.Property<int?>("Int4")
.HasColumnType("int");
b.Property<int?>("Int5")
.HasColumnType("int");
b.Property<int?>("Int6")
.HasColumnType("int");
b.Property<int?>("Int7")
.HasColumnType("int");
b.Property<int?>("Int8")
.HasColumnType("int");
b.Property<int?>("Int9")
.HasColumnType("int");
b.Property<long?>("Long0")
.HasColumnType("bigint");
b.Property<long?>("Long1")
.HasColumnType("bigint");
b.Property<long?>("Long2")
.HasColumnType("bigint");
b.Property<long?>("Long3")
.HasColumnType("bigint");
b.Property<long?>("Long4")
.HasColumnType("bigint");
b.Property<long?>("Long5")
.HasColumnType("bigint");
b.Property<long?>("Long6")
.HasColumnType("bigint");
b.Property<long?>("Long7")
.HasColumnType("bigint");
b.Property<long?>("Long8")
.HasColumnType("bigint");
b.Property<long?>("Long9")
.HasColumnType("bigint");
b.Property<short?>("Short0")
.HasColumnType("smallint");
b.Property<short?>("Short1")
.HasColumnType("smallint");
b.Property<short?>("Short2")
.HasColumnType("smallint");
b.Property<short?>("Short3")
.HasColumnType("smallint");
b.Property<short?>("Short4")
.HasColumnType("smallint");
b.Property<short?>("Short5")
.HasColumnType("smallint");
b.Property<short?>("Short6")
.HasColumnType("smallint");
b.Property<short?>("Short7")
.HasColumnType("smallint");
b.Property<short?>("Short8")
.HasColumnType("smallint");
b.Property<short?>("Short9")
.HasColumnType("smallint");
b.Property<string>("String0")
.HasColumnType("nvarchar(max)");
b.Property<string>("String1")
.HasColumnType("nvarchar(max)");
b.Property<string>("String2")
.HasColumnType("nvarchar(max)");
b.Property<string>("String3")
.HasColumnType("nvarchar(max)");
b.Property<string>("String4")
.HasColumnType("nvarchar(max)");
b.Property<string>("String5")
.HasColumnType("nvarchar(max)");
b.Property<string>("String6")
.HasColumnType("nvarchar(max)");
b.Property<string>("String7")
.HasColumnType("nvarchar(max)");
b.Property<string>("String8")
.HasColumnType("nvarchar(max)");
b.Property<string>("String9")
.HasColumnType("nvarchar(max)");
b.ToTable("QueryableValuesEntity");
b.ToView("QueryableValuesEntity");
});
Tyler-V commented
Found the culprit :)
modelBuilder.RemovePluralizingTableNameConvention();
public static void RemovePluralizingTableNameConvention(this ModelBuilder modelBuilder)
{
foreach (IMutableEntityType entity in modelBuilder.Model.GetEntityTypes())
{
if (entity.BaseType == null)
{
entity.SetTableName(entity.DisplayName());
}
}
}
This was picking up the views and causing it to create a Table
Tyler-V commented
For anyone using fluent to rename plural table names, checking to see if the entity has a view name first resolves this issue
public static class ModelBuilderExtensions
{
public static void RemovePluralizingTableNameConvention(this ModelBuilder modelBuilder)
{
foreach (IMutableEntityType entity in modelBuilder.Model.GetEntityTypes())
{
if (entity.BaseType is null && entity.GetViewName() is null)
{
entity.SetTableName(entity.DisplayName());
}
}
}
}
yv989c commented
Hi @Tyler-V , well that was quick! I'm glad you figure it out 🙂!
Thanks for the comprehensive explanation.
Background
Behind the scenes I'm registering some internal models to make QuaryableValues work. I define these models as views to avoid their output during scaffolding operations. They are only required to make the EF Core SQL expression engine happy and don't have to be present in the database.
I'm planning on changing this strategy for future versions of EF Core once Raw SQL queries for unmapped types (dotnet/efcore#10753) is implemented. This feature will simplify the library in a few places.
I believe I haven't added this to the roadmap, I'll do it now.
Tyler-V commented
@yv989c sounds good, thank you for the additional insight
I appreciate this library and your work here immensely, this not only removes WHERE IN limitations resulting in query processing engine errors but adds a tremendous speed boost - this is a must have addition for Entity Framework Core and I wish I could add more stars