ISM 6225

Distributed
Information
Systems
L E C T U R E 4 – E N T I T Y F RA M E W O R K
BASED ON JULIA LERMAN,CHS 1-8
Agenda
• Background – domain models
• Code first components
• Entity framework example
• Loose ends
◦ Constraints - Fluent API and Data Annotations
◦ Connection strings - Database selection
◦ Migrations
◦ LINQ

• Entity Framework in MVC

2
Object-relational
mapping
• Entity Framework
◦ Minimize SQL
◦ All SQL relations can be represented
◦ 0 to many
◦ 1 to many
◦ Many to many
◦ Type-checking
◦ Fewer run-time errors
◦ Downloaded from NuGet repository

3
Code first
• Developers create classes
◦ E.g. Person, Course etc
◦ Hence the name code first
◦ As opposed to database first

• Entity Framework builds and updates the SQL database with this
information

4
Code first components
1. POCO classes
◦ Plain old C# objects
◦ Name analogous to POJO
◦ Plain old java objects
◦ Or, POTS
◦ Plain old Telephone system
◦ No signs of Entity Framework
◦ But EF can draw many inferences from structure
◦ Convention over configuration

2. Entity Configuration
◦ Data annotations or Fluent

3. Application
◦ Uses POCO classes and entity configuration

5
Resources
• Based on personal experience
◦ Julia Lerman and Rowan Miller, “Programming Entity Framework: Code
first,” O’Reilly Media, ISBN 978-1449312947
◦ Fast-paced, technically-sound introduction
◦ Should know cover-to-cover

◦ Stack Overflow
◦ Professionally courteous responses to advanced questions
◦ Links to relevant resources – blogs etc

6
Technology evolution
EF Core 2
Aug 14, 2017
EF Core 1
Cross-platform, NoSQL, Jun ’16
EF 6 Used in
EdvisionApp
Last Windows exclusive release, Oct 2013
System.Data.Entity.DbContext
(introduced in EF 4.1, ~2011)
System.Data.Entity.Infrastructure.ObjectContext
(Introduced with .NET 3.5, ~2007)
Dot net class library
Initial release ~ 2002

7
Using Entity Framework
• We will learn by example, starting with a simple MVC application
• For those interested
◦ Highly recommended: Simple Vet office, as in Julia Lerman book

• Learnings
◦ Installing Entity Framework
◦ Architecting use of Entity Framework
◦ Connecting to the database
◦ Configuring entities

8
Entity framework use
• Consider the typical application

Stock Application { SportsStore Application {

… <business logic> … <business logic>
Create/ Read/ Update/ Delete Patient records Create/ Read/ Update/ Delete Shopping carts
… <business logic> … <business logic>
} }

Companies Users
Quotes Products
Employees Vendors

9
Typical data
architecture
Entities
Application { Used in
… <business logic>
Class Comapny(Id, Name, …)
CreateCompany(Id = 1) Class Quote(Id, Date, …)
ReadQuote(Id = 1)
Related Class Employee(Id, Name, …)
… <business logic> DBContext : EntityFramework …
} in
Company(Id, Name, …)
Quote(Id, Date, …)
Employee(Id, Name, …)
…

Persisted
in Companies
Quotes
Employees

10
Example setup
• We will map our solution to the data architecture

File name ORM function

EF_Models.cs Entity definitions
ApplicationDbContext.cs Entity framework context
HomeController.cs Business application
Startup.cs Database connection
appsettings.json Connection string

11
Example setup
Application { Used in Entities
… <business logic>
CreateCompany(Id = 1) ApplicationDbContext Class Company(Id, Name, …)
Class Quote(Id, Date, …)
ReadQuote(Id = 1) Related
… <business logic> ApplicationDbContext : DbContext …
in
}
DbSet<Company> Companies EF_Models
HomeController DbSet<Equity> Equities
…

Persisted
in Companies
Equities

12
Github project
• https://github.com/magrawal-USF/MVC_EF_Start.git

13
Additions to MVC start
project
• Company and Equity POCO classes added to Models
• ApplicationDbContext.cs added to the project
◦ Conventionally, in a folder named DataAccess

• Added appsettings.json to specify connection strings

