Slides from Gang Luo, Xuting Zhao Slides from Gang Luo, Xuting Zhao and Damien Guardand Damien Guard LINQLINQ

(1)

LINQ LINQ

Slides from Gang Luo, Xuting Zhao Slides from Gang Luo, Xuting Zhao

and Damien Guard and Damien Guard

(2)

What is LINQ?

 Language Integrated QueryLanguage Integrated Query

 Make query a part of the languageMake query a part of the language

 Component of .NET Framework 3.5Component of .NET Framework 3.5

(3)

Query without LINQ Query without LINQ

 Objects using loops and conditionsObjects using loops and conditions

foreach (Customer c in customers) foreach (Customer c in customers)

if (c.Region == "UK") ...

 Databases using SQLDatabases using SQL

SELECT * FROM Customers WHERE SELECT * FROM Customers WHERE

Region='UK' Region='UK'

 XML using XPath/XQueryXML using XPath/XQuery

//Customers/Customer[@Region='UK']

(4)

ADO without LINQ ADO without LINQ

SqlConnection con = new SqlConnection(...);

con.Open();

SqlCommand cmd = new SqlCommand(

@"SELECT * FROM Customers

WHERE c.Region = @Region", conWHERE c.Region = @Region", con ););

cmd.Parameters.AddWithValue("@Region", "UK");

DataReader dr = cmd.ExecuteReader();

while (dr.Read()) { while (dr.Read()) {

string name = dr.GetString(dr.GetOrdinal("Name")); string name = dr.GetString(dr.GetOrdinal("Name"));

string phone = string phone =

dr.GetString(dr.GetOrdinal("Phone"));

DateTime date = dr.GetDateTime(3); DateTime date = dr.GetDateTime(3);

}}

dr.Close();

con.Close();

(5)

Query with LINQ Query with LINQ

C#C#

var myCustomers = from c in customers var myCustomers = from c in customers

where c.Region == "UK"

select c;

(6)

More LINQ queries More LINQ queries

var goodCusts = (from c in db.Customers

where c.PostCode.StartsWith("GY") orderby c.Sales descending

select c).Skip(10).Take(10);

(7)

Local variable type Local variable type

inference inference

 Compiler can infer types from initializer Compiler can infer types from initializer assignments

assignments

 var keyword indicates compiler inferred typevar keyword indicates compiler inferred type

 Still strong-typed

 This is not like JavaScript var

 Essential when using anonymous typesEssential when using anonymous types

var a = 10; // Simple types var x = new {

Blog = “attardi”, Created = DateTime.Now };

// Anonymous types

(8)

Anonymous Types Anonymous Types

 Object of new type generated on the fly without first defining Object of new type generated on the fly without first defining it.it.

Useful for projection to select one or more fields of another Useful for projection to select one or more fields of another

structure.

 The type will be dynamically generated with setters and The type will be dynamically generated with setters and getters to corresponding members.

getters to corresponding members.

Some common methods are also provided.

 No other methods will be added to this type.No other methods will be added to this type.

But that is already enough!

 The object is created and initialized by Anonymous Object The object is created and initialized by Anonymous Object Initializer.

Initializer.

(9)

Advantages Advantages

 Unified data accessUnified data access

Single syntax to learn and remember Single syntax to learn and remember

 Strongly typedStrongly typed

Catch errors during compilation Catch errors during compilation

 IntelliSenseIntelliSense

Prompt for syntax and attributes Prompt for syntax and attributes

 Bindable result setsBindable result sets

(10)

Architecture

(11)

LINQ to Objects LINQ to Objects

int[] nums = new int[]

{0,4,2,6,3,8,3,1};

double average = double average =

nums.Take(6).Average();

var above = from n in nums var above = from n in nums

where n > average where n > average

select n;

(12)

LINQ to Objects LINQ to Objects

 Query any IEnumerable<T> sourceQuery any IEnumerable<T> source Includes arrays, List<T>, Dictionary...

Includes arrays, List<T>, Dictionary...

 Many useful operators availableMany useful operators available

