MySQL, LINQ and the ADO.NET Entity Framework

1
MySQL, LINQ and theADO.NET Entity Framework

• Reggie Burnett, MySQL
• David Sceppa, Microsoft

2
Agenda

• From DataSets to data access layers
• Introducing LINQ
• Using LINQ to access your data
• The ADO.NET Entity Framework
• Introducing ADO.NET Data Services
• Summary

3
Working with DataSets

• Basic example

//Execute a query, retrieve and store the
results connectionString "Data
SourcelocalhostUser ID..." commandText
"SELECT CompanyName, City FROM Customers "
"WHERE Country ?Country" adapter
new MySqlDataAdapter(commandText,
connectionString) adapter.SelectCommand.Parameter
s.AddWithValue("?Country", "Brazil") table new
DataTable() adapter.Fill(table) //Display the
results DataGridView resultsGrid new
DataGridView() resultsGrid.DataSource
table //Submit pending changes commandBuilder
new MySqlCommandBuilder(adapter) adapter.Update(t
able)
4
Working with DataSets

• Benefit
• Rapid development - Designers, wizards, bound
  controls make it easy to write code and generate
  user interface
• Drawbacks
• Developers must become SQL experts to build
  advanced queries
• Code becomes more complex when app model and
  database schema diverge

5
Moving to data access layers

• Focus on your application model
• Queries expressed in terms of app model
• Classes track their own changes
• Data access layer responsible for
• Translating into SQL queries
• Returning results as objects
• Submitting pending changes

6
Moving to data access layers

• Basic example

//Partial class to separate data access
code public class Customer //Add methods to
return data public static ListltCustomergt
GetCustomers(string country) ListltCustomergt
results new ListltCustomergt ()
MySqlConnection connection new
MySqlConnection(connectString)
connection.Open() MySqlCommand command new
MySqlCommand(queryString, connection)
command.Parameters.AddWithValue("?Country",
country) MySqlDataReader reader
command.ExecuteReader() while
(reader.Read()) results.Add(Customer.CreateF
romReader(reader)) return results
//Support tracking and submitting changes
private string origCustomerId, origCompanyName,
... public void SubmitChanges() ...
7
Moving to data access layers

• Benefit
• Division of labor - Manage data access code
  separately, only subset of developers need to
  become SQL gurus
• Drawbacks
• Data access code quickly gets complex
• Classes that span multiple tables
• Handling inheritance hierarchies
• Note Patterns and frameworks emerge

8
Introducing LINQ

• What is LINQ?
• Stands for Language INtegrated Query
• Allows developers to query data structures using
  SQL-like syntax from within their applications
  code
• Is available to both C and Visual Basic
  developers in Visual Studio 2008

9
Introducing LINQ

• LINQ Example - Querying an array

//Create an array of integers int myarray new
int 49, 28, 20, 15, 25,
23, 24, 10, 7, 34
//Create a query for odd numbers var oddNumbers
from i in myarray where i 2 1 select
i //Compose the original query to create a
query for odd numbers var sorted from i in
oddNumbers orderby i descending select i
//Create a query for odd numbers var oddNumbers
from i in myarray where i 2
1 select i //Display the
results of the query foreach (int i in
oddNumbers) Console.WriteLine(i)
//Create a query for odd numbers, sorted var
oddNumbers from i in myarray
where i 2 1 orderby i
select i
//Create a query for odd numbers, sorted in
descending order var oddNumbers from i in
myarray where i 2 1
orderby i descending
select i
10
LINQ-enabled classes

• Collection classes extended
• Arrays, Hashtable, ListltTgt, etc.
• LINQ to DataSet
• Extended for untyped and strongly typed
• LINQ to XML
• Data access technologies
• LINQ to SQL
• LINQ to Entities

11
Agenda

• From DataSets to data access layers
• Introducing LINQ
• Using LINQ to access your data
• The ADO.NET Entity Framework
• Introducing ADO.NET Data Services
• Summary

12
LINQ to DataSet

• DataSet class enhanced to support LINQ
• Available via typed and untyped DataSet

Dim ordersQuery From o In dataSet.Orders _
Where o.CustomerID "ALFKI" _
Select o For Each o As
NorthwindDataSet.OrdersRow In ordersQuery
Console.WriteLine("0 1,10d", _
o.OrderID, o.OrderDate) Next o
13
LINQ to DataSet

• Benefits
• Quickest way to use LINQ w/ .NET 2.0 feature set
• Supports query capabilities previously
  unavailable using a DataSet
• Returns results as series of DataRows or as
  anonymous types
• Limitations
• All client-side processing

14
LINQ to SQL

• Benefits
• Rapid application development scenarios
• Query processing performed at the server
• Designer makes mapping classes to tables simple
• Limitations
• Supports limited mapping scenarios
• No provider model for extensibility

• Benefits
• Rapid application development scenarios
• Query processing performed at the server
• Designer makes mapping classes to tables simple
• Limitations
• Supports limited mapping scenarios

15
Agenda

• From DataSets to data access layers
• Introducing LINQ
• Using LINQ to access your data
• The ADO.NET Entity Framework
• Introducing ADO.NET Data Services
• Summary

16
LINQ to Entities

• What is LINQ to Entities?
• LINQ implementation of the Entity Data Model
• What is the Entity Data Model?
• Definition for your application model
• Map between app model, database schema
• Advanced mapping scenarios supported
• One entity mapped across multiple tables
• Multiple inheritance hierarchy mappings
• Many-to-many without "link" table in model

