ado net entity framework l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
ADO.NET Entity Framework PowerPoint Presentation
Download Presentation
ADO.NET Entity Framework

Loading in 2 Seconds...

play fullscreen
1 / 114

ADO.NET Entity Framework - PowerPoint PPT Presentation


  • 703 Views
  • Uploaded on

ADO.NET Entity Framework.  José A. Blakeley Partner Architect Microsoft Corporation.  Michael Pizzo Principal Architect Microsoft Corporation. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'ADO.NET Entity Framework' - salena


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ado net entity framework

ADO.NET Entity Framework

 José A. Blakeley

Partner Architect

Microsoft Corporation

 Michael Pizzo

Principal Architect

Microsoft Corporation

ado net a to z agenda
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

section 1 evolution of ado net
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
proprietary apis 70 s early 80 s
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
sql apis late 80 s early 90 s
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
object databases mid 90 s
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
component data access late 90 s
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
ado net 1 0 early 00 s
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
section 1 evolution of ado net9
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
ado net 1 0 architecture
ADO.NET 1.0 Architecture

Controls,Designers,Code-gen, etc

DataSet

XmlReader

OLTP operations,Programmatic Processing,Frameworks

XmlWriter

Managed Provider

DataReader

DataAdapter

Command

Connection

getting data from a sql database ado net data provider

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
working with data dataset

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
dataset scenarios
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
dataadapter

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

ado net and xml
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
section 1 evolution of ado net17
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
the problem programming data is hard
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
the opportunity increase developer productivity
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
where s your data model
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"
where s your data model21
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
the microsoft entity data model
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…

entitysql
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
section 1 evolution of ado net24
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
section 1 evolution of ado net26
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
entity designer
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
entity designer v1 limitations
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
section 1 evolution of ado net30
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
entityclient
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
section 1 evolution of ado net33
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
object services
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)
section 2 ado net and linq
Section 2: ADO.NET and LINQ
  • Introduction to LINQ
  • LINQ to SQL
  • LINQ to Entities
  • LINQ to DataSet
introduction to linq
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
introduction to linq40
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 };
section 2 ado net and linq41
Section 2: ADO.NET and LINQ
  • Introduction to LINQ
  • LINQ to SQL
    • Design Points
    • Features
    • Demo
  • LINQ to Entities
  • LINQ to DataSet
linq to sql direct mapping
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
linq to sql strongly typed sql database
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
linq to sql features
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
section 2 ado net and linq46
Section 2: ADO.NET and LINQ
  • Introduction to LINQ
  • LINQ to SQL
  • LINQ to Entities
    • Design Points
    • Features
    • Demo
  • LINQ to DataSet
linq to entities flexible mapping to relational data
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
linq to entities features
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
section 2 ado net and linq50
Section 2: ADO.NET and LINQ
  • Introduction to LINQ
  • LINQ to SQL
  • LINQ to Entities
  • LINQ to DataSet
linq to dataset linq over disconnected cache with change tracking
LINQ to DataSetLINQ over Disconnected Cache with Change Tracking
  • Disconnected Cache
    • Offline/Remote
    • Data Aggregation
    • Application Data

All with Change Tracking

  • Queryable
    • Filter, Projection
    • Joins
      • Across Tables
      • Other in-Memory sources
    • Local expressions

All through Common LINQ syntax

linq to dataset typed and untyped
LINQ to DataSetTyped and UnTyped
  • UntypedDataSet
    • Call AsEnumerable() on DataTable
    • Reference Fields by Name
      • Use Field<T>(columnName)
    • Project out fields for strongly typed result

varquery = from rowin myDataSet.Tables["Customers"].AsEnumerable()

where row .Field<string>("City") =="London"

select new { row.Field <string> ("CustomerID"),

row.Field <string> ("ContactName") } ;

  • Typed DataSet
    • Use strongly typed accessors

varquery = from customerin northwind.Customers

where customer.City== "London"

select customer;

summary ado net and linq
Summary: ADO.NET and LINQ
  • LINQ to SQL

Strongly Typed Database

    • Emphasis on rapid application development
    • Direct mapping to Microsoft SQL Server family of databases
  • LINQ to Entities

