Transcript ASP.NET 2.0 AJAX 1.0 Training

Fons Sonnemans (Trainer)
[email protected]
http://www.reflectionit.nl
Reflection IT
LINQ & Entity Framework
Agenda
C# 3.0
• LINQ
• LINQ to SQL
• Entity Framework
•
2
ADO.NET Problems
Queries in
quotes
using (SqlConnection c = new SqlConnection(…)) {
c.Open();
string sql =
"SELECT c.Name, c.Phone, c.CreationDate " +
"FROM Customers c " +
"WHERE c.City= @p0"
Loosely bound
arguments
SqlCommand cmd = new SqlCommand(sql, c);
cmd.Parameters.AddWithValue("@p0", "London");
SqlDataReader dr = c.ExecuteReader(cmd);
while(dr.Read()) {
string name = dr.GetString(0);
string phone = dr.GetString(1);
DateTime date = dr.GetDateTime(2);
…
}
Loosely typed
resultsets
No compiletime
checking
No IntelliSense
}
3
Reflection IT
C# 3.0
C# 3.0: Local Variable Type Inference
•
Local variable type inference is a feature in C#
3.0 where you can use the var keyword instead
of explicitly specifying the type of a variable. The
C# 3.0 compiler makes the type of the variable
match the type of the right side of the
assignment.
public void Foo() {
var i = 5;
var s = "Hello";
var d = 1.0;
var z;
// compiler error, no initializer
z = DateTime.Today;
}
5
C# 3.0: Object Initializers
public class Point
{
private int x, y;
public int X { get { return x; } set { x = value; } }
public int Y { get { return y; } set { y = value; } }
}
Field or property
assignments
Point a = new Point { X = 0, Y = 1 };
Point a = new Point();
a.X = 0;
a.Y = 1;
6
C# 3.0: Anonymous Types
var emp = new { Name = "Fons",
Salary = 2000,
DateTime.Today.Year };
var year = emp.Year;
class XXX {
public string Name { get; set; }
public int Salary { get; set; }
public int Year { get; set; }
}
• Different anonymous object initializers that
define properties with same names in the same
order generate the same anonymous type
7
C# 3.0: Extension Methods
Extend existing types with additional methods.
•
namespace MyStuff {
public static class Util {
public static bool IsWeekend(this DateTime value) {
return (value.DayOfWeek == DayOfWeek.Sunday ||
value.DayOfWeek == DayOfWeek.Saturday);
}
}
}
Brings extensions
into scope
using MyStuff;
DateTime dt = DateTime.Today;
bool b = dt.IsWeekend();
dt.IsWeekend()

DateTime.IsWeekend(dt)
8
C# 3.0: Lambda Expressions
delegate string SomeDelegate(string s);
C# 1.0
private static string TestMethod1(string s) {
OO Functionreturn s.ToUpper();
}
Pointer
…
SomeDelegate d1 = new SomeDelegate(TestMethod1);
string a = d1("abcde");
C# 2.0
SomeDelegate d2 = TestMethod1;
string a = d2("abcde");
C# 2.0
SomeDelegate d3 = delegate(string s) {
return s.ToUpper();
};
string a = d3("abcde");
Anonymous
Method
C# 3.0
SomeDelegate d4 = s => s.ToUpper();
string a = d4("abcde");
Lambda
Expression
Delegate
Inference
9
Language-Integrated Query
Reflection IT
LINQ
What is LINQ?
•
Uniform way to write queries over data



•
LINQ is about query keywords

•
Data == Objects
Imperative  Declarative
Works against objects, relational and XML
Built into new languages C# 3.0 and VB 9.0
LINQ is about query operators


40+ standard query operators are defined
Methods that operate in queries or act on its results
11
C# 3.0: Query Expressions
Starts with
from
Zero or more from,
join, let, where, or
orderby
from id in source
{ from id in source |
join id in source on expr equals expr [ into id ] |
let id = expr |
Ends with select
where condition |
or group by
orderby ordering, ordering, … }
select expr | group expr by key
[ into id query ]
Optional into
continuation
12
C# 3.0: Query Expressions
•
Queries translate to method invocations

