ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

Code First

Categories: EF

O'Reilly's Programming Entity Framework - Code First

By Julia Lerman & Rowan Miller

  • Table name will be plural of class name created with dbo schema.
  • Column names same as properties they map to.
  • Strings are mapped to nvarchar(max)
  • Byte[] is mapped to varbinary(max)
  • Bool to bit not null
  • A class must have a key property (default named Id or typenameId)
  • One-to-Many Relationships with foreign key [Name of navigation property]_[Primary key of related class]
  • Foreign keys are nullable by convention.
  • In memory metadata created from code or EDMX model (System.Data.Metadata.Edm)
  • Only use DetectChanges if needed for performance tuning.

Data Annotations (System.ComponentModel.DataAnnotations) vs Fluent API (modelBuilder.Entity<AnimalType>())

[Table("Species")]
this.ToTable("Species");

[Table("Species", Schema="baga")]
this.ToTable("Species", "baga");

[Column("LocationName")]
this.Property(d => d.Nam).HasColumnName("LocationName");

[Key]
this.HasKey(t => t.Guid);

[Required]
this.Property(p => p.TypeName).IsRequired();

[MinLength(10), MaxLength(500)]
this.Property(p => p.Description).HasMaxLength(500); (no min available)

[Column(TypeName="image")]
this.Property(d => d.Photo).HasColumnType("image");

[DatabaseGeneratedOption.Identity]
this.Property(t => t.Guid).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

[Timestamp] must be a byte[]
this.Property(t => t.TimeStamp).IsRowVersion();

[ConcurrencyCheck]
this.Property(t => t.SocialSecurityNumber).IsConcurrencyToken();

[ComplexType]
modelBuilder.ComplexType<Address>();

[ForeignKey("BillingAddressId")]
modelBuilder.Entity<User>().HasRequired(a => a.BillingAddress).WithMany().HasForeignKey(u => u.BillingAddressId);

[Required]
modelBuilder.Entity<PersonPhoto>().HasRequired(p => p.PhotoOf).WithRequiredDependent(p => p.Photo);

[InverseProperty("PrimaryContactFor")]
modelBuilder.Entity<Lodging>().HasOptional(l => l.PrimaryContact).WithMany(p => p.PrimaryContactFor);

this.Property(t => t.SomeAscii).IsUnicode(false);

this.Property(t => t.SomeMiles).HasPrecision(8,1);

foo.WillCascadeOnDelete(false);

[NotMapped]
Ignore(d => d.TodayForecast);
  • By default, Code First will create the database only if it doesn't already exist.
  • Complex types have no key property, can only contain primitive properties and when used as a property in another class, the property must represent a single instance. It cannot be a collection type. [HostPropertyName_PropertyName]
  • Entity.Has[Multiplicity](Property).With[Multiplicity](Property)
  • .HasOptional, .HasRequired, .HasMany + WithOptional, WithRequired, WithMany
  • [Target Type Key Name], [Target Type Name] + [Target Type Key Name], [Navigation Property Name] + [Target Type Key Name]
var tripWithActivities = context.Trips.Include("Activities").FirstOrDefault();

Table splitting
modelBuilder.Entity<Person>().ToTable("People");
modelBuilder.Entity<PersonPhoto>().ToTable("People");

Entity splitting
Map(m => { m.Properties(d => new { d.Name, d.Country, d.Description }); m.ToTable("Locations"); });
Map(m => { m.Properties(d => new { d.Photo }); m.ToTable("LocationPhotos"); });
  • For protected and private properties, the configuration class must be nested inside the class that is part of the model.
  • The setter can be marked with a more restrictive accessor, but the getter must remain public for the property to be mapped automatically. (not supported in medium trust because of reflection)
public class Person
{
 public int PersonId { get; set; }
 private string Name { get; set; }

 public class PersonConfig : EntityTypeConfiguration<Person>
 {
 public PersonConfig()
 {
 Property(b => b.Name);
 }
 }

public string GetName()
 {
 return this.Name;
 }

public static Person CreatePerson(string name)
 {
 return new Person { Name = name };
 } 
}
modelbuilder.Configuration.Add(new Person.PersonConfig());

public string FullName
{
 get { return String.Format("{0}{1}",FirstName.Trim(), LastName); }
}

Table per hierarchy (TPH) describes mapping inherited types to a single database table that uses a discriminator column to differentiate one subtype from another. This is default for Entity Framework. (nvarchar(128), not null containing the type name)

  • Map(m => { m.ToTable("Lodgings"); m.Requires("LodgingType").HasValue("Standard"); });
  • Map<Resort>(m => { m.Requires("LodgingType").HasValue("Resort"); });
  • Map(m => { m.ToTable("Lodgings"); m.Requires("IsResort").HasValue(false); });
  • Map<Resort>(m => { m.Requires("IsResort").HasValue(true); });