Flexible mapping to existing Schema

    • Focus on enterprise-grade data scenarios
    • Flexible Mapping to Microsoft SQL Server and third-party databases
  • LINQ to DataSet

In-Memory Cache w/Change Tracking

    • All the scenarios where DataSet is useful today
      • Offline, Disconnected, Aggregation
      • Change Tracking

..Plus support for Query Operations

    • Strongly typed or UntypedDataSet Support
section 3 ado net entity framework deep dive
Section 3: ADO.NET Entity Framework Deep Dive
  • Customizing Data Classes
  • Entity Framework Mapping Scenarios
  • Database Design Considerations
  • Advanced Mapping Techniques
customizing data classes
Customizing Data Classes
  • Partial Classes
    • Add business logic, methods, non-persisted properties to partial classes
  • Codegen Customization
    • Add custom logic to Property, Type Generation
      • Attributes, Interfaces, Members, BaseClass,…
      • Getters, Setters, BackingField, Return Type,…
  • Custom Classes
    • Write your own classes that
      • Derive from EntityObject, or
      • Implement interfaces:
        • IEntityWithKey, IEntityWithChangeTracker, IEntityWithRelationships
section 3 entity framework deep dive
Section 3: Entity Framework Deep Dive
  • Customizing Data Classes
  • Entity Framework Mapping Scenarios
    • Core Mapping Scenarios
    • Function Mapping
    • Mapping Limitations
  • Database Design Considerations
  • Advanced Mapping Techniques
core mapping scenarios
Core Mapping Scenarios
  • EntitySets map to Table(s)
    • EntityTypes, Properties can be renamed
    • A single EntitySet may be mapped to multiple tables
    • Different EntityTypes in a hierarchy can be mapped to:
      • Different columns within a table or different tables
    • An EntitySet may be mapped to a subset of the rows within a table
  • AssociationSets map to tables with foreign keys
    • M:N relationships map to join table
    • 0..1 : * relationships may be "co-located"
    • Relationships may map to/from subtypes in a hierarchy
    • Can have "Owning" relationships
function mapping
Function Mapping
  • Functions exposed through Function Imports
    • Parameters may be mapped
    • Stored Procedures can return different results
  • Mapping CUD operations to Functions
    • Insert, Update, Delete operations
core mapping limitations
Core Mapping Limitations
  • Must use a common EntityKey throughout type hierarchy
  • Relationships must be based on EntityKey
    • EntityKey can't contain reference
  • Function results must be mapped to EntityTypes
  • Can't Map multiple entities to different columns of a single row
  • If used, modification functions must include all CUD operations on EntitySetand References
  • Can't map Multiple Results from a Stored Proc
  • No Support for Composable TVFs in V1
database design considerations
Database Design Considerations
  • Use a common PK…
    • For all tables in a Hierarchy
    • When splitting information across tables
    • For Relationships
  • Hierarchy Models
    • Single Table per Hierarchy generally results in simpler queries (fewer joins)
    • Separate table for each subtype when most queries are against a single Subtype
    • Separate table for additional subtype columns When most queries are against supertype
section 3 entity framework deep dive65
Section 3: Entity Framework Deep Dive
  • Customizing Data Classes
  • Entity Framework Mapping Scenarios
  • Database Design Considerations
  • Advanced Mapping Techniques
    • Anatomy of an .edmx file
    • Complex Types
    • Custom Mapping
anatomy of an edmx file
Anatomy of an .edmx File
  • Conceptual Schema Definition (CSDL)
    • Container
      • EntitySets
      • AssociationSets
    • Entity Types
    • Association Types
  • Storage Schema Definition (SSDL)
    • Same Structure as CSDL
  • Mapping Definition (MSDL)
    • Mapping for EntitySets, AssociationSets
conceptual schema definition csdl
Conceptual Schema Definition (CSDL)

<SchemaNamespace="AdventureWorksModel"Alias="Self"xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

<EntityContainerName="AdventureWorksEntities">

<EntitySetName="Contacts"

EntityType="AdventureWorksModel.Contact" />

<AssociationSetName="ManagerEmployees"

Association="AdventureWorksModel.ManagerEmployee">

<EndRole="Employees"EntitySet="Contacts" />

<EndRole="Manager"EntitySet="Contacts" />

</AssociationSet>

