Defining the Database Structure - Code-First Development with Entity Framework (2015)

Code-First Development with Entity Framework (2015)

Chapter 3. Defining the Database Structure

In this chapter, you will learn how to specify the details of our database structure by using the Entity Framework API. We will build on what you have learned in Chapter 2, Your First Entity Framework Application, and write entity classes that define types of columns in destination tables. We will discover how to specify a relationship between tables in your database, through properties in entity classes and the configuration API. We will look at various ways to configure table structures. We will also see how .NET types map to SQL Server column types.

In this chapter, we will cover how to:

· Create classes that define a table structure using the simple and primitive types

· Handle nullable and required properties

· Define attributes and configuration classes, as well as use the model builder API to specify column types

· Specify One-to-One, One-to-Many, and Many-to-Many relationships between classes

Creating table structures

Let's consider all the structures that can be created.

Mapping .NET types to SQL types

Before we start, it would be helpful to take a look at the mappings between .NET types and SQL Server column types. You remember that there is a distinct mismatch between the two, which is one of the problems that Entity Framework strives to fix. You can also find similar mappings between .NET and other RDBMSes, such as Oracle. In this book, we will concentrate on SQL Server. It is not always important to keep these mappings in mind. For example, if you define a property in .NET as integer, you can safely assume that Entity Framework will handle the column's definition and use the appropriate type; for example, int in SQL Server. Here are the mappings for the most commonly used .NET types:

SQL Server Database type

.NET Framework type

Bigint

Int64

binary, varbinary

Byte[]

Bit

Boolean

date, datetime, datetime2, smalldatetime

DateTime

Datetimeoffset

DateTimeOffset

decimal, money, smallmoney, numeric

Decimal

float

Double

int

Int32

nchar, nvarchar,, char, varchar

String

real

Single

rowversion, timestamp

Byte[]

smallint

Int16

time

TimeSpan

tinyint

Byte

uniqueidentifier

Guid

You can view the complete list on the MSDN website at http://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx. If you want to see the mapping for other database engines, you can easily find them on the Internet. For example, you can find Oracle mappings at http://msdn.microsoft.com/en-us/library/cc716726(v=vs.110).aspx.

Configuring primitive properties

Let's start the discussion by looking at string properties. You noticed that SQL Server has many types that map to the string type in .NET. The same is the case with other major RDBMSes. Unlike numeric types, it is actually important to decide how you want to store string-based information in the database. The reason is that most relational database management engines have multiple character storage types. They usually have character types that start with the letter N. This letter signifies that the data to be stored in such columns is Unicode data, based on character sets used to store each character in the double-byte format. These types are necessary to store data using the most non-Latin-based languages, such as Chinese. So, if you are writing an application for US-based users who only use English, you can define your string data as varchar or char instead of nvarchar or nchar, to save physical storage and possibly speed up queries. You can also use fixed length or variable length character columns, signified by the presence or absence of var in the type name. Hence, you need to make some decisions before writing the code. Now, let's look at examples. Let's start with an example of the Person class again. Let's presume that the first and last name properties need to be of variable length and accommodate, at most, 30 characters each. The middle name is of a fixed length of one character.

There are a few ways to configure database structures in Entity Framework. You can use the following:

· Attributes

· Configuration Classes

· The DbModelBuilder API

Let's start with attributes. The property-defining attributes are part of .NET and live in the System.ComponentModel.DataAnnotations namespace. Here is how we will configure the Person class's string properties using attributes:

public class Person

{

public int PersonId { get; set; }

[MaxLength(30)]

public string FirstName { get; set; }

[MaxLength(30)]

public string LastName { get; set; }

[StringLength(1, MinimumLength = 1)]

[Column(TypeName = "char")]

public string MiddleName { get; set; }

}

