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 IEnumerable < out T > { . . . } public interface 2 Enumerable { public s t a t i c class 3 Where and other IEnumerable < T > extension methods 4 } 5 6 IQueryable < out T > : IEnumerable < T > { public interface 7 Provider ; public 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 on 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 on 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 , o . 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 , o . 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 count = Orders . Count ( ) ; var 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 count = Orders . Count ( ) ; var 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 count = Orders . Count ( ) ; var 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 r1 = Orders . OrderBy ( o = > o . OrderID ) ; var 2 r2 = r1 . Skip ( 4 ) ; var 3 r3 = r2 . Take ( 2 ) ; var 4 r4 = r3 . S e l e c t ( o = > new { o . OrderID , . . . } ) ; var • 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 r1 = Orders . OrderBy ( o = > o . OrderID ) ; var 2 r2 = r1 . Skip ( 4 ) ; var 3 r3 = r2 . Take ( 2 ) ; var 4 r4 = r3 . S e l e c t ( o = > new { o . OrderID , . . . } ) ; var • 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