Next Generation Data Access: ADO'Net Entity Framework

1
Next Generation Data AccessADO.Net Entity
Framework

• Guy Burstein
• Senior Consultant
• Advantech Microsoft Division
• http//blogs.microsoft.co.il/blogs/bursteg

2
Agenda
Working with Database Schemas
A Conceptual View of Data
Querying the Conceptual Model
Working with .NET objects
Language-integrated query in ADO.NET
Summary
3
Data Modeling

• Every business application has, explicitly or
  implicitly, a conceptual data model
• Is the Database Schema Ideal for us?

4
Working with Database Schemas (1)

• Get all full-time employees that were hired
  during 2006 and list their names and titles

SELECT c.FirstName, e.Title FROM Employee
e INNER JOIN Contact c ON e.EmployeeID
c.ContactID WHERE e.HireDate gt '2006-01-01 AND
e.SalariedFlag 1
Create Appropriate View using JOIN
View only Subset of Data Explicitly
5
Working with Database Schemas (2)

• Obtain all of the sales persons that have sales
  orders for more than 200,000

SELECT SalesPersonID, FirstName, LastName,
HireDate FROM SalesPerson sp INNER JOIN Employee
e ON sp.SalesPersonID e.EmployeeID INNER JOIN
Contact c ON e.EmployeeID c.ContactID INNER
JOIN SalesOrder o ON sp.SalesPersonID
o.SalesPersonID WHERE o.TotalDue gt 200000 AND
e.SalariedFlag 1
What is a Sales Person actually?
Very Complicated !
Schema is too fragmented
Cannot leverage Relationships
6
The Reality of Schemas

• Database schemas are not always ideal
• Operational or performance reasons
• New applications on top of existing schemas
• Not the same set of constructs and abstractions
• Relations, Inheritance
• Schemas tend to permeate through code
• Apps creates a view of data that makes sense
• Using sprocs, views and most often ad-hoc queries
• We have implicit knowledge in the code

7
ADO.Net Entity Framework
Introducing

• Not included in .Net Framework 3.0
• Will ship with Visual Studio Orcas

8
ADO.NET Entity Framework Themes
Conceptual Models Separate apps from the
reality of schemas
Data modeling
Interact with data as objects or as rows and
columns, your choice
Better Integration
LINQ support throughout the ADO.NET stack
Programming languages
9
ADO.NET and Evolution

• Almost every app is a data-centric app
• Big investment in ADO.NET
• Know-how, application code, extensions, etc.
• Evolve ADO.NET while preserving investments
• Widen capabilities of the existing stack when
  possible
• Build layers on top of existing ones when
  appropriate

10
ADO.Net Client Views

• Each app has an appropriate view of data
• Different apps can have different views
• Views entirely implemented on the client
• Avoid polluting DB schema with per-app views
• No added maintenance to the database
• Powerful
• Very broad set of views that are updatable
• Updates work across tables, across relationships
  
• Updatability can be statically verified

11
Moving to the Conceptual Model
12
The Entity Data Model (EDM)

• An Entity-relationship model
• Key Concepts
• Entity Type is a structured record with a key
• An Entity is an instance of an Entity Type
• Entities are grouped in Entity-Sets
• Entity types can inherit from other entity types
• The EDM model is effectively executable
• Not just to stick to the wall -)

13
Conceptual Model
1
0..
14
Mapping between the Models
Generate From Database
Create Entity Data Model
15
Entity Data Model
16
Map Provider

• Client views are surfaced as an ADO.NET provider
• MapConnection, MapCommand etc.
• Connect to a virtual store with the conceptual
  schema
• Map provider will in turn connect to the real
  store
• Using a regular ADO.NET provider
• Once connected, execute queries as usual

Mapping provider
StoreProviders(e.g. SqlClient)
Store (e.g. SQL Server)
17
Using the Map Provider
18
Querying Against an EDM Schema

• Native database query language wont work
• The schema the application sees is unknown to
  it
