The ADO.NET E n tity Framework: Tips & Tricks

2. The ADO.NET Entity Framework: Tips & Tricks Shyam Pather (spather@microsoft.com) Development Manager Microsoft Session Code: DTL312

3. Julie Lerman made these slides Mentor/Consultant jlerman@thedatafarm.com thedatafarm.com/blog LearnEntityFramework.com

4. 10 Tips & Tricks • Solving Pain Points • Foreign Keys: Reading • : Writing • : Set Default Value • Improve Inefficient Store Queries • Use Random Results from Stored Procedures • Use ObjectQuery methods with L2E • Using EF More Effectively • Create Generic Queries • Find Exception Handling Details • Encapsulate Query Execution • Improve Query Performance

5. EF Model Hides Foreign Key !

6. 1. Get FK Value When No Reference Object Is Available

7. 2. Set Reference without Ref Object from Single EntityKey Property Order.CustomerReference.EntityKey = new EntityKey("MyEntities.Customers", "PersonID", 1) from Multiple EntityKey Properties vareKeyValues = new KeyValuePair<string, object>[] { new KeyValuePair<string, object>(“PropertyA", 12), new KeyValuePair<string, object>(“PropertyB", 103) }; EntityKey ekey= new EntityKey(“MyEntities", eKeyValues); instance.EntityRef.EntityKey=ekey;

8. 3. Set Default Entity Reference Value Create Constructor in Entity's Partial Class C# Public Customer() { CustomerTypeReference.EntityKey = new EntityKey("MyEntities.CustomerTypes", “TypeID", 1) } • VB • Public Sub New() • CustomerTypeReference.EntityKey = • New EntityKey("MyEntities.CustomerTypes", “TypeID", 1) • End Sub

9. Undesirable Store Queries LINQ to Entities Query From p In context.PeopleWhere p.LastName.StartsWith(“K") • Resulting T-SQL Query • SELECT [Extent1].[PersonID] AS [PersonID], ... • WHERE (CAST(CHARINDEX(N'T', Extent1].[LastName]) • AS int)) = 1 !

10. 4. Control Store Query w/ ESQL Entity SQL SELECT VALUE p FROM EFEntities.People AS p WHERE p.LastName LIKE “T%” Query Builder Methodscontext.People.Where("it.LastName LIKE 'T%'") • Resulting T-SQL • SELECT [Extent1].[PersonID] AS [PersonID], • WHERE [Extent1].[LastName] LIKE 'T%'

11. Bonus Tip! Testing ESQL Expressions eSql Blast! code.msdn.com/adonetefx

12. Stored Procs Return Non-Entity • Requires lots of manual editing of the model, MSL and SSDL to create an entity that matches the return value CREATE PROCEDURE OrdersBySalesPersonbyDate AS SELECT MIN(Person.FirstName) as First, MIN(Person.LastName) as Last, COUNT(Orderid) as OrderCount FROM … !

13. 5. Use Views in Place of Sprocs CREATE VIEW OrdersBySalesPersonbyDate AS SELECT MIN(Person.FirstName) as First, MIN(Person.LastName) as Last, COUNT(Orderid) as OrderCount FROM … • Wizard creates all entity metadata elements • Other benefits • View is composable • Use in queries • Change Tracking (use Stored Procs for DML)

14. ObjectQuery Special Methods in L2E Include from p in context.Orders.Include(“Customer”) select p OfType from p in context.People.OfType<Customer> select p MergeOption context.People.MergeOption=MergeOption.NoTracking; from p in context.People select p; Execute ??? ToTraceString ??? !

15. 6. Cast L2E to Get ObjectQuery Methods • C# • var l2eQuery=from c in context.Customers select c ; • varstoreSQL=((ObjectQuery)l2eQuery).ToTraceString(); • VB • Dim l2eQuery=From c In context.Customers Select c • Dim storeSQL = CType(l2eQuery, ObjectQuery).ToTraceString

16. Redundant L2E Queries Country Reference List List<Country> GetCountries() { return context.Countries.OrderBy(c => c.Name) .ToList(); } Account Type Reference List List<AccountType> GetAccountTypes() { return context.AccountTypes.OrderBy(a => a.TypeName) .ToList(); } Product Reference List List<Product> GetProducts() { return context.Products.OrderBy(p => p. Name) .ToList(); } !

17. 7. Build Reusable, Generic Queries public static List<TEntity> GetReferenceList<TEntity> (this ObjectContext context) context.GetReferenceList<Country> context.GetReferenceList(Of AccountType) context.GetReferenceList<Product>

18. UpdateExceptions Anonymity s Order B ? Person A Person B Order A db Error ? Person D Order C ? Order D Person C !

19. 8. ObjectStateEntry & Entity Instance Are Provided in UpdateExceptions

20. Redundant Code around Queries var query=from p in context.People select p; try { var people=query.ToList(); } catch (EntityException) { ... } Catch (CommandExecutionException) { ... } var query2=from o in context.Orders select o; try { var orders =query2.ToList(); } catch (EntityException) { ... } Catch (CommandExecutionException) { ... } !

21. 9. Encapsulate Query Execution Reusable Repository Methods public List<TEntity> ExecuteList<TEntity> (ObjectQuery<TEntity> objectQuery) public List<TEntity> ExecuteList<TEntity> (IQueryable<TEntity> L2EQuery) Calling Reusable Methods IEnumerable<Person> L2EQuery = from p in context.People select p; List<Person> people = dal.ExecuteList<Person>(L2EQuery); ObjectQuery oQuery = context.Orders; List<Order> orderList = dal.ExecuteList<Order>(oQuery);

22. Bonus Tip! Pre-Compile L2E Queries PreCompiled Query LINQ to Entities Query Store Query

23. Web Sites Lose Pre-Compiled L2E Queries • Short-lived ObjectContext used to compile • Pre-Compilation is repeated each time • App process loses benefit of pre-compilation • Big performance loss !

24. 10. Cache Pre-Compiled Query Func Class Level Static Function static Func<MyEntities, IQueryable<Customer>> customerPCQ; Class Constructor public CustomerProvider() { if (customerPCQ == null) { customerPCQ = CompiledQuery.Compile<MyEntities, IQueryable<Customer>> (ctx => from cust in ctx.Customers where cust.Orders.Any() select cust ); } }

25. EF4 Removes Some of the Pain • Solving Pain Points • Foreign Keys: Reading • : Writing • : Set Default Value • Improve Inefficient Store Queries • Use Random Results from Stored Procedures • Use ObjectQuery methods with L2E • Using EF More Effectively • Create Generic Queries • Find Exception Handling Details • Encapsulate Query Execution • Improve Query Performance

26. Julie Lerman's Contact Info Again Julie Lerman jlerman@thedatafarm.com thedatafarm.com/blog LearnEntityFramework.com

27. My Contact Info spather@microsoft.com

28. question & answer

29. Required Slide Speakers, TechEd 2009 is not producing a DVD. Please announce that attendees can access session recordings from Tech-Ed website. These will only be available after the event. Resources Tech·Ed Africa 2009 sessions will be made available for download the week after the event from: www.tech-ed.co.za • www.microsoft.com/teched International Content & Community • www.microsoft.com/learning • Microsoft Certification & Training Resources • http://microsoft.com/technet • Resources for IT Professionals • http://microsoft.com/msdn Resources for Developers

30. Required Slide Speakers, please list the Breakout Sessions, TLC Interactive Theaters and Labs that are related to your session. Any queries, please check with your Track Owner. Related Content Sessions: DTL402: The ADO.NET Entity Framework 4 DTL208: An Introduction to the ADO.NET Data Services Framework v1.5 DTL401: How LINQ Works: A Deep Dive into the Microsoft Visual Basic and C# Implementations Whiteboard Sessions: WTB211: A Strategic Comparison of Data Access Technologies from Microsoft Hotlabs: DTL20H: Application Development with the ADO.NET Entity Framework in the Microsoft .NET Framework 4

31. Required Slide Track Owners to provide guidance. Please address any queries to your track owners. Track Resources ADO.NET Team Blog: http://blogs.msdn.com/adonet/ Entity Framework Design Blog: http://blogs.msdn.com/efdesign/ Daniel Simmons’s Blog: http://blogs.msdn.com/dsimmons/ Julie Lerman’s Blog: http://thedatafarm.com/blog/ • Visual Studio Data Blog: http://blogs.msdn.com/vsdata/

32. Required Slide 10 pairs of MP3 sunglasses to be won Complete a session evaluation and enter to win!

33. Required Slide © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.