Reputation: 11
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
Upvotes: 0
Views: 2874
Reputation: 35175
CashFlow on Category should be virtual.
public virtual CashFlow CashFlow { get; set; }
Category should be
public class Category
{
[Key]
public int Id { get; set; }
/// <summary>
/// <remarks> Is Indexed with CashflowId </remarks>
/// </summary>
[MaxLength(32)]
[Required]
public string Name { get; set; }
[Required]
[Index("IX_NameCashFlowId", 1, IsUnique = true)]
public int CashFlowId { get; set; }
[Index("IX_NameCashFlowId", 1, IsUnique = true)]
public CashFlow CashFlow { get; set; }
}
With this setup modelBuilder.Entity<Category>().HasKey
is not needed.
Upvotes: 0
Reputation: 4634
I believe this is because your Transaction
model has a reference (navigation property) to the Category
model, along with a CategoryId
. But it can't actually reference the Category
model by the CategoryId
because the Category
model actually has a composite key of Name
and CashFlowId
. So Entity Framework adds these behind the scenes to reference it.
I think you can create an alternate key on Category
using CategoryId
and it would be referenced that way without creating the extra columns.
modelBuilder.Entity<Category>()
.HasKey(c => new { c.Name, c.CashFlowId })
.HasAlternateKey(c => c.CategoryId);
Reference: https://learn.microsoft.com/en-us/ef/core/modeling/alternate-keys
Upvotes: 0
Reputation: 96
when u use this config for
modelBuilder.Entity<Category>().HasKey(c => new { c.Name, c.CashFlowId });
category table primary key is Name and CashFlowId and transaction table for relation must generate these columns.
Upvotes: 0
Reputation: 2929
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.
Upvotes: 1