We are using a few different attributes to fulfill our goal. We are using the MaxLength attributes to configure two properties, specifying the maximum length. We are using StringLength to specify both minimum and maximum length for the middle name property. We are also using the Column attribute to force the char type for the middle name column. Entity Framework will use the Unicode storage by convention, unless explicitly specified otherwise. If we run the code using the preceding configuration, we will end up with exactly the structure we were aiming for, as you can see in the following screenshot:

Configuring primitive properties

Here is how this code looks in VB.NET:

Public Class Person

Property PersonId() As Integer

<MaxLength(30)>

Property FirstName() As String

<MaxLength(30)>

Property LastName() As String

<StringLength(1, MinimumLength := 1)>

<Column(TypeName := "char")>

Property MiddleName() As String

End Class

There is one important thing to know about data annotation attributes. In addition to specifying the data structure, they will also be used as validation attributes. If you, for example, attempt to add a person object with a middle name longer than one character and save this data, you will get an exception before the actual SQL query is constructed and sent to the database. The message details will contain information related to the violated rule(s).

You probably spotted a small problem with the column attribute. We are essentially hardcoding the column type. What would happen if we were to run our project against another RDBMS that uses type names that differ from SQL Server? Entity classes that map to tables should ideally be agnostic to persistence, but in our cases they carry some RDBMS-specific information. In addition to this, we currently do not control the message text for the errors that would occur if we violate the validation rules we specified. So, to deal with this issue, we need to add the error message, as shown in the following lines of code:

[MaxLength(30, ErrorMessage = "First name cannot be longer than 30")]

public string FirstName { get; set; }

The same attribute in VB.NET looks as follows:

<MaxLength(30, ErrorMessage:="First name cannot be longer than 30")>

Property FirstName() As String

Data annotations support localization, so we do not need to hardcode the error message in English. Finally, some situations cannot be handled with annotations at all, such as delete rules for relationships. Luckily, Microsoft provides multiple ways to configure table structures and column types. One way is to use the DbModelBuilder API. To get to this API, we need to override the OnModelCreating method of our DbContext object. Then, we can configure properties for an entity class, Person in our case, as shown in the following code:

public class Context : DbContext

{

public Context()

: base("name=chapter2")

{

}

public DbSet<Person> People { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Entity<Person>().Property(p =>p.FirstName)

.HasMaxLength(30);

modelBuilder.Entity<Person>().Property(p =>p.LastName)

.HasMaxLength(30);

modelBuilder.Entity<Person>().Property(p =>p.MiddleName)

.HasMaxLength(1)

.IsFixedLength()

.IsUnicode(false);

}

}

Note

The Entity class refers to a class that maps to a table in the database.

In the preceding code, we get an instance of the EntityTypeConfiguration class from the context by providing the type of entity we want to configure, Person in our case. Then, we configure one property of the Person class at a time, using the Property method. If we follow this coding practice, we do not need to use data annotation attributes any longer. You will notice the parity between attributes we used in the prior example and methods exposed on the StringPropertyConfiguration class. In the case of the last name, we simply configure the maximum length. Since the default for string properties is Unicode, we did not need to configure the Unicode setting explicitly. In the case of the middle name, we did specify that it is a non-Unicode column. Additionally, we specified that the middle name is a fixed-width column. As a result, we did not have to hardcode the char type for the MiddleName property. So, you now see that the configuration API has an advantage over data annotations in this case.

There is one problem with the preceding code that you undoubtedly noticed. For one table with three columns, the OnModelCreating method is pretty short. What if we have 1,000 tables? The preceding approach will result in unmanageable code as the number of entities grows. Entity Framework provides a way to break this code apart using a separate configuration class for each entity class. We will call our configuration buddy class for Person, class PersonMap, as shown in the following code:

public class PersonMap : EntityTypeConfiguration<Person>

{

publicPersonMap()

{

Property(p =>p.FirstName)

.HasMaxLength(30);

Property(p =>p.LastName)

.HasMaxLength(30);

Property(p =>p.MiddleName)

.HasMaxLength(1)

.IsFixedLength()

.IsUnicode(false);

}

}