Where, Join, OrderBy, Select, GroupBy, …
from c in customers
where c.State == "WA"
select new { c.Name, c.Phone };
customers
.Where(c => c.State == "WA")
.Select(c => new { c.Name, c.Phone });
13
Language-Integrated Query
C#
Others…
VB
LINQ Provider
ADO.NET Based
LINQ
To Objects
LINQ
To Datasets
LINQ
To SQL
LINQ
To Entities
LINQ
To XML
<book>
<title/>
<author/>
<price/>
</book>
Objects
Relational
XML
14
Two kinds of LINQ
Enumerable types
Queryable types
Execution
Local in-memory
Usually remote
Implementation
Iterators using yield
return
Expression tree parsing
Interface
IEnumerable<T>
IQueryable<T>
Providers
LINQ to Objects
LINQ to SQL
LINQ to Entities
Other APIs
LINQ to XML
LINQ to DataSets
15
Standard Query Operators
Restriction
Where
Projection
Select, SelectMany
Ordering
OrderBy, OrderByDescending, ThenBy, ThenByDecending
Grouping
GroupBy
Quantifiers
Any, All, Contains
Partitioning
Take, Skip, TakeWhile, SkipWhile
Sets
Distinct, Union, Concat, Intersect, Except
Elements
First, FirstOrDefault, Last, Single, ElementAt
Aggregation
Count, Sum, Min, Max, Average
Conversion
ToArray, ToList, ToDictionary, ToLookup
Casting
Cast<T>, OfType<T>
16
LINQ to Objects
17
LINQ to SQL
•
O/R Mapper

Maps .NET classes to relational SQL Server data
•
Translates LINQ queries to SQL execution
•
Supports change tracking for insert, update,
delete operations
•
Built on top of ADO.NET and integrates with
connection-pooling and transactions
18
LINQ to SQL
db.Customers.InsertOnSubmit(c1);
from c in db.Customers
where c.City == "London"
select c.CompanyName
Application
c2.City = "Asten";
db.Customers.DeleteOnSubmit(c3);
IQueryable<T>
[Attributes]
SQL Query or SProc
SELECT CompanyName
FROM Customer
WHERE City = 'London'
Objects
SubmitChanges()
LINQ to SQL
(DataContext)
Resultset
SQL Server
DML or SProcs
INSERT INTO Customer …
UPDATE Customer …
DELETE FROM Customer …
19
LINQ to SQL
20
Included in Visual Studio SP1
Reflection IT
ADO.NET Entity Framework
ADO.NET Entity Framework
•
O/R Mapper

Maps .NET classes to relational SQL data
•
Translates LINQ and Entity SQL queries to SQL
execution
•
Supports change tracking for insert, update,
delete operations
•
Built on top of ADO.NET and integrates with
connection-pooling and transactions
22
ADO.NET Entity Framework
Entity SQL
•



Entity SQL Objects
Object Services (ORM API)
EntityClient (ADO.NET API )
ObjectConnection
ObjectCommand
ObjectDataReader
Provides classes similar to ADO.NET
Providers
Can return results as DbDataReaders
ObjectContext
EntityObject
ObjectQueries
Textual SQL language for dynamic
queries
Entity Data Model
Conceptual Model

Enables you to work with object
instances
Provides persistence and change
tracking
Map
Storage/Logical Model
Entity Data Model
Schema
*.CSDL
Object Services

•
Abstraction over a relational database
Consists of conceptual & logical models
Provides mapping layer between
conceptual model and logical model
Entity SQL

•
Objects
Entity Client

•
LINQ
Entity Data Model

•
DataReader
*.MSL
Datastore
Objects
Schema
*.SSDL
Providers
SQL Server
Oracle
DB2
LINQ to Entities