"Data": {
"IEXTrading": {
"ConnectionString":
"Server=(localdb)\\MSSQLLocalDB;Database=IEXTrading;Trusted_Connection=True;Multipl
eActiveResultSets=true"
}
}

• Updated Startup.cs to configure EF with specified information

14
EntityFramework in
action
• We have done a lot of little things, what should happen if all goes
well?
◦ The database should be created on local SQL Server instance

• Run solution
• Open SQL Server Object Explorer
◦ Personal opinion: One factor behind Microsoft’s success among developers
is their attention to detail in making things work easily
◦ Welcoming to newbies

• We can now build our database driven application

15
Results
• Out of the box, EF
worked with the default
database setup in Visual
Studio
• Use View → SQL Server
Object Explorer to find it
◦ May need to use “Add
SQL Server” to locate the
database

• EF created and
populated the database
as specified in our
connection string

16
Basic EntityFramework
conventions
• View the tables in SQL Server object explorer
◦ Id column automatically identified as the
primary key
◦ AnimalType_Id added as a foreign key
◦ How did these happen?
◦ EntityFramework code first

• Remember?
◦ Convention over configuration

• Also, check out the data in the

_MigrationHistory table
◦ Very useful as your application evolves

17
EntityFramework
conventions
• Class names are singular
◦ E.g. Person

• Table names are in plural

◦ E.g. People
◦ Using Entity Framework’s pluralization service
◦ Usually, appends s to class names
◦ E.g. State → States

• Columns have same names as class properties

18
EF conventions (contd.)
• Primary key
◦ Id field, or
◦ <class name>Id field

• One to many relationships

◦ Foreign key for A in class B
◦ List<B> in class A
◦ A in class B
◦ Slightly different implementation in Visit
◦ int PatientId
◦ EF detected intended foreign key relationship and named property accordingly
◦ But see next slide and listing 4-2 for recommended practice

19
Recommended model
for foreign key
relationship
• See example 4-2 in JL
• Preferred model for Visit class would be:
public class Visit
{
public int Id { get; set; }
public DateTime Date { get; set; }
public String ReasonForVisit { get; set; }
public String Outcome { get; set; }
public Decimal Weight { get; set; }
//public int PatientId { get; set; }

public Patient Patient { get; set; }

}

20
Progress so far
• Questions?

• Previous presentation covered basic MVC application components

• This presentation covered basic database connectivity

• We now have the basic setup for database connectivity in place

◦ Can create data models of arbitrary complexity
◦ Use in application, and
◦ Persist in the database

21
Loose ends in database
use
• Specifying constraints
◦ How do you specify more complex constraints?
◦ E.g. required fields

• Connection strings
◦ How do you connect to databases in non-default locations?
◦ E.g. AWS, Azure

• Migrations
◦ How do you gracefully change the data model?
◦ Not just deleting the existing database

• LINQ
◦ How do you work with the data in the application?
◦ Without using SQL

22
Specifying constraints
• 2 methods
◦ Data annotations in model classes, e.g.
[Required]
public string Name { get; set; }
◦ Fluent API

• Personal preference
◦ Fluent API
◦ Keeps class definitions clean and readable
◦ More comprehensive than data annotations
◦ Allows complex relationships between classes

23
Organizing Fluent
configurations
• Convenient to organize fluent configurations for each class in a separate configuration class
◦ EntityTypeConfiguration
◦ But, not yet supported in EntityFrameworkCore (Jan 2018)
◦ http://www.learnentityframeworkcore.com/configuration/fluent-api

• Add file EntityConfigs.cs in DataAccess project

◦ Adapt code in listing 2-11
◦ Use Ctrl+. To fix compile errors

public class AnimalTypeConfiguration : EntityTypeConfiguration<AnimalType>

{
public AnimalTypeConfiguration()
{
ToTable("Species");
Property(p => p.TypeName).IsRequired();
}
}

24
Organizing Fluent –
contd.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new AnimalTypeConfiguration());
}
• In VetContext.cs, add the OnModelCreating() method
◦ Invoked when the database is created
◦ Invoke the fluent configuration
◦ adapted from JL listing 1-6

• Test newly added Fluent configuration

◦ Delete database
◦ Run application
◦ Check the database for changes

