IT:AD:EF/CodeFirst:HowTo:Define Models/Relationships/Examples/Source
Summary
The source code used for the other samples.
Process
namespace XAct.Spikes.CodeFirstBasics
{
/* Assuming a config file containing:
*
<connectionStrings>
<add name="AppDbContext"
connectionString="data source=localhost;initial catalog=SPIKE_RELATIONSHIPS;integrated security=True;MultipleActiveResultSets=True;"
providerName="System.Data.SqlClient" />
</connectionStrings>
*/
public class Program
{
private static void Main(string[] args)
{
Database.SetInitializer(new MyInitializer());
EnsureDbBuilt();
//Tests:
T_CanGetFirstInvoice();
T_FirstInvoiceHasUser();
T_FirstInvoiceHasComment();
T_FirstInvoiceHasComment2();
T_FirstInvoiceHasCategory();
T_FirstInvoiceHasSubCategory();
T_FirstInvoiceHasLineItems();
T_FirstInvoiceHasLineItemsWithProductsIncluded();
T_FirstInvoiceLineItemHasMndatoryProduct();
T_FirstInvoiceLineItemHasMndatoryProduct_IfRequested();
}
public static void EnsureDbBuilt()
{
using (AppDbContext context = new AppDbContext())
{
context.Invoices.Load();
}
}
public static void T_CanGetFirstInvoice()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.FirstOrDefault();
Debug.Assert(invoice != null, "Invoice missing.");
}
}
public static void T_FirstInvoiceHasUser()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include(x=>x.User).FirstOrDefault();
Debug.Assert(invoice.User!=null, "Invoice missing User.");
Debug.Assert(!string.IsNullOrEmpty(invoice.User.FirstName), "Invoice User FirstName is missing.");
}
}
public static void T_FirstInvoiceHasComment()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include(x=>x.Comments).FirstOrDefault();
Debug.Assert(invoice.Comments.Count >0, "Invoice missing Comments.");
Debug.Assert(!string.IsNullOrEmpty(invoice.Comments.First().Text), "Invoice missing first Comment text.");
}
}
public static void T_FirstInvoiceHasComment2()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include(x => x.Comments2).FirstOrDefault();
Debug.Assert(invoice.Comments2.Count > 0, "Invoice missing Comments2.");
var comment2 = invoice.Comments2.First();
Debug.Assert(!string.IsNullOrEmpty(comment2.Text), "Invoice missing first Comments2 text.");
Debug.Assert(comment2.InvoiceFK != 0);
}
}
public static void T_FirstInvoiceHasCategory()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include(x => x.Category).FirstOrDefault();
Debug.Assert(invoice.Category!=null, "Invoice missing Category.");
Debug.Assert(!string.IsNullOrEmpty(invoice.Category.Text), "Invoice missing Category text.");
}
}
public static void T_FirstInvoiceHasSubCategory()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include(x => x.SubCategory).FirstOrDefault();
Debug.Assert(invoice.SubCategory!=null, "Invoice missing Category2.");
Debug.Assert(!string.IsNullOrEmpty(invoice.SubCategory.Text), "Invoice missing SubCategory text.");
}
}
public static void T_FirstInvoiceHasComment2NavigationProperty()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include(x => x.Comments2).FirstOrDefault();
Debug.Assert(invoice.Comments2.Count > 0, "Invoice missing Comments2.");
Debug.Assert(!string.IsNullOrEmpty(invoice.Comments2.First().Text), "Invoice missing first Comments2 text.");
Debug.Assert(invoice.Comments2.First().Invoice!=null);
}
}
public static void T_FirstInvoiceHasLineItems()
{
using (AppDbContext context = new AppDbContext())
{
Invoice invoice = context.Invoices.Include(x=>x.LineItems).FirstOrDefault();
ICollection<LineItem> lineItems;
lineItems = invoice.LineItems;
//Note that without mentioning Product, that sub record is not loaded...
Debug.Assert(lineItems.Count() > 0, "Invoice missing line items if not specifically Included.");
}
}
public static void T_FirstInvoiceHasLineItemsWithProductsIncluded()
{
using (AppDbContext context = new AppDbContext())
{
//Correcting, and including product required for AMount:
Invoice invoice = context.Invoices.Include(x => x.LineItems).Include("LineItems.Product").FirstOrDefault();
ICollection<LineItem> lineItems;
lineItems = invoice.LineItems;
Debug.Assert(lineItems.Count() > 0, "Invoice missing line items if not specifically Included.");
}
}
public static void T_FirstInvoiceLineItemHasMndatoryProduct()
{
using (AppDbContext context = new AppDbContext())
{
var invoice = context.Invoices.Include("LineItems.Product").FirstOrDefault();
var product = invoice.LineItems.First().Product;
Debug.Assert(product!=null);
}
}
public static void T_FirstInvoiceLineItemHasMndatoryProduct_IfRequested()
{
using (AppDbContext context = new AppDbContext())
{
Invoice invoice = context.Invoices.Include("LineItems.Product").FirstOrDefault();
//Notice neat way of double including:
var lineItem = context.Invoices
.Include(i => i.LineItems.Select(li => li.Product))
.FirstOrDefault();
var product = invoice.LineItems.First().Product;
Debug.Assert(product != null);
}
}
}
namespace Seeding
{
//Invoke *early*, eg from a Boottrapper:
//Database.SetInitializer<GraphContext>(new GraphInitializer());
public class MyInitializer : DropCreateDatabaseAlways<AppDbContext>
{
protected override void Seed(AppDbContext context)
{
SeedAddresses(context);
SeedUsers(context);
SeedCategories(context);
SeedCategories2(context);
SeedProducts(context);
SeedInvoices(context);
base.Seed(context);
}
private void SeedAddresses(AppDbContext context)
{
context.Addresses.Add(new Address {Street = "123 Abc..."});
context.Addresses.Add(new Address {Street = "345 All that jive..."});
}
private void SeedUsers(AppDbContext context)
{
context.Users.Add(new User {FirstName = "John", BillingAddressId = 1});
context.Users.Add(new User {FirstName = "Paul", BillingAddressId = 1});
context.Users.Add(new User {FirstName = "Betty", BillingAddressId = 2});
}
private void SeedCategories(AppDbContext context)
{
context.Categories.Add(new Category {Text = "Cat1A"});
context.Categories.Add(new Category {Text = "Cat1B"});
context.SaveChanges();
}
private void SeedCategories2(AppDbContext context)
{
context.Categories2.Add(new SubCategory {Text = "Cat2A"});
context.Categories2.Add(new SubCategory {Text = "Cat2B"});
context.SaveChanges();
}
private void SeedProducts(AppDbContext context)
{
context.Products.Add(new Product
{
Description = "Guide to seeing specs clearly",
Price = 123.45m
});
context.Products.Add(new Product
{
Description = "My Opinion on how to develop software",
Price = 0.02m
});
context.Products.Add(new Product
{
Description = "Developing Software and Life/Work Balance",
Price = 100.00m
});
context.SaveChanges();
}
private void SeedInvoices(AppDbContext context)
{
context.SaveChanges();
User user = context.Users.First(u => u.FirstName == "John");
var invoice = new Invoice
{
Summary = "Invoice A",
CategoryId = 1,
SubCategoryId = 2,
UserId = 1,
ShippingAddressId = 1
};
invoice.LineItems.Add(new LineItem {Quantity = 1, ProductFK = 1});
invoice.LineItems.Add(new LineItem { Quantity = 2, ProductFK = 2 });
invoice.LineItems.Add(new LineItem { Quantity = 3, ProductFK = 3, Discount = 0.80m });
var p = invoice.LineItems.First().Product;
invoice.Comments.Add(new Comment {Created = DateTime.Now, Text = "Foo"});
invoice.Comments.Add(new Comment {Created = DateTime.Now, Text = "Bar"});
invoice.Comments2.Add(new CommentWithNavigationProperty {Created = DateTime.Now, Text = "Foo"});
invoice.Comments2.Add(new CommentWithNavigationProperty {Created = DateTime.Now, Text = "Bar"});
context.Invoices.Add(invoice);
context.SaveChanges();
}
}
}
namespace Entities
{
using System;
// Requirements to enable the use of change-tracking POCO Proxies:
// Class must be public, non-abstract, non-sealed.
public class User
{
// Public *virtual* get/set for all persisted properties.
public virtual int Id { get; private set; }
// Marking *scalar* as virtual, marks it for Proxy change tracking.
public virtual string FirstName { get; set; }
// 1-1 (required) Relationship:
public virtual int BillingAddressId { get; set; }
public virtual Address BillingAddress { get; set; }
// Collection based Navigation properties must implement ICollection<T>
// Marking a *Navigation Collection* as virtual, marks it for Lazy Loading
public virtual ICollection<Invoice> Invoices
{
get { return _invoices ?? (_invoices = new Collection<Invoice>()); }
private set { _invoices = value; }
}
private ICollection<Invoice> _invoices;
}
public class Address
{
public virtual int Id { get; set; }
public virtual string Street { get; set; }
//...
}
//See above requirements to enable the use of change-tracking POCO Proxies
public class Invoice
{
//PK can be guessed if called convention of 'Id', but don't rely on it:
//define it always in the InvoiceMap.
//Scalar property (if all props are virtual, then object can be proxied):
public virtual int Id { get; private set; }
//Scalar property (if all props are virtual, then object can be proxied):
public virtual string Summary { get; set; }
//1-1...* (Mandatory) Relationship to user:
public virtual int UserId { get; set; }
public virtual User User { get; set; }
//1-0..1 (optional) Relationship:
public virtual int ShippingAddressId { get; set; }
public virtual Address ShippingAddress { get; set; }
//1...*-1 (Mandatory) Relationship:
//Putting an Id above a Navigation property
// * allows setting the Item by Id
public virtual int CategoryId { get; set; }
public virtual Category Category { get; private set; }
//0...1-* (optional) Relationship:
public virtual int? SubCategoryId { get; set; }
public virtual SubCategory SubCategory { get; private set; }
//False 1-1 Relationship (ie, a Complex Type):
//Build Collections on demand:
public virtual AComplexType InvoiceDiscount
{
get { return _invoiceDiscount ?? (_invoiceDiscount = new AComplexType()); }
set { _invoiceDiscount = value; }
}
private AComplexType _invoiceDiscount;
//1-1...* (Required) Relationship:
//Build Collections on demand:
public virtual Collection<LineItem> LineItems
{
get { return _lineItems ?? (_lineItems = new Collection<LineItem>()); }
}
private Collection<LineItem> _lineItems;
//1-0...* (optional) Relationship:
//Build Collections on demand:
public virtual ICollection<Comment> Comments
{
get { return _comments ?? (_comments = new Collection<Comment>()); }
private set { _comments = value; }
}
private ICollection<Comment> _comments;
//1-0...* (optional) Relationship:
//Build Collections on demand:
public virtual ICollection<CommentWithNavigationProperty> Comments2
{
get { return _comments2 ?? (_comments2 = new Collection<CommentWithNavigationProperty>()); }
private set { _comments2 = value; }
}
private ICollection<CommentWithNavigationProperty> _comments2;
//Example of an unsetable, but saveable, calculated account:
public virtual decimal Total
{
get
{
try
{
var result = (1.00m - InvoiceDiscount.Discount)*LineItems.Sum(i => i.Amount);
return result;
}
catch
{
throw;
}
}
private set { }
}
}
//A complex type is one that pretends to be an separate entity, but
//is really stored in its parent table. Requirements are:
//* No Id
//* 1-1 relationship
//[ComplexType]
public class AComplexType
{
public decimal Discount { get; set; }
public string Reason { get; set; }
}
//See above requirements to enable the use of change-tracking POCO Proxies
public class Category
{
public virtual int Id { get; private set; }
//Note that we can remove Convention expecting ICollection<Invoice>
//and it will still create Relationship, as it is defined in FluentAPI
public virtual string Text { get; set; }
}
//See above requirements to enable the use of change-tracking POCO Proxies
public class SubCategory
{
public virtual int Id { get; private set; }
//Note that we can remove Convention expecting ICollection<Invoice>
//and it will still create Relationship, as it is defined in FluentAPI
public virtual string Text { get; set; }
}
//See above requirements to enable the use of change-tracking POCO Proxies
public class LineItem
{
public virtual int Id { get; private set; }
public virtual int InvoiceFK { get; set; }
public virtual Invoice Invoice { get; set; }
//Note that we can remove Nav Reference Nav back to Invoice
//and it will still create Relationship, as it is defined in FluentAPI
public virtual decimal Discount { get; set; }
public virtual int Quantity { get; set; }
//Note navigation property.
public virtual int ProductFK { get; set; }
public virtual Product Product { get; set; }
//A calculated value that is saved:
public virtual decimal Amount
{
get
{
//IMPORTANT: There's a first pass when it's being shaped, when the Product is not yet loaded.
return Product == null ? 0 : (1.00m - Discount)*Quantity*Product.Price;
}
private set { }
}
}
public class Comment
{
public virtual int Id { get; private set; }
public virtual DateTime Created { get; set; }
public virtual string Text { get; set; }
}
public class CommentWithNavigationProperty
{
public virtual int Id { get; private set; }
public virtual int InvoiceFK { get; set; }
public virtual Invoice Invoice { get; set; }
public virtual DateTime Created { get; set; }
public virtual string Text { get; set; }
}
public class Product
{
public virtual int Id { get; private set; }
public virtual string Description { get; set; }
public virtual decimal Price { get; set; }
}
}
namespace Context
{
public class AppDbContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Address> Addresses { get; set; }
//public DbSet<Invoice> Invoices { get; set; }
public DbSet<Category> Categories { get; set; }
public DbSet<SubCategory> Categories2 { get; set; }
public DbSet<Invoice> Invoices { get; set; }
public DbSet<Product> Products { get; set; }
//Use name of ConnectionString in App.Config
public AppDbContext() //: base("AppDbContext")
{
Configuration.LazyLoadingEnabled = false;
}
protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new UserMap());
modelBuilder.Configurations.Add(new CategoryMap());
modelBuilder.Configurations.Add(new SubCategoryMap());
modelBuilder.Configurations.Add(new CommentMap());
modelBuilder.Configurations.Add(new CommentWithNavigationPropertyMap());
modelBuilder.Configurations.Add(new InvoiceMap());
modelBuilder.Configurations.Add(new LineItemMap());
modelBuilder.Configurations.Add(new ProductMap());
modelBuilder.Configurations.Add(new AddressMap());
modelBuilder.Configurations.Add(new AComplexTypeMap());
base.OnModelCreating(modelBuilder);
}
}
}
namespace Maps
{
internal class UserMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<User>
{
public UserMap()
{
this.HasKey(u => u.Id);
//1-1:
this
.HasRequired(i => i.BillingAddress)
.WithMany();//From context of Address, has Multiplicity (a single address can be attached to more than one user).
}
}
internal class InvoiceMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Invoice>
{
public InvoiceMap()
{
//easy enough to set Entity constraints, such as PK:
this.HasKey(u => u.Id);
//It's not often you need to, but worth knowing you can change identity technique:
//modelBuilder.Entity<User>().Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
//Easy enough to set some Property constraints as well:
this.Property(i => i.Summary)
.IsRequired()
.HasMaxLength(50);
//////1-0..1:
this
.HasRequired(i => i.ShippingAddress)
.WithMany()
.WillCascadeOnDelete(false);
//1-0: as it can be shared with User, don't bind Invoice and Address, so avoid .WithRequired
//Many to One, Required
////1..*-1:
this.HasRequired(i => i.Category)
.WithMany(); //From context of Category: one SubCat can be shared between many Invoices, so: 1-n
//Many to One, Optional
////0..1-1
this.HasOptional(i => i.SubCategory)
.WithMany(); //From context of Category: one SubCat can be shared between many Invoices, so: 0-n
this.HasMany(x => x.Comments)
.WithRequired();
this.HasMany(x => x.Comments2)
.WithRequired()
.HasForeignKey(x => x.InvoiceFK);
this.HasMany(i => i.LineItems)
.WithRequired()
.HasForeignKey(li => li.InvoiceFK) // special name (normally would be InvoiceId):
;
}
}
internal class LineItemMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<LineItem>
{
public
LineItemMap()
{
this.HasKey(li => li.Id);
//this
// ////1...*-1:
// .HasRequired(li => li.Product)
// .WithRequiredPrincipal()
// // special name (normally would be ProductId):
// .HasForeignKey(p => p.ProductFK);
// ;
this.HasRequired(x => x.Product)
.WithMany()
.HasForeignKey(x=>x.ProductFK)
;
}
}
internal class CommentMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Comment>
{
public CommentMap()
{
//easy enough to set Entity constraints, such as PK:
this.HasKey(u => u.Id);
this.Property(x => x.Text).HasMaxLength(20);
}
}
internal class CommentWithNavigationPropertyMap :
System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<CommentWithNavigationProperty>
{
public CommentWithNavigationPropertyMap()
{
//easy enough to set Entity constraints, such as PK:
this.HasKey(u => u.Id);
this.Property(x => x.Text).HasMaxLength(20);
this.HasRequired(x => x.Invoice)
.WithMany()
.HasForeignKey(x => x.InvoiceFK);
}
}
internal class ProductMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Product>
{
public
ProductMap()
{
this.HasKey(p => p.Id);
this.Property(x => x.Description).HasMaxLength(50);
}
}
internal class AddressMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Address>
{
public
AddressMap()
{
this.HasKey(p => p.Id);
this.Property(x => x.Street).HasMaxLength(20);
}
}
internal class CategoryMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<Category>
{
public
CategoryMap()
{
this.HasKey(p => p.Id);
this.Property(x => x.Text).HasMaxLength(20);
}
}
internal class SubCategoryMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<SubCategory>
{
public
SubCategoryMap()
{
this.HasKey(p => p.Id);
this.Property(x => x.Text).HasMaxLength(20);
}
}
internal class AComplexTypeMap : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<AComplexType>
{
public AComplexTypeMap()
{
this.Property(x => x.Reason).HasMaxLength(20);
}
}
}
}
Output
Running
/****** Script for SelectTopNRows command from SSMS ******/ PRINT 'Invoices:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[Invoices] PRINT 'Categories:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[Categories] PRINT 'SubCategories:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[SubCategories] PRINT 'Comments:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[Comments] PRINT 'CommentsWithNavigationProperties:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[CommentWithNavigationProperties] PRINT 'Users:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[Users] PRINT 'Addresses:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[Addresses] PRINT 'LineItems:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[LineItems] PRINT 'Products:' SELECT * FROM [SPIKE_RELATIONSHIPS].[dbo].[Products]
gives:
Still a WIP: note the extra LineItem column that I am having trouble supressing…
Invoices: Id Summary UserId ShippingAddressId CategoryId SubCategoryId InvoiceDiscount_Discount InvoiceDiscount_Reason Total ----------- -------------------- ----------- ----------------- ----------- ------------- --------------------------------------- ---------------------- --------------------------------------- 1 Invoice A 1 1 1 2 0.00 NULL 183.49 (1 row(s) affected) Categories: Id Text ----------- -------------------- 1 Cat1A 2 Cat1B (2 row(s) affected) SubCategories: Id Text ----------- -------------------- 1 Cat2A 2 Cat2B (2 row(s) affected) Comments: Id InvoiceFK Created Text ----------- ----------- ----------------------- -------------------- 1 1 2014-02-26 16:18:28.573 Foo 2 1 2014-02-26 16:18:28.573 Bar (2 row(s) affected) CommentsWithNavigationProperties: Id InvoiceFK Created Text ----------- ----------- ----------------------- -------------------- 1 1 2014-02-26 16:18:28.573 Foo 2 1 2014-02-26 16:18:28.573 Bar (2 row(s) affected) Users: Id FirstName BillingAddressFK ----------- -------------------- ---------------- 1 John 1 2 Paul 1 3 Betty 2 (3 row(s) affected) Addresses: Id Street ----------- -------------------- 1 123 Abc... 2 345 All that jive... (2 row(s) affected) LineItems: Id InvoiceFK Discount Quantity ProductFK Amount Invoice_Id ----------- ----------- --------------------------------------- ----------- ----------- --------------------------------------- ----------- 1 1 0.00 1 1 123.45 NULL 2 1 0.00 2 2 0.04 NULL 3 1 0.80 3 3 60.00 NULL (3 row(s) affected) Products: Id Description Price ----------- -------------------------------------------------- --------------------------------------- 1 Guide to seeing specs clearly 123.45 2 My Opinion on how to develop software 0.02 3 Developing Software and Life/Work Balance 100.00 (3 row(s) affected)