The code is virtually identical to the one we wrote using model builder, but it is now much better organized to handle multiple entities. You also saw that we chain multiple methods for a single property together. This chaining of methods is called the Fluent API. The same code in VB.NET looks as follows:

Public Class PersonMap

Inherits EntityTypeConfiguration(Of Person)

Public Sub New()

Me.Property(Function(p) p.FirstName) _

.HasMaxLength(30)

Me.Property(Function(p) p.LastName) _

.HasMaxLength(30)

Me.Property(Function(p) p.MiddleName) _

.HasMaxLength(1).IsFixedLength().IsUnicode(False)

End Sub

End Class

Finally, we need to tell our Context class that we have a configuration class for it to use during the database structure generation. You can do so in the familiar OnModelCreating method. We need to add an instance of our Configuration class to modelBuilder'sConfiguration collection, as shown in the following code:

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Configurations.Add(new PersonMap());

}

We now successfully configured string properties for the Person class, thereby configuring the People table in our database. We used the entity configuration class tied to the Person class via the generic type parameter. The same code in VB.NET looks as follows:

Protected Overrides Sub OnModelCreating(ByValmodelBuilder As DbModelBuilder)

modelBuilder.Configurations.Add(New PersonMap)

End Sub

In the preceding example we used StringPropertyConfiguration class. It was used to configure string properties. This class inherits from LengthPropertyConfiguration, which in turn inherits from PrimitivePropertyConfiguration. These classes support definition for columns that correspond to primitive properties. What primitive properties does Entity Framework support in this fashion? Here is the short list, as follows:

· DateTimePropertyConfiguration

· DecimalPropertyConfiguration

· BinaryPropertyConfiguration

· StringPropertyConfiguration

Let's add more properties to the Person class to exercise various configurations, and become more familiar with the available API, in addition to using other property types, such as integer. Here are the updated Person and PersonMap classes:

public class Person

{

public int PersonId { get; set; }

public string FirstName { get; set; }

public string LastName { get; set; }

public string MiddleName { get; set; }

publicDateTimeBirthDate { get; set; }

public decimal HeightInFeet { get; set; }

public byte[] Photo { get; set; }

public bool IsActive { get; set; }

publicintNumberOfCars { get; set; }

}

publicclass PersonMap()

{

Property(p =>p.FirstName)

.HasMaxLength(30);

Property(p =>p.LastName)

.HasMaxLength(30);

Property(p =>p.MiddleName)

.HasMaxLength(1)

.IsFixedLength()

.IsUnicode(false);

Property(p =>p.HeightInFeet)

.HasPrecision(4, 2);

Property(p =>p.Photo)

.IsVariableLength()

.HasMaxLength(4000);

}

Different methods are available on various property types configuration classes, based on what is applicable at the database level. For example, decimal columns support precision and scale and the number of digits before and after decimal points, so we can configure these for decimal properties. Only the length makes sense for binary properties, so we specify that we can store an image up to 4,000 bytes in the Photo column. For other properties, such as integer and Boolean, there is really nothing to configure, hence just specifying property type is sufficient. Here is what the updated database structure looks like:

Configuring primitive properties

Handling nullable properties

As you noticed, some columns are nullable and some are not. Entity Framework decides if a column is nullable based on the property type by convention. For example, the string type allows null values, hence matching character-based columns are nullable. On the other hand, datetime and int variables cannot be set to null in .NET, hence these columns are non-nullable. What should we do if we want to make these columns nullable, or make string storing columns to be filled compulsory? There are two ways to accomplish this as well. You can just use nullable types to make columns nullable, and this is by far the easiest way. For example, to allow a blank BirthDate value, we can change the property declaration to the following:

public DateTime? BirthDate { get; set; }

On the other hand, if we want to make sure that the first name is required, we can add code to the configuration class, as shown:

