real-world entity framework
DESCRIPTION
deck from our talk at DevTeachTRANSCRIPT
Real-world Entity Framework
Llewellyn Falco – A Developer’s DeveloperLynn Langit – A DBA and more
Agenda
QueryQuery
SaveSave
PatternsPatterns
What is Entity Framework 4?
Object Relational Mapper (ORM)•Included in .NET 4.0•Developers write .NET code
• using LINQ • generate database access
code
DEMO - startING with EFGenerating a Model
What did we just do?
• Created a new project in Visual Studio• Added a new ADO.NET Entity Model • Generated a model from a database• Reviewed the generated model
diagram• Created an ObjectContext
Programmatically access data by…
ADO.NET
• Custom DAO• T-SQL
Entity Framework
• Complete DAO• LINQ
Agenda
QueryQuery
Real-World Problem #1 - Report
• Report for customer ‘Lynn’• Sent orders
# 54, #63, #57
• Partially sent orders #82: awaiting shoes, pants #92: awaiting gloves
• Unshipped orders #93 6 items #97 5 items
The Report T-SQL
Code DemoCurrent Practices- Life without Entity Framework
Code DemoReport with Entity Framework
Using EF – Writing a Query using LINQ
Compare Extracting Data
Mapping Relationships
CustomerIdName
CustomerIdName
Id Name Id Number Total Customerid
ADO.Net
InvoiceIdNumberTotalCustomerId
InvoiceIdNumberTotalCustomerId
CustomerIdName
CustomerIdName
InvoiceIdNumberTotalCustomerId
InvoiceIdNumberTotalCustomerId
EF
Code Demo - ContinuedReport with Entity Framework
Smart Objects
InvoiceIsShippedIsUnshippedIsPartiallyShiped
InvoiceIsShippedIsUnshippedIsPartiallyShiped
CustomerGetShippedGetUnshippedGetPartiallyShiped
CustomerGetShippedGetUnshippedGetPartiallyShiped
Looking at the T-SQL Generated by EF
• Use SQL Profiler• Create a trace• Filter for
Application:• EntityFramework
The N+1 Problem
1 + 1 + 5 = 7Customer Customers
InvoicesInvoices
ItemsTrips to
Database
More about Loading – Why Lazy Loading
• Lazy is Good and Easy, but
Lazy isn’t always efficient Lazy is always correct Sometimes better to use selective eager loading
• Syntax – use .Include("invoices.invoiceItems”)• More about – lazy loading - here
Code DemoConstrained Eager Loading
Agenda
SaveSave
Real-world Problem #2 – CRUD operations
WarehouseReport
Process Shipment
Load / Create Customer
Load / Create Invoice
Load / Create Invoice Item
Action
Remove Item
Set Item Shipped
ShippedCancelled
Code Demo
Warehouse
Processing
Using EF – Adding Data using LINQ
With Entity Framework…
• Simplified/ Reduced data access code Save Encapsulates
Delete Insert Update Key relationship Order of operations Transactions
• Prevents Most SQL Injection Sanitizes database inputs
SQL Injection and Entity Framework
“Little Bobby Tables”
Agenda
Patterns
Data Usage Patterns Load Model
Save Model
Manipulate Model
CommonPreferred
Code DemoModularity
Making Entity Framework Testable
Old Way
DoSomething()1)Get Data2)Manipulate Data3)Render Data
DoSomething()1)Get Data2)Manipulate Data3)Render Data
DoSomething()1)Load Data2)DoSomething(Data)
DoSomething()1)Load Data2)DoSomething(Data)
DoSomething(Data)1)Manipulate Data2)Render Data
DoSomething(Data)1)Manipulate Data2)Render Data
Load()1)Get DataLoad()1)Get Data
New Way - Testable
Code DemoTesting Loaders
MyProject.ControllerMyProject.Controller
MyProject.BusinessMyProject.Business
MyProject.ViewsMyProject.Views
MyProject.RulesMyProject.Rules
MyProject.ProcessesMyProject.Processes
Common Usage Pattern
Load Model
Save Model
Manipulate Model
Common Usage Pattern
MyProject.ControllerMyProject.Controller
MyProject.BusinessMyProject.Business
MyProject.ViewsMyProject.Views
MyProject.RulesMyProject.Rules
MyProject.ProcessesMyProject.ProcessesMyProject.LoadersMyProject.Loaders
Load Model
Save Model
Manipulate Model
Stored Procedures
• Security• Reduced Access• Performance• Modularity• Containment• Maintainability
Mapping Stored Procedures to EF Models
ORM to DB should be 1-1
• Complex mappings obscure the database
?=
Existing Database Problems are
Amplified
• Primary Keys• Wide Tables• Bad Names• Dead Columns
“Next Steps” for Attendees
• Use Entity Framework Write less CRUD code Protect against SQL Injection Master LINQ
• Use patterns / best practices with EF i.e. Loader pattern Modularity for testability (mocking)
• Map EF as closely as possible to the database Map 1-to-1 Fix design issues at the database level first
More about learning LINQ – using Koans
• 28 progressive examples to help you learn LINQ• LinqKoans.codeplex.com
Resources for Attendees
• EF team blog – here
• Julie Lehrman book - here
• Testing EF – video coming soon
On ApprovalTests YouTube playlist - here
www.ApprovalTests.com
Related Session(s)
http://www.devteach.com/Schedule.aspx •SQL361 - Query Tuning Tips for SQL Server
TeachingKidsProgramming.org
Do a Recipe Teach a Kid (Ages 10 ++)SmallBasic or Java Free Courseware (recipes)
Contact Information
@LlewellynFalco@LlewellynFalcowww.ApprovalTests.comwww.ApprovalTests.com