</EntityContainer>

<EntityTypeName="Contact">

<Key>

<PropertyRefName="ContactID" />

</Key>

<PropertyName="ContactID"Type="Int32"Nullable="false" />

<PropertyName="Title"Type="String" />

<PropertyName="FirstName"Type="String"Nullable="false" />

<PropertyName="LastName"Type="String"Nullable="false" />

</EntityType>

<AssociationName="ManagerEmployee">

<EndRole="Employees"

Type="AdventureWorksModel.Employee"Multiplicity="*" />

<EndRole="Manager"

Type="AdventureWorksModel.Employee"Multiplicity="0..1" />

</Association>

</Schema>

storage schema definition ssdl
Storage Schema Definition (SSDL)

<SchemaNamespace="AdventureWorksModel.Store"Alias="Self"Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"

xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

<EntityContainerName="HumanResources">

<EntitySetName="Contact"

EntityType="AdventureWorksModel.Store.Contact"Schema="Person" />

<AssociationSetName="FK_Employee_Employee_ContactID"

Association="AdventureWorksModel.Store.FK_Employee_Employee_ContactID">

<EndRole="Employees"EntitySet="Employee" />

<EndRole="Manager"EntitySet="Employee" />

</AssociationSet>

</EntityContainer>

<EntityTypeName="Contact">

<Key>

<PropertyRefName="ContactID" />

</Key>

<PropertyName="ContactID"Type="int"Nullable="false"StoreGeneratedPattern="Identity" />

<PropertyName="Title"Type="nvarchar" />

<PropertyName="FirstName"Type="nvarchar"Nullable="false" />

<PropertyName="LastName"Type="nvarchar"Nullable="false" />

</EntityType>

<AssociationName="FK_Employee_Employee_ContactID">

<EndRole="Employees"Type="AdventureWorksModel.Store.Employee"Multiplicity="*" />

<EndRole="Manager"Type="AdventureWorksModel.Store.Employee"Multiplicity="0..1" />

</Association>

</Schema>

mapping definition msl
Mapping Definition (MSL)

<MappingSpace="C-S"xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">

<EntityContainerMappingStorageEntityContainer="HumanResources"

CdmEntityContainer="AdventureWorksEntities">

<EntitySetMappingName="Contacts"

TypeName="AdventureWorksModel.Contact"StoreEntitySet="Contact">

<ScalarPropertyName="ContactID"ColumnName="ContactID" />

<ScalarPropertyName="Title"ColumnName="Title" />

<ScalarPropertyName="FirstName"ColumnName="FirstName" />

<ScalarPropertyName="MiddleName"ColumnName="MiddleName" />

<ScalarPropertyName="LastName"ColumnName="LastName" />

</EntitySetMapping>

<AssociationSetMappingName="ManagerEmployees"

TypeName="AdventureWorksModel.ManagerEmployee"StoreEntitySet="Employee">

<EndPropertyName="Employees">

<ScalarPropertyName="ContactID"ColumnName="ContactID" />

</EndProperty>

<EndPropertyName="Manager">

<ScalarPropertyName="ContactID"ColumnName="ManagerID" />

</EndProperty>

<ConditionColumnName="ManagerID"IsNull="false" />

</AssociationSetMapping>

</EntityContainerMapping>

</Mapping>

section 3 entity framework deep dive70
Section 3: Entity Framework Deep Dive
  • Customizing Data Classes
  • Entity Framework Mapping Scenarios
  • Database Design Considerations
  • Advanced Mapping Techniques
    • Anatomy of an .edmx file
    • Complex Types
    • Custom Mapping
complex types
Complex Types
  • <ComplexTypeName ="FullName">
  • <PropertyName="Title"Type="String" />
  • <PropertyName="FirstName"Type="String"Nullable="false" />
  • <PropertyName="MiddleName"Type="String" />
  • <PropertyName="LastName"Type="String"Nullable="false" />
  • </ComplexType>
  • Define your Complex Type in CSDL
  • Use your ComplexType in your Entities

<EntityTypeName="Contact">

<Key>

<PropertyRefName="ContactID" />

</Key>

<PropertyName="ContactID"Type="Int32"Nullable="false" />

<PropertyName="Name"Type="Self.FullName"Nullable="false"/>

