How to add the same column to all entities in EF Core?
Your question title is about adding the same properties to multiple entities. However, you actually know how to achieve this (use a base type) and your actual question is how to ensure that these properties come last in the generated tables' columns.
Although column order shouldn't really matter nowadays, I'll show an alternative that you may like better than a base type and also positions the common properties at the end of the table. It makes use of shadow properties:
Shadow properties are properties that are not defined in your .NET entity class but are defined for that entity type in the EF Core model.
Most of the times, auditing properties don't need much visibility in the application, so I think shadow properties is exactly what you need. Here's an example:
I have two classes:
public class Planet
{
public Planet()
{
Moons = new HashSet<Moon>();
}
public int ID { get; set; }
public string Name { get; set; }
public virtual ICollection<Moon> Moons { get; set; }
}
public class Moon
{
public int ID { get; set; }
public int PlanetID { get; set; }
public string Name { get; set; }
public Planet Planet { get; set; }
}
As you see: they don't have auditing properties, they're nicely mean and lean POCOs. (By the way, for convenience I lump IsDeleted
together with "audit properties", although it isn't one and it may require another approach).
And maybe that's the main message here: the class model isn't bothered with auditing concerns (single responsibility), it's all EF's business.
The audit properties are added as shadow properties. Since we want to do that for each entity we define a base IEntityTypeConfiguration
:
public abstract class BaseEntityTypeConfiguration<T> : IEntityTypeConfiguration<T>
where T : class
{
public virtual void Configure(EntityTypeBuilder<T> builder)
{
builder.Property<bool>("IsDeleted")
.IsRequired()
.HasDefaultValue(false);
builder.Property<DateTime>("InsertDateTime")
.IsRequired()
.HasDefaultValueSql("SYSDATETIME()")
.ValueGeneratedOnAdd();
builder.Property<DateTime>("UpdateDateTime")
.IsRequired()
.HasDefaultValueSql("SYSDATETIME()")
.ValueGeneratedOnAdd();
}
}
The concrete configurations are derived from this base class:
public class PlanetConfig : BaseEntityTypeConfiguration<Planet>
{
public override void Configure(EntityTypeBuilder<Planet> builder)
{
builder.Property(p => p.ID).ValueGeneratedOnAdd();
// Follows the default convention but added to make a difference :)
builder.HasMany(p => p.Moons)
.WithOne(m => m.Planet)
.IsRequired()
.HasForeignKey(m => m.PlanetID);
base.Configure(builder);
}
}
public class MoonConfig : BaseEntityTypeConfiguration<Moon>
{
public override void Configure(EntityTypeBuilder<Moon> builder)
{
builder.Property(p => p.ID).ValueGeneratedOnAdd();
base.Configure(builder);
}
}
These should be added to the context's model in OnModelCreating
:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new PlanetConfig());
modelBuilder.ApplyConfiguration(new MoonConfig());
}
This will generate database tables having columns InsertDateTime
, IsDeleted
and UpdateDateTime
at the end (independent of when base.Configure(builder)
is called, BTW), albeit in that order (alphabetical). I guess that's close enough.
To make the picture complete, here's how to set the values fully automatically in a SaveChanges
override:
public override int SaveChanges()
{
foreach(var entry in this.ChangeTracker.Entries()
.Where(e => e.Properties.Any(p => p.Metadata.Name == "UpdateDateTime")
&& e.State != Microsoft.EntityFrameworkCore.EntityState.Added))
{
entry.Property("UpdateDateTime").CurrentValue = DateTime.Now;
}
return base.SaveChanges();
}
Small detail: I make sure that when an entity is inserted the database defaults set both fields (see above: ValueGeneratedOnAdd()
, and hence the exclusion of added entities) so there won't be confusing differences caused by client clocks being slightly off. I assume that updating will always be well later.
And to set IsDeleted
you could add this method to the context:
public void MarkForDelete<T>(T entity)
where T : class
{
var entry = this.Entry(entity);
// TODO: check entry.State
if(entry.Properties.Any(p => p.Metadata.Name == "IsDeleted"))
{
entry.Property("IsDeleted").CurrentValue = true;
}
else
{
entry.State = Microsoft.EntityFrameworkCore.EntityState.Deleted;
}
}
...or turn to one of the proposed mechanisms out there to convert EntityState.Deleted
to IsDeleted = true
.
EFCore Map 2 entities to same table
The main cause of issue is, EF Core cannot figure out how to use same table for 2 different entities. There is lack of data in mapping, once you fill that in, it works as expected.
First you will need to define how are they related to each other. Sharing same table with same PK does not have Foreign Key defined on server side but there is still intrinsic relationship between both entities which is one-to-one and using PK as FK. Once you define relationship, you will see that it works and both entities are mapped to same table. (Just like how owned entities are mapped to same table). This may not be end of mapping for your case though. Since from EF perspective they are still 2 different entities, except for Id (or PK property), they will have own columns to store data. But what if you have columns which are common in both the context (like Email
in your scenario). In such case, you would need to provide mapping for those additional column too. If you map them to same column explicitly, they will start sharing the column in database. Overall the code would look like this.
namespace UserContext
{
public class User
{
public int Id { get; set; }
public string Email { get; set; }
// Other properties
}
}
namespace OrderContext
{
public class User
{
public int Id { get; set; }
public string Email { get; set; }
}
}
// In OnModelCreating method
modelBuilder.Entity<UserContext.User>(u =>
{
u.ToTable("User");
u.Property(e => e.Email).HasColumnName("Email");
// Configuration for other properties
});
modelBuilder.Entity<OrderContext.User>(u =>
{
u.ToTable("User");
u.Property(e => e.Email).HasColumnName("Email");
u.HasOne<UserContext.User>().WithOne().HasForeignKey<OrderContext.User>(e => e.Id);
});
Above code creates single table with shared columns and should work as expected. You can add more entities in the same table if you want by following same configuration. Here, I used User
from UserContext
as principal side but you can use any side. The main reasoning for me was, UserContext.User
will be the entity which will be added when adding new User. Entities sharing the table do not have to be subset either. But there will be columns for additional properties which are not shared.
Update one column and set all values to 1 using Entity Framework
You need to load the existing records, modify them and then save the changes, e.g.
var notYetDoneCustomers = await context.Customers.Where(c => c.DoneFlag == 0).ToListAsync();
foreach(var cust in notYetDoneCustomers) {
cust.DoneFlag = 1;
}
await context.SaveChangesAsync();
EF Core multiple realationship of a single column
No, you can't relate a foreign key to multiple tables. But you can put another property named EntityType
to store the type of entity. Then on the client-side, you can handle it. The EntityType
can be an enum type.
Another approach is that storing "EntitesSchemaId" in the Plant
, Area
, Unit
, etc models and relate them to the EntitiesSchema
.
ASP MVC - Adding columns to all tables using EF Code First
I found the solution, thanks to Muthu.
To automatically add those "common columns" to all my tables, I just had to create a "Base Model" containing these columns, and make all of my models inherit from it.
This avoids repeatedly adding the columns to each Model as properties, and looks cleaner I believe.
Related Topics
Can Razor Class Library Pack Static Files (Js, CSS etc) Too
Expression-Bodied Function Members Efficiency and Performance in C# 6.0
Exporting Datagridview to CSV File
How to Add Moving Effects to My Controls in C#
How to Detect the Original MAC Address After It Has Been Spoofed
Reasons for Why a Winforms Label Does Not Want to Be Transparent
C# Code to Linkify Urls in a String
How to Set Read Permission on the Private Key File of X.509 Certificate from .Net
How to Read Image Pixels' Values as Rgb into 2D Array
Share Session Between Two Web Sites Using ASP.NET and State Server
Asynchronous File Download with Progress Bar
Wpf Combobox: Different Template in Textbox and Drop-Downlist