Queryable LINQ Radu Nicolescu Department of Computer Science - - PowerPoint PPT Presentation

Q&E Basic CAVEAT Joins

Queryable LINQ

Radu Nicolescu Department of Computer Science University of Auckland 10 Oct 2018

1 / 17

Q&E Basic CAVEAT Joins

1 Queryables and Enumerables 2 Basic methods 3 Deferred evaluation and CAVEAT 4 Joins

2 / 17

Q&E Basic CAVEAT Joins

Outline

1 Queryables and Enumerables 2 Basic methods 3 Deferred evaluation and CAVEAT 4 Joins

3 / 17

Q&E Basic CAVEAT Joins

Queryables and Enumerables

• For the end-user, the IQueryable<T> extension methods seem

to duplicate the IEnumerable<T> extension methods

1 public interface IEnumerable<out T> { . . . } 2 public s t a t i c class Enumerable { 3 Where and other IEnumerable<T> extension methods 4 } 5 6 public interface IQueryable <out T>: IEnumerable<T>{ 7 public Provider ; 8 // genie AST ⇒ SQL, REST, bytecode 9 . . . 10 } 11 public s t a t i c class Queryable { 12 Where and other IQueryable <T> extension methods 13 }

4 / 17

Q&E Basic CAVEAT Joins

Two different versions of Where

• Enumerable.Where signature:

1 public s t a t i c IEnumerable<TSource> 2 Where<TSource> ( 3 t h i s IEnumerable<TSource> source , 4 Func<TSource , bool> p r e d i c a t e 5 )

• Queryable.Where signature:

1 public s t a t i c IQueryable <TSource> 2 Where<TSource> ( 3 t h i s IQueryable <TSource> source , 4 Expression <Func<TSource , bool> > p r e d i c a t e 5 )

• For the end-user, both methods seem duplicates and can be

used mostly in the same way, but ...

5 / 17

Q&E Basic CAVEAT Joins

Queryables and Enumerables

• Usually, the type of the first source sequence determines

which of the two methods is called and the provider

• A lambda will NOT contribute to this differentiation, because

lambdas have two faces (recall), e.g.

1 Func<int , bool> func = x = > x < 100; 2 3 Expression <Func<int , bool> > t r e e = x = > x < 100;

• And the two sets of functions work differently
• The Queryable methods generate trees, further translated by

their associated provider to SQL code, REST URL, ... , which will be sent to the service (unless it is bytecode)

• The Enumerable methods generate CLR bytecodes, which run
• n sequences of in-memory objects

6 / 17

Q&E Basic CAVEAT Joins

Queryables and Enumerables

• Usually, the type of the first source sequence determines

which of the two methods is called and the provider

• A lambda will NOT contribute to this differentiation, because

lambdas have two faces (recall), e.g.

1 Func<int , bool> func = x = > x < 100; 2 3 Expression <Func<int , bool> > t r e e = x = > x < 100;

• And the two sets of functions work differently
• The Queryable methods generate trees, further translated by

their associated provider to SQL code, REST URL, ... , which will be sent to the service (unless it is bytecode)

• The Enumerable methods generate CLR bytecodes, which run
• n sequences of in-memory objects

6 / 17

Q&E Basic CAVEAT Joins

Queryables and Enumerables

• Conversions
• IQueryable<> → implicit upcast → IEnumerable<>
• IQueryable<> → AsEnumerable() → IEnumerable<>
• IQueryable<> ← AsQueryable()

← IEnumerable<>

• Thus, you can also run Queryable methods on IEnumerable<>

sequences, after converting these to IQueryable<>

• however, in this case, the associated Queryable provider will

generate CLR bytecodes – like their corresponding Enumerable methods

7 / 17

Q&E Basic CAVEAT Joins

Queryables and Enumerables

• Conversions
• IQueryable<> → implicit upcast → IEnumerable<>
• IQueryable<> → AsEnumerable() → IEnumerable<>
• IQueryable<> ← AsQueryable()

← IEnumerable<>

• Thus, you can also run Queryable methods on IEnumerable<>

sequences, after converting these to IQueryable<>

• however, in this case, the associated Queryable provider will

generate CLR bytecodes – like their corresponding Enumerable methods

7 / 17

Q&E Basic CAVEAT Joins

Outline

1 Queryables and Enumerables 2 Basic methods 3 Deferred evaluation and CAVEAT 4 Joins

8 / 17

Q&E Basic CAVEAT Joins

Where+Select – example

1 var a l f k i = Orders 2 . Where ( o = > o . CustomerID == ”ALFKI” ) . 3 . S e l e c t ( o = > new { o . OrderID ,

• . CustomerID

} ) ; 4 a l f k i .Dump ( ” a l f k i ” ) ;

• If Orders corresponds to the population of an SQL table, then

a parametrised query is sent to the SQL server:

1 DECLARE @p0 NVarChar (1000) = ’ALFKI ’ 2 SELECT [ t0 ] . [ OrderID ] , [ t0 ] . [ CustomerID ] 3 FROM [ Orders ] AS [ t0 ] 4 WHERE [ t0 ] . [ CustomerID ] = @p0

• If Orders corresponds to an OData entity, then a REST URL

is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders ()? 2 $ f i l t e r =CustomerID eq ’ALFKI ’ & 3 $select=OrderID , CustomerID

9 / 17

Q&E Basic CAVEAT Joins

Where+Select – example

1 var a l f k i = Orders 2 . Where ( o = > o . CustomerID == ”ALFKI” ) . 3 . S e l e c t ( o = > new { o . OrderID ,

• . CustomerID

} ) ; 4 a l f k i .Dump ( ” a l f k i ” ) ;

• If Orders corresponds to the population of an SQL table, then

a parametrised query is sent to the SQL server:

1 DECLARE @p0 NVarChar (1000) = ’ALFKI ’ 2 SELECT [ t0 ] . [ OrderID ] , [ t0 ] . [ CustomerID ] 3 FROM [ Orders ] AS [ t0 ] 4 WHERE [ t0 ] . [ CustomerID ] = @p0

• If Orders corresponds to an OData entity, then a REST URL

is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders ()? 2 $ f i l t e r =CustomerID eq ’ALFKI ’ & 3 $select=OrderID , CustomerID

9 / 17

Q&E Basic CAVEAT Joins

Count – example

1 var count = Orders . Count ( ) ; 2 count .Dump ( ” count ” ) ;

• If Orders corresponds to the population of an SQL table, then

the following SQL query is sent to the SQL server:

1 SELECT COUNT(∗) AS [ value ] 2 FROM [ Orders ] AS [ t0 ]

• If Orders corresponds to an OData entity, then a REST URL

is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders ()/$count

• This is efficient, the count is performed on the server size

(who might know it, without actually counting) and we receive back (over the network) only the required value

10 / 17

Q&E Basic CAVEAT Joins

Count – example

1 var count = Orders . Count ( ) ; 2 count .Dump ( ” count ” ) ;

• If Orders corresponds to the population of an SQL table, then

the following SQL query is sent to the SQL server:

1 SELECT COUNT(∗) AS [ value ] 2 FROM [ Orders ] AS [ t0 ]

• If Orders corresponds to an OData entity, then a REST URL

is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders ()/$count

• This is efficient, the count is performed on the server size

(who might know it, without actually counting) and we receive back (over the network) only the required value

10 / 17

Q&E Basic CAVEAT Joins

Count – example

1 var count = Orders . Count ( ) ; 2 count .Dump ( ” count ” ) ;

• If Orders corresponds to the population of an SQL table, then

the following SQL query is sent to the SQL server:

1 SELECT COUNT(∗) AS [ value ] 2 FROM [ Orders ] AS [ t0 ]

• If Orders corresponds to an OData entity, then a REST URL

is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders ()/$count

• This is efficient, the count is performed on the server size

(who might know it, without actually counting) and we receive back (over the network) only the required value

10 / 17

Q&E Basic CAVEAT Joins

Outline

1 Queryables and Enumerables 2 Basic methods 3 Deferred evaluation and CAVEAT 4 Joins

11 / 17

Q&E Basic CAVEAT Joins

Deferred evaluation

• The SQL queries are built in-memory and kept there until

there is a imperative “need” to process the results

• The following lines only contribute towards a big query, which

is NOT yet sent!

1 var r1 = Orders . OrderBy ( o = > o . OrderID ) ; 2 var r2 = r1 . Skip ( 4 ) ; 3 var r3 = r2 . Take ( 2 ) ; 4 var r4 = r3 . S e l e c t ( o = > new { o . OrderID , . . . } ) ;

• NOTHING will happen, until we run a processing

statement,e.g.:

1 r4 .Dump( ” r4 ” ) ;

• Only at this stage the successively grown query is sent to the

server (SQL, Odata)!

12 / 17

Q&E Basic CAVEAT Joins

Deferred evaluation

• The SQL queries are built in-memory and kept there until

there is a imperative “need” to process the results

• The following lines only contribute towards a big query, which

is NOT yet sent!

1 var r1 = Orders . OrderBy ( o = > o . OrderID ) ; 2 var r2 = r1 . Skip ( 4 ) ; 3 var r3 = r2 . Take ( 2 ) ; 4 var r4 = r3 . S e l e c t ( o = > new { o . OrderID , . . . } ) ;

• NOTHING will happen, until we run a processing

statement,e.g.:

1 r4 .Dump( ” r4 ” ) ;

• Only at this stage the successively grown query is sent to the

server (SQL, Odata)!

12 / 17

Q&E Basic CAVEAT Joins

Deferred evaluation

• The SQL queries are built in-memory and kept there until

there is a imperative “need” to process the results

• The following lines only contribute towards a big query, which

is NOT yet sent!

1 var r1 = Orders . OrderBy ( o = > o . OrderID ) ; 2 var r2 = r1 . Skip ( 4 ) ; 3 var r3 = r2 . Take ( 2 ) ; 4 var r4 = r3 . S e l e c t ( o = > new { o . OrderID , . . . } ) ;

• NOTHING will happen, until we run a processing

statement,e.g.:

1 r4 .Dump( ” r4 ” ) ;

• Only at this stage the successively grown query is sent to the

server (SQL, Odata)!

12 / 17

Q&E Basic CAVEAT Joins

Deferred evaluation

• The SQL queries are built in-memory and kept there until

there is a imperative “need” to process the results

• The following lines only contribute towards a big query, which

is NOT yet sent!

1 var r1 = Orders . OrderBy ( o = > o . OrderID ) ; 2 var r2 = r1 . Skip ( 4 ) ; 3 var r3 = r2 . Take ( 2 ) ; 4 var r4 = r3 . S e l e c t ( o = > new { o . OrderID , . . . } ) ;

• NOTHING will happen, until we run a processing

statement,e.g.:

1 r4 .Dump( ” r4 ” ) ;

• Only at this stage the successively grown query is sent to the

server (SQL, Odata)!

12 / 17

Q&E Basic CAVEAT Joins

Count – CAVEAT

• What is returned and what is sent to the SQL/Odata server

for this C# code (and why)?

1 var count2 = Orders . AsEnumerable ( ) . Count ( ) ; 2 count2 .Dump ( ” count2 ” ) ;

• If Orders corresponds to the population of an SQL table, then

the following SQL query is sent to the SQL server:

1 SELECT [ t0 ] . ∗ 2 FROM [ Orders ] AS [ t0 ]

• If Orders corresponds to an OData entity, then this REST

URL is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders

13 / 17

Q&E Basic CAVEAT Joins

Count – CAVEAT

• What is returned and what is sent to the SQL/Odata server

for this C# code (and why)?

1 var count2 = Orders . AsEnumerable ( ) . Count ( ) ; 2 count2 .Dump ( ” count2 ” ) ;

• If Orders corresponds to the population of an SQL table, then

the following SQL query is sent to the SQL server:

1 SELECT [ t0 ] . ∗ 2 FROM [ Orders ] AS [ t0 ]

• If Orders corresponds to an OData entity, then this REST

URL is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders

13 / 17

Q&E Basic CAVEAT Joins

Count – CAVEAT

• What is returned and what is sent to the SQL/Odata server

for this C# code (and why)?

1 var count2 = Orders . AsEnumerable ( ) . Count ( ) ; 2 count2 .Dump ( ” count2 ” ) ;

• If Orders corresponds to the population of an SQL table, then

the following SQL query is sent to the SQL server:

1 SELECT [ t0 ] . ∗ 2 FROM [ Orders ] AS [ t0 ]

• If Orders corresponds to an OData entity, then this REST

URL is sent to the OData service:

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders

13 / 17

Q&E Basic CAVEAT Joins

Count – CAVEAT

• AsEnumerable() is just a type cast to IEnumerable (no

physical change), but it does change the method resolution!

• Instead of calling Queryable.Count(), the complier generates a

call to Enumerable.Count(), which works on in-memory sequences!

• Thus, the whole population of Orders is brought into the

memory and counted there – very inefficient!

14 / 17

Q&E Basic CAVEAT Joins

Count – CAVEAT

• AsEnumerable() is just a type cast to IEnumerable (no

physical change), but it does change the method resolution!

• Instead of calling Queryable.Count(), the complier generates a

call to Enumerable.Count(), which works on in-memory sequences!

• Thus, the whole population of Orders is brought into the

memory and counted there – very inefficient!

14 / 17

Q&E Basic CAVEAT Joins

Count – CAVEAT

• AsEnumerable() is just a type cast to IEnumerable (no

physical change), but it does change the method resolution!

• Instead of calling Queryable.Count(), the complier generates a

call to Enumerable.Count(), which works on in-memory sequences!

• Thus, the whole population of Orders is brought into the

memory and counted there – very inefficient!

14 / 17

Q&E Basic CAVEAT Joins

Outline

1 Queryables and Enumerables 2 Basic methods 3 Deferred evaluation and CAVEAT 4 Joins

15 / 17

Q&E Basic CAVEAT Joins

Joins

• Joins: using the Join operator (SQL only – NOT OData v3)

1 var j o i n 1 = Orders 2 . OrderBy ( o = > o . OrderID ) . Take (3) 3 . Join ( Customers , 4

• =

> o . CustomerID , c = > c . CustomerID , 5 (o , c ) = > new {o . OrderID , c . CompanyName } ) ;

• Joins: by navigation (SQL, OData)

1 var j o i n 2 = Orders 2 . OrderBy ( o = > o . OrderID ) . Take (3) 3 . S e l e c t ( o = > 4 new {o . OrderID ,

• . Customer . CompanyName } ) ;

5 j o i n 2 .Dump ( ” j o i n 2 ” ) ;

16 / 17

Q&E Basic CAVEAT Joins

Joins

• SQL (Join operator, navigation)

1 SELECT [ t1 ] . [ OrderID ] , [ t2 ] . [ CompanyName ] 2 FROM ( 3 SELECT TOP (3) [ t0 ] . [ OrderID ] , [ t0 ] . [ CustomerID ] 4 FROM [ Orders ] AS [ t0 ] 5 ORDER BY [ t0 ] . [ OrderID ] 6 ) AS [ t1 ] 7 LEFT OUTER JOIN [ Customers ] AS [ t2 ] 8 ON [ t2 ] . [ CustomerID ] = [ t1 ] . [ CustomerID ] 9 ORDER BY [ t1 ] . [ OrderID ]

• URL (navigation)

1 http : // l o c a l h o s t :8181/ Wcf1 . svc / Orders ()? 2 $orderby=OrderID&$top=3&$expand=Customer& 3 $select=OrderID , Customer/CompanyName

17 / 17