Sum, Max, Min, Distinct, Intersect, Union Sum, Max, Min, Distinct, Intersect, Union

 Expose your own data with Expose your own data with

IEnumerable<T> or IQueryable<T>

 Create operators using extension methodsCreate operators using extension methods

(13)

LINQ operators LINQ operators

Aggregate Conversion Ordering Partitioning Sets Aggregate

Average Count MaxMin Sum

Cast OfType ToArray

ToDictionary ToList

ToLookup ToSequence

OrderBy ThenBy

Descending Reverse

Skip

SkipWhile Take

TakeWhile

Concat Distinct Except Intersect Union

and others …

(14)

Query Expression Query Expression

 SQL-like:SQL-like:

from s in names

where s.Length == 5 orderby select s.ToUpper();

 OO-style:OO-style:

names.Where(s => s.Length==5) .OrderBy(s => s)

.Select(s => s.ToUpper());

 Where, OrderBy, and Select are operators. Where, OrderBy, and Select are operators.

The arguments to these operators are Lambda The arguments to these operators are Lambda

Expression.

(15)

Lambda Expressions Lambda Expressions

 Examples:Examples:

 s => s.Length == 5

 Executable functionExecutable function

 Anonymous functional. Can be assigned to a delegate variable.

 No need to indicate the types

 Can be passed to methods as parameters.

 Expression TreeExpression Tree

 Efficient in-memory data representations of lambda expressions

 Changing the behaviors of the expressions

 Applying your own optimization

(16)

Function Types Function Types

 Func<int, bool> is a shorthand forFunc<int, bool> is a shorthand for

public delegate bool Func(int a0);

// Initialized with anonymous method // Initialized with anonymous method

Func<int, bool> even=