<!-- … -->

</EntityType>

  • Map the Complex Type in your MSL

<EntitySetMappingName="Contacts"TypeName="AdventureWorksModel.Contact"

StoreEntitySet="Contact">

<ScalarPropertyName="ContactID"ColumnName="ContactID" />

<ComplexPropertyName="Name">

<ScalarPropertyName="Title"ColumnName="Title" />

<ScalarPropertyName="FirstName"ColumnName="FirstName" />

<ScalarPropertyName="LastName"ColumnName="LastName" />

</ComplexProperty>

</EntitySetMapping>

section 3 entity framework deep dive72
Section 3: Entity Framework Deep Dive
  • Entity Framework Mapping Scenarios
  • Database Design Considerations
  • Customizing Data Classes
  • Advanced Mapping Techniques
    • Anatomy of an .edmx file
    • Complex Types
    • Custom Mapping
customize conceptual definition
Customize Conceptual Definition
  • Multiple EntitySets of the same EntityType

Specify the same EntityType in multiple EntitySet definitions

    • Useful for partitioned data
  • Split Model Across Files

Reference other CSDL, MSL using <Using/>

    • Common SSDL across Referenced MSL files
    • Useful for:
      • Dealing with large models
      • Partitioning model between users
customize mapping definition
Customize Mapping Definition
  • Specify Custom Mapping to exposed tables

Specify the EntitySQL that describes the EntitySet mapping using <QueryView/>

    • Use ModificationFunctionMappings to update
    • Useful for:
      • Getting around mapping limitations
        • Different Keys in Hierarchy
        • Relationships on non-Key fields
        • Splitting an Entity between tables with different keys
customize the storage view
Customize the Storage View
  • In-Line Function Definitions:

Expose arbitrary command as a function using <CommandText/>

    • Useful for:
      • Defining client side functions
      • Defining ModificationFunctionMappings
  • Change the columns used as Keys
    • Useful for hierarchies without a common key
  • Change the underlying table:

Specify table using the store:Table attribute

    • Useful for exposing the same table as multiple targets in SSDL
customize the storage view76
Customize the Storage View
  • In-Line Table Definitions

Expose a native SQL command as a virtual table using <DefiningQuery/>

    • Use ModificationFunctionMappings to update
    • Useful for:
      • Ultimate escape hatch for mapping to storage constructs
      • Utilizing provider-specific commands
      • Composing parameterless TVFs
      • Adding query hints
      • Provider-specific datatypes

Tip: After modifying SSDL, don’t refresh from Database; instead add to new model and Copy/Paste

section 4 building on ado net
Section 4: Building on ADO.NET
  • Data Access Across Tiers
    • Entity Framework Remoting
    • Entity DataSourceControl
    • ADO.NET Data Services
  • ADO.NET and SQL Server
  • Futures
  • Summary: ADO.NET A to Z
entity framework remoting
Entity Framework Remoting
  • Generated Entities are DataContractSerializable
    • Graph Serialization for current values
  • Track Changes on Client
    • For Optimistic Updates Send original and current
    • For “Last Writer Wins” just send current
    • Use EntityReference to set related Key Values
  • Apply changes on server
    • Inserts:
      • Call ObjectContext.AddObject()
    • Updates:
      • Attach (or query for) Original Entity
      • Call ObjectContext.ApplyPropertyChanges()
    • Deletes:
      • Attach (or query for) Original Entity
      • Call ObjectContext.DeleteObject()
    • Call SaveChanges() when done
entitydatasource control
EntityDataSource Control
  • Create an ASP.NET Web Application
  • Define or reference your Entity Data Model
  • Add an EntityDataSource Control
  • Configure the EntityDataSource Control
    • Set NamedConnection and DefaultContainer
    • Set EntitySetName, select properties to display
    • Specify Filter, Ordering, Grouping, Span, TypeFilter
      • Include Parameters from:
        • Controls, Cookies, QueryString, Form, Session
  • Specify support for Insert, Update, Delete
  • Bind DataSourceControl to grid, dropdown,…