Property(p =>p.FirstName)

.HasMaxLength(30)

.IsRequired();

There is an opposite of IsRequired on property configuration classes; the IsOptional method. This method allows you do make non-nullable types to be nullable columns in the database. It is better to just use nullable types, as it makes it easier to leave property values as null in .NET instead of trying to, for example, convert specified values to null for storage in the database.

Here is how the same code looks in VB.NET:

Public Class Person

Property PersonId() As Integer

Property FirstName() As String

Property LastName() As String

Property MiddleName() As String

Property BirthDate() As DateTime?

Property HeightInFeet() As Decimal

Property Photo() As Byte()

Property IsActive() As Boolean

Property NumberOfCars() As Integer

End Class

Public Class PersonMap

Inherits EntityTypeConfiguration(Of Person)

Public Sub New()

Me.Property(Function(p) p.FirstName) _

.HasMaxLength(30) _

.IsRequired()

Me.Property(Function(p) p.LastName) _

.HasMaxLength(30) _

.IsRequired()

Me.Property(Function(p) p.MiddleName) _

.HasMaxLength(1).IsFixedLength().IsUnicode(False)

Me.Property(Function(p) p.BirthDate) _

.HasPrecision(1)

Me.Property(Function(p) p.HeightInFeet) _

.HasPrecision(4, 2)

Me.Property(Function(p) p.Photo) _

.IsVariableLength().HasMaxLength(4000)

End Sub

End Class

It is useful at this point to experiment with simple properties to get a feel of Entity Framework's configuration approach, and study the types not covered in the preceding example. It is important to notice that some of the SQL Server types shown in .NET to SQL Server mapping in the preceding table are specific to SQL Server and may not be available in other relational database systems. As a result, you will not find the configuration API to support such columns. The answer to this problem is to use the HasColumnTypemethod on property-configuring classes, and specify the type you want to use explicitly by name. If you want to generically support multiple database engines, just write a helper class for this purpose that will return the appropriate database type as string, based on the currently configured database engine. You cannot use this approach for attributes because parameters must be constants. All primitive property configuration classes share two more methods, HasColumnName and HasColumnOrder. The latter allows you to precisely control the ordinal position of a column in the table. The former allows you to have a column name different from the property name. If they are the same, you do not need to use explicit column names.

However, if you are dealing with a legacy database with short column names for example, the ability to rename properties that map to such columns comes in quite handy, as it increases the code readability. On the other hand, you do not have to have Entity Framework control your table structure. You can use Entity Framework Code-First to talk to an existing database just as well. Still, you have to have correct mapping between properties in classes and columns in tables regardless of whether you update the database structure yourself, or you let Entity Framework do it.

Defining relationships

Now let's take a look at relationships between classes, and consequently between tables. There are three main types of relationships in database theory:

· One-to-Many

· One-to-One

· Many-to-Many

First of all, let's define what a relationship is. It is defined based on how two or more objects relate to each other. It is identified by the multiplicity value on both ends of the relationship. For example, One-to-Many means that on one end of a relationship, sometimes called the parent, we only have one entity. On the other end of the relationship, we can have multiple entities, sometimes called children. The Entity Framework API refers to those ends as principal and dependent, respectively. The One-to-Many relationship has a slight variation, called One-or-Zero-to-Many, which means that a child may or may not have a parent. The One-to-One relationship has variants as well, where either end of a relationship is optional. Let's take a look at them separately.

The One-to-Many relationship

Let's model an example to get a clear understanding of the One-to-Many relationship. Let's say that in our sample project, each person from earlier in this chapter can have multiple phone numbers. Hence, our next object would be a phone. We are going to create a very simple object with an identifier and a phone number, as follows:

public class Phone

{

public int PhoneId { get; set; }

public string PhoneNumber { get; set; }

}