• What is LINQ to Entities?
• LINQ implementation of the Entity Data Model

17
LINQ to DataSet

• DataSet class enhanced to support LINQ
• Available via typed and untyped DataSet

Dim ordersQuery From o In dataSet.Orders _
Where o.CustomerID "ALFKI" _
Select o For Each o As
NorthwindDataSet.OrdersRow In ordersQuery
Console.WriteLine("0 1,10d", _
o.OrderID, o.OrderDate) Next o
18
LINQ to Entities or LINQ to SQL?

• LINQ to SQL
• Shipped with Visual Studio 2008 and .NET 3.5
• Emphasis on rapid application development
• Supports Microsoft SQL Server family of databases
• LINQ to Entities
• Will ship as an update to .NET 3.5
• Offers a provider model for third-party databases
• Designed for enterprise-grade data scenarios
• Higher level of abstraction for programming
  databases
• Just one layer of the overall ADO.NET Entity
  Framework

19
LINQ to Entities Generating Queries

• Converting LINQ queries to SQL
• Compiler converts code into a LINQ expression
  tree
• LINQ to Entities converts LINQ expression tree
  into a DbCommandTree based on mapping information
• DbCommandTree expressed in terms of the database
  schema
• ADO.NET provider generates a DbCommand
• LINQ to Entities executes the DbCommand,
  assembles results into the structure(s) specified
  in the LINQ query

20
Agenda

• From DataSets to data access layers
• Introducing LINQ
• Using LINQ to access your data
• The ADO.NET Entity Framework
• Introducing ADO.NET Data Services
• Summary

21
Entity Framework LINQ and more

• ADO.NET Entity Framework Layers
• First implementation of Entity Data Model (EDM)
• Includes a text-based queryquery language
  (Entity SQL)
• New Provider EntityClient
• New ORM stack (Object Services) leverages Entity
  Client
• LINQ support over Entities
• An evolution of ADO.NET preserving customer
  investment

LINQ to Entities
ObjectServices
EntityClient
ADO.NET Provider
22
Entity Framework Query Options

• LINQ to Entities
• Sweet spot
• Strongly typed queries and results
• Compile-time type checking and Intellisense
• Results returned as objects entities /
  projections
• Queries must be well defined at compile time

var ordersQuery from o in context.Orders
where o.Customers.CustomerID
"ALFKI" select o foreach (var
o in ordersQuery) Console.WriteLine("0
1d", o.OrderID, o.OrderDate)
23
Entity Framework Query Options

• ObjectQueryltTgt / ObjectQuery(Of T)
• Sweet spot
• Strongly typed results, text-based queries
• Results returned as objects entities /
  projections
• Queries more loosely defined at compile-time

Dim eql As String "SELECT VALUE o FROM Orders
AS o " _ " WHERE
o.Customer.CustomerID 'ALFKI'" Dim ordersQuery
context.CreateQuery(Of Order)(esql) For Each o
As Order In ordersQuery Console.WriteLine("0
1,10d ", o.OrderID, o.OrderDate) Next o
24
Entity Framework Query Options

• EntityClient Provider
• Sweet spot
• Untyped queries and results
• Results returned as DataReaders
• Queries can be generated purely at run time
• No application model classes needed at compile
  time

string eSql "SELECT VALUE o FROM
NorthwindEntities.Orders AS o "
"WHERE o.Customers.CustomerID
'ALFKI'" EntityCommand cmd new
EntityCommand(eSql, connectionString) EntityDataR
eader rdr cmd.ExecuteReader() while
(rdr.Read()) Console.WriteLine("0 1d",
rdr"OrderID", rdr"OrderDate")
25
Agenda

• From DataSets to data access layers
• Introducing LINQ
• Using LINQ to access your data
• The ADO.NET Entity Framework
• Introducing ADO.NET Data Services
• Summary

26
ADO.NET Data Services

• Formerly known as "Project Astoria"
• Part of ASP.NET Extensions Preview
• Integrated into .NET Framework going forward
• Targeting Web development technologies
• Silverlight and AJAX
• Data returned via Web-friendly formats
• ATOM (XML-based) and JSON

27
ADO.NET Data Services

• Service exposed via lightweight data access API
• Supply both location and query as a URI
• http//myserver/data.svc/CustomersALFKI/Orders
• Query converted to LINQ inside of service
• Supports submitting changes
• Specialized Entity Framework logic for submitting
  changes
• Online data hosting service available
• For more information
• http//astoria.mslivelabs.com

28
Agenda

• From DataSets to data access layers
• Introducing LINQ
• Using LINQ to access your data
• The ADO.NET Entity Framework
• Introducing ADO.NET Data Services
• Summary

29
Summary - LINQ

• Represents a revolution for developers
• Query your objects using SQL-like syntax
• LINQ to DataSet
• Rich query scenarios for data residing in a
  DataSet
• LINQ to Entities
• Provider model for working with other data stores
• Supports enterprise mapping scenarios

30
Summary - ADO.NET Entity Framework

• More than just LINQ to Entities
• Also supports text-based query language ESQL
• ObjectQueryltTgt for strongly typed results
• EntityClient for reader-based results
• Enterprise-grade data scenarios
• Provider model for working with other data stores

31
Summary - ADO.NET Data Services

• Designed for Silverlight, AJAX environments
• Lightweight API, connect and query via URI
• Retrieve results via familiar formats
• ATOM
• JSON

32
Questions?