• EDM allows for more constructs
• Entity SQL
• Store-independent
• Explicit support for entities
• Relationship traversal
• Inheritance-related constructs

Entity SQL
Mapping provider
StoreProviders(e.g. SqlClient)
Store (e.g. SQL Server)
19
Entity SQL
20
Objects Versus Rows/Columns

• No need for deep philosophy
• Objects are typically easier for heavy logic
• If youre doing reporting, integration, etc.
  rows/columns tend to be a better fit
• It may also be just a matter of taste
• ADO.NET and Objects
• Objects as a presentation choice
• Thin layer on top of the rows / columns stack
• Shape of object model follows shape of EDM
• ? no need for heavy object mapping

21
ADO.NET Object Layer

• EDM schema as objects
• Tools generate partial classes from a schema
• Single entry point ObjectContext
• Takes mapping and schema information
• Contains a connection to the underlying store

ObjectServices
Mapping provider
StoreProviders(e.g. SqlClient)
Store (e.g. SQL Server)
22
Queries
Entity SQL

• Querying in the object layer
• Just use E-SQL
• Query in terms of the object model
• QueryltTgt represents a query

ObjectServices
Mapping provider
StoreProviders(e.g. SqlClient)
Store (e.g. SQL Server)
23
Updates, Transactions

• Objects returned from queries can be changed in
  memory
• Call ObjectContext.SaveChanges() to push the
  changes to the store
• ADO.NET does optimistic concurrency checks
• The set of updates is wrapped in a transaction
• Transactions
• Fully integrated with System.Transactions
• Explicit transaction API available too

24
Querying the Object Layer
25
Language-Integrated Query
No intellisence
AdventureWorks db new AdventureWorks()
QueryltSalesPersongt newSalesPeople
db.GetQueryltSalesPersongt(_at_ SELECT VALUE
sp FROM SalesPeople AS sp WHERE
sp.HireDate gt _at_Date", new
QueryParameter(Date", hireDate))   foreach(Sales
Person p in newSalesPeople)
Console.WriteLine("0\t1", p.FirstName,
p.LastName)
No compile time checks
Loosely bound arguments
26
Language-Integrated Query
27
LINQ to Entities
Entity SQL
LINQ

• Maintain clean semantics and avoid unexpected
  results
• Queries are always translated into SQL and sent
  to the engine

ObjectContext
Object Services
QueryltTgt
MapConnection
MapCommand
Mapping provider
MapDataReader

Connection
Store Providers (e.g. SqlClient)
Command
DataReader
Store(e.g. SQL Server)
28
LINQ To Entities
29
LINQ to DataSet

• Queries can be formulated against DataTables
• Support the standard query operators
• Joins, filters, grouping, etc.
• Both regular and typed DataSets work
• Much better experience with typed-DataSets
• DataSet-specific extensions
• ToDataTable() Turn query into a DataTable
• LoadSequence() load a table from LINQ query

30
LINQ To Datasets
31
Not Just an ORM Tool
Entity Relational Mapping
Model at a higher level of abstraction
Modeling
Objects / Rows Columns as a presentation choice
Results
Rich query available through libraries and
languages
Queries
32
Future Directions

• Entity Mapper - Visual Mapping Tool
• Entity SQL DML Support
• Windows Communication Foundation
• EntitySet Preview
• ObjectContext.RejectChanges()
• Tables with Self References
• Data Types Xml, Money, Text
• Abstract Entities
• Spans to pre-fetch data

33
(No Transcript)
34
Summary

• Database Efficiency and Domain Simplicity
• Working at a higher level of abstraction with
  Conceptual Schemas
• Present data as objects or rows/columns
• Rich query available through libraries and
  languages
• Continued evolution path for ADO.NET
• Preserve current and future investments on the
  technology
• Download and send Feedback!

35
Additional Resources
36
Guy Burstein Senior Consultant Advantech
Microsoft Division http//blogs.microsoft.co.il/bl
ogs/bursteg