We also need to add a property onto the context using the type DbSet of Phone so that we can have access to phone numbers. You have seen this code many times already. Each person can have many phone numbers. This just screams collection of phone numbers on the person object, right? So, we will just add a new property to the Person class. We will use ICollection of Phone instead of a specific type of collection. This will work just fine, and in fact, you can let Entity Framework define the collections for you. Here is what the new property definition looks like:

public virtual ICollection<Phone> Phones { get; set; }

In order to avoid potential null reference exception possibility, we want to create a new instance of collection when a Person object is created. We will use HashSet of the T collection type, as shown in the following code:

public Person()

{

Phones = new HashSet<Phone>();

}

You will notice that we use the virtual keyword (Overridable in VB.NET) when defining the property. This keyword will enable us to use lazy loading when looking for phone numbers for a person. What this means is that Entity Framework will actually dynamically load phone objects into the collection from the database on demand, at the time you attempt to access the phone's property. This is a new concept called lazy loading, which we will cover in more detail in subsequent chapters. It is so called because Entity Framework will initially not issue a query to populate phone numbers, but instead will load the data when code asks for it. There is an alternate approach to loading related data called eager loading. With this approach, the phone numbers would be proactively loaded prior to access to the phone's property. For now, let's assume that we want to take advantage of the lazy loading functionality, hence the virtual keyword. Interestingly enough, there is no person identifier contained in the Phone class. This is something that you must do as a database developer. However, in the Entity Framework world, you have the flexibility to omit this property. The reason is that we may not have a legitimate business reason to know person ID when looking at phones. In our case, we want to know about the phone number only in the context of a person and never as a standalone object. Let's convince ourselves that this will actually work, by running the app after adding a new person with some phone numbers to our app and saving them as a batch. Once we do this, we will take a look at the structure that is created.

Here is what the same code in VB.NET looks like:

Public Class Phone

Property PhoneId() As Integer

Property PhoneNumber() As String

End Class

Overridable Property Phones() As ICollection(Of Phone)

Public Sub New

Phones = new HashSet(Of Phone)

End Sub

Now, let's write a few more lines of code to actually add a person with phone numbers to our database. We already saw how to add and save a new person; now we just need to add some phone numbers, as shown in the following code:

using (var context = new Context())

{

var person = new Person

{

LastName = "Doe",

FirstName = "John",

IsActive = true

};

person.Phones.Add(new Phone { PhoneNumber = "123-446-7890" });

person.Phones.Add(new Phone { PhoneNumber = "123-446-7891" });

context.People.Add(person);

context.SaveChanges();

}

The preceding code is not really specific to Entity Framework, with the exception of context method calls. We simply write the basic object-oriented code. We create an instance of a class and then add a few more instances of another class to a collection. Entity Framework contains all the magic required to create the appropriate database structure, as well as convert object operations to database queries. To convince yourself that this worked, simply open SSMS and look at the data and table structure, as shown in the following screenshot:

The One-to-Many relationship

Entity Framework uses a naming convention to define PersonId in the Phone table. If you do not like this naming convention, you can easily add some code to our mapping class of the person object to correct the issue. Just add a property called PersonId to the Phoneclass, which is what we want to name the column. Then, we need to configure the person object and teach it that it has many phones, each containing a link back to the person in the PersonId property. Here is what this code looks like in our PersonMap class:

HasMany(p =>p.Phones)

.WithRequired()

.HasForeignKey(ph =>ph.PersonId);

This code is quite typical for relationship definitions. The HasMany method tells Entity Framework that there is a One-to-Many relationship between the Person and Phone classes. The WithRequired method specifies that the Person link on Phones is required. In other words, the phone is not a standalone object and must be linked to a person. Finally, the HasForeignKey method identifies which property serves as this link. Consequently, Entity Framework will use PersonId as the column name. We do not need to populate this new property at all; our code will still work fine, even with these new changes.

