ADO.NET vNext The Entity Data Model, Object Services, and LINQ

1. ADO.NET vNextThe Entity Data Model, Object Services, and LINQ Bob Beauchemin Director of Developer Resources, SQLskills http://www.SQLskills.com/blogs/bobb

2. About Bob Beauchemin • Independent Consultant/Trainer/Writer/Speaker • Director of Developer Resources, SQLskills • Website: http://www.SQLskills.com • Blog: http://www.SQLskills.com/blogs/bobb • SQL Server MVP • Author of books and numerous resources related to SQL Server and data access • A Developer's Guide to SQL Server 2005 • A First Look at SQL Server 2005 for Developers • “Best Book” SQL Server Magazine’s Reader’s Choice Awards (2005) • Essential ADO.NET

3. Overview • What IS in vNext? • The Entity Data Model • Entity Provider • Entity SQL • Object Services • LINQ for Entities • LINQ for SQL • LINQ for DataSet

4. What is ADO.NET vNext? • A part of Visual Studio Orcas release • In .NET Framework 3.5 • Changes to languages • VB 9.0 • C# 3.0 • CTP available now

5. What IS in ADO.NET vNext? • A new data model • Entity Data Model • Three XML Schemas for defining and mapping • CSDL - conceptual data language (DDL for EDM) • SSDL - store-specific data language • MSL - mapping data language • A set of Object Services • An ADO.NET data provider (EntityProvider) • Hooks for provider writers • EDM not restricted to SQL Server

6. What IS in Orcas? • Two new data access languages • LINQ • Entity SQL (ESQL) • Implementations for databases • LINQ to SQL • LINQ to Entities • ...and the ADO.NET DataSet • LINQ to DataSet

7. Entity Data Model • Applications model in terms of entities • EDM is a conceptual model that includes • Structural inheritance • Composition • Relationships as first class types • EDM is not a 1-1 match of the relational model • Entities != Rows • Collections != Tables

8. Views of Data • Views of data are layered • Lowest level - physical storage • Filegroups • Exact layout of rows on disk • Index structure • Relational apps unaware of this level • Logical level • Tables, Keys, Column Constraints • PK/FK constructs, "Join tables" • Relational modeling works at this level • Conceptual level • Entities and relationships • EDM modeling works at this level

9. Uses of the Conceptual Level • Existing tools work at conceptual level • Report Builder - SMDL files • Replication - Transactional replication groups • UDM - Data Source Views in Analysis Services • Each tool uses a slightly different model • EDM could be used to consolidate models

10. Entity Data Model • Borrows from Relational and E-R models • Borrows from UML and object models • Concepts • Entity Types and Entity Sets • Associations and Association Sets • Complex types • Structural Inheritance • Collections • Entity SQL to query against EDM sets

11. EDM Description Language • EDM is a model • Not a physical implementation • EDM is described in CSDL • Conceptual Schema Definition Language • An XML-based grammar • Defines model in terms of • Complex types made up of simple types • Inheritance • Relationships • EDM model is mapped to RDBMS • SSDL - Describes relational database • MDL - Maps

12. Entity SQL Language • Entity SQL - query language for EDM • Basic relational query concepts + • SQL 1999 query concepts + • Retrieval as tuples or types • Polymorphic queries • Support for relationships • Entity SQL is a textual query language • Query text not programming language specific • No compile-time checking or IntelliSence • Currently query-only • Update through Object Services • Update through ESQL in future

13. Object Services • Object Services provides services for EDM • Fetch semantics • Lazy loading option • Change tracking • Identity management • Updatability • Transaction support • Object Services uses ESQL to query • Updates through change tracking layer • Stored procedures and functions supported • Object Services built over ADO.NET provider

14. EntityProvider • ADO.NET provider for EDM • "Native store" is EDM • "Native language" is ESQL • Provider support useful for • Integration with existing tools • Familiarity for ADO.NET programmers

15. Wither LINQ? • LINQ is language integrated query • Strong typing • IntelliSense • Anonymous types that represent projections • LINQ enables query over • Object collections • XML (LINQ to XML) • Relational Databases (LINQ to SQL) • ADO.NET DataSet (LINQ to DataSet) • EDM (LINQ to Entities)

16. LINQ and ESQL • Why are there two query languages? • LINQ is programming language based • multiple types of data as input • can be extended to new input types • uses LINQ to SQL for database programming • ESQL is data model based • EDM-specific • an extension to the SQL language • update DML is planned • integration with DB-centric tools planned • can use Object Services for database programming • Or ... • You can use LINQ to Entities for database programming

17. LINQ to SQL and EDM • LINQ to SQL allows • Object model based on relational model • One class per table • Relationships (1-1, 1-many, many-many) • Inheritance not related to persistence • Stored procedures or LINQ Services for inserts, updates, and deletes • EDM mapping allows • Entity model independent of relational model • Structural inheritance and composition • Relationships as first-class types • Stored procedures on Object Services for inserts, updates, and deletes • Neither currently supports SQLCLR UDTs

18. Object Services • EDM is the only "data store" • ESQL is the only programming language • ADO.NET provider writers can include support for EDM in providers

19. LINQ Services • LINQ Services useable with • LINQ to Entities • LINQ to SQL • LINQ to DataSet • XML data • Model is similar for all types • IQueryable<T> • Operations can be expanded with user-defined constructs in code

20. LINQ Tool Support • In Visual Studio Orcas Mar CTP • Add LINQ to SQL file (dbml) • Can drag tables from Server Explorer • Can drag class definitions from Solution Explorer • Can add properties, associations, inheritance to the model • Can configure insert/update/delete procedures • Generates classes in project • LINQ - database table DDL generation from dbml • No relationships or constraints in generated DDL • SQLMetal command line tool for LINQ

21. EDM Tool Support • In Visual Studio Orcas Mar CTP • Add ADO.NET Entity Model • Can add tables from the database • Generates CSDL, SSDL, MSL • Generates classes in project • Can customize using schema-guided XML editor • EDMGen command line tool for EDM

22. Summary • ADO.NET vNext added support for EDM • ADO.NET provider • Object Services • ESQL and LINQ for Entities • ADO.NET includes layered approach • Rich object model • Coexistence with familiar ADO.NET model • with extensions for entities • Object Services for insert/update/delete, transactions • LINQ for SQL includes straightforward relational mapping • Tight integration with programming languages • LINQ for DataSets allows queries over DataSet

24. Resources • The LINQ project • http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx • ADO.NET - Making the Conceptual Level Real • http://msdn2.microsoft.com/en-us/library/aa730866(VS.80).aspx • Visual Studio Orcas Forums • http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=153&SiteID=1 • ADO.NET Team Blog • http://blogs.msdn.com/adonet • Data Blog • http://blogs.msdn.com/data • Dinesh's Cyberstation • http://blogs.msdn.com/dinesh.kulkarni • mikechampion's weblog • http://blogs.msdn.com/mikechampion • Oakleaf Systems • http://oakleafblog.blogspot.com • Panopticon Central • http://www.panopticoncentral.net • Paul Gielens Thoughts Service • http://weblogs.asp.net/pgielens • System.Data.Objects dev guy • http://blogs.msdn.com/dsimmons • Tim Mallalieu's Blog • http://blogs.msdn.com/timmall • Bob Beauchemin's Blog • http://www.SQLskills.com/blogs/bob

26. ADO.NET vNextThe Entity Data Model, Object Services, and LINQ Bob Beauchemin Director of Developer Resources, SQLskills http://www.SQLskills.com/blogs/bobb