25
Fluent configuration
observations
• Model classes have no reference to the constraints
◦ Look as clean as before
◦ Data Annotations can clutter up the class definitions
◦ And mix database constraints into POCO classes

• All database constraints are now defined in a separate file

◦ EntityConfigs.cs
◦ In the project with the DbContext definitions
◦ This project has 2 files
◦ Database context definition
◦ Database model constraint definitions

26
Some fluent methods
• HasKey(t => t.Identifier);
◦ Specifies that Identifier column is the primary key

• HasKey(w => new { w.Provider, w.ProviderUserId });

◦ Specifies that Provider and ProviderUserId are the composite key

• HasMany(p => p.Visits).WithRequired(v => v.Patient);

◦ One to many relationship
◦ Note: cannot be done in our example since patientId is not a class
◦ See JL Example 4-6 and Fig 4-2
• Property(p => p.FirstVisit).IsRequired().HasColumnName("StartDate");
◦ Specifies database column

27
Developer convenience
feature - Initialization
• During development, it is convenient for code first to modify database
schema, instead of deleting the database
static void Main(string[] args)
{
System.Data.Entity.Database.SetInitializer(
new
System.Data.Entity.DropCreateDatabaseIfModelChanges<VetContext>());
// OR
//System.Data.Entity.Database.SetInitializer(
// new
System.Data.Entity.CreateDatabaseIfNotExists<VetContext>());
…;
}

28
Code first
recommendations
• Fluent API over data annotations
◦ Data annotations are convenient
◦ But Fluent is more comprehensive
◦ Also much cleaner to read
◦ Subjective opinion

• Implement model classes and entity configuration as a separate

solution
◦ Class library
◦ Build target
◦ Reuse across solutions

29
Database location and
authentication
• We have connected to the default local SQL Server database
◦ How do you connect to a specified database on a specific server
◦ How do you provide authentication information?

• Try connecting to SQL Server on your VM

30
Connection strings
• The connection parameters to the database are specified in the
application as the connection string
◦ Database location (IP address)
◦ Database instance
◦ Username, password

• Specified in appsettings.json

31
Connection string –
contd.
"Data": {
"IEXTrading": {
"ConnectionString":
"Server=(localdb)\\MSSQLLocalDB;Database=IEXTrading;Trusted_Connection=True;M
ultipleActiveResultSets=true"
}
}