We should also take look at an additional use case for the One-to-Many configuration. This use case arises when we have a lookup property on a main entity that points to another entity. Lookup properties point to a full parent of a child entity. Such properties are useful when you need access to parent information while manipulating or examining a child record. For example, let's add a person type to the person entity in the previous example. Essentially, this example is still One-to-Many, but the approach is slightly different. In this case, you would typically use a dropdown control on the main entity edit screen that contains values from a lookup parent table. The lookup table in our example is very simple; it just contains ID and name columns. We are also going to make this relationship optional to illustrate how to add nullable foreign keys. Hence, the new property PersonTypeId in the person class must be nullable. Since we are going to make all primary keys be identity columns, we will use the nullable integer type to define this property. We will also need to add another property, using actual PersonType in order to configure the relationship, as shown in the following code:

public int? PersonTypeId { get; set; }

public virtual PersonTypePersonType { get; set; }

Conversely, we will need to add a collection of people to the PersonType class to signify that there could be many people for each type, as shown in the following code:

public class PersonType

{

publicintPersonTypeId { get; set; }

public string TypeName { get; set; }

publicvirtual ICollection<Person> Persons { get; set; }

}

The same code in VB.NET looks as follows:

Property PersonTypeId() As Integer?

Overridable Property PersonType() As PersonType

Public Class PersonType

Property PersonTypeId() As Integer

Property TypeName() As String

Property Persons() As ICollection(Of Person)

End Class

When it comes to relationships, you can configure them from either side of the relationship, principal or dependent. So, let's create a new PersonTypeMap class that will serve this purpose, as shown in the following code:

public class PersonTypeMap : EntityTypeConfiguration<PersonType>

{

publicPersonTypeMap()

{

HasMany(pt =>pt.Persons)

.WithOptional(p =>p.PersonType)

.HasForeignKey(p =>p.PersonTypeId)

.WillCascadeOnDelete(false);

}

}

We use the WithOptional method to signify that the foreign key constraint will be nullable. We also specify the delete rule for the constraint, using the WillCascadeOnDelete method. Most database engines support multiple actions for delete rules for foreign key relationship constraints. These rules specify what happens when a parent is deleted. You can set the foreign key column to null, that is, do nothing, thus creating an error if child rows exist or delete all related dependents. Entity Framework allows developers to either delete all child rows or do nothing. There are database administrators who object to the use of cascade delete rules because some engines do not provide adequate logging when this happens. Here is how the code looks in VB.NET:

Public Class PersonTypeMap

Inherits EntityTypeConfiguration(Of PersonType)

Public Sub New()

HasMany(Function(pt) pt.Persons) _

.WithOptional(Function(p) p.PersonType) _

.HasForeignKey(Function(pt) pt.PersonTypeId) _

.WillCascadeOnDelete(False)

End Sub

End Class

We must remember to update DbContext (the Context class) and add a new DbSet to it, this time of the type PersonType, as well as adding our new PersonType configuration class to the configurations collection on the model builder. As an alternative to callingWillCascadeOnDelete, you can globally remove the convention from the model builder and turn off the cascade delete rule for the entire database model in the OnModelCreating method of the context. Conventions in Entity Framework allow developers to perform certain configuration actions globally, in all entity classes within a context. We will discuss conventions in more detail in a later chapter. The following specific convention deals with relationships only:

protected override void OnModelCreating(

DbModelBuilder modelBuilder)

{

modelBuilder.Conventions

.Remove<OneToManyCascadeDeleteConvention>();

modelBuilder.Conventions

.Remove<ManyToManyCascadeDeleteConvention>();

}

The code in VB.NET is identical, as shown here:

Protected Overrides Sub OnModelCreating(ByValmodelBuilder As DbModelBuilder)

modelBuilder.Conventions.Remove(Of OneToManyCascadeDeleteConvention)()

modelBuilder.Conventions.Remove(Of ManyToManyCascadeDeleteConvention)()

End Sub

We actually remove two conventions that control delete behavior—one for One-to-Many, the other for Many-to-Many relationships.

