Who's Afraid of Entity Framework

Do any applications in your environment use SQL written not by a person but by the application itself? No, the system isn't self-aware. Chances are it is using the Microsoft Entity Framework (EF).

EF and other object relational mapping technologies have been a boon for developers. But is EF a technology that developers should even be using? This session reviews what EF is and how it's changed over time. We'll also dive into how it works and what to look for when inspecting a database generated by EF Finally, we'll review EF-generated T-SQL and give some tips on how to improve performance. If you're a data professional who manages databases that are accessed through EF or want a basic knowledge of how EF works, this session is for you.

Transcript

1. Who’s Afraid of Entity Framework? Richie Rump, Dataveloper
2. Who Am I?
3. My Clique
4. 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.
5. What is an ORM? • Object Relational Mapping • Converts pragmatic objects into a relational database. • Hibernate (Java) • Active Record (Ruby) Objects Mapping Data
6. 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.
7. 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
8. var stores = context.Stores; Now With EF
9. How to Use EF: Designer
10. How to use EF: Code
11. 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
12. 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.
13. 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)
14. 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
15. 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
16. 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.
17. Common Entity Framework Problems • Database Generation • N + 1 Problem • Murder on the Index Express • Searching • Implicit Conversions • Query Complexity
18. 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.
19. Demo Database Generation
20. 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)
21. N + 1 Problem Occurs when object have a 1 : M relationship. Store Customer
22. Demo N + 1 Problem
23. N + 1 Fixes • Use the Includes feature in LINQ queries • Use a Stored Procedure
24. 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.
25. Demo Murder on the Index Express
26. 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
27. Demo Searching
28. Implicit Conversions • This problem has been mostly fixed in EF 4.1+. • But you can still run into problems with Code First.
29. Demo Implicit Conversions
30. Query Complexity • Complex LINQ queries can generate some awful SQL. • Cut your losses and replace the LINQ query with a SQL one.
31. Demo Query Examples
32. 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
33. 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.
34. 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
35. Contact Richie Rump @Jorriss http://jorriss.net http://awayfromthekeyboard.com http://dotnetmiami.com http://brentozar.com
36. Thank You!