Add a warning when store-generated PK is not supported #11162
Comments
|
@viveknuna Looks like the migration was generated against SQL Server, but is being used with SQLite. Does it work correctly if the migration is instead created for use with SQLite? |
|
Yes, migration is generated against sql server. I don’t run any migrations explicitly for my test cases, But test cases use SQLite. SQLite generates same schema. So is this SQLite related issue or there is some problem with my current implementation? Please let me know what should I try at my end? |
|
My application uses sql server only, but test cases use SQLite. I’m using XUnit. |
|
@viveknuna Migrations are generated against the provider that you are using and may contain code specific to that provider. If you want to test against a different provider, then consider either:
|
ajcvickers
added
the
closed-no-further-action
|
@ajcvickers Entity Framework Core .NET Command-line Tools 2.2.4-servicing-10062
INSERT INTO "DevicesOrderStatus" ("OrderId", "Status", "UtcSetAt")
VALUES (@p0, @p1, @p2);
SELECT "Id"
FROM "DevicesOrderStatus"
WHERE changes() = 1 AND "OrderId" = @p0 AND "Id" = last_insert_rowid();So I assume However, SQLite shows this SQL for the table: CREATE TABLE "DevicesOrderStatus" (
"OrderId" INTEGER NOT NULL,
"Id" INTEGER NOT NULL,
"Status" INTEGER NOT NULL,
"UtcSetAt" TEXT NOT NULL,
CONSTRAINT "PK_DevicesOrderStatus" PRIMARY KEY ("OrderId", "Id"),
CONSTRAINT "FK_DevicesOrderStatus_DevicesOrder_OrderId" FOREIGN KEY ("OrderId") REFERENCES "DevicesOrder" ("Id") ON DELETE CASCADE,
CONSTRAINT "FK_DevicesOrderStatus_DevicesOrderStatusType_Status" FOREIGN KEY ("Status") REFERENCES "DevicesOrderStatusType" ("Id") ON DELETE RESTRICT
)I see nothing which indicates automatic increment of
internal sealed class DevicesOrderConfiguration : IEntityTypeConfiguration<DevicesOrder>
{
public void Configure(EntityTypeBuilder<DevicesOrder> builder)
{
builder.ToTable("DevicesOrder");
builder.Property(_ => _.Version)
.HasDefaultValue(0)
.IsConcurrencyToken();
builder.Ignore(_ => _.OrderItemsCount);
builder.Ignore(_ => _.CurrentStatus);
builder.HasKey(_ => _.Id);
builder.OwnsMany(_ => _.OrderItems, b =>
{
b.ToTable("DevicesOrderItem");
b.HasForeignKey("OrderId");
b.HasKey("OrderId", nameof(DevicesOrderItem.ModelId));
b.HasOne<DeviceModel>()
.WithMany()
.HasForeignKey(m => m.ModelId)
.IsRequired()
.OnDelete(DeleteBehavior.Restrict);
});
builder.OwnsMany(_ => _.StatusHistory, b =>
{
b.ToTable("DevicesOrderStatus");
b.HasForeignKey("OrderId");
b.Property<int>("Id").ValueGeneratedOnAdd();
b.HasKey("OrderId", "Id");
b.HasOne<DevicesOrderStatusLookup>()
.WithMany()
.HasForeignKey(m => m.Status)
.IsRequired()
.OnDelete(DeleteBehavior.Restrict);
});
builder.HasOne<Organization>()
.WithMany()
.HasForeignKey(_ => _.OrganizationId)
.IsRequired()
.OnDelete(DeleteBehavior.Cascade);
}
} |
|
@ajcvickers I am spoiled by SQL Server =) But is it hard for EF Core to detect this situation? Can EF signal that configuration in the context of particular provider is not correct? |
|
Re-opening to discuss @voroninp's suggestion. |
|
Note from triage: putting this on the backlog to add |
ajcvickers
added
type-enhancement
and removed
closed-no-further-action
|
Hello, It seems we're hitting a similar issue when using owned entity types with a composite key including an using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
namespace ConsoleApp1
{
internal class Program
{
private static void Main()
{
var options = new DbContextOptionsBuilder<MyContext>()
.UseSqlite("datasource=db.sqlite")
.Options;
using var ctx = new MyContext(options);
ctx.Database.EnsureCreated();
ctx.Add(new Input { Boosters = { new Booster { Index = 0 } } }); // no exception when the index > 0
ctx.SaveChanges();
}
}
public class MyContext : DbContext
{
public MyContext(DbContextOptions options) : base(options) { }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Input>().OwnsMany(i => i.Boosters, b =>
{
b.HasKey("InputId", "Index");
});
}
}
public class Input
{
public int InputId { get; set; }
public ICollection<Booster> Boosters { get; } = new List<Booster>();
}
public class Booster
{
public int Index { get; set; }
}
}ExceptionMicrosoft.EntityFrameworkCore.DbUpdateException
HResult=0x80131500
Message=An error occurred while updating the entries. See the inner exception for details.
Source=Microsoft.EntityFrameworkCore.Relational
StackTrace:
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
at ConsoleApp1.Program.Main() in C:\deleteme\ef\ConsoleApp1\Program.cs:line 17
This exception was originally thrown at this call stack:
Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int, SQLitePCL.sqlite3)
Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(System.Data.CommandBehavior)
Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(System.Data.CommandBehavior)
System.Data.Common.DbCommand.ExecuteReader()
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject)
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(Microsoft.EntityFrameworkCore.Storage.IRelationalConnection)
Inner Exception 1:
SqliteException: SQLite Error 19: 'NOT NULL constraint failed: Booster.Index'.Further technical detailsEF Core version: 3.0.1 (also tried 3.1.0-preview3.19554.8) WorkaroundIt looks like configuring the modelBuilder.Entity<Input>().OwnsMany(i => i.Boosters, b =>
{
b.HasKey("InputId", "Index");
b.Property(b => b.Index).ValueGeneratedNever();
}); |
|
@gojanpaolo Thank you very much, It's worked for me. |
|
Ive run into this issue. I am trying to initialize a SQLDatabase and seed it.
Inspecting my entity I see the UpdatedOn field is set. I am using a custom OnConfiguring
and custom IDesignTimeDbContextFactory with SQLITE
|
Problem solved by workaround at dotnet/efcore#11162.
AndriySvyryd
changed the title
|
@ajcvickers |
|
This issue still repros since the generated SQLite table does NOT have autoincrement flag in it. Is there a way to fix the table after it is created just in the case of Sqlite ? For me, the issure repro'd only when the identity field was also part of an unique index constraint (may be that is not needed?) however when I swapped it to be a primary key the auto-increment annotations were correctly added to the migrations code and the issue is fixed. |
ajcvickers
added
punted-for-7.0
ajcvickers
removed
the
punted-for-7.0
ajcvickers
added
closed-fixed
|
thank you. glad to hear a warning has been added now. |
I'm using ABP framework which is built on top of asp.net core and using entity framework core. I have tried to create PK to a different column other than the default Id column with the help of this solution. Its working fine for every scenario, but my test cases are failing. I have created TestId as PK, Id as autoincrement identity.
It's failing when I'm running test case to create the record and giving the following exception.
Testcase:
CreateNewTest Method
CreateTest Method
StackTrace
Code:
Generated migration:
Usage:
Further technical details
EF Core version: 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.4
The text was updated successfully, but these errors were encountered: