ADO.NET Entity Framework

1. ADO.NET Entity Framework  José A. Blakeley Partner Architect Microsoft Corporation  Michael Pizzo Principal Architect Microsoft Corporation

2. ADO.NET A to Z: Agenda 10-12:00 Section 1: Evolution of ADO.NET 12:00-1:15 <Lunch> 1:15-2:30 Section 2: ADO.NET and LINQ 2:30-2:45 <Break> 2:45-4:00 Section 3: ADO.NET Entity Framework Deep Dive 4:00-4:15 <Break> 4:15-5:45 Section 4: Building on ADO.NET

3. Section 1: Evolution of ADO.NET • Evolution of Data Access APIs • Proprietary APIs • Object Databases • Standard SQL Data Access APIs • ODBC, DAO, RDO • Componentized Data Access • OLE DB, ADO • .NET Data Access • ADO.NET • ADO.NET 1.0 • Building a Data Platform • The ADO.NET Entity Framework

4. Proprietary APIs – 70’s & early 80’s • Application-specific, monolithic data access • Files, hierarchical DBMSs • CICS, IMS, etc. • Relational DBMSs emerged • DBMS-specific APIs and protocols • OCI, DBLIB, SQL*, TDS, DRDA • Embedded SQL • 1xN data access problem • Each app writes data-source-neutral data access layer

5. SQL APIs – Late 80’s & early 90’s • ODBC • Common C API for accessing SQL Databases • Driver Manager loads DBMS-specific drivers • Developed with SQL Access Group, ANSI/ISO • Adopted as SQL-CLI Addendum to SQL92 • DAO • "OLE Automation" interface to Microsoft Access • Provided Access, VB developers access to ODBC sources • RDO • DAO-style interfaces directly over ODBC • Subsumed by DAO "ODBC Direct" mode

6. Object Databases – Mid 90’s • Transparent Language Persistence • C++, Lisp, SmallTalk • Transaction support - low throughput • Very transparent persistence • Fast navigational performance • Object graph loaded to memory • Most navigation in memory • Object faulting based on memory mapped IO and pointer swizzling • Limited query support • ODMG OQL standard • No efficient implementations • Limited indexing support • Limited customer acceptance

7. Component Data Access – Late 90’s • OLE DB • COM-Based Data Access API • Relational and non-relational sources • Tables, hierarchies, OLAP, documents • Factored interfaces • Providers: SQL, ISAM, Excel, AS400 FS, Exchange • Services: cursor engine, shaping, remoting, resource pooling, transaction enlistment • Used by SQL Server internally and externally • SQL Engine, Federated Query, Integration Services,… • ADO • "OLE Automation" interface for OLE DB • Interface for VB developers • Similar programming model to DAO • Introduced disconnected RecordSets

8. ADO.NET 1.0 – Early 00’s • Need a managed API for the new .NET Framework • Consistency across framework • Explicit separation of connected access from disconnected • Connected Model • Lower-level, common API for exposing the functionality native to a data store • Higher level abstractions, common functionality built on top • Dataset as “in-memory data cache" • Database independent • Predictable semantics, latency, error handling,… • Combine data from multiple sources • Support for XML

9. Section 1: Evolution of ADO.NET • Evolution of Data Access APIs • ADO.NET 1.0 • Architecture • Getting Data from a SQL Database • Working with Data • ADO.NET and XML • Building a Data Platform • The ADO.NET Entity Framework

10. ADO.NET 1.0 Architecture Controls,Designers,Code-gen, etc DataSet XmlReader OLTP operations,Programmatic Processing,Frameworks XmlWriter Managed Provider DataReader DataAdapter Command Connection

11. Connection CreateCommand() Command Parameters Parameters Parameters ExecuteReader() DataReader Data Provider Data store Getting Data From a SQL DatabaseADO.NET Data Provider • Specific to a particular DBMS • Directly exposes consumer interfaces • No more COM/Automation dichotomy • ADO.NET DataProviderObject Model • Connection • Establishes connection to DataSource • Transaction • Explicit Transaction Control • Command • Execute SQL statement • DataReader • Forward-only, Read-Only Result Stream • Fields accessed through strongly typed, indexed accessors

12. DataSet Tables Table Columns Column Constraints Constraint Rows Row Relations Relation Working with DataDataSet • Relational View of Data • Tables, Columns, Rows, Constraints, Relations • Directly create metadata and insert data • Explicit Disconnected Model • Disconnected, remotable object • No knowledge of data source or properties • Common Behavior • Predictable performance characteristics • Array-like indexing • Strong Typing

13. DataSet Scenarios • Application Data • RemotingResults • SOAP, WebMethods, Remoting • Caching Results • ASP.NET Cache • Persisting results • Save Data as XML, Schema as XSD • User interaction • Scrolling, sorting, filtering • DataView, DataViewManager • Binding Windows controls

14. Mappings Mappings Mappings DataAdapter DataSet Fill() Update() SelectCommand • Loads a table from a data store and writes changes back. • Exposes two methods: • Fill(DataSet,DataTable) • Update(DataSet,DataTable) • Provides mappings between tables & columns • User provides insert/update/delete commands • Allows use of Stored Procedures • CommandBuilder component available • Allows single DataSet to be populated from multiple different datasources InsertCommand UpdateCommand DeleteCommand DataAdapter Data store

15. ADO.NET and XML • The DataSet • Loads/saves XML data into/out of DataSet • Schema can be loaded/saved as XSD • Schema can be inferred from XML Data • The DataSet can be associated with an XmlDataDocument • Exposes a relational view over structured XML • According to the DataSet schema • Allows strong typing, control binding, relational access of XML data • Allows XML tools (schema validation, XSL/T, XPath queries) against relational data • Preserves full fidelity of XML Document

16. demo ADO.NET 1.0

17. Section 1: Evolution of ADO.NET • Evolution of Data Access APIs • ADO.NET 1.0 • Building a Data Platform • Why a Conceptual Model? • The Microsoft Entity Data Model • Entity SQL • The ADO.NET Entity Framework

18. The ProblemProgramming Data is Hard • Writing queries is difficult • No help from compiler • Results are untyped rectangular records • Database Schemas optimized for storage concerns • Relational Tables contain flat, homogenous records • Implicit Logic Embedded in Application • Brittle, Hard to maintain • Lack of common syntax across relational databases

19. The OpportunityIncrease Developer Productivity • Rapid Development • Strongly typed queries • Strongly typed results with Business Logic • Lower TCO • Work with an explicit data model • Types, Inheritance, Relationships, Complex Properties,… • Decouple application from storage schema • Better Portability • Common query language across disparate sources

20. Where’s Your Data Model? • Applications Today… • Implicitly Contain the Data Model • Logic and Model Intertwined • Conceptual Mismatch • Often encapsulate in a "Data Access Layer"

21. Where’s Your Data Model? • Applications Today… • Implicitly Contain the Data Model • Logic and Model Intertwined • Conceptual Mismatch • Often encapsulate in a "Data Access Layer" • The Need… • Applications work with a well Defined Model • Storage Schema Abstraction • Declarative mapping between application and storage models • No brittle, hard-coded mapping

22. The Microsoft Entity Data Model • An extended relational model with Entity-Relationship Model concepts • Entity Types • Strong type with Identity • Inheritance • Scalar/Complex properties • EntitySets • Hold instances of Entity Types • Similar to relational tables • Can have multiple Entitysets of the same EntityTypes • Relationships ("Associations") • Named relationships between Entities • 0..1:*, 0..1:0..1, 1:1, 1:M, M:N • Navigation may be exposed as NavigationProperties on EntityTypes • AssociationSets • Contains instances of associations • May be queried directly • EntityContainers • Contains EntitySets, AssociationSets SalesPerson EmployeeID = 294272LoginID = adamTitle = "Dev Lead"VacationHours = 0… Manager 1 1 N Reports SalesPerson SalesPerson EmployeeID = 729742LoginID = peteTitle = "Developer"VacationHours = 0…ExpenseAccount = …CarLicenseNum = …… SalesPerson EmployeeID = 729742LoginID = peteTitle = "Developer"VacationHours = 0…ExpenseAccount = …CarLicenseNum = …… EmployeeID = 729742LoginID = peteTitle = "Developer"VacationHours = 0…ExpenseAccount = true…

23. EntitySQL SQL extended to support the Entity Model • SELECT Value • Promotes a single column to the record • Entity construction • Useful in DefiningQueries • Type Operations • OFTYPE, TREAT,… • Relationship Navigation • Navigate(), . • Manipulation of Keys • Key(), Ref()/DeRef(), CreateRef, Row() • Other • Set()/Flatten() • CrossApply, OuterApply • Skip/Limit

24. Section 1: Evolution of ADO.NET • Data Access in the 80s • ADO.NET 1.0 • Building a Data Platform • ADO.NET Entity Framework Introduction • Overview • Entity Designer • EntityClient • Object Services

25. ADO.NET Entity Framework

26. Section 1: Evolution of ADO.NET • Data Access in the 80s • ADO.NET 1.0 • Building a Data Platform • The ADO.NET Entity Framework • Overview • Entity Designer • EntityClient • Object Services

27. Entity Designer • Define / Modify Conceptual Entity Model • Define Entities, Associations, Functions • Create from scratch or Reverse-Engineer from database • Define mapping to Relational Store • Generate/Refresh storage schema definition from Database • Generate CLR Objects for Entity Model • Partial classes • Generate runtime metadata • .csdl, .msl, .ssdl files or embed as resources

28. Entity Designer V1 Limitations • No support for: • "Model-First" • Complex Types • Abstract Types • Multiple Entity Sets per Type • Practical limitation of ~120 Entities • No design support for SSDL • Must manually edit XML • Refresh overwrites manual changes to SSDL • Lack of Automatic Pluralization support • Modify EntitySet names in model

29. demo Entity Designer

30. Section 1: Evolution of ADO.NET • Data Access in the 80s • ADO.NET 1.0 • Building a Data Platform • The ADO.NET Entity Framework • Overview • Entity Designer • EntityClient • Object Services

31. EntityClient • …is an ADO.NET Data Provider • EntityConnection • Metadata/Mapping specified in ConnectionString • Can also load from XmlReader • EntityCommand • Canonical EntitySQL syntax • Stored Procedure Support • Access to Provider-specific SQL • EntityDataReader • Rectangular results described through IDataReader • Typing, Polymorphism described through IExtendedDataReader • Limitations • No DML, DDL • Call Functions in SSDL • No DataAdapter

32. demo EntityClient

33. Section 1: Evolution of ADO.NET • Data Access in the 80s • ADO.NET 1.0 • Building a Data Platform • The ADO.NET Entity Framework • Overview • Entity Designer • EntityClient • Object Services

34. Object Services • Provides core object service abstractions • Context, identity, state management • Query, object materialization • ObjectContext, ObjectQuery<T>, ObjectStateManager • Tools generate partial classes from EDM models • Strongly typed collections • Navigational patterns, relationship fix up • Users can extend the partial classes to add business logic • Provides query and update interfaces • LINQ integration, Entity SQL • Strongly typed CRUD operations (add, delete, savechanges)

35. demo Object Services

36. Questions?

38. Section 2: ADO.NET and LINQ • Introduction to LINQ • LINQ to SQL • LINQ to Entities • LINQ to DataSet

39. Introduction to LINQ • Queries as first-class concept in .NET languages • Builds on several language features • Type inference, Delegates, Generics • Enabled by • Lambda expressions • Anonymous types • Object initialization expressions • Extension methods • Query expressions

40. Introduction to LINQ • // Lambda Expressions • string[] names = { "Luis", "Mary", "Mike", "Jose" }; • Display( names, s => s.Length > 3); • // Anonymous Types and object initialization • varemp = new { Name = "Mary", Company = "Microsoft", Age = 30 }; • // Extension Methods • public static class ExtensionMethods { • public static void Display<T>(this T[] names, • Func<T, bool> filter) { • foreach (T s in names) { • if (filter(s)) Console.WriteLine(s); • } • } • } • // Query Expressions • var query = from c in Customers • where c.Discount >= 3.0 && c.Discount < 4.0 • select new { c.Name, Perc = c.Discount / 100.0 };

41. Section 2: ADO.NET and LINQ • Introduction to LINQ • LINQ to SQL • Design Points • Features • Demo • LINQ to Entities • LINQ to DataSet

42. LINQ to SQLDirect Mapping • Direct Mapping • Each class maps to a single SQL Schema Object • Table, View • Stored Procedure, Table Valued Function • Simple renaming of Tables, Columns • Foreign Keys can be expressed as Relationships • Properties to navigate in query, results • Inheritance • Multiple Classes in a Hierarchy can map to a single Table/View/Stored Proc/TVF with a discriminator column

43. LINQ to SQLStrongly typed SQL Database • Design Points • Rapid Development against SQL Database • Direct Mapping to SQL Server Schema • Mappings expressed in Attributes or XML file • "Just Work" for common scenarios • Execute when needed • Naming Conventions • Business Logic • Custom Insert/Update/Delete operations • Minimally Intrusive object model • Provide Customization, Optimizations where required • Targets: Microsoft SQL Server

44. LINQ to SQLFeatures • Customization • Business Logic • Partial classes for generated Objects • Add Methods, non-persistent members, etc. • Business Logic through Partial methods based on naming conventions • Update Logic • Implement partial methods in derived Class • Call Stored Procedures or invoke custom logic • Optimizations • Loading Options • "Span" related information • ObjectTrackingEnabled • DeferredLoadingEnabled • Compiled Query • Save overhead of SQL generation from Language Expression • LinqDataSource for use in ASP.NET

45. demo LINQ to SQL

46. Section 2: ADO.NET and LINQ • Introduction to LINQ • LINQ to SQL • LINQ to Entities • Design Points • Features • Demo • LINQ to DataSet

47. LINQ to EntitiesFlexible Mapping to Relational Data • Design Points • Flexible Mapping to Existing Relational Schema • Well defined Conceptual model • Share common model across products (Reporting, Analysis, etc…) • Declarative Mapping between Application and Store • Allows Storage Schema and Application to evolve independently • Explicit Operations • Server interactions should be explicit • Build implicit logic on top of explicit operations • Targets: Microsoft SQL Server and third-party databases

48. LINQ to EntitiesFeatures • Customization • Business Logic • Partial Classes, Events, Partial Methods • Update Logic • Generated Update Views • Declarative stored procedures • Optimizations • "Span" related members • NoTracking • Extensibility • Partitioning of Metadata • Flexible Runtime Mapping • Metadata Pluggability • EntityDataSource for use from ASP.NET

49. demo LINQ to Entities

50. Section 2: ADO.NET and LINQ • Introduction to LINQ • LINQ to SQL • LINQ to Entities • LINQ to DataSet