Transcript PRE01: ADO.NET Entity Framework

 José A. Blakeley
Partner Architect
Microsoft Corporation
 Michael Pizzo
Principal Architect
Microsoft Corporation









ADO.NET 1.0
 Building a Data Platform
 The ADO.NET Entity Framework




























































Evolution of Data Access APIs





Getting Data from a SQL Database
Working with Data
ADO.NET and XML
Building a Data Platform
 The ADO.NET Entity Framework

Controls,
Designers,
Code-gen, etc
XmlReader
DataSet
OLTP operations,
Programmatic Processing,
Frameworks
XmlWriter
DataAdapter
DataReader
Command
Connection
Managed Provider
ADO.NET Data Provider
Data
Provider


Data
store





Connection

CreateCommand()

Command
Parameters
Parameters
Parameters
ExecuteReader()


DataReader


DataSet

Columns
Column
Constraints
Constraint
Rows
Row
Relations
Relation









DataSet
Tables
Table











DataSet
Fill() Update()

SelectCommand
InsertCommand
UpdateCommand

DeleteCommand


Mappings
Mappings
Mappings
DataAdapter

Data
store














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
Programming Data is Hard








Increase Developer Productivity




















The Need…
Applications work with a well
Defined Model
 Storage Schema Abstraction

Declarative mapping between
application and storage models
 No brittle, hard-coded mapping

SalesPerson

Manager

EmployeeID = 294272
LoginID = adam
Title = "Dev Lead"
VacationHours = 0
…
1


Reports

N

SalesPerson












SalesPerson
EmployeeID = 729742
LoginID = pete
EmployeeID
= 729742
SalesPerson
Title = "Developer"
LoginID
= pete
VacationHours
=0
Title …= "Developer"
EmployeeID
= 729742
VacationHours
=0 =…
LoginID
=ExpenseAccount
pete
Title…= "Developer"
CarLicenseNum = …
ExpenseAccount
VacationHours
=0 =…
…
… CarLicenseNum = …
…
ExpenseAccount
= true
…














Data Access in the 80s
 ADO.NET 1.0
 Building a Data Platform






Entity Designer
EntityClient
Object Services
Data Access in the 80s
 ADO.NET 1.0
 Building a Data Platform
 The ADO.NET Entity Framework





Overview
EntityClient
Object Services




















Data Access in the 80s
 ADO.NET 1.0
 Building a Data Platform
 The ADO.NET Entity Framework





Overview
Entity Designer
Object Services















Data Access in the 80s
 ADO.NET 1.0
 Building a Data Platform
 The ADO.NET Entity Framework





Overview
Entity Designer
EntityClient
























// Lambda Expressions
string[] names = { "Luis", "Mary", "Mike", "Jose" };
Display( names, s => s.Length > 3);
// Anonymous Types and object initialization
var emp = 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 };
 Introduction to LINQ




LINQ to Entities
 LINQ to DataSet

Direct Mapping









Strongly typed SQL Database












Features
















 Introduction to LINQ
 LINQ to SQL





LINQ to DataSet
Flexible Mapping to Relational Data
•
•
•
•
•
•
•
•
•

Features














 Introduction to LINQ
 LINQ to SQL
 LINQ to Entities

LINQ over Disconnected Cache with Change Tracking










Typed and UnTyped

AsEnumerable()



Field<T>(columnName)

var query = from row in 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
var query = from customer in northwind.Customers
where customer.City == "London"
select customer;















Customizing Data Classes













Customizing Data Classes
Entity Framework Mapping Scenarios





Core Mapping Scenarios
Function Mapping
Mapping Limitations
Database Design Considerations
Advanced Mapping Techniques
































Customizing Data Classes
 Entity Framework Mapping Scenarios
 Database Design Considerations















<Schema Namespace="AdventureWorksModel" Alias="Self"
xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="AdventureWorksEntities">
<EntitySet Name="Contacts"
EntityType="AdventureWorksModel.Contact" />
<AssociationSet Name="ManagerEmployees"
Association="AdventureWorksModel.ManagerEmployee">
<End Role="Employees" EntitySet="Contacts" />
<End Role="Manager" EntitySet="Contacts" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Contact">
<Key>
<PropertyRef Name="ContactID" />
</Key>
<Property Name="ContactID" Type="Int32" Nullable="false" />
<Property Name="Title" Type="String" />
<Property Name="FirstName" Type="String" Nullable="false" />
<Property Name="LastName" Type="String" Nullable="false" />
</EntityType>
<Association Name="ManagerEmployee">
<End Role="Employees"
Type="AdventureWorksModel.Employee" Multiplicity="*" />
<End Role="Manager"
Type="AdventureWorksModel.Employee" Multiplicity="0..1" />
</Association>
</Schema>
<Schema Namespace="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">
<EntityContainer Name="HumanResources">
<EntitySet Name="Contact"
EntityType="AdventureWorksModel.Store.Contact" Schema="Person" />
<AssociationSet Name="FK_Employee_Employee_ContactID"
Association= "AdventureWorksModel.Store.FK_Employee_Employee_ContactID">
<End Role="Employees" EntitySet="Employee" />
<End Role="Manager" EntitySet="Employee" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Contact">
<Key>
<PropertyRef Name="ContactID" />
</Key>
<Property Name="ContactID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
<Property Name="Title" Type="nvarchar" />
<Property Name="FirstName" Type="nvarchar" Nullable="false" />
<Property Name="LastName" Type="nvarchar" Nullable="false" />
</EntityType>
<Association Name="FK_Employee_Employee_ContactID">
<End Role="Employees" Type="AdventureWorksModel.Store.Employee" Multiplicity="*" />
<End Role="Manager" Type="AdventureWorksModel.Store.Employee" Multiplicity="0..1" />
</Association>
</Schema>
<Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="HumanResources"
CdmEntityContainer="AdventureWorksEntities">
<EntitySetMapping Name="Contacts"
TypeName="AdventureWorksModel.Contact" StoreEntitySet="Contact">
<ScalarProperty Name="ContactID" ColumnName="ContactID" />
<ScalarProperty Name="Title" ColumnName="Title" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
<ScalarProperty Name="MiddleName" ColumnName="MiddleName" />
<ScalarProperty Name="LastName" ColumnName="LastName" />
</EntitySetMapping>
<AssociationSetMapping Name="ManagerEmployees"
TypeName="AdventureWorksModel.ManagerEmployee" StoreEntitySet="Employee">
<EndProperty Name="Employees">
<ScalarProperty Name="ContactID" ColumnName="ContactID" />
</EndProperty>
<EndProperty Name="Manager">
<ScalarProperty Name="ContactID" ColumnName="ManagerID" />
</EndProperty>
<Condition ColumnName="ManagerID" IsNull="false" />
</AssociationSetMapping>
</EntityContainerMapping>
</Mapping>
Customizing Data Classes
 Entity Framework Mapping Scenarios
 Database Design Considerations
 Advanced Mapping Techniques




