MySQL, LINQ and the ADO.NET Entity Framework

MySQL, LINQ and theADO.NET Entity Framework • Reggie Burnett, MySQL • David Sceppa, Microsoft

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

Working with DataSets • Basic example //Execute a query, retrieve and store the results connectionString = "Data Source=localhost;User ID=...;"; commandText = "SELECT CompanyName, City FROM Customers " + "WHERE Country = ?Country"; adapter = new MySqlDataAdapter(commandText, connectionString); adapter.SelectCommand.Parameters.AddWithValue("?Country", "Brazil"); table = new DataTable(); adapter.Fill(table); //Display the results DataGridViewresultsGrid = new DataGridView(); resultsGrid.DataSource = table; //Submit pending changes commandBuilder = new MySqlCommandBuilder(adapter); adapter.Update(table);

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

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

Moving to data access layers • Basic example //Partial class to separate data access code public class Customer { //Add methods to return data public static List<Customer>GetCustomers(stringcountry) { List<Customer> results = new List<Customer>(); MySqlConnectionconnection = new MySqlConnection(connectString); connection.Open(); MySqlCommandcommand = new MySqlCommand(queryString, connection); command.Parameters.AddWithValue("?Country", country); MySqlDataReaderreader = command.ExecuteReader(); while (reader.Read()) results.Add(Customer.CreateFromReader(reader)); return results; } //Support tracking and submitting changes private string origCustomerId, origCompanyName, ...; public void SubmitChanges() { ... } }

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

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

Introducing LINQ • LINQ Example - Querying an array //Create an array of integers int[] myarray = newint[] { 49, 28, 20, 15, 25, 23, 24, 10, 7, 34 }; //Create a query for odd numbers varoddNumbers = fromiinmyarraywherei % 2 == 1 selecti; //Compose the original query to create a query for odd numbers var sorted = fromiinoddNumbersorderbyidescending selecti; //Create a query for odd numbers varoddNumbers = fromiinmyarray wherei % 2 == 1 selecti; //Display the results of the query foreach (intiinoddNumbers) Console.WriteLine(i); //Create a query for odd numbers, sorted varoddNumbers = fromiinmyarray wherei % 2 == 1 orderbyi selecti; //Create a query for odd numbers, sorted in descending order varoddNumbers = fromiinmyarray wherei % 2 == 1 orderbyidescending selecti;

LINQ-enabled classes • Collection classes extended • Arrays, Hashtable, List<T>, etc. • LINQ to DataSet • Extended for untypedand strongly typed • LINQ to XML • Data access technologies • LINQ to SQL • LINQ to Entities

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 ForEach o As NorthwindDataSet.OrdersRow In ordersQuery Console.WriteLine("{0} {1,10:d}", _ o.OrderID, o.OrderDate) Next o

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

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

LINQ to Entities • What is LINQ to Entities? • LINQ implementation of the Entity Data Model • 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

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 ForEach o As NorthwindDataSet.OrdersRow In ordersQuery Console.WriteLine("{0} {1,10:d}", _ o.OrderID, o.OrderDate) Next o

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

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

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

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} {1:d}", o.OrderID, o.OrderDate);

Entity Framework Query Options • ObjectQuery<T> / 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,10:d} ", o.OrderID, o.OrderDate) Next o

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 stringeSql = "SELECT VALUE o FROM NorthwindEntities.Orders AS o " + "WHERE o.Customers.CustomerID = 'ALFKI'"; EntityCommandcmd = newEntityCommand(eSql, connectionString); EntityDataReaderrdr = cmd.ExecuteReader(); while (rdr.Read()) Console.WriteLine("{0} {1:d}", rdr["OrderID"], rdr["OrderDate"]);

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

ADO.NET Data Services • Service exposed via lightweight data access API • Supply both location and query as a URI: • http://myserver/data.svc/Customers[ALFKI]/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

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

Summary - ADO.NET Entity Framework • More than just LINQ to Entities • Also supports text-based query language – ESQL • ObjectQuery<T> for strongly typed results • EntityClient for reader-based results • Enterprise-grade data scenarios • Provider model for working with other data stores

Summary - ADO.NET Data Services • Designed for Silverlight, AJAX environments • Lightweight API, connect and query via URI • Retrieve results via familiar formats • ATOM • JSON

Questions?

MySQL, LINQ and the ADO.NET Entity Framework