Entity Framework: Modeling an Email Domain with CC and BCC Addresses
Working with Entity Framework Code First takes a lot of the guesswork out of entity relationship modeling in your SQL database. But, if you’re like me, you need to have some awareness of how Entity Framework has designed your database schema.
As your data model’s complexity grows, what is the best solution for modeling an object that requires proper (or, at least, decent) normalization in your database? Should you try to prevent unnecessary record duplication? What about primary key constraint violation?
Example: Email Addresses
Say you’re working on a WCF service that sends emails.
Let’s set up a prototype entity model as quick as possible to see what Entity Framework Code First generates in our SQL database:
[Table("Emails")]
public class Email {
public Email() {
this.ToAddresses = new HashSet<EmailAddress>();
this.CcAddresses = new HashSet<EmailAddress>();
this.BccAddresses = new HashSet<EmailAddress>();
}
[Key]
public int ID { get; set; }
public string Subject { get; set; }
public string Body { get; set; }
public EmailAddress FromAddress { get; set; }
public List<EmailAddress> ToAddresses { get; set; }
public List<EmailAddress> CcAddresses { get; set; }
public List<EmailAddress> BccAddresses { get; set; }
}
[Table("EmailAddresses")]
public class EmailAddress {
[Key]
public int ID { get; set; }
public string Address { get; set; }
}
We can seed the tables with a test:
[Fact]
public void SeedEmailsTable() {
using (EfRelationshipsDbContext db = new EfRelationshipsDbContext()) {
Email email = new Email() {
Subject = "Hello",
Body = "Here's some work for you to derail you from your project entirely!",
FromAddress = "test@test.com",
ToAddresses = new List<EmailAddress> {
new EmailAddress() { Address = "test@test.com" },
new EmailAddress() { Address = "test@test.com" },
new EmailAddress() { Address = "test@test.com" }
},
CcAddresses = new List<EmailAddress> {
new EmailAddress() { Address = "test@test.com" },
new EmailAddress() { Address = "test@test.com" },
new EmailAddress() { Address = "test@test.com" }
},
BccAddresses = new List<EmailAddress> {
new EmailAddress() { Address = "test@test.com" },
new EmailAddress() { Address = "test@test.com" },
new EmailAddress() { Address = "test@test.com" }
}
};
db.Emails.Add(email);
db.SaveChanges();
}
}
By running this test, we let Entity Framework generate the tables in our database, then insert some sample data into the tables. Here’s what the database looks like:
Yea, I don’t know either… It looks like Entity Framework tried hard to model our entity relationships, but it didn’t do a great job. We’re going to have to give it a little push…
Refactoring our Email Model
We can simplify the data model by treating the email address type as an enum that we can simply type switch on when we create new/reconstitute the Email entities.
public enum EmailAddressType {
To,
Cc,
Bcc
}
[Table("Emails")]
public class Email {
[Key]
public int ID { get; set; }
public string Subject { get; set; }
public string Body { get; set; }
public string FromAddress { get; set; }
public virtual ICollection<EmailAddress> ToAddresses { get; set; }
}
[Table("EmailAddresses")]
public class EmailAddress {
[Key]
public int ID { get; set; }
public string Address { get; set; }
public EmailAddressType EmailAddressType { get; set; }
}
We can now seed the table like this:
[Fact]
public void SeedEmailsTable() {
using (EfRelationshipsDbContext db = new EfRelationshipsDbContext()) {
Email email = new Email() {
Subject = "Hello",
Body = "Here's some work for you to derail you from your project!",
FromAddress = "test@test.com",
ToAddresses = new List<EmailAddress> {
new EmailAddress() {
Address = "test@test.com",
EmailAddressType = EmailAddressType.To
},
new EmailAddress() {
Address = "test@test.com",
EmailAddressType = EmailAddressType.To
},
new EmailAddress() {
Address = "test@test.com",
EmailAddressType = EmailAddressType.Cc
},
new EmailAddress() {
Address = "test@test.com",
EmailAddressType = EmailAddressType.Cc
},
new EmailAddress() {
Address = "test@test.com",
EmailAddressType = EmailAddressType.Bcc
},
new EmailAddress() {
Address = "test@test.com",
EmailAddressType = EmailAddressType.Bcc
},
}
};
db.Emails.Add(email);
db.SaveChanges();
}
}
I’m pleased with the schema that Entity Framework has generated:
The data looks good too:
We can now construct an email with the System.Net.Mail namespace:
var email = db.Emails.Where(e => e.ID == 1).FirstOrDefault();
var toAddresses = email.ToAddresses
.Where(x => x.EmailAddressType == EmailAddressType.To)
.Select(y => new MailAddress(y.EmailAddress.Address))
.Select(z => new MailAddressCollection() { z }).FirstOrDefault();
var ccAddresses = email.ToAddresses
.Where(x => x.EmailAddressType == EmailAddressType.Cc)
.Select(y => new MailAddress(y.EmailAddress.Address))
.Select(z => new MailAddressCollection() { z }).FirstOrDefault();
var bccAddresses = email.ToAddresses
.Where(x => x.EmailAddressType == EmailAddressType.Bcc)
.Select(y => new MailAddress(y.EmailAddress.Address))
.Select(z => new MailAddressCollection() { z }).FirstOrDefault();
var emailMessage = new MailMessage() {
Subject = email.Subject,
Body = email.Body,
};
foreach (var address in toAddresses) {
emailMessage.To.Add(address);
}
foreach (var address in ccAddresses) {
emailMessage.CC.Add(address);
}
foreach (var address in bccAddresses) {
emailMessage.Bcc.Add(address);
}
Code is here
Summary
We weren’t sure up front how we were going to model our Email domain in a way that Entity Framework generated a satisfactory entity relationship model in our SQL database.
Our initial concerns over duplicate records and primary key constraint violation were superseded by focusing on generating an understandable database schema through Entity Framework Code First.
When you’re faced with difficulty modeling a domain through Entity Framework Code First, it helps to find a middle way between a good object oriented model and a good entity relational database model.
Do you agree? If so, sign up for my newsletter. Or drop me a comment and let me know, I love to learn and would love to hear from you!