Note

Entity Framework contains many conventions you can remove if necessary.

The Many-to-Many relationship

The Many-to-Many relationship is used when you have multiple entities on both ends of the relationship. For example, one person can work for multiple companies, and each company can employ multiple people. At the database layer, this relationship will be defined in the so-called junction table, sometimes also called a cross reference table. This table will contain primary key columns from tables on both ends of the relationship. There are two use cases for this type of relationship that matter to us. A junction table can have no additional values or columns, or it can have additional data. If a junction table has no other data, we technically do not need to have this table represented in the object model at all. Let's code this situation. We will add a new class to model the company and add code to the person map to specify the relationship. Just like in the One-to-Many relationship, we will have a property that is a collection of related entities. We will add new collections to both Person and Company classes, using the related entity as the type of the collection. Here is how the Company class looks:

public class Company

{

public int CompanyId { get; set; }

public string CompanyName { get; set; }

publicvirtual ICollection<Person> Persons { get; set; }

}

Now, we just need to add code to the entity type configuration class that defines the person table to specify the other end of the relationship. The code will exist in the PersonMap class, as shown in this example:

HasMany(p =>p.Companies)

.WithMany(c =>c.Persons)

.Map(m =>

{

m.MapLeftKey("PesonId");

m.MapRightKey("CompanyId");

});

Technically speaking, this configuration is optional if you are okay with Entity Framework generating names for columns. What we mean is that Entity Framework will actually create a junction table solely based on classes and properties that define both ends of the relationship, because they have collection properties for related entities. Since we want something different from the default, we specify column names explicitly in the junction table. The same code in VB.NET looks like the following:

HasMany(Function(p) p.Companies) _

.WithMany(Function(c) c.Persons) _

.Map(Sub(m)

m.MapLeftKey("PesonId")

m.MapRightKey("CompanyId")

End Sub)

If you look at the database structure, you will find our junction table, called PersonCompanies in the database. Feel free to take out this configuration code and regenerate the database to see what the default naming convention is.

What if our junction table needs to hold more data? For example, we want to add hire date for each person, as the date they start working for a company. In this case, we will actually need to add a class to model the junction table. We can call it PersonCompany, for example. It will still have the same two primary key properties; company and person identifiers. It will also have properties for one person and one company and a property for the hire date. Also, both Person and Company classes will have a collection ofPersonCompanies instead of companies and persons, respectively. Finally, you will configure the Many-to-Many relationship, just like in the previous example between this new class and the Person class, and between the new class and the Company class. Take a few minutes and model this use case for practice if you would like.

The One-to-One relationship

The One-to-One relationship is not very common, but does occasionally come up. You may choose to pursue this design if you have a lot of optional data for an entity that is grouped somehow. For example, a person can be a student with a college name and enrolment date. These fields will be nullable for anyone who is not a student. So, we will group these fields into another entity called student, as shown in the following code:

public class Student

{

public intPersonId { get; set; }

public virtual Person Person { get; set; }

public string CollegeName { get; set; }

publicDateTimeEnrollmentDate { get; set; }

}

The same code in VB.NET looks as follows:

Public Class Student

Property PersonId() As Integer

Overridable Property Person() As Person

Property CollegeName() As String

Property EnrollmentDate() As DateTime

End Class

You will notice that we continue to use the virtual keyword (Overridable in VB) in order to enable lazy loading. Our configuration class looks very familiar to you, as shown in the following code:

public class StudentMap : EntityTypeConfiguration<Student>

{

publicStudentMap()

{

HasRequired(s =>s.Person)

.WithOptional(p =>p.Student);

HasKey(s =>s.PersonId);

Property(s =>s.CollegeName)

.HasMaxLength(50).IsRequired();

}

}