Provides LINQ syntax and stronglytyped objects for queries over EDM
RDBMS
23
Entity Data Model
•
The edmx file is composed of three important parts:
• The csdl section which describes your entities
• The ssdl section which describes your database
• The msl section which do the mapping between the
two others
Conceptual Model
Entity Data
Model
Schema
*.CSD
L
OO Classes
(Properties +
Methods)
Map
Storage/Logical Model
*.MSL
Datastore
Objects
Schema
*.SSD
L
RDBMS
(tables, views,
SP’s, FN’s)
24
Mapping Examples
Store
Mapping
Entities
Customers
Customer
ID
CustomerId
FirstName
First
LastName
Last
IsPremium
?
Overdraft
AccountManager
PremiumCustomer
Overdraft
AccountManager
25
Mapping Examples
Store
Good Customers
Mapping
Type=“G”
Customers
ID
FirstName
LastName
CustomerId
First
Last
Type
Bad Customers
ID
ForeName
Surname
Entities
Type=“B”
26
LINQ to Entity Framework
db.AddToCustomer(c1);
from c in db.Customers
where c.City == "London"
select c.CompanyName
Application
c2.City = "Asten";
db.DeleteObject(c3);
IQueryable<T>
.edmx File
(Models &
Mapping)
SQL Query or SProc
SELECT CompanyName
FROM Customer
WHERE City = 'London'
Objects
SaveChanges()
LINQ to EF
(ObjectContext)
Resultset
RDBMS
DML or SProcs
INSERT INTO Customer …
UPDATE Customer …
DELETE FROM Customer …
27
Entity SQL (SELECT only)
// Object Services
using (NorthwindEntities db = new NorthwindEntities()) {
// Entity SQL
var q = db.CreateQuery<Products>("SELECT VALUE p FROM NorthwindEntities.Products AS p " +
"WHERE p.UnitPrice > @price", new ObjectParameter("price", 60));
foreach (var prod in q) {
Console.WriteLine(prod.ProductName);
}
}
// Entity Client
using (EntityConnection con = new EntityConnection("name=NorthwindEntities")) {
con.Open();
// Entity SQL
EntityCommand cmd = new EntityCommand("SELECT p.ProductName FROM NorthwindEntities.Products"
+ " AS p WHERE p.UnitPrice > @price", con);
cmd.Parameters.AddWithValue("price", 60);
using (EntityDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) {
while (r.Read()) {
Console.WriteLine(r["ProductName"]);
}
}
}
28
EF Providers in Progress
Vendor
DB Support
Microsoft
SQL Server
Core Lab
Oracle, MySQL, PostgreSQL, SQLite
IBM
DB2, Informix Dynamic Server
MySQL AB
MySQL
Npgsql
PostgreSQL
OpenLink
Many via OpenLink ODBC or JDBC
Phoenix
SQLite
DataDirect
Oracle, Sybase, SQL Server, DB2
Firebird
Firebird
29
LINQ to SQL vs Entity Framework
LINQ to SQL
ADO.NET Entities
Framework
Database Support SQL Server
Many
Object Relational
Mapping
Capabilities
Simple -> 1:1
Complex
Metadata
Attributes
edmx file
Status
Released
Beta, Summer 2008
30
ADO.NET Entity Framework
31
Summary
•
LINQ is a really important technology
•
Native query integration within languages
improves productivity and error checking
•
LINQ to SQL and ADO.NET Entity Framework are
both O/R Mappers
•
Using LINQ in ASP.NET is both easy and fun
32
Resources
http://www.datadeveloper.net/
• http://code.msdn.microsoft.com/adonetefx
• http://msdn.microsoft.com/enus/netframework/aa904594.aspx
• http://blogs.msdn.com/adonet/default.aspx
•
•
Visual Studio 2008 Upgrade Training

http://www.reflectionit.nl/Training/default.aspx#orcas
33
Questions
mailto:[email protected]
http://www.reflectionit.nl
http://www.objectmap.nl
34