Table per type (TPT) only stores properties for base class in a single table. Additional properties are stored in separate table.

  • modelBuilder.Entity<Resort>().ToTable("Resorts");
  • modelBuilder.Entity<Lodging>().Map<Resort>(m => { m.ToTable("Resorts"); });

Table per concrete type (TPC) stores all the properties for each type in a separate table. Map inheritance to tables with common overlapping fields.

  • modelBuilder.Entity<Lodging>().Map(m => { m.Table("Lodgings"); }).Map<Resort>(m => { m.ToTable("Resorts"); m.MapInheritedProperties(); } );

Mapping foreign keys

  • HasRequired(l => l.Destination).WithMany(d => d.Lodgings).Map(c => c.MapKey("destination_id"));
  • HasRequired(l => l.Destination).WithMany(d => d.Lodgings).Map(c => c.MapKey("destination_id").ToTable("LodgingInfo"));
  • HasMany(t => t.Activities).WithMany(a => a.Trips).Map(c => c.ToTable("TripActivities"));
  • HasMany(t => t.Activities).WithMany(a => a.Trips).Map(c => { c.ToTable("TripActivities"); c.MapLeftKey("TripIdentifier"); c.MapRightKey("ActivityId"); });
  • MapLeftKey affects the foreign key column that points to the class being configured!

Connections

// you need to take care of disposing the connection unless your context owns the connection (property)
using (var connection = new SqlConnection(connectionString))
{
 using (var db = new MyDbContext(connection)) { ... }
 using (var dbUsingSameConnection = new MyDbContext(connection)) { ... }
}
  • SqlConnectionFactory : IDbConnectionFactory
  • Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");

Initialization is triggered the first time that the context is used. Initialization occurs lazily.

  • Database.SetInitializer(new DropCreateDatabaseIfModelChanges<BreakAwayContext>());
  • Database.SetInitializer(new CreateDatabaseIfNotExists<BreakAwayContext>());
  • Database.SetInitializer(new DropCreateDatabaseAlways<BreakAwayContext>()); // use system.Transactions.TransactionScope if you have a lot of integration tests
  • EFCodeFirst.CreateTablesOnly nuget package
using (var context = new BreakAwayContext())
{
 try 
 {
 context.Database.Initialize(force: false);
 }
 catch (Exception ex) { ... }
}

Database.SetInitializer(null); // do not magically create a database
<appSettings>
 <add key="DatabaseInitializerForType DataAccess.BreakAwayContext, DataAccess" value="Disabled" />
</appSettings>

public class PromptForDropCreateDatabaseWhenModelChanges<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext
{
 public void InitializeDatabase(TContext context)
 {
 var exists = context.Database.Exists();
 if (exists && context.Database.CompatibleWithModel(true)) return;
 if (exists)
 {
 // prompt
 context.Database.Delete();
 }
 context.Database.Create();
 }
}

public class DropCreateBreakAwayWithSeedData : DropCreateDatabaseAlways<BreakAwayContext>
{
protected override void Seed(BreakAwayContext context)
{
context.Database.ExecuteSqlCommand("CREATE INDEX IX_Lodgings_Name ON Lodgings (Name)");
context.Destinations.Add(new Destination { Name = "foo" });
base.Seed(context);
}
}

Mapping to Updatable views

Using views to populate objects

var destinations = context.Destinations.SqlQuery(@"SELECT Id AS DestinationId, Name FROM dbo.TopTenDestinations");

public class DestinationSummary
{
 public int DestinationId { get; set; }
 public string Name { get; set; }
 public int ResortCount { get; set; }
}
var summary = context.Database.SqlQuery<DestinationSummary>("SELECT * FROM dbo.DestinationSummaryView");

Stored Procedures

var country = "Belgium";
var keywords ="sun, yeahright";
var destinations = context.Database.SqlQuery<DestinationSummary>("dbo.GetDestinationSummary @p0, @p1", country, keywords);

Removing Conventions

  • modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

Model Caching

  • Using the same DbCompiledModel to create contexts against different types of database servers is not supported. Instead, create a separate DbCompiledModel for each type of server being used.

var sql_model = GetBuilder().Build(new DbProviderInfo("System.Data.SqlClient", "2008")).Compile();
var context = new BreakAwayContext (connection, sql_model);
public static DbModelBuilder GetBuilder()
{
var builder = new DbModelBuilder();
builder.Entity<EdmMetadata>().ToTable("EdmMetadata");
...
builder.Entity<Trip>();
return builder;
}

EdmMetadata Table

  • Snapshot of your model as a SHA256 hash.
  • var modelHash = EdmMetadata.TryGetModelHash(context);
  • var databaseHash = context.Set<EdmMetadata>().Single().ModelHash;
  • var compatible = context.Database.CompatibleWithModel(throwIfNoMetadata:true);
  • modelBuilder.Conventions.Remove<IncludeMetadataConvention>();

More