There is one new method we use—HasKey. This specifies the primary key for a table; in other words, a unique value that will allow us to find an entity. We did not have to use it before because we followed a naming convention. Entity Framework will figure out the key if the property name consists of the class name plus the "Id" suffix or just "Id". Since we use PersonId as the primary key value now, we need to provide an additional hint to the runtime, and that is where the HasKey method comes in. The primary key in the child table will also become the foreign key to the parent table. Here is how this class looks in VB.NET:

Public Class StudentMap

Inherits EntityTypeConfiguration(Of Student)

Public Sub New()

HasRequired(Function(s) s.Person) _

.WithOptional(Function(p) p.Student)

HasKey(Function(s) s.PersonId)

Me.Property(Function(s) s.CollegeName) _

.HasMaxLength(50).IsRequired()

End Sub

End Class

Because this relationship is optional, it is called One-or-Zero-to-One. You may have another use case where both ends of the relationship are required. This type is called One-to-One. For example, each person must have a single login, which is mandatory. The code for this use case will be almost identical, with one exception—you will use the WithRequiredDepentent or WithRequiredPrincipal method instead of the WithOptional method.

Note

We can always configure relationships from either the dependent or principal side of the relationship. We need to always configure both ends of a One-to-One relationship, using the Has and With methods to ensure that the One-to-One relationship is created.

Self-test questions

Q1. You would like to define a column to store a number without a fractional value, but you want to make the value optional. What .NET type should you use for such property?

1. Decimal

2. Decimal?

3. Int

4. Int?

Q2. If you want to make the first name column to be non-nullable in the database, you can rely on default conventions in Entity Framework and avoid all configuration, true or false?

Q3. You cannot override conventions that are preloaded with Entity Framework, such as the one that makes all foreign key constraints be setup to cascade on delete, true or false?

Q4. Which of the following is not a type of relationship?

1. One-to-Many

2. Many-to-Many

3. One-or-Zero-to-Many

4. Many-to-Default

Q5. The best way to configure all properties in all classes is to list them one by one in OnModelCreating method of the context, true or false?

Q6. If you do not configure any additional information for string properties, what type will be used in the SQL Server database?

1. NVARCHAR(4000)

2. NVARCHAR(MAX)

3. VARBINARY(MAX)

4. VARCHAR(MAX)

Q7. Which is not an appropriate name for the first end in a relationship definition?

1. Principal

2. Parent

3. Domain

Q8. If you want to use a "buddy" class to configure an entity, what class do you need to inherit from?

1. EntityTypeConfiguration<T> (of T)

2. PrimitivePropertyConfiguration<T> (of T)

3. ComplexTypeConfiguration<T> (of T)

4. EntityConfiguration<T> (of T)

Summary

In this chapter, you learned how we can configure persistence layer details for entities and classes that map to database structures, specifically tables. We learned that we can use attributes, entity type configuration classes or model builder APIs to perform this task. We discovered that we can make columns nullable by using appropriate nullable types in .NET. We looked at mappings between .NET types and SQL Server types as an example of an RDMBS. We discovered that primitive types, such as numbers and strings, have corresponding property configuration classes that expose methods, allowing us to make those properties required, or configure the maximum allowable length. We learned that using the EntityTypeConfiguration class allows us to neatly organize our configuration code. We saw that this class exposes the API that affords developers an opportunity to configure all the properties in a fluent manner.

We also learned that classes can have relationships between each other. We saw there are three distinct types of relationships: One-to-Many (or One-to-Zero-to-Many), Many-to-many, and One-to-One (or One-to-Zero-to-One). The type names refer to a multiplicity value at each end of the relationship, with the principal or parent being the first end and dependent or child being the second one. We saw that configuration objects, such as entity type configuration, expose the API that allows us to configure the relationship using methods such as HasMany or HasRequired. We discovered that in the case of the Many-to-Many relationship, you may have additional data in the junction table or not. If no additional data is required, the entire table only exists in the database and not in the object model.

In the next chapter, we will start using our structure to query and manipulate the data.