ado net data services
ADO.NET Data Services
  • ADO.NET Data Service
    • Publish your Data Model as a REST-based Web Service
    • Supports pluggable DataServiceProviders
      • EntityFramework, LINQ , Custom DataServiceProviders
  • Protocol
    • Model-based Contract
    • Uniform Interface
      • REST-style URI-based addressing
      • CRUD using standard HTTP verbs
    • Format Independence: Results as ATOM, JSON
    • Shared with SQL Server Data Services, LIVE, …
  • Extensibility
    • Query Interceptors
    • Service Operations

Data Services for the Web

ado net data services86
ADO.NET Data Services
  • Consuming Data Services
    • Any HTTP client
      • From Any Browser
      • Specify URLs for query, insert, update, delete
    • LINQ Enabled Silverlight & .NET Framework Client Library
      • Build queries using LINQ
      • Results as generated or hand-coded objects
      • Save changes back to Service
    • Future
      • ASP.NET Data Source Control
      • AJAX Client Library (CodePlex)

Data Services for the Web

section 4 building on ado net88
Section 4: Building on ADO.NET
  • Data Access Across Tiers
  • ADO.NET and SQL Server
    • New data Types
      • Table Valued Parameters
      • New Date Types
      • Spatial: Geography, Geometry
      • Hierarchy Id
    • Asynchronous Operations
    • MultipleActiveResultSets
    • SqlBulkCopy
    • SqlNotifications
    • SQL Server Functions callable from EF
  • Futures
new sql server 2008 data types
New SQL Server 2008 Data Types
  • Table-valued Parameters
  • Date Types
    • Date, Time, DateTimeOfsset, DateTime2
  • Spatial: Geometry and Geography
  • HierarchyId
  • Sparse Columns
  • File Stream
table valued parameters tvp
Table-valued Parameters (TVP)
  • Input parameters of Table type on SPs/Functions
  • Optimized to scale and perform better for large data
  • Behaves like BCP in server
  • Simple programming model
  • Strongly typed
  • Reduce client/server round trips
  • Do not cause a statement to recompile

CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT);

CREATE PROCEDURE myProc(@tvpmyTableType READONLY) AS

UPDATE Inventory SET

qty += s.qty

FROM Inventory AS i INNER JOIN @tvp AS tvp

ON i.id = tvp.id

GO

table valued parameters tvp tvp client stack support
Table-valued Parameters (TVP)TVP Client Stack Support

Fully supported in ADO.Net3

New Parameter type: SqlDbType.Structured

Parameters can be passed in multiple ways

DataTable

IEnumerable<SqlDataRecord> (fully streamed)

DbDataReader

table valued parameters ado net example using datatable
Table-valued ParametersADO.NET Example using DataTable
  • Using (MyConnection){
  • //Create a data table
  • DataTabledt = new DataTable(“TVPOrdersDataTable”);
  • dt.Columns.Add(“ProductType”, typeof(string));
  • dt.Columns.Add(“Quantity”, typeof(int));
  • // Add rows
  • dt.Rows.Add(“Canon Digital Camera”, 20);
  • dt.Rows.Add(“June”, 10);
  • dt.Rows.Add(“Xbox-360”, 8);
  • // Create a command and bind parameter
  • SqlCommandtvp_cmd = new SqlCommand(“sp_UpdataInventory”, MyConnection);
  • SqlParametertvpParam = tvp_cmd.Parameters.AddWithValue(
  • @OrdersTvp, dt);
  • //Execute command
  • tvp_cmd.ExecuteNonQuery();
new datetimeoffset
New DateTimeOffset
  • SqlCommand command =
  • new SqlCommand(string.Empty, sqlConnection);
  • command.CommandText = "insert into MoviesRented values(@customerId, @MovieID, @RentalDate,
  • @DueDate)";
  • ….
  • // create a parameter for RentalDate
  • SqlParameterrentDateParam = new SqlParameter("RentDate", System.Data.SqlDbType.DateTimeOffset);
  • rentDateParam.Value = DateTimeOffset.Now;
  • command.Parameters.Add(rentDateParam);
  • // create a parameter for DueDate
  • SqlParameterdueDateParam = new SqlParameter("DueDate", System.Data.SqlDbType.DateTimeOffset);
  • dueDateParam.Value = DateTimeOffset.Now.AddDays(7);
  • command.Parameters.Add(dueDateParam);
  • ….
