Working with Code First Fluent Mapping API
Julie Lerman
Download the code for this article:
- Visual Basic (VS2010)
- C# (VS2010)
Using Entity Framework 4.1 Code First workflow, you can let EF create a database from your classes or you can map your classes to an existing database. Code First inspects the classes and uses a set of assumptions, or conventions, to figure out how to map to the related database. If the database doesn’t exist, Code First will create that database based on its interpretation.
But your classes might not always align with the database or the intent you have for a database to be created using Code First conventions. Code First allows you to use either annotations or Code First's fluent API to add extra configuration details to ensure that everything lines up between the classes and the database.
This article will focus on providing this additional mapping information using the fluent API. I’ll walk you through some of the most commonly needed mappings, giving you an understanding of how these configurations affect your application and Code First’s understanding of your database.
I'm working with a simple set of classes for a blogging application. Here are the Blog and Post classes.
public class Blog
{
public int Id { get; set; }
public string Title { get; set; }
public string BloggerName { get; set; }
public virtual ICollection<Post> Posts { get; set; }
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public DateTime DateCreated { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public byte[] Image { get; set; }
public Blog Blog { get; set; }
public ICollection<Comment> Comments { get; set; }
}
Changing the Database Table Name
Code first convention presumes that the name of the table your class maps to is the same as the name of the DbSet for that type on your derived context. If the type is not exposed in a DbSet then the English plural of the type name is used.But what if your table names don’t follow Code First’s assumptions? For example, your table for the Person data may be “Personnes”, or the table for the Blog data might be called “InternalBlogs”. You can use the fluent API mappings to instruct Code First to map to the correct table name.
Fluent API configurations are applied as Code First is building the model from the classes. You can inject the configurations by overriding the DbContext class’ OnModelCreating method shown here.
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//configure model with fluent API
}
The DbModelBuilder gives you a hook into the configurations. Here we can tell the model builder that we want to affect one of the entities, and you can use generics to specify which entity, Blog. Once you have access to that, you can use the ToTable method to specify the name of the table that Blog should map to.
modelBuilder.Entity<Blog>().ToTable("InternalBlogs");
This won’t affect your code. You will still work with Blog and query from the Blogs entity set for example:
var blog = db.Blogs.Find(id);
But entity framework will create its database commands to work with the InternalBlogs table. For example, the above code using the value, 1, for id would generate the following TSQL.
exec sp_executesql N'SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Title] AS [Title],
[Limit1].[BloggerName] AS [BloggerName]
FROM ( SELECT TOP (2)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent1].[BloggerName] AS [BloggerName]
FROM [dbo].[InternalBlogs] AS [Extent1]
WHERE [Extent1].[Id] = @p0
) AS [Limit1]',N'@p0 int',@p0=1
Fixing a Column Name Mapping
You can just as easily specify the database name of a column if it doesn’t match a property name. For example, perhaps the Post.Content property is named Body in the database.
You would begin again by telling the ModelBuilder that you want to affect an entity—this time, Post.
modelBuilder.Entity<Post>()
From there, you indicate that you want to impact a particular property in this entity and using a lambda expression, which property.
modelBuilder.Entity<Post>().Property(p => p.Content)
Finally you can use the HasColumn method to specify the name of the column.
modelBuilder.Entity<Post>().Property(p => p.Content).HasColumnName("Body");
Code First will now anticipate the new column name when it builds database queries and commands. If it is building the database for you, it will create the Posts table using the Body column name.
.jpg "Treeview") |
| Figure 1 |
There are a number of mappings available from the Property configuration in addition to HasColumnName, as shown in the code completion window in Figure 2.
.jpg "Intellisense") |
| Figure 2 |
Splitting Classes Across Multiple Tables with Entity Splitting
The Post class has a property for storing an image as a byte array. Images can be quite large and impact the performance of a database table. You may prefer to store the Image column in a separate table— yet have it remain a property of the Post class.
Entity Splitting allows you to do this. Entity Splitting lets you split an entity across multiple tables. You can use a mapping to instruct Code First to split the Post entity across two tables. The overall mapping divides the class properties into two groups and then, using the ToTable mapping you saw earlier, specifies the table name of each group.
Here’s how to construct the mapping.
You’ll begin with a configuration for the Post Entity as you have done in the previous mappings:
modelBuilder.Entity<Post>()
Then you will use the Map method for each of the groups, appending the first one to the Entity and the second one on the first. The map method uses lambda expressions to express the mapping. First you define the mapping properties and then the mapping table.
Here is the first mapping which says to put the Id, Content, DateCreated, BlogID and Title into a table called Posts.
.Map(mc =>
{
mc.Properties(p => new
{
p.Id,
p.Content,
p.DateCreated,
p.BlogId,
p.Title
});
mc.ToTable("Posts");
})
And here is another mapping to put the Id and Image into another table called PostImages. The Id needs to be repeated because that is how EF will be able to join the two tables when it constructs queries and commands.
.Map(mc =>
{
mc.Properties(p => new
{
p.Id,
p.Image
});
mc.ToTable("PostImage");
});
The complete mapping looks like this:
modelBuilder.Entity<Post>()
.Map(mc =>
{
mc.Properties(p => new
{
p.Id,
p.Content,
p.DateCreated,
p.BlogId,
p.Title
});
mc.ToTable("Posts");
})
.Map(mc =>
{
mc.Properties(p => new
{
p.Id,
p.Image
});
mc.ToTable("PostImage");
});
The resulting database tables are shown in Figure 3.
.jpg) |
| Figure 3 |
The beauty of this mapping is that you don’t have to know about the multiple tables when you write your code. You’ll code against the Post class that has the Image property inside of it. For any database interaction, Entity Framework will take care of the multiple tables. For example, if you were to insert a new Post that includes an image you’re code could be as simple as:
db.Posts.Add(post);
db.SaveChanges();
After entering a new post in my MVC 3 application:
.jpg) |
| Figure 4 |
And executing the code shown above to add the post and save changes, Entity Framework constructs two insert commands. The first inserts the relevant fields into the Posts table and also returns the Id that the database generated for the new post.
exec sp_executesql N'insert [dbo].[Posts]([Title], [DateCreated], [Body], [BlogId], [CreatedBy_Id], [UpdatedBy_Id])
values (@0, @1, @2, @3, null, null)
select [Id]
from [dbo].[Posts]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',
N'@0 nvarchar(128),@1 datetime2(7),@2 nvarchar(128),@3 int',
@0=N'My South Park Avatar',@1='2011-03-15 00:00:00',
@2=N'A little Vermont, a little cow (my dog), some snow and some red hair. That oughta do it!',
@3=1
The second command inserts that Id value and the streamed image data into the PostImages table.
exec sp_executesql N'insert [dbo].[PostImage]([Id], [Image])
values (@0, @1)
',N'@0 int,@1 varbinary(max) ',@0=4,@1=0x89504E470D0A1A0A0000000D494844520000017F0000017C08060000….
And to display the post, you need only query the single post and EF takes care of querying across both tables:
exec sp_executesql N'SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Title] AS [Title],
[Limit1].[DateCreated] AS [DateCreated],
[Limit1].[Body] AS [Body],
[Limit1].[BlogId] AS [BlogId],
[Limit1].[Image] AS [Image],
[Limit1].[CreatedBy_Id] AS [CreatedBy_Id],
[Limit1].[UpdatedBy_Id] AS [UpdatedBy_Id]
FROM ( SELECT TOP (2)
[Extent1].[Id] AS [Id],
[Extent1].[Image] AS [Image],
[Extent2].[Title] AS [Title],
[Extent2].[DateCreated] AS [DateCreated],
[Extent2].[Body] AS [Body],
[Extent2].[BlogId] AS [BlogId],
[Extent2].[CreatedBy_Id] AS [CreatedBy_Id],
[Extent2].[UpdatedBy_Id] AS [UpdatedBy_Id]
FROM [dbo].[PostImage] AS [Extent1]
INNER JOIN [dbo].[Posts] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
WHERE [Extent1].[Id] = @p0
) AS [Limit1]',N'@p0 int',@p0=4
This returns the post so that you can display it.
.jpg) |
| Figure 5 |
Summary
Entity Framework 4.1 Code First let you define your classes in a way that makes most sense to your application without having to worry about the database schema. The fluent API mappings provide the ability to ensure that your classes can find their way to the database that is being used to persist your data. There are a great number of available mappings to work with.
Be sure to check the Entity Framework 4.1 MSDN Documentation (https://msdn.microsoft.com/en-us/library/gg696172(v=VS.103).aspx) and the Entity Framework team blog (https://blogs.msdn.com/adonet ) to discover more of the relationship mappings you can achieve with the fluent API.