Thursday 15 November 2012

Some Notes about Entity Framework Code First Fluent API on Properties

- By convention a property with name 'Id' or '[Class]Id' will become the generated table primary key.

- string property will become an nvarchar(max) column.

- Keys properties and value types (any numeric, DateTime, bool and char) properties will become non-nullable columns. Reference types (String and arrays) and nullable value types (e.g.; Int16?, int?, decimal?, etc) properties will yield as nullable columns.

- byte[] property will become varbinary(max) column.

- Configuring primary key
modelBuilder.Entity<[ClassName]>().HasKey(p => p.[PropertyName]);

- Non-nullable column
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsRequired();

- Nullable column
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsOptional();

- Set the maximum length for a property and the generated column
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).HasMaxLength([NumberLength]);

- Largest possible length of column's data type
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsMaxLength();

- Use fixed rather than variable data type, e.g.; varchar instead of nvarchar
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsFixedLength();
To extend the fixed data type column use
.IsFixedLength().HasMaxLength([NumberLength])
To have largest possible length of the fixed data type column use
.IsFixedLength().IsMaxLength()
For string property, we can change the default data type generated (nvarchar) to varchar by using
.IsUnicode(false)

- Use variable length data type
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsVariableLength();

- Specify the generated column data type
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).HasColumnType("[ColumnName]");

- Set the property to be used for concurrency checking
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsConcurrencyToken();

- Set a row version column in the generated table to be used as the concurrency token
modelBuilder.Entity<[ClassName]>().Property(p => p.[PropertyName]).IsRowVersion();
The property must have Byte[] type. IsRowVersion() is only allowed one in a class.


Further reading:
Configuring Properties and Types with the Fluent API

Friday 2 November 2012

Get Started with Entity Framework Code First

Entity Framework version 5.0.0 is used when writing this post.

First thing we need to do is add Entity.Framework library into the project. If you are using NuGet, you can do:
PM> Install-Package EntityFramework

Then prepare your POCO classes. An example of POCO classes:
public class Stock
{
    public int StockId { get; set; }
    public int ItemId { get; set; }
    public Int16 Quantity { get; set; }
    public DateTime DateUpdated { get; set; }

    public virtual Item Item { get; set; }
}
and
public class Invoice
{
    public int InvoiceId { get; set; }
    public string Name { get; set; }    
    public string Description { get; set; }
    public decimal TotalPrice { get; set; }
    public DateTime DateSold { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime DateUpdated { get; set; }

    public virtual ICollection<ItemSelling> Items { get; set; }
}
To allow lazy loading, declare each navigational property as public virtual. For change tracking, declare the property as public virtual and use ICollection<T> for navigational property which contains collection. For complete details, please see this MSDN article 'Requirements for Creating POCO Proxies'

Next, create a context class that inherits from DBContext. Then specify one DBSet property for each of the POCO class that we have. If you would like to use Fluent API for configuring POCO class properties, then override the OnModelCreating() method. See an example below:
public class MyContext : DbContext
{
    . . .

    public DbSet<Stock> Stocks { get; set; }
    public DbSet<Invoice> Invoices { get; set; }

    . . .

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity().HasKey(p => p.StockId);
        modelBuilder.Entity().Property(p => p.ItemId).IsRequired();
        modelBuilder.Entity().Property(p => p.Quantity).IsRequired();

        modelBuilder.Entity().HasKey(p => p.InvoiceId);
        modelBuilder.Entity().Property(p => p.Name).HasMaxLength(50);
    }
}

If a database connection string has not been specified, EF will try to find an SQL Server Express for the database. If you would like EF to create the database in a specified server, then you need to specify a connection string and make sure to named it similar as the database context class' name.

Then we might want to specify Database.SetInitializer() method to determine the behaviour of EF Code First when initialising our database. By default, it will create the database if it not exists yet but will not change it afterwards even if the model(s) has changed. In an ASP.NET application, we put this inside Global.asax.cs file. An instance:
Database.SetInitializer(new DropCreateDatabaseIfModelChanges());
There are three built in database initialisers options available:
- CreateDatabaseIfNotExists (default)
- DropCreateDatabaseIfModelChanges
- DropCreateDatabaseAlways
We can also pass null as the parameter to the Database.SetInitializer() method to skip database initialisation process.

By default the database will be initialised when the context is used for the first time. For example, when the code is trying to retrieve items from an entity. To do the database initialisation explicitly without waiting for the context to be used, call:
db.Database.Initialize(false);
For example, you can put this code below in Global.asax.cs
// do the database initialisation explicitly without waiting for the context to be used 
using (var db = new MyContext())
{
    db.Database.Initialize(false);
}