new datetimeoffset95
New DateTimeOffset
  • // create a command to get the DueDate
  • SqlCommand command =
  • new SqlCommand(String.Empty, sqlConnection);
  • command.CommandText =
  • "select DueDate from MoviesRented where MovieId = @MovieId";
  • // Execute the DataReader
  • //
  • using (SqlDataReaderdataReader = command.ExecuteReader())
  • {
  • if (dataReader.Read() == false)
  • {
  • Console.WriteLine("Movie has not been rented");
  • }
  • DateTimeOffsetdueDate =
  • dataReader.GetDateTimeOffset (0);
  • Console.WriteLine("Movie due back on : {0}", dueDate);
  • }
asynchronous operations
Asynchronous Operations
  • SqlClientprovides true async I/O
    • Opening connections
    • Executing commands
  • Client applications don’t block UI
  • Server applications execute multiple requests without blocking threads
  • Consistent with .NET async pattern
    • BeginXXX and EndXXX methods
    • Use IAsyncResult to poll or block
    • …Or use a callback
      • Callback is called in a different thread
asynchronous example
Asynchronous Example

// Poll for completion

IAsyncResult result = cmd.BeginExecuteReader();

while(!result.IsCompleted) {

// do some work

}

SqlDataReader reader = cmd.EndExecuteReader(result);

// Use a Callback

IAsyncResult result = cmd.ExecuteReader(

new AsyncCallback( myDataCallback ));

// do other work…

// optionally wait using sync object

result.WaitHandle.WaitOne();

public void myDataCallback( IAsyncResult result ) {

SqlDataReader reader = cmd.EndExecuteReader(result);

}

multiple active resultsets
Multiple Active ResultSets
  • Connection String attribute:

MultipleActiveResultSets=True

  • Enables multiple results per connection
  • Performance and scalability gain
    • Multiple sessions per physical connection
    • Reduction in client and server resources usage
    • Avoids pooling/connection initialization logic
    • Combine with async for maximum perf
  • Single result per command
  • Available against SQL 2005 and newer
notifications
Notifications
  • Deliver a message to a queue when a change occurs that would affect results
    • Rows in the result changed/deleted
    • New rows added that would have been in the result
  • Client specifies message body and delivery queue
    • Any authorized client can check for notifications
      • Poll or blocking query
  • Use SqlDependency for ease of use
  • Use NotificationContext to automatically enlist any commands w/in context
    • Enables notification w/LINQ to SQL and LINQ to Entities
notification example
Notification Example

public SqlDataReaderGetProducts(int Category) {

SqlCommandcmd = new SqlCommand(

"Select ProductName, UnitPrice from Products " +

"where CategoryID = @CatID", cnn);

cmd.Parameters.Add("@CatID",Category);

cmd.Notification = new SqlNotificationRequest(

Category.ToString(), // message

"myQueue", // message body

3000); // timeout

return cmd.Execute();

}

public void WaitForChanges() {

SqlCommandcmd = new SqlCommand(

"Receive message_body from myQueue " +

"WITH wait_for_results", cnn);

cmd.CommandTimeout = 0;

int category = (int)cmd.ExecuteScalar();

Console.WriteLine("Category {0} changed.",category);

}

bulk copy
Bulk-Copy
  • High-perf bulk load
  • Load Data from several data sources
    • IDataReader, DataTable, DataRow[]
  • No support for BCP files and BCP out

public void LoadFromDataReader(IDataReader reader)

{

// Copy the Data to SqlServer

SqlBulkCopybcp =

new SqlBulkCopy( connectString );

bcp.DestinationTableName = "Customers";

bcp.WriteToServer( reader );

}

user defined types
User-Defined Types
  • SqlClient natively supports UDTs
    • UDTs are CLR types
      • Data readers return instances of UDTs
      • Parameters can be instances of UDTs
    • SQLOLEDB also provides UDT support
      • Using COM/interop
  • Assembly location
    • Application can use strong typed references to local assembly
      • Methods execute on client
    • Assembly downloaded if needed
      • Must be granted download priviledge on assembly
      • Security warning on download
      • Class is limited by security policy, application domain permissions
      • Access objects through reflection
udt example
UDT Example

SqlConnectioncnn = new SqlConnection(connectString);

cnn.Open();

SqlCommandcmd =

