I am trying to migrate a simple table but ef-core is creating additional columns which i haven't specified
Here is my model which i used to create a sql table
public class Transaction
{
[Key]
public int Id { get; set; }
[Required]
public decimal Amount { get; set; }
[Required]
public DateTime DateTimeCreated { get; set; } = DateTime.UtcNow;
[MaxLength(1024)]
public string Description { get; set; }
[Required]
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
[Required]
public int UserId { get; set; }
public virtual User User { get; set; }
}
EF-Core is creating the following table.
How to stop EF-Core from generating CategoryName and CategoryCashFlowId columns
migrationBuilder.CreateTable(
name: "Transactions",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Amount = table.Column<decimal>(nullable: false),
DateTimeCreated = table.Column<DateTime>(nullable: false),
Description = table.Column<string>(maxLength: 1024, nullable: true),
CategoryId = table.Column<int>(nullable: false),
CategoryName = table.Column<string>(nullable: false),
CategoryCashFlowId = table.Column<int>(nullable: false),
UserId = table.Column<int>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Transactions", x => x.Id);
table.ForeignKey(
name: "FK_Transactions_Users_UserId",
column: x => x.UserId,
principalTable: "Users",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Transactions_Categories_CategoryName_CategoryCashFlowId",
columns: x => new { x.CategoryName, x.CategoryCashFlowId },
principalTable: "Categories",
principalColumns: new[] { "Name", "CashFlowId" },
onDelete: ReferentialAction.Cascade);
});
Here is my fluent api code
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//base.OnModelCreating(modelBuilder);
modelBuilder.Entity<User>().HasIndex("Username").IsUnique();
modelBuilder.Entity<Category>().HasKey(c => new { c.Name, c.CashFlowId });
modelBuilder.Entity<Common.Models.CashFlow>().HasData(
new CashFlow { Id = 1, Name = "Income" },
new CashFlow { Id = 2, Name = "Expense" }
);
modelBuilder.Entity<Common.Models.Category>().HasData(
new Category { Id = 1, Name = "Food and Drinks", CashFlowId = 2 },
new Category { Id = 2, Name = "Travel", CashFlowId = 2 },
new Category { Id = 3, Name = "Salary", CashFlowId = 1 }
);
}
Edit 1: Category Model
public class Category
{
public int Id { get; set; }
/// <summary>
/// <remarks> Is Indexed with CashflowId </remarks>
/// </summary>
[MaxLength(32)]
[Required]
public string Name { get; set; }
[Required]
public int CashFlowId { get; set; }
public CashFlow CashFlow { get; set; }
}
Edit 2: Added CashFlow model
public class CashFlow
{
[Key]
public int Id { get; set; }
[MaxLength(32)]
public string Name { get; set; }
}
Edit 3: Posted my DbContext
public class AppDbContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Common.Models.Transaction> Transactions { get; set; }
public DbSet<Category> Categories { get; set; }
public DbSet<CashFlow> CashFlows { get; set; }
public AppDbContext(DbContextOptions<AppDbContext> dbContextOptions) : base(dbContextOptions)
{
ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//base.OnModelCreating(modelBuilder);
modelBuilder.Entity<User>().HasIndex("Username").IsUnique();
modelBuilder.Entity<Category>().HasKey(c => new { c.Name, c.CashFlowId });
modelBuilder.Entity<Common.Models.CashFlow>().HasData(
new CashFlow { Id = 1, Name = "Income" },
new CashFlow { Id = 2, Name = "Expense" }
);
modelBuilder.Entity<Common.Models.Category>().HasData(
new Category { Id = 1, Name = "Food and Drinks", CashFlowId = 2 },
new Category { Id = 2, Name = "Travel", CashFlowId = 2 },
new Category { Id = 3, Name = "Salary", CashFlowId = 1 }
);
}
}
I don't understand why CategoryName and CategoryCashFlowId columns are getting created
Please help
why CategoryName and CategoryCashFlowId columns are getting created
With this line: modelBuilder.Entity<Category>().HasKey(c => new { c.Name, c.CashFlowId });
You are creating a composite key for table Category
which cannot be an int
.
In your Transaction
object you are trying to map a key CategoryId
for your Category
as stated before your composite key cannot be an int
nether a single property just by nature of composition. So at this point CategoryId
is not a foreign key, it's just another property.
EF core is putting the two properties you used for the primary composite key of your Category
object in to your Transaction
object to be used as a foreign composite key.
You can resolve this by removing the composition.