Who’s Afraid of Entity Framework? Richie Rump, Dataveloper 

Who Am I? 

My Clique 

Agenda • What is Entity Framework? • How does Entity Framework work? • Why do developers use it? • What should you, the Data Professional, be looking for in an Entity Framework project. 

What is an ORM? • Object Relational Mapping • Converts pragmatic objects into a relational database. • Hibernate (Java) • Active Record (Ruby) Objects Mapping Data 

What is Entity Framework • ORM for .NET Applications • Allows the developer to: • Generate databases • Save object data to a database • Generate DDL scripts from object changes • Generate SQL for data retrieval • All done with very minimal code. 

using (conn = new SqlConnection(connectString)) { conn.Open(); DbCommand cmd = conn.CreateCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT Name, AccountNumber FROM sales.Store"; dbReader = cmd.ExecuteReader(); // do something cmd.Dispose(); conn.Close(); } What Devs Used to Write 

var stores = context.Stores; Now With EF 

How to Use EF: Designer 

How to use EF: Code 

Table Based Results  Entity Based Results Query Compilation Database Creation How Entity Framework Works Programming Entity Framework: Code First by Julie Lerman and Rowan Miller. Page 9 public Post() { public int Id { get; set; } public int ParentId { get; set; } public string Title { get; set; } public string Body { get; set; } } System.Data.Metadata.Edm EntityType .EdmProperty .AssociationType .EdmType 

Entity Framework Models Design Centric Code Centric New Database Model First Create .edmx model in designer Generate DB from .edmx Classes auto-generate from .edmx Code First Define classes & mapping in code Database auto-created at runtime Existing Database Database First Reverse engineer .edmx model Classes auto-generate from .edmx Code First Define classes & mapping in code Adapted from Programming Entity Framework: Code First by Julie Lerman and Rowan Miller. Page 3. 

Entity Framework Versions • v3.5 – .NET 3.5 AKA v1.0 • v4.0 – .NET 4.0. - Lazy Loading, POCO, Perf Enhancements • v4.1 – Code First • v4.2 – Bug Fixes - Semantic versioning • v4.3 – EF Migrations • v5.0 – .NET 4.5 - ENums, Table-valued functions, Spatial Data Types • v6.0 – Async Support, Connection Resiliency • Core v1.0 aka v7.0 – Beta, New Devices (non-Windows), New Data Stores (non-relational) 

Why Use Entity Framework? • Developer works with objects. • Focus on Business Domain (objects) not DB, Connections, commands, etc. • Developer Productivity • No need to write SQL or CRUD commands 

Where Does Microsoft Stand • Microsoft is making minimal investment in ADO.NET. • LINQ to SQL is essentially dead • EF is now recommended for Data Access • EF is now open-source software • Next version of Entity Framework is a complete rewrite 

What Can The Data Professional Do? • Identify common problems the Entity Framework applications have. • Work together with developers to find and address problems. • Educate developers on how SQL Server works. 

Common Entity Framework Problems • Database Generation • N + 1 Problem • Murder on the Index Express • Searching • Implicit Conversions • Query Complexity 

Database Generation • EF can generate databases. • EF Migrations can even generate database updates and roll- forward or rollback. • I don’t recommend blindly generating any database from any ORM. 

Demo Database Generation 

Database Generation • Things to look for in a ORM generated DB • Normalization • Relationships (They may not exist but should) • Proper Types (NVARCHAR / BIGINT) • Indexes on Foreign Keys • Primary Keys • Alternate Keys • Table Naming Convention (Singular vs. Plural) • Object Naming (such as Keys and Indexes) 

N + 1 Problem Occurs when object have a 1 : M relationship. Store Customer 

Demo N + 1 Problem 

N + 1 Fixes • Use the Includes feature in LINQ queries • Use a Stored Procedure 

Murder on the Index Express • By default, the a LINQ query will return ALL attributes in an object. • Essentially, it’s performing a SELECT * against the table. • Key Lookup City. • To fix use LINQ projections. 

Demo Murder on the Index Express 

Searching • Using the CONTAINS function in a LINQ query creates a LIKE clause. • LIKE has been know to decimate performance. • We can fix this by using a Full Text Search 

Demo Searching 

Implicit Conversions • This problem has been mostly fixed in EF 4.1+. • But you can still run into problems with Code First. 

Demo Implicit Conversions 

Query Complexity • Complex LINQ queries can generate some awful SQL. • Cut your losses and replace the LINQ query with a SQL one. 

Demo Query Examples 

Other Tips • Use a Caching Layer • NoSQL (Redis, Elasticsearch, Solr, etc.) • ASP.NET has a caching library built in. • Use a EF Profiler • Hibernating Rhinos • MiniProfiler 

Rewind • Entity Framework is used by developers to simplify access to a database. • Entity Framework concepts are a bit tricky and the grammar is important. • Look for the common issues. • Work along side developers to resolve issues. 

The More You Know • Julie Lerman’s Blog - http://thedatafarm.com/blog • Rowan Miller’s Blog - http://romiller.com • Arthur Vicker’s Blog - http://blog.oneunicorn.com • ADO.NET Blog - http://blogs.msdn.com/b/adonet • #efhelp on twitter • StackOverflow • PluralSite – Julie Lerman’s EF videos 

Contact Richie Rump @Jorriss http://jorriss.net http://awayfromthekeyboard.com http://dotnetmiami.com http://brentozar.com 

Thank You!