new SqlCommand("SELECT p FROM PointTable", cnn );

SqlDataReader reader = cmd.ExecuteReader();

while( reader.Read() )

{

Point point=(Point)reader[0];

Console.WriteLine(

"x:{0}, y:{1}", point.x, point.y );

}

cnn.Close();

sql server functions in entiysql
SQL Server Functions in EntiySQL

String Functions

ASCII

CHAR

CHARINDEX

DIFFERENCE

LEFT

LEN

LOWER

LTRIM

nchar

PATINDEX

QUOTENAME

REPLACE

REPLICATE

REVERSE

RIGHT

RTRIM

SOUNDEX

SPACE

STR

STUFF

SUBSTRING

UNICODE

UPPER

System Functions

DATALENGTH

CHECKSUM

NEWID

CURRENT_TIMESTAMP

CURRENT_USER

HOST_NAME

USER_NAME

ISNUMERIC

ISDATE

Aggregates

AVG

CHECKSUM_AGG

COUNT

COUNT_BIG

MAX

MIN

STDEV

STDEVP

VAR

VARP

Math Functions

ABS

ACOS

ASIN

ATAN

ATN2

CEILING

COS

COT

DEGREES

EXP

FLOOR

LOG

LOG10

PI

POWER

RADIANS

RAND

ROUND

SIGN

SIN

SQRT

SQUARE

TAN

Date Functions

DATEADD

DATEDIFF

DATENAME

DATEPART

DAY

GETDATE

SYSDATETIME

SYSUTCDATETIME

SYSDATETIMEOFFSET

GETUTCDATE

MONTH

YEAR

section 4 building on ado net106
Section 4: Building on ADO.NET
  • Data Access Across Tiers
  • ADO.NET and SQL Server
  • Futures
ado net entity framework futures
ADO.NET Entity Framework Futures
  • Tool Support
    • Model-First
    • Support for Larger Models
    • Complex Types
    • Exensibility
  • Data Class Support
    • Pure POCO
    • Templates
  • Improved Mapping Support
    • Composable TVFs
  • New Entity Services
    • Synchronization, Offline
    • SSRS integration
summary ado net data access components
Summary: ADO.NET Data Access Components
  • DataSet
    • Queryable, remoteable In-memory relational collection
  • DBMS-Specific ADO.NET Data Provider
    • Direct access to native store syntax, types
  • EntityClient
    • Canonical Ad hoc queries with Flexible Mapping
  • LINQ to SQL
    • Strongly typed Microsoft SQL Server schema
    • LinqDataSource for use in ASP.NET
summary ado net data access components109
SummaryADO.NET Data Access Components
  • ObjectServices
    • Strongly typed Entity Model with Flexible Mapping
    • LINQ to Entities
      • Strongly typed Queries
    • EntitySQL
      • Complex Queries, Ad-hoc Queries, Provider-specific Functions
  • EntityDatasource
    • Simple ASP.NET Integration
  • ADO.NET Data Services
    • Standard, Robust, REST-based service
    • Easy to use, Highly Extensible
    • .NET Client for simple programming
summary ado net entity framework
Summary: ADO.NET Entity Framework
  • ADO.NET Entity Framework V1 delivers…
    • Applications work in terms of a rich Conceptual Model
      • Strongly typed LINQ queries
      • Ad-hoc ESQL queries
      • UntypedEntityClient
    • Flexible mapping to existing storage schemas, DBMSs
    • Tool support for generating model, mapping
  • ADO.NET Entity Framework promises…
    • More Mapping Flexibility
    • Richer Entity Model Concepts
    • Less Intrusive Persistence Logic
    • Better support for provider-specific functions
    • Better tooling for model/code-driven development
    • More services: Synchronization, Data Definition,…
summary for more information
Summary:For More Information…
  • ADO.NET Forum:

http://blogs.msdn.com/adonet/default.aspx

  • LINQ to SQL Forum

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=2035&SiteID=1

  • ADO.NET Entity Framework Forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&SiteID=1

  • ADO.NET Entity Framework Design BLOG:

http://blogs.msdn.com/efdesign/

  • Danny Simmons’ BLOG:

http://blogs.msdn.com/dsimmons/default.aspx

slide114

© 2008 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.