Anatomy of an .edmx file
Custom Mapping

<ComplexType Name ="FullName">
<Property Name="Title" Type="String" />
<Property Name="FirstName" Type="String" Nullable="false" />
<Property Name="MiddleName" Type="String" />
<Property Name="LastName" Type="String" Nullable="false" />
</ComplexType>

Use your ComplexType in your Entities
<EntityType Name="Contact">
<Key>
<PropertyRef Name="ContactID" />
</Key>
<Property Name="ContactID" Type="Int32" Nullable="false" />
<Property Name="Name" Type="Self.FullName" Nullable="false"/>
<!-- … -->
</EntityType>

Map the Complex Type in your MSL
<EntitySetMapping Name="Contacts" TypeName="AdventureWorksModel.Contact"
StoreEntitySet="Contact">
<ScalarProperty Name="ContactID" ColumnName="ContactID" />
<ComplexProperty Name="Name">
<ScalarProperty Name="Title" ColumnName="Title" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
<ScalarProperty Name="LastName" ColumnName="LastName" />
</ComplexProperty>
</EntitySetMapping>
Entity Framework Mapping Scenarios
 Database Design Considerations
 Customizing Data Classes
 Advanced Mapping Techniques




Anatomy of an .edmx file
Complex Types
Customize Conceptual Definition







Customize Mapping Definition



























ADO.NET and SQL Server
 Futures
 Summary: ADO.NET A to Z






















































Data Access Across Tiers












Futures










 Simple programming model
 Strongly typed
CREATE TYPE myTableType AS TABLE
(id INT, name NVARCHAR(100),qty
INT);
CREATE PROCEDURE myProc (@tvp
myTableType READONLY) AS
UPDATE Inventory SET
qty += s.qty
FROM Inventory AS i INNER JOIN
@tvp AS tvp
ON i.id = tvp.id
GO
 Reduce client/server round trips
 Do not cause a statement to recompile
TVP Client Stack Support

SqlDbType.Structured





ADO.NET Example using DataTable
Using (MyConnection){
//Create a data table
DataTable dt = 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
SqlCommand tvp_cmd = new
SqlCommand(“sp_UpdataInventory”,
MyConnection);
SqlParameter tvpParam =
tvp_cmd.Parameters.AddWithValue(
@OrdersTvp, dt);
//Execute command
tvp_cmd.ExecuteNonQuery();
SqlCommand command =
new SqlCommand(string.Empty, sqlConnection);
command.CommandText = "insert into MoviesRented
values(@customerId, @MovieID, @RentalDate,
@DueDate)";
….
// create a parameter for RentalDate
SqlParameter rentDateParam = new SqlParameter("RentDate",
System.Data.SqlDbType.DateTimeOffset);
rentDateParam.Value = DateTimeOffset.Now;
command.Parameters.Add(rentDateParam);
// create a parameter for DueDate
SqlParameter dueDateParam = new SqlParameter("DueDate",
System.Data.SqlDbType.DateTimeOffset);
dueDateParam.Value = DateTimeOffset.Now.AddDays(7);
command.Parameters.Add(dueDateParam);
….
// 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 (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.Read() == false)
{
Console.WriteLine("Movie has not been rented");
}
DateTimeOffset dueDate =
dataReader.GetDateTimeOffset (0);
Console.WriteLine("Movie due back on : {0}", dueDate);
}










// 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);
}


















public SqlDataReader GetProducts(int Category) {
SqlCommand cmd = 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() {
SqlCommand cmd = 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);
}




public void LoadFromDataReader(IDataReader reader)
{
// Copy the Data to SqlServer
SqlBulkCopy bcp =
new SqlBulkCopy( connectString );
bcp.DestinationTableName = "Customers";
bcp.WriteToServer( reader );
}














SqlConnection cnn = new SqlConnection(connectString);
cnn.Open();
SqlCommand cmd =
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();
Aggregates
AVG
CHECKSUM_AGG
COUNT
COUNT_BIG
MAX
MIN
STDEV
STDEVP
VAR
VARP
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
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
System Functions
DATALENGTH
CHECKSUM
NEWID
CURRENT_TIMESTAMP
CURRENT_USER
HOST_NAME
USER_NAME
ISNUMERIC
ISDATE
 Data Access Across Tiers
 ADO.NET and SQL Server



















































http://forums.microsoft.com/MSDN/ShowForum.asp
x?ForumID=2035&SiteID=1

http://forums.microsoft.com/MSDN/ShowForum.aspx?Forum
ID=533&SiteID=1


© 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.