delegate (int x) { return x % 2 delegate (int x) { return x % 2

== 0; };

// Initialized with lambda expression // Initialized with lambda expression

Func<int, bool> even2 = x => x % 2 ==

0;0;

(17)

Methods Extension Methods Extension

 Control not only by Lambda Expression, but Control not only by Lambda Expression, but also by methods extension

also by methods extension

public static class Enumerable { public static class Enumerable {

public static IEnumerable<T> Where<T>(public static IEnumerable<T> Where<T>(

this IEnumerable<T> source, this IEnumerable<T> source,

Func<T, bool> predicate) { Func<T, bool> predicate) {

foreach (T item in source)foreach (T item in source)

if (predicate(item))if (predicate(item)) yield return item;

yield return item;

}} }}

(18)

LINQ Operations LINQ Operations

 JoinJoin

 When there is relationship (e.g. foreign key) between two tables, no explicit join operation is needed

 Using dot notation to access the relationship properties, and navigate all the matching objects.

var q = from o in db.Orders where o.Customer.City == “London”

select o;

 To join any two data sources on any attribute, you need an explicit join operation.

var query = names.Join(people, n => n, p =>

p.Name, (n, p) => p);

The lambda expression for shaping (n, p) => p will be applied on each matching pairs.

(19)

LINQ Operations (cont.) LINQ Operations (cont.)

 Group JoinGroup Join

 The lambda expression for shaping is applied on the outer element and the set of all the inner elements that matches the outer one.

 Shape the result at a set level

var query = names.GroupJoin(people, n => n,

p => p.Name,

(n, matching) => new { Name = n, Count = matching.Count() }

)

(20)

LINQ Operations (cont.) LINQ Operations (cont.)

 Select ManySelect Many

 Each object in the result set may contain a collection or array

 Select many help decompose the structure and flatten the result var query = names.SelectMany(n =>

people.Where(p => n == p.Name))

 All the elements could be traversed in one foreach loop.

 AggregationAggregation

 Standard aggregation operators: Min, Max, Sum, Average.

int totalLength=names.Sum(s => s.Length);

 General purpose (generic) operator:

static U Aggregate<T, U>(this IEnumerable<T>

source,

U seed, Func<U, T, U> func)

(21)

LINQ Deferred LINQ Deferred

 A LINQ data source can actually implement A LINQ data source can actually implement one of two interfaces:

one of two interfaces:

 IEnumerable<T>

 IQueryable<T>

 Create deferred query execution planCreate deferred query execution plan

public interface IQueryable<T> :

IEnumerable<T>, IQueryable, IEnumerable { IQueryable<S> CreateQuery<S>(Expression exp);

S Execute<S>(Expression exp);

}

(22)

IQueryable IQueryable

 IQueryable<T> interface will defer the IQueryable<T> interface will defer the evaluation of the query.

evaluation of the query.

 An expression tree will represent all the An expression tree will represent all the deferred queries as a whole.

deferred queries as a whole.

 Several operations could be “merged”, only Several operations could be “merged”, only one SQL query will be generated and sent to one SQL query will be generated and sent to

database.

 Multi-level defer Multi-level defer

(23)

Lambda Expressions Lambda Expressions

Revisited Revisited

 Lambda expressionscan represent either IL Lambda expressionscan represent either IL code or data

code or data

 Expression<T> makes the difference

 Compiler handles Expression<T> types Compiler handles Expression<T> types differently

differently

 Emits code to generate expression tree instead of usual IL for delegate

Func<int, bool> lambdaIL = n => n % 2 == 0;

Expression<Func<int, bool>> lambdaTree = n => n % 2 == 0;

(24)

Expression Trees Expression Trees

 Expression tree are hierarchical trees of Expression tree are hierarchical trees of instructions that compose an expression instructions that compose an expression

 Add value of expression treesAdd value of expression trees

 Actual creating of IL is deferred until execution of query

 Implementation of IL creation can vary

 Trees can even be remoted for parallel Trees can even be remoted for parallel processing

processing

(25)

Creating IL from Creating IL from

Expression Trees Expression Trees

 Right before execution tree is compiled into ILRight before execution tree is compiled into IL

 Implementation of IL generation differs very Implementation of IL generation differs very much for each Linq flavor.

much for each Linq flavor.

 Linq to SQL generates IL that runs SQL commands

 Linq to Objects builds IL with Sequence extensions methods

Expression<Func<Posting,bool>> predicate = p => p.Posted < DateTime.Now.AddDays(-5);

Func<Posting,bool> d = predicate.Compile();

(26)

Nested defer Nested defer

 Nested deferNested defer

 What if you want the intermediate result?What if you want the intermediate result?

var q = from c in db.Customers where c.City == “London”

select new { c.ContactName, c.Phone } var q2 = from c in q.AsEnumerable()

select new {

Name = DoNameProcessing(c.ContactName), Phone = DoPhoneProcessing(C.Phone)

};

string lastName = “Simpson”

var persons = from p in personList where p.LastName = lastName select p;

lastName = “Flanders”

foreach (Person p in persons)

Console.WriteLine(“{0} {1}”, p.FirstName, p.LastName);

(27)

Deferred Execution Deferred Execution



Advantages Advantages

 Performance!

 Query dependency!



Disadvantages Disadvantages

 Divide one query into multiple ones

 If you iterate over the result set 100 times, the query will be

executed 100 times.

 Users have to be very careful

(28)

LINQ to SQL LINQ to SQL

 Data ModelData Model

 LINQ to SQL helps connect to relational and manipulate the LINQ to SQL helps connect to relational and manipulate the relational data as objects in memory.

relational data as objects in memory.

 It achieves this by translating the operations into SQL statements.It achieves this by translating the operations into SQL statements.

[Table(Name=“Customers”)]

public class Customer { [Column(Id=true)]

public string CustomerID;

…

private EntitySet<Order> _Orders;

[Association(Storage=“_Orders”, OtherKey=“CustomerID”)]

public EntitySet<Order> Orders { get { return _Orders; }

set { _Orders.Assign(value); } }

}

(29)

LINQ to SQL LINQ to SQL

 Object-relational mappingObject-relational mapping

Records become strongly-typed objects Records become strongly-typed objects

 Data context is the controller mechanismData context is the controller mechanism

 Facilitates update, delete & insertFacilitates update, delete & insert

 Translates LINQ queries behind the scenesTranslates LINQ queries behind the scenes

 Type, parameter and injection safeType, parameter and injection safe

(30)

Database mapping Database mapping

 Map tables & fields to classes & propertiesMap tables & fields to classes & properties

 Generates partial classes with attributesGenerates partial classes with attributes

 Each record becomes an objectEach record becomes an object

 Data context represents the databaseData context represents the database

 Utilize tables, views or stored proceduresUtilize tables, views or stored procedures

(31)

Modifying objects Modifying objects

 UpdateUpdate

Set object properties Set object properties

 DeleteDelete

context.

context.TableTable.DeleteOnSubmit(object).DeleteOnSubmit(object)

 InsertInsert

context.

context.TableTable.InsertOnSubmit(object).InsertOnSubmit(object)

 Commit changes backCommit changes back

context.SubmitChanges() context.SubmitChanges()

Transactional - all or nothing Transactional - all or nothing

(32)

Consistency Consistency

 Every object will be tracked by LINQ the Every object will be tracked by LINQ the moment it is loaded from database.

moment it is loaded from database.

 The tracking mechanism monitor the The tracking mechanism monitor the manipulation on relationship properties.

manipulation on relationship properties.

Once you modify one side of the relationship, Once you modify one side of the relationship,

LINQ will modify the other to keep it LINQ will modify the other to keep it

consistent.

 When an object is deleted, it could still exist When an object is deleted, it could still exist in memory, but it will not cause inconsistency.

in memory, but it will not cause inconsistency.

(33)

Concurrency Concurrency

 Optimistic concurrencyOptimistic concurrency

 Conflict checking when SubmitChanges() is Conflict checking when SubmitChanges() is called

called

 By default, transaction will abort and an By default, transaction will abort and an exception will be thrown when a conflict is exception will be thrown when a conflict is

detected.

 User can handle the conflict in the exception User can handle the conflict in the exception catch block.

catch block.

 User can set whether or not to detect the User can set whether or not to detect the conflict when one column get updated.

conflict when one column get updated.

(34)

Transaction/Update Transaction/Update

 When update, first check whether new object When update, first check whether new object is added (by tracking mechanism) if yes,

is added (by tracking mechanism) if yes, insert statement will be generated.

insert statement will be generated.

What does Django do here?

 Modification will not hit the database until the Modification will not hit the database until the SubmitChanges() method is called

SubmitChanges() method is called

 All operations will be translated into SQL All operations will be translated into SQL statements

statements

 All modifications will be encapsulated into a All modifications will be encapsulated into a transaction.

transaction.

(35)

Transaction/Update (cont.) Transaction/Update (cont.)



If an exception is throw during the If an exception is throw during the

update, all the changes will be rolled update, all the changes will be rolled back back



One One SubmitChanges() SubmitChanges() is actually is actually one transaction. (pros and cons?)

one transaction. (pros and cons?)



Users can also explicitly indicate a new Users can also explicitly indicate a new transaction scope.

transaction scope.

(36)

LINQ to XML Class LINQ to XML Class

Hierarchy Hierarchy

httphttp://msdn.microsoft.com/en-us/library/bb308960.aspx://msdn.microsoft.com/en-us/library/bb308960.aspx

(37)

LINQ to XML LINQ to XML

 LINQ to XMLLINQ to XML

 XML to LINQXML to LINQ

var query = from p in people where p.CanCode

select new XElement(“Person”,

new XAttribute(“Age”, p.Age), p.Name);

var x = new XElement(“People”, from p in people where p.CanCode select

new XElement(“Person”,

new XAttribute(“Age”, p.Age), p.Name);

(38)

Performance Performance

 LINQ has more control and efficiency in O/R LINQ has more control and efficiency in O/R Mapping than NHibernate

Mapping than NHibernate

 LINQ: Externl Mapping or Attribute Mapping

 NHibernate: Externl Mapping

 Because of mapping, LINQ is slower than Because of mapping, LINQ is slower than database tools such as SqlDataReader or database tools such as SqlDataReader or

SqlDataAdapter SqlDataAdapter

 In large dataset, their performance are more and more similar