Entity Framework for Data Professionals

Entity Framework and other Object Relational Mapping technologies have been a boon for developers but how do they really affect the database? More importantly is Entity Framework a technology that developers should even be using? In this session, we'll review what Entity Framework is and how it's changed over time. We'll also dive into how Entity Framework works and what to look for when inspecting a database generated by Entity Framework. Finally, we'll review T-SQL generated by Entity Framework and give some tips on how to improve performance.

Transcript

1. Richie Rump Jorriss LLC @Jorriss www.jorriss.net
2. Richie Rump Jorriss LLC @Jorriss www.jorriss.net
6. • What is Entity Framework? • How does Entity Framework work? • Why do developers like it? • What should you, the Data Professional, be looking for in an Entity Framework project.
7. Photo Credit: http://www.patrickfallonphoto.com/2008/11/04/election-2008-barack-obamas-election-night-grant-park/
8. Photo Credit: http://www.sacbee.com/static/weblogs/photos/2008/08/014666.html
9. Photo Credit: http://triggerpit.com/2010/11/22/incredible-pics-nasa-astronaut-wheelock/
10. Photo Credit: http://www.nydailynews.com/sports/football/giants/eli-manning-making-quarterback-article-1.1013353
12. • Object Relational Mapping • Converts pragmatic objects into a relational database. – Hibernate (Java) – Active Record (Ruby) Objects Mapping Data
13. • 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.
14. 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(); }
15. var stores = context.Stores;
18. Lovingly stolen from Programming Entity Framework: Code First by Julie Lerman and Rowan Miller page 9.
19. Design Centric Code Centric New Database Existing Database Model First Create .edmx model in designer Generate DB from .edmx Classes auto-generate from .edmx Database First Reverse engineer .edmx model Classes auto-generate from .edmx Code First Define classes & mapping in code Database auto-created at runtime Code First Define classes & mapping in code Adapted from Programming Entity Framework: Code First by Julie Learman and Rowan Miller page 3.
20. • v1.0 – .NET 3.5 • 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 • v7.0 – Alpha, New Devices, New Data Stores
21. • Developer works with objects. • Focus on Business Domain (objects) not DB, Connections, commands, etc. • Developer Productivity • No need to write SQL or CRUD commands
22. • 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
23. • Database Generation • N + 1 Problem • Murder on the Index Express • Searching • Implicit Conversions • Caching
24. • 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
25. • Things to look for in a ORM generated DB – Normalization – Relationships (They may not exist but should) – Proper Types (NVarchar / BigInt) – Indexes on FKs – Primary Keys – Table Naming Convention (Singular vs. Plural) – Object Naming (such as Keys and Indexes)
26. • Occurs when object have a 1 : M relationship. Store Customer
27. • Use the Includes feature • Use a Stored Procedure
28. • 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
29. • 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
30. • This problem has been mostly fixed in EF 4.1+. • But you can still run into problems with Code First. • DEMO
31. • Complex LINQ queries can generate some awful SQL. • Cut your losses and replace the LINQ query with a SQL one.
32. • Use a Caching Layer – NoSQL – ASP.Net has a caching library built in. • Use a EF Profiler – Hibernating Rhinos – MiniProfiler
34. • Julie Lerman’s Blog http://thedatafarm.com/blog/ • Rowan Miller’s Blog http://romiller.com • Arthur Vicker’s Blog http://blog.oneunicorn.com • #efhelp on twitter • StackOverflow (of course) • PluralSite – Julie Lerman’s EF videos
35. Richie Rump @Jorriss http://jorriss.net http://slideshare.net/jorriss http://dotnetmiami.com