• In Startup.cs:
services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(Configuration["Data:IEXTrading:Connec
tionString"]));

32
Migrations
• Local database
◦ You can delete and create database at will

• Deployment servers
◦ Permissions are limited after initial database creation
◦ How do you
◦ add a column
◦ delete column
◦ change column type etc?

• Migrations (Any alternatives?)

◦ Note: As EF and .NET evolve, confirm migrations compatibility between
application framework and EF version used
◦ https://stackoverflow.com/questions/34956083/ef-6-with-a-dnx-project/38295583#38295583

33
Migrations – contd.
• Migrations references
◦ http://ilmatte.wordpress.com/2012/12/05/entity-framework-5-code-first-enabling
-migrations-in-a-real-project
/
◦ http://
stackoverflow.com/questions/13469881/how-do-i-enable-ef-migrations-for-multi
ple-contexts-to-separate-databases
• Links refer to older version of Entity Framework
◦ But describe concepts nicely
◦ Verified Jan 2018

• Important (https://stackoverflow.com/a/22262341/1385857 )
◦ Using a corporate shared drive may prevent proper execution of EF install scripts
◦ At USF, saving on U: breaks migration commands, even though regular EF functionality is fine

34
Migrations – contd.
• Two scenarios
◦ Setup database from scratch using migrations
◦ Migrations creates the tables
◦ Use SQL etc. to populate tables
◦ We are following this scenario

◦ Setup migrations with existing database

◦ Migrations modifies database from existing schema
◦ No data loss acceptable
◦ Julia Lerman’s blog post (verified Jan 2018):
◦ http://
www.goodreads.com/author_blog_posts/2101481-using-ef-4-3-code-first-migrations-wi
th-an-existing-database

35
Migrating from scratch
• 2 steps

◦ Enable migrations
◦ Done one-time at the start of the project
◦ PM> Enable-Migrations
◦ Creates baseline model from existing database structure
◦ Add migration
◦ Done each time a model change is to be applied to the database
◦ PM> Add-Migration <Migration name>
◦ Detects the difference between current code-first model and all prior migrations
◦ Adds needed operations to pending migrations
◦ PM> Update-Database –verbose
◦ Applies these updates to the database

36
Enabling Migrations
• EF 6 known to be compatible with .NET framework
◦ But not with .NET Standard

• Tools → NuGet Package Manager → Package Manager Console → Select

Default Project: DataAccess
PM> Enable-Migrations
Checking if the context targets an existing database...
Detected database created with a database initializer. Scaffolded migration
'201801290114150_InitialCreate' corresponding to existing database …
Code First Migrations enabled for project DataAccess.

37
Effects of enabling
Migrations
• Migrations folder
added to the project
with the DbContext
◦ Check out the
contents of the Up()
method
◦ These statements are applied
to the database

38
Changing model
• To change model with migrations
◦ E.g.
◦ Add LastName property to Patient class

PM> add-migration 20180129LastName

◦ Check up() method in new migration

PM> update-database –verbose

◦ Verify database update

39
Naming migrations
• Personal experience
◦ Migrations accumulate
◦ Sorted by name
◦ But useful to have them sorted in creation order

• Useful naming convention

◦ yyyymmdd<MigrationImpact>
◦ E.g. 20180129lastName
◦ Ensures sort by date
◦ Across years, months and days

• More details
◦ Check out first blog link in migrations references (slide) for examples
◦ Also MSDN documentation etc.

40
Exercise
• Update the VetOffice object model by adding 2 relevant and related
classes

• Use migrations to update the database with the updated object model

• Use the new classes in queries

41
Migrations – with
existing databases
• http://
stackoverflow.com/questions/9270887/how-do-i-create-a-migration-for
-an-existing-database-in-entityframework-4-3
• http://
www.goodreads.com/author_blog_posts/2101481-using-ef-4-3-code-firs
t-migrations-with-an-existing-database
• http://
www.ralphlavelle.net/2012/02/using-entity-framework-code-first-with
_26.html

42
LINQ
• Querying entities typically done using Linq
◦ Language integrated Query
◦ Filter items in database using SQL-like syntax
◦ But strongly typed, e.g.

// https://stackoverflow.com/questions/11597373/the-specified-type-member-date-is-not-supported-in-linq-to-entities-exception

int checkYear = DateTime.Now.AddYears(-2).Year;

Visit RecentJohnFirstVisit = testContext

.Patients
.Include(p => p.Visits)
.Where(p => p.FirstVisit.Year >= checkYear)
.Where(p => p.LastName == "John")
.FirstOrDefault()
.Visits
.FirstOrDefault();

43
LINQ usage - where
• Common constructs used in Linq queries
• Where
◦ Apply constraints
◦ E.g.

Visit RecentJohnFirstVisit = testContext

.Patients
.Include(p => p.Visits)
.Where(p => p.FirstVisit.Year >= checkYear)

44
LINQ usage - select
• Select
◦ Retrieve collections
◦ E.g. add a list of comments to each visit
public class Visit
{
public …
public List<VisitComment> Comments { get; set; }
}

public class VisitComment

{
public int Id { get; set; }
public int VisitId { get; set; }
public string Comment { get; set; }
}

45
LINQ usage - select
• Select
◦ Retrieve collections
◦ How to retrieve all the comments for the visits by a patient?

Visit RecentJohnFirstVisit = testContext

.Patients
.Include(p => p.Visits.Select(vi => vi.Comments))
.Where(p => p.FirstVisit.Year >= checkYear)
.Where(p => p.LastName == "John")
.OrderByDescending(p => p.Id)
.FirstOrDefault()
.Visits
.FirstOrDefault();

46
LINQ usage - Include
• Entity framework tries to optimize queries
◦ Related objects are not loaded unless specifically asked for

• Include asks for related entities to also be retrieved from the database
• Consider 2 queries
◦ What is the difference in outputs?
◦ Why?

Visit RecentJohnFirstVisit1 = testContext Visit RecentJohnFirstVisit2 = testContext

.Patients .Patients
.Where(p => p.FirstVisit.Year >= checkYear) .Include(p => p.Visits)
.Where(p => p.LastName == "John") .Where(p => p.FirstVisit.Year >= checkYear)
.OrderByDescending(p => p.Id) .Where(p => p.LastName == "John")
.FirstOrDefault() .OrderByDescending(p => p.Id)
.Visits .FirstOrDefault()
.FirstOrDefault(); .Visits
.FirstOrDefault();

47
Next steps
• Complete the Sports Store example in the book

• Some concepts I’ve encountered in the past

◦ May be useful
◦ May be obsolete as EF evolves

48
Relationship fix-up
• Queries typically allow selection at top level
◦ Select Patients meeting certain criteria
◦ E.g. Patients over age 50

• What if
◦ you also wanted to filter visits by certain criteria?
◦ E.g. Visits by these patients this year

• Relationship fix-up is useful

◦ Select patients and visits separately
◦ EF will link all visits with their related patients

• http://
blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditio
nal-include.aspx

49
Modeling inheritance
• How do you model inheritance in general?
◦ Table per hierarchy
◦ 1 common table for all classes in the hierarchy
◦ Common columns for base class fields
◦ Discriminator column
◦ Value in row == <class name>
◦ Plus, columns for inherited classes
◦ Table per type
◦ 1 table for base class
◦ Separate tables for specialized classes
◦ With foreign key to base class

50
Modeling inheritance –
contd.
• Table per concrete type
◦ One table per class
◦ Includes all fields from all inherited classes

• Personal preference
◦ Fewer joins
◦ Easier to understand
◦ But more redundancy
◦ Change in base class requires change in all concrete classes

51
Modeling inheritance –
contd.
• http://
weblogs.asp.net/manavi/archive/2010/12/24/inheritance-mapping-strate
gies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarch
y-tph.aspx
◦ Inheritance with EF Code First: Part 1 – Table per Hierarchy (TPH)

52
Domain models
• Layered architectures are common in modern applications
• In model-driven design, the domain layer represents the model of the
business
◦ Rest of the architecture (services etc) solve complex technical problems

User interface layer

Application layer

Domain layer (e.g.

code-first models)

Infrastructure layer
(e.g. EF dbContext)

53
Entity vs Value objects
• Source: Eric Evans, Domain-driven design
• Consider
◦ Object: User, customer
◦ Object: State, Country etc

• User is an example of an entity

◦ Defined by a thread of continuity through a life-cycle, an identity, not just a set of attributes
◦ Need to be matched with other objects even if attributes differ (e.g. during integration)
◦ We care about who or which they are
◦ Two people with the same name may or may not be the same person

• State is an example of a value object in most business applications

◦ Have no conceptual identity for the application
◦ Represents a descriptive aspect of the domain
◦ We only care for what they are

54
Modeling entities and value
objects
• Entities
◦ Keep class definition simple
◦ Find ways of distinguishing objects
◦ Define what it means to be the same thing

• Value objects
◦ Focus on expressing the meanings of its attributes
◦ Generally, treat value objects as immutable
◦ i.e. cannot be changed by the application
◦ Whole value pattern
◦ Gather all relevant attributes into the value object, and make the value object a property of an entity
◦ E.g. put street, city, postal code etc in an address object and make the address object a property of
the customer entity
◦ Simplifies entities

55
Services
• Models are classes
◦ But many concepts in a domain are not things
◦ They are activities (verbs, not nouns)
◦ And are not a natural responsibility for an entity or value object in the
model
◦ E.g. SendEmailService, TransferFundsService

• Services are operations performed for clients (typically entities)

◦ Defined in terms of what they can do for a client
◦ Have no state (any client can use any instance of the service at any time)

56
Other important model
concepts
• Explicit constraints
◦ Require data that is not a part of the object’s definition
◦ Consolidates rules into one object
◦ Simplifies focus on constraints by isolating them into constraint objects

• Specifications
◦ Value objects that test whether an object satisfies some criteria
◦ E.g. IsDelinquent()
◦ Useful for validation, selection and building to order

• Processes
◦ Can be modeled as services
◦ Make important algorithms into objects

57
Summary
• Background
• Code first components
• Entity framework example
• Loose ends
◦ Constraints - Fluent API and Data Annotations
◦ Connection strings - Database selection
◦ Migrations
◦ LINQ

• Entity Framework in MVC

58