Mysql linq and the ado net entity framework
Download
1 / 32

MySQL, LINQ and the ADO.NET Entity Framework - PowerPoint PPT Presentation


  • 100 Views
  • Uploaded on

MySQL, LINQ and the ADO.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.

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 ' MySQL, LINQ and the ADO.NET Entity Framework' - nedra


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
Mysql linq and the ado net entity framework
MySQL, LINQ and theADO.NET Entity Framework

  • Reggie Burnett, MySQL

  • David Sceppa, Microsoft


Agenda
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
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 datasets1
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
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 layers1
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 layers2
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
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 linq1
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
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


Agenda1
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


Linq to dataset
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 dataset1
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
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


Agenda2
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


Linq to entities
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 dataset2
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 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
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


Agenda3
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


Entity framework linq and more
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
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 options1
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 options2
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"]);


Agenda4
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


Ado net data services
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 services1
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


Agenda5
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


Summary linq
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
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
Summary - ADO.NET Data Services

  • Designed for Silverlight, AJAX environments

  • Lightweight API, connect and query via URI

  • Retrieve results via familiar formats

  • ATOM

  • JSON



ad