# Archives

Archives / 2019

Monad is a powerful structure, with the LINQ support in C# language, monad enables chaining operations to build fluent workflow, which can be pure. With these features, monad can be used to manage I/O, state changes, exception handling, shared environment, logging/tracing, and continuation, etc., in the functional paradigm.

• ## Category Theory via C# (7) Monad and LINQ to Monads

As fore mentioned endofunctor category can be monoidal (the entire category. Actually, an endofunctor In the endofunctor category can be monoidal too. This kind of endofunctor is called monad. Monad is another important algebraic structure in category theory and LINQ. Formally, monad is an endofunctor equipped with 2 natural transformations:

• ## Category Theory via C# (6) Monoidal Functor and Applicative Functor

Given monoidal categories (C, ⊗, IC) and (D, ⊛, ID), a strong lax monoidal functor is a functor F: C → D equipped with:

• ## Category Theory via C# (5) Bifunctor

A functor is the mapping from 1 object to another object, with a “Select” ability to map 1 morphism to another morphism. A bifunctor (binary functor), as the name implies, is the mapping from 2 objects and from 2 morphisms. Giving category C, D and E, bifunctor F from category C, D to E is a structure-preserving morphism from C, D to E, denoted F: C × D → E:

• ## Category Theory via C# (4) Natural Transformation

If F: C → D and G: C → D are both functors from categories C to category D, the mapping from F to G is called natural transformation and denoted α: F ⇒ G. α: F ⇒ G is actually family of morphisms from F to G, For each object X in category C, there is a specific morphism αX: F(X) → G(X) in category D, called the component of α at X. For each morphism m: X → Y in category C and 2 functors F: C → D, G: C → D, there is a naturality square in D:

• ## Category Theory via C# (3) Functor and LINQ to Functors

In category theory, functor is a mapping from category to category. Giving category C and D, functor F from category C to D is a structure-preserving morphism from C to D, denoted F: C → D:

• ## Category Theory via C# (2) Monoid

Monoid is an important algebraic structure in category theory. A monoid M is a set M equipped with a binary operation ⊙ and a special element I, denoted 3-tuple (M, ⊙, I), where

• ## Category Theory via C# (1) Fundamentals

Category theory is a theoretical framework to describe abstract structures and relations in mathematics, first introduced by Samuel Eilenberg and Saunders Mac Lane in 1940s. It examines mathematical concepts and properties in an abstract way, by formalizing them as collections of items and their relations. Category theory is abstract, and called "general abstract nonsense" by Norman Steenrod; It is also general, therefore widely applied in many areas in mathematics, physics, and computer science, etc. For programming, category theory is the algebraic theory of types and functions, and also the rationale and foundation of LINQ and any functional programming. This chapter discusses category theory and its important concepts, including category, morphism, natural transform, monoid, functor, and monad, etc. These general abstract concepts will be demonstrated with intuitive diagrams and specific C# and LINQ examples. These knowledge also helps building a deep understanding of functional programming in C# or other languages, since any language with types and functions is a category-theoretic structure.

• ## Lambda Calculus via C# (8) Undecidability of Equivalence

All the previous parts demonstrated what lambda calculus can do – defining functions to model the computing, applying functions to execute the computing, implementing recursion, encoding data types and data structures, etc. Lambda calculus is a powerful tool, and it is Turing complete. This part discuss some interesting problem that cannot be done with lambda calculus – asserting whether 2 lambda expressions are equivalent.

• ## Lambda Calculus via C# (7) Fixed Point Combinator and Recursion

p is the fixed point (aka invariant point) of function f if and only if:

• ## Lambda Calculus via C# (6) Combinatory Logic

In lambda calculus, the primitive is function, which can have free variables and bound variables. Combinatory logic was introduced by Moses Schönfinkel and Haskell Curry in 1920s. It is equivalent variant lambda calculus, with combinator as primitive. A combinator can be viewed as an expression with no free variables in its body.

• ## Lambda Calculus via C# (5) List

In lambda calculus and Church encoding, there are various ways to represent a list with anonymous functions.

• ## Lambda Calculus via C# (4) Tuple and Signed Numeral

Besides modeling values like Boolean and numeral, anonymous function can also model data structures. In Church encoding, Church pair is an approach to use functions to represent a tuple of 2 items.

• ## Lambda Calculus via C# (3) Numeral, Arithmetic and Predicate

Anonymous functions can also model numerals and their arithmetic. In Church encoding, a natural number n is represented by a function that calls a given function for n times. This representation is called Church Numeral.

• ## Lambda Calculus via C# (2) Church Encoding: Boolean and Logic

Lambda calculus is a formal system for function definition and function application, so in lambda calculus, the only primitive is anonymous function. Anonymous function is actually very powerful. With an approach called Church encoding. data and operation can be modeled by higher-order anonymous functions and their application. Church encoding is named after Alonzo Church, who first discovered this approach. This part discusses Church Boolean - modeling Boolean values and logic operators with functions.

• ## Lambda Calculus via C# (1) Fundamentals

Lambda calculus (aka λ-calculus) is a theoretical framework to describe function definition, function application, function recursion, and uses functions and function application to express computation. It is a mathematics formal system, but can also be viewed as a smallest programming language that can express and evaluate any computable function. As an universal model of computation, lambda calculus is important in programming language theory, and especially it is the foundation of functional programming. The knowledge of lambda calculus greatly helps understanding functional programming, LINQ, C# and other functional languages.

• ## Entity Framework Core and LINQ to Entities in Depth (8) Optimistic Concurrency

Conflicts can occur if the same data is read and changed concurrently. Generally, there are 2 concurrency control approaches:

• ## Entity Framework Core and LINQ to Entities in Depth (7) Data Changes and Transactions

Besides LINQ to Entities queries, EF Core also provides rich APIs for data changes, with imperative paradigm.

• ## Entity Framework Core and LINQ to Entities in Depth (6) Query Data Loading

After translated to SQL, in LINQ to Entities, sequence queries returning IQueryable<T> implements deferred execution too.

• ## Entity Framework Core and LINQ to Entities in Depth (5) Query Translation Implementation

Regarding different database systems can have different query languages or different query APIs, EF Core implement a provider model to work with different kinds of databases. In EF Core, the base libraries are the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Relational NuGet packages. Microsoft.EntityFrameworkCore provides the database provider contracts as Microsoft.EntityFrameworkCore.Storage.IDatabaseProviderServices interface. And the SQL database support is implemented by the Microsoft.EntityFrameworkCore,SqlServer NuGet package, which provides Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseProviderServices type to implement IDatabaseProviderServices. There are other libraries for different databases, like Microsoft.EntityFrameworkCore.SQLite NuGet package for SQLite, etc.

• ## Entity Framework Core and LINQ to Entities in Depth (4) Query Methods (Operators)

This part discusses how to query SQL database with the defined mapping entities. In EF Core, LINQ to Entities supports most of the standard queries provided by Queryable:

1. Sequence queries: return a new IQueryable<T> source

o Filtering (restriction): Where, OfType*

o Mapping (projection): Select

o Generation: DefaultIfEmpty*

o Grouping: GroupBy*

o Join: Join, GroupJoin, SelectMany, Select

o Concatenation: Concat*

o Set: Distinct, GroupBy*, Union*, Intersect*, Except*

o Convolution: Zip

o Partitioning: Take, Skip, TakeWhile, SkipWhile

o Ordering: OrderBy*, ThenBy, OrderByDescending*, ThenByDescending, Reverse

o Conversion: Cast, AsQueryable

2. Value queries: return a single value

o Element: First, FirstOrDefault, Last*, LastOrDefault*, ElementAt, ElementAtOrDefault, Single, SingleOrDefault

o Aggregation: Aggregate, Count, LongCount, Min, Max, Sum, Average*

o Quantifier: All, Any, Contains

o Equality: SequenceEqual

In above list:

· The crossed queries are not supported by LINQ to Entities (the list provided by MDSN is not up to date), because they cannot be translated to proper SQL database operations. For example, SQL database has no built-in Zip operation support. Calling these crossed queries throws NotSupportedException at runtime

· The underlined queries have some overloads supported by LINQ to Entities, and other overloads not supported:

o For GroupBy, Join, GroupJoin, Distinct, Union, Intersect, Except, Contains, the overloads accepting IEqualityComparer<T> parameter are not supported, because apparently IEqualityComparer<T> has no equivalent SQL translation

o For OrderBy, ThenBy, OrderByDescending, ThenByDescending, the overloads with IComparer<T> parameter are not supported

o For Where, Select, SelectMany, the indexed overloads are not supported

· In EF Core, the queries marked with * can execute the query locally in some cases, without being translated to SQL.

For LINQ to Entities, apparently these queries enable fluent chaining, implement the same LINQ query expression pattern as LINQ to Objects and Parallel LINQ. So in this part, most of the LINQ to Entities queries are demonstrated with queries.

#### Sequence queries

Similar to the other kinds of LINQ, LINQ to Entities implements deferred execution for these queries returning IQueryable<T>. The SQL query is translated and executed only when trying to pull the result value from IQueryable<T> for the first time.

##### Filtering (restriction)

EF Core translates Where function call to WHERE clause in SQL, and translates the predicate expression tree (again, not predicate function) to the condition in WHERE clause. The following example queries categories with ProductCategoryID greater than 0:

{

IQueryable<ProductCategory> categories = source.Where(category => category.ProductCategoryID > 0); // Define query.

categories.WriteLines(category => category.Name); // Execute query.

// SELECT [category].[ProductCategoryID], [category].[Name]

// FROM [Production].[ProductCategory] AS [category]

// WHERE [category].[ProductCategoryID] > 0

}

When WriteLines executes, it pulls the results from the query represented by IQueryable<ProductCategory>. At this moment, the query is translated to SQL, and executed in database, then SQL execution results are read by EF Core and yielded.

The C# || operator in the predicate expression tree is translated to SQL OR operator in WHERE clause:

{

IQueryable<ProductCategory> categories = source.Where(category =>

category.ProductCategoryID < 2 || category.ProductCategoryID > 3); // Define query.

categories.WriteLines(category => category.Name); // Execute query.

// SELECT [category].[ProductCategoryID], [category].[Name]

// FROM [Production].[ProductCategory] AS [category]

// WHERE ([category].[ProductCategoryID] < 2) OR ([category].[ProductCategoryID] > 3)

}

Similarly, the C# && operator is translated to SQL AND operator:

{

IQueryable<ProductCategory> categories = source.Where(category =>

category.ProductCategoryID > 0 && category.ProductCategoryID < 5); // Define query.

categories.WriteLines(category => category.Name); // Execute query.

// SELECT [category].[ProductCategoryID], [category].[Name]

// FROM [Production].[ProductCategory] AS [category]

// WHERE ([category].[ProductCategoryID] > 0) AND ([category].[ProductCategoryID] < 5)

}

Multiple Where calls are also translated to one single WHERE clause with AND:

{

IQueryable<ProductCategory> categories = source

.Where(category => category.ProductCategoryID > 0)

.Where(category => category.ProductCategoryID < 5); // Define query.

categories.WriteLines(category => category.Name); // Execute query.

// SELECT [category].[ProductCategoryID], [category].[Name]

// FROM [Production].[ProductCategory] AS [category]

// WHERE ([category].[ProductCategoryID] > 0) AND ([category].[ProductCategoryID] < 5)

}

The other filtering query, OfType, can be used for entity types in inheritance hierarchy. And it is equivalent to Where query with is operator. The following examples both query sales transactions from all transactions:

{

IQueryable<TransactionHistory> transactions = source.Where(transaction => transaction is SalesTransactionHistory); // Define query.

transactions.WriteLines(transaction => \$"{transaction.GetType().Name} {transaction.TransactionDate} {transaction.ActualCost}"); // Execute query.

// SELECT [transaction].[TransactionID], [transaction].[ActualCost], [transaction].[ProductID], [transaction].[Quantity], [transaction].[TransactionDate], [transaction].[TransactionType]

// FROM [Production].[TransactionHistory] AS [transaction]

// WHERE [transaction].[TransactionType] IN (N'W', N'S', N'P') AND ([transaction].[TransactionType] = N'S')

}

{

IQueryable<WorkTransactionHistory> transactions = source.OfType<WorkTransactionHistory>(); // Define query.

transactions.WriteLines(transaction => \$"{transaction.GetType().Name} {transaction.TransactionDate} {transaction.ActualCost}"); // Execute query.

// SELECT [t].[TransactionID], [t].[ActualCost], [t].[ProductID], [t].[Quantity], [t].[TransactionDate], [t].[TransactionType]

// FROM [Production].[TransactionHistory] AS [t]

// WHERE [t].[TransactionType] = N'W'

}

When primitive type is specified for OfType, it works locally. The following example queries products with ProductSubcategoryID not null:

{

IQueryable<int> products = source.Select(product => product.ProductSubcategoryID).OfType<int>(); // Define query.

products.ToArray().Length.WriteLine(); // Execute query.

// SELECT [p].[ProductSubcategoryID]

// FROM [Production].[Product] AS [p]

}

In EF Core, the above query is translated to a basic SELECT statement without filtering. EF Core executes the translated SQL to query the specified nullable int column of all rows to local, then the int results are locally filtered from all the nullable int results.

##### Mapping (projection)

In above queries, Queryable.Select is not called, and the query results are entities. So in the translated SQL, the SELECT clause queries all the mapped columns in order to construct the result entities. When Select is called, the selector expression tree is translated into SELECT clause. The following example queries persons’ full names by concatenating the first name and last name:

{

IQueryable<string> names = source.Select(person =>

person.FirstName + " " + person.LastName); // Define query.

names.WriteLines(); // Execute query.

// SELECT ([person].[FirstName] + N' ') + [person].[LastName]

// FROM [Person].[Person] AS [person]

}

In EF Core, Select also work with anonymous type. For example:

{

var products = source.Select(product =>

new { Name = product.Name, IsExpensive = product.ListPrice > 1_000 }); // Define query.

products.WriteLines(); // Execute query.

// SELECT [product].[Name], CASE

// WHEN [product].[ListPrice] > 1000.0

// THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

// END

// FROM [Production].[Product] AS [product]

}

In EF Core, Select supports entity type too:

{

IQueryable<Product> products = source

.Where(product => product.ListPrice > 1_000)

.Select(product => new Product()

{

ProductID = product.ProductID,

Name = product.Name

}); // Define query.

products.WriteLines(product => \$"{product.ProductID}: {product.Name}"); // Execute query.

// SELECT [product].[ProductID], [product].[Name]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 1000.0

}

##### Generation

As fore mentioned, DefaultIfEmpty is the only built-in generation query:

{

IQueryable<ProductCategory> categories = source

.Where(category => category.ProductCategoryID < 0)

.DefaultIfEmpty(); // Define query.

categories.ForEach( // Execute query.

category => (category == null).WriteLine()); // True

// SELECT [t].[ProductCategoryID], [t].[Name]

// FROM (

// SELECT NULL AS [empty]

// ) AS [empty]

// LEFT JOIN (

// SELECT [category].[ProductCategoryID], [category].[Name]

// FROM [Production].[ProductCategory] AS [category]

// WHERE [category].[ProductCategoryID] < 0

// ) AS [t] ON 1 = 1

}

In the above query, Where function call is translated to SQL query with WHERE clause. Since DefaultIfEmpty should yield at least 1 entity, it is translated to LEFT JOIN with a single row table on a condition that always holds, so that the final query result is guaranteed to have at least 1 row. Here Where filters out all entities, in another word, the right table of LEFT JOIN has no rows, so the LEFT JOIN results 1 row, where all columns are NULL, including primary key. Therefore, DefaultIfEmpty yields a null entity. Besides entity type, DefaultIfEmpty works with primitive type in the same way.

The other DefaultIfEmpty overload accepts a specified default value. EF Core does not translate it to SQL, but execute the query logic locally. For example:

{

ProductCategory @default = new ProductCategory() { Name = nameof(ProductCategory) };

IQueryable<ProductCategory> categories = source

.Where(category => category.ProductCategoryID < 0)

.DefaultIfEmpty(@default); ; // Define query.

categories.WriteLines( // Execute query.

category => category?.Name); // ProductCategory

// SELECT [category].[ProductCategoryID], [category].[Name]

// FROM [Production].[ProductCategory] AS [category]

// WHERE [category].[ProductCategoryID] < 0

}

Here the source query for DefaultIfEmpty is translated to SQL and executed, then EF Core reads the results to local, and detect the results locally. If there is no result row, the specified default value is used. DefaultIfEmpty works for specified default primitive value locally too.

{

IQueryable<int> categories = source

.Where(category => category.ProductCategoryID < 0)

.Select(category => category.ProductCategoryID)

.DefaultIfEmpty(-1); // Define query.

categories.WriteLines(); // Execute query.

// SELECT [category].[ProductCategoryID]

// FROM [Production].[ProductCategory] AS [category]

// WHERE [category].[ProductCategoryID] < 0

}

Notice the default value –1 is translated into the remote SQL query. It is the query result if the right table of left outer join is empty. So there is no local query or local detection executed.

Just like in LINQ to Objects, DefaultIfEmpty can also be used to implement outer join, which is discussed later.

##### Grouping

When Group query is not used with aggregation query, EF Core executes grouping locally. For example. The following examples group the subcategories by category:

{

IQueryable<ProductSubcategory> grouped = source

.GroupBy(keySelector: subcategory => subcategory.ProductCategoryID)

.SelectMany(group => group); // Define query.

grouped.WriteLines(subcategory => subcategory.Name); // Execute query.

// SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]

// FROM [Production].[ProductSubcategory] AS [subcategory]

// ORDER BY [subcategory].[ProductCategoryID]

}

{

IQueryable<IGrouping<int, string>> groups = source.GroupBy(

keySelector: subcategory => subcategory.ProductCategoryID,

elementSelector: subcategory => subcategory.Name); // Define query.

groups.WriteLines(group => \$"{group.Key}: {string.Join(", ", group)}"); // Execute query.

// SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]

// FROM [Production].[ProductSubcategory] AS [subcategory]

// ORDER BY [subcategory].[ProductCategoryID]

}

EF Core only translates GroupBy an additional ORDER BY clause with the grouping key, so that when reading the SQL execution results to local, the subcategories appears group by group.

When GroupBy is used with supported aggregation query, it is translated to GROUP BY clause. This can be done with a GroupBy overload accepting a result selector, or equivalently an additional Select query. The following examples call aggregation query Count to flatten the results, and they have identical translation:

{

var groups = source.GroupBy(

keySelector: subcategory => subcategory.ProductCategoryID,

elementSelector: subcategory => subcategory.Name,

resultSelector: (key, group) => new { CategoryID = key, SubcategoryCount = group.Count() }); // Define query.

groups.WriteLines(); // Execute query.

// SELECT [subcategory].[ProductCategoryID] AS [CategoryID], COUNT(*) AS [SubcategoryCount]

// FROM [Production].[ProductSubcategory] AS [subcategory]

// GROUP BY [subcategory].[ProductCategoryID]

}

{

var groups = source

.GroupBy(

keySelector: subcategory => subcategory.ProductCategoryID,

elementSelector: subcategory => subcategory.Name)

.Select(group => new { CategoryID = group.Key, SubcategoryCount = group.Count() }); // Define query.

groups.WriteLines(); // Execute query.

// SELECT [subcategory].[ProductCategoryID] AS [CategoryID], COUNT(*) AS [SubcategoryCount]

// FROM [Production].[ProductSubcategory] AS [subcategory]

// GROUP BY [subcategory].[ProductCategoryID]

}

GroupBy’s key selector can return anonymous type with multiple properties to support grouping by multiple keys:

{

var groups = source

.GroupBy(

keySelector: product => new

{

ProductSubcategoryID = product.ProductSubcategoryID,

ListPrice = product.ListPrice

},

resultSelector: (key, group) => new

{

ProductSubcategoryID = key.ProductSubcategoryID,

ListPrice = key.ListPrice,

Count = group.Count()

})

.Where(group => group.Count > 1); // Define query.

groups.WriteLines(); // Execute query.

// SELECT [product].[ProductSubcategoryID], [product].[ListPrice], COUNT(*) AS [Count]

// FROM [Production].[Product] AS [product]

// GROUP BY [product].[ProductSubcategoryID], [product].[ListPrice]

// HAVING COUNT(*) > 1

}

The additional Where query is translated to HAVING clause, as expected.

##### Join
###### Inner join

Similar to LINQ to Objects, Join is provided for inner join. The following example simply join the subcategories and categories with foreign key:

{

var categorySubcategories = outer.Join(

inner: inner,

outerKeySelector: category => category.ProductCategoryID,

innerKeySelector: subcategory => subcategory.ProductCategoryID,

resultSelector: (category, subcategory) =>

new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// join subcategory in inner

// on category.ProductCategoryID equals subcategory.ProductCategoryID

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

// SELECT [category].[Name], [subcategory].[Name]

// FROM [Production].[ProductCategory] AS [category]

// INNER JOIN [Production].[ProductSubcategory] AS [subcategory] ON [category].[ProductCategoryID] = [subcategory].[ProductCategoryID]

}

Join’s key selectors can return anonymous type to join with multiple keys:

{

var transactions = outer.Join(

inner: inner,

outerKeySelector: product =>

new { ProductID = product.ProductID, UnitPrice = product.ListPrice },

innerKeySelector: transaction =>

new { ProductID = transaction.ProductID, UnitPrice = transaction.ActualCost / transaction.Quantity },

resultSelector: (product, transaction) =>

new { Name = product.Name, Quantity = transaction.Quantity }); // Define query.

// var transactions =

// on new { ProductID = product.ProductID, UnitPrice = product.ListPrice }

// equals new { ProductID = transaction.ProductID, UnitPrice = transaction.ActualCost / transaction.Quantity }

// select new { Name = product.Name, Quantity = transaction.Quantity };

transactions.WriteLines(); // Execute query.

// SELECT [product].[Name], [transaction].[Quantity]

// FROM [Production].[Product] AS [product]

// INNER JOIN [Production].[TransactionHistory] AS [transaction] ON ([product].[ProductID] = [transaction].[ProductID]) AND ([product].[ListPrice] = ([transaction].[ActualCost] / [transaction].[Quantity]))

// WHERE [transaction].[TransactionType] IN (N'W', N'S', N'P')

}

Just like LINQ to Objects, inner join can be done by SelectMany, Select, and GroupJoin as well. In the following example, Select returns hierarchical data, so an additional SelectMany can flatten the result:

{

var categorySubcategories = outer

.Select(category => new

{

Category = category,

Subcategories = inner

.Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID)

// LEFT OUTER JOIN if DefaultIfEmpty is called.

})

.SelectMany(

collectionSelector: category => category.Subcategories,

resultSelector: (category, subcategory) =>

new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// select new

// {

// Category = category,

// Subcategories = from subcategory in inner

// where category.ProductCategoryID == subcategory.ProductCategoryID

// select subcategory

// } into category

// from subcategory in category.Subcategories

// select new { Category = category.Category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

// SELECT [category].[Name], [subcategory].[Name]

// FROM [Production].[ProductCategory] AS [category]

// CROSS JOIN [Production].[ProductSubcategory] AS [subcategory]

// WHERE [category].[ProductCategoryID] = [subcategory].[ProductCategoryID]

}

EF Core translates the above query to CROOS JOIN with WHERE clause, which is equivalent to the previous INNER JOIN query, with the same query plan.

The following example implement the same inner join directly with SelectMany. Its SQL translation is the same INNER JOIN as the first Join example:

{

var categorySubcategories = outer

.SelectMany(

collectionSelector: category => inner

.Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID),

// LEFT OUTER JOIN if DefaultIfEmpty is called.

resultSelector: (category, subcategory) =>

new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// from subcategory in (from subcategory in inner

// where category.ProductCategoryID == subcategory.ProductCategoryID

// select subcategory)

// select new { Category = category.Name, Subcategory = subcategory.Name };

// Or equivalently:

// var categorySubcategories =

// from category in outer

// from subcategory in inner

// where category.ProductCategoryID == subcategory.ProductCategoryID

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

}

The above Select and SelectMany has a Where subquery to filter the related entities to join with. The Where subquery can be substituted by collection navigation property. After the substitution, the queries are translated to the same INNER JOIN as the first Join example:

{

var categorySubcategories = outer

.Select(category => new { Category = category, Subcategories = category.ProductSubcategories })

.SelectMany(

collectionSelector: category => category.Subcategories,

// LEFT OUTER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// select new { Category = category, Subcategories = category.ProductSubcategories } into category

// from subcategory in category.Subcategories

// select new { Category = category.Category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

}

{

var categorySubcategories = outer.SelectMany(

collectionSelector: category => category.ProductSubcategories,

// LEFT OUTER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// from subcategory in category.ProductSubcategories

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

}

GroupJoin also returns hierarchical result, so again an additional SelectMany can flatten the result. The following example still has the same INNER JOIN translation as the first Join example:

{

var categorySubcategories = outer

.GroupJoin(

inner: inner,

outerKeySelector: category => category.ProductCategoryID,

innerKeySelector: subcategory => subcategory.ProductCategoryID,

resultSelector: (category, subcategories) =>

new { Category = category, Subcategories = subcategories })

.SelectMany(

collectionSelector: category => category.Subcategories,

// LEFT OUTER JOIN if DefaultIfEmpty is called.

resultSelector: (category, subcategory) =>

new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// join subcategory in inner

// on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories

// from subcategory in subcategories

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

}

Navigation property makes it very easy to join entities with relationship. The following example inner joins  3 entity types, where 2 entity types have many-to-many relationship with a junction entity type:

{

var productPhotos = source.SelectMany(

collectionSelector: product => product.ProductProductPhotos,

resultSelector: (product, productProductPhoto) => new

{

Product = product.Name,

Photo = productProductPhoto.ProductPhoto.LargePhotoFileName

}); // Define query.

// var productPhotos =

// from product in source

// from productProductPhoto in product.ProductProductPhotos

// select new { Product = product.Name, Photo = productProductPhoto.ProductPhoto.LargePhotoFileName };

productPhotos.WriteLines(); // Execute query.

// SELECT [product].[Name], [product.ProductProductPhotos.ProductPhoto].[LargePhotoFileName]

// FROM [Production].[Product] AS [product]

// INNER JOIN [Production].[ProductProductPhoto] AS [product.ProductProductPhotos] ON [product].[ProductID] = [product.ProductProductPhotos].[ProductID]

// INNER JOIN [Production].[ProductPhoto] AS [product.ProductProductPhotos.ProductPhoto] ON [product.ProductProductPhotos].[ProductPhotoID] = [product.ProductProductPhotos.ProductPhoto].[ProductPhotoID]

}

###### Left outer join

GroupJoin is provided for left outer join. The following example have categories to left outer join subcategories with foreign key, and the results have all categories with or without matching subcategories. It is translated to LEFT JOIN:

{

var categorySubcategories = outer

.GroupJoin(

inner: inner,

outerKeySelector: category => category.ProductCategoryID,

innerKeySelector: subcategory => subcategory.ProductCategoryID,

resultSelector: (category, subcategories) =>

new { Category = category, Subcategories = subcategories }); // Define query.

// var categorySubcategories =

// from category in outer

// join subcategory in inner

// on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories

// select new { Category = category, Subcategories = subcategories };

categorySubcategories.WriteLines(categorySubcategory =>

\$@"{categorySubcategory.Category.Name}: {string.Join(

", ", categorySubcategory.Subcategories.Select(subcategory => subcategory.Name))}"); // Execute query.

// SELECT [category].[ProductCategoryID], [category].[Name], [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]

// FROM [Production].[ProductCategory] AS [category]

// LEFT JOIN [Production].[ProductSubcategory] AS [subcategory] ON [category].[ProductCategoryID] = [subcategory].[ProductCategoryID]

// ORDER BY [category].[ProductCategoryID]

}

GroupJoin returns hierarchical results. So here the translated SQL also sorts the result by the key, so that EF Core can read the query results group by group. To have flattened results from GroupJoin, SelectMany can be called. As discussed in the LINQ to Objects chapter, an DefaultIfEmpty subquery is required (It becomes inner join if DefaultIfEmpty is missing). The following example has the same SQL translation as above, it just yields result by result instead of group by group.

{

var categorySubcategories = outer

.GroupJoin(

inner: inner,

outerKeySelector: category => category.ProductCategoryID,

innerKeySelector: subcategory => subcategory.ProductCategoryID,

resultSelector: (category, subcategories) =>

new { Category = category, Subcategories = subcategories }) // Define query.

.SelectMany(

collectionSelector: category => category.Subcategories

.DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Category, Subcategory = subcategory }); // Define query.

// var categorySubcategories =

// from category in outer

// join subcategory in inner

// on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories

// from subcategory in subcategories.DefaultIfEmpty()

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(categorySubcategory =>

\$"{categorySubcategory.Category.Name} {categorySubcategory.Subcategory?.Name}"); // Execute query.

}

Similar to inner join, left outer join can be done with Select and SelectMany too, with a DefaultIfEmpty subquery. The following queries have the same SQL translation:

{

var categorySubcategories = outer

.Select(category => new

{

Category = category,

Subcategories = inner

.Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID)

})

.SelectMany(

collectionSelector: category => category.Subcategories

.DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// select new

// {

// Category = category,

// Subcategories = from subcategory in inner

// where subcategory.ProductCategoryID == category.ProductCategoryID

// select subcategory

// } into category

// from subcategory in category.Subcategories.DefaultIfEmpty()

// select new { Category = category.Category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

// SELECT [category].[Name], [t1].[Name]

// FROM [Production].[ProductCategory] AS [category]

// CROSS APPLY (

// SELECT [t0].*

// FROM (

// SELECT NULL AS [empty]

// ) AS [empty0]

// LEFT JOIN (

// SELECT [subcategory0].*

// FROM [Production].[ProductSubcategory] AS [subcategory0]

// WHERE [category].[ProductCategoryID] = [subcategory0].[ProductCategoryID]

// ) AS [t0] ON 1 = 1

// ) AS [t1]

}

{

var categorySubcategories = outer

.SelectMany(

collectionSelector: category => inner

.Where(subcategory => category.ProductCategoryID == subcategory.ProductCategoryID)

.DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// from subcategory in (from subcategory in inner

// where category.ProductCategoryID == subcategory.ProductCategoryID

// select subcategory).DefaultIfEmpty()

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

}

In EF Core, the above 2 queries are both translated to CROSS APPLY, but this is logically equivalent to LEFT JOIN of the GroupJoin example.

As demonstrated for inner join, in the above Select and SelectMany queries, the Where subquery is equivalent to collection navigation property. EF Core support collection navigation property for left outer join with Select and SelectMany. The following queries are translated to the same LEFT JOIN query:

{

var categorySubcategories = outer

.Select(category => new { Category = category, Subcategories = category.ProductSubcategories })

.SelectMany(

collectionSelector: category => category.Subcategories

.DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// select new { Category = category, Subcategories = category.ProductSubcategories } into category

// from subcategory in category.Subcategories.DefaultIfEmpty()

// select new { Category = category.Category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

// SELECT [category].[Name] AS [Category], [category.ProductSubcategories].[Name] AS [Subcategory]

// FROM [Production].[ProductCategory] AS [category]

// LEFT JOIN [Production].[ProductSubcategory] AS [category.ProductSubcategories] ON [category].[ProductCategoryID] = [category.ProductSubcategories].[ProductCategoryID]

}

{

var categorySubcategories = outer.SelectMany(

collectionSelector: category => category.ProductSubcategories

.DefaultIfEmpty(), // INNER JOIN if DefaultIfEmpty is missing.

resultSelector: (category, subcategory) =>

new { Category = category.Name, Subcategory = subcategory.Name }); // Define query.

// var categorySubcategories =

// from category in outer

// from subcategory in category.ProductSubcategories.DefaultIfEmpty()

// select new { Category = category.Name, Subcategory = subcategory.Name };

categorySubcategories.WriteLines(); // Execute query.

}

###### Cross join

Just like LINQ to Objects, cross join can be done with SelectMany and Join. The following example queries the expensive products (list price greater than 2000) and cheap products (list price less than 100), and then cross join them to get all possible product bundles, where each bundle has one expensive product and one cheap product:

{

IQueryable<Product> outer = adventureWorks.Products.Where(product => product.ListPrice > 2000);

IQueryable<Product> inner = adventureWorks.Products.Where(product => product.ListPrice < 100);

var bundles = outer.SelectMany(

collectionSelector: expensiveProduct => inner,

resultSelector: (expensiveProduct, cheapProduct) =>

new { Expensive = expensiveProduct.Name, Cheap = cheapProduct.Name }); // Define query.

// var bundles =

// from outerProduct in outer

// from innerProduct in inner

// select new { Expensive = outerProduct.Name, Cheap = innerProduct.Name };

bundles.WriteLines(); // Execute query.

// SELECT [product].[Name], [product0].[Name]

// FROM [Production].[Product] AS [product]

// CROSS JOIN [Production].[Product] AS [product0]

// WHERE ([product].[ListPrice] > 2000.0) AND ([product0].[ListPrice] < 100.0)

}

The following implementation with Join is equivalent, just have the 2 key selectors always return equal values:

{

IQueryable<Product> outer = adventureWorks.Products.Where(product => product.ListPrice > 2000);

IQueryable<Product> inner = adventureWorks.Products.Where(product => product.ListPrice < 100);

var bundles = outer.Join(

inner: inner,

outerKeySelector: product => 1,

innerKeySelector: product => 1,

resultSelector: (outerProduct, innerProduct) =>

new { Expensive = outerProduct.Name, Cheap = innerProduct.Name }); // Define query.

// var bundles =

// from outerProduct in outer

// join innerProduct in inner

// on 1 equals 1

// select new { Expensive = outerProduct.Name, Cheap = innerProduct.Name };

bundles.WriteLines(); // Execute query.

// SELECT [product].[Name], [t].[Name]

// FROM [Production].[Product] AS [product]

// INNER JOIN (

// SELECT [product1].*

// FROM [Production].[Product] AS [product1]

// WHERE [product1].[ListPrice] < 100.0

// ) AS [t] ON 1 = 1

// WHERE [product].[ListPrice] > 2000.0

}

It is translated to INNER JOIN, which is equivalent to previous CROSS JOIN, with the same query plan.

##### Concatenation

The following example concatenates the cheap products and the expensive products, and query the products’ names:

{

IQueryable<Product> first = adventureWorks.Products.Where(product => product.ListPrice < 100);

IQueryable<Product> second = adventureWorks.Products.Where(product => product.ListPrice > 2000);

IQueryable<string> concat = first

.Concat(second)

.Select(product => product.Name); // Define query.

concat.WriteLines(); // Execute query.

// SELECT [product1].[ProductID], [product1].[ListPrice], [product1].[Name], [product1].[ProductSubcategoryID], [product1].[RowVersion]

// FROM [Production].[Product] AS [product1]

// WHERE [product1].[ListPrice] < 100.0

// SELECT [product2].[ProductID], [product2].[ListPrice], [product2].[Name], [product2].[ProductSubcategoryID], [product2].[RowVersion]

// FROM [Production].[Product] AS [product2]

// WHERE [product2].[ListPrice] > 2000.0

}

EF Core supports Concat for primitive type locally as well. In the above example, Select is called after Concat. It is logically equivalent to call Select before Concat, which works in EF Core:

{

.Where(product => product.ListPrice < 100)

.Select(product => product.Name);

.Where(product => product.ListPrice > 2000)

.Select(product => product.Name);

IQueryable<string> concat = first.Concat(second); // Define query.

concat.WriteLines(); // Execute query.

// SELECT [product].[Name]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] < 100.0

// SELECT [product0].[Name]

// FROM [Production].[Product] AS [product0]

// WHERE [product0].[ListPrice] > 2000.0

}

EF Core translates Concat’s 2 data sources to 2 SQL queries, reads the query results to local, and concatenates them locally.

##### Set

Distinct works with entity type and primitive type. It is translated to the DISTINCT keyword:

{

IQueryable<ProductCategory> distinct = source

.Select(subcategory => subcategory.ProductCategory)

.Distinct(); // Define query.

distinct.WriteLines(category => \$"{category.ProductCategoryID}: {category.Name}"); // Execute query.

// SELECT DISTINCT [subcategory.ProductCategory].[ProductCategoryID], [subcategory.ProductCategory].[Name]

// FROM [Production].[ProductSubcategory] AS [subcategory]

// INNER JOIN [Production].[ProductCategory] AS [subcategory.ProductCategory] ON [subcategory].[ProductCategoryID] = [subcategory.ProductCategory].[ProductCategoryID]

}

IQueryable<int> distinct = source

.Select(subcategory => subcategory.ProductCategoryID)

.Distinct(); // Define query.

distinct.WriteLines(); // Execute query.

// SELECT DISTINCT [subcategory].[ProductCategoryID]

// FROM [Production].[ProductSubcategory] AS [subcategory]

}

GroupBy returns groups with distinct keys, so in theory it can be used to query the same result as Distinct:

{

IQueryable<int> distinct = source.GroupBy(

keySelector: subcategory => subcategory.ProductCategoryID,

resultSelector: (key, group) => key); // Define query.

distinct.WriteLines(); // Execute query.

// SELECT [subcategory].[ProductCategoryID] AS [Key]

// FROM [Production].[ProductSubcategory] AS [subcategory]

// GROUP BY [subcategory].[ProductCategoryID]

}

However, as fore mentioned, in EF Core, GroupBy executes locally. The above example only queries grouping keys, however it reads all rows of  the table to local, which can be a performance issue.

GroupBy can also be used for more complex scenarios. The following example queries the full product entities with distinct list price:

{

IQueryable<Product> distinct = source.GroupBy(

keySelector: product => product.ListPrice,

resultSelector: (key, group) => group.FirstOrDefault()); // Define query.

distinct.WriteLines(); // Execute query.

// SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product]

// ORDER BY [product].[ListPrice]

}

Again, EF Core does not translate grouping to SQL. In this example, only 1 entities for each key is queried, but EF Core reads all rows to local, and execute the grouping logic locally.

EF Core supports Union for entity and primitive types locally.

{

.Where(product => product.ListPrice > 100);

.Where(product => product.ProductSubcategoryID == 1);

IQueryable<Product> union = first.Union(second); // Define query.

union.WriteLines(); // Execute query.

// SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 100.0

// SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product]

// [product0].[ProductSubcategoryID] = 1

}

{

.Where(product => product.ListPrice > 100)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice });

.Where(product => product.ProductSubcategoryID == 1)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice });

var union = first.Union(second); // Define query.

union.WriteLines(); // Execute query.

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 100.0

// SELECT [product0].[Name], [product0].[ListPrice]

// FROM [Production].[Product] AS [product0]

// WHERE [product0].[ProductSubcategoryID] = 1

}

EF Core executes Intersect and Except locally as well.

{

.Where(product => product.ListPrice > 100);

.Where(product => product.ListPrice < 2000);

IQueryable<Product> intersect = first.Intersect(second); // Define query.

intersect.WriteLines(); // Execute query.

// SELECT [product0].[ProductID], [product0].[ListPrice], [product0].[Name], [product0].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product0]

// WHERE [product0].[ListPrice] < 2000.0

// SELECT [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 100.0

}

{

.Where(product => product.ListPrice > 100)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice });

.Where(product => product.ListPrice > 2000)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice });

var except = first.Except(second); // Define query.

except.WriteLines(); // Execute query.

// SELECT [product0].[Name], [product0].[ListPrice]

// FROM [Production].[Product] AS [product0]

// WHERE [product0].[ListPrice] > 2000.0

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 100.0

}

##### Partitioning

Skip is translate to OFFSET filter:

{

IQueryable<string> names = source

.Select(product => product.Name)

.Skip(10); // Define query.

names.WriteLines(); // Execute query.

// exec sp_executesql N'SELECT [product].[Name]

// FROM [Production].[Product] AS [product]

// ORDER BY (SELECT 1)

// OFFSET @__p_0 ROWS',N'@__p_0 int',@__p_0=10

}

In SQL, OFFSET is considered to be a part of the ORDER BY clause, so here EF Core generates ORDERBY (SELECT 1) clause.

When Take is called without Skip, it is translate to TOP filter:

{

IQueryable<string> products = source

.Take(10)

.Select(product => product.Name); // Define query.

products.WriteLines(); // Execute query.

// exec sp_executesql N'SELECT TOP(@__p_0) [product].[Name]

// FROM [Production].[Product] AS [product]',N'@__p_0 int',@__p_0=10

}

When Take is called with Skip, they are translated to FETCH and OFFSET filters:

{

IQueryable<string> products = source

.OrderBy(product => product.Name)

.Skip(20)

.Take(10)

.Select(product => product.Name); // Define query.

products.WriteLines(); // Execute query.

// exec sp_executesql N'SELECT [product].[Name]

// FROM [Production].[Product] AS [product]

// ORDER BY (SELECT 1)

// OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=20,@__p_1=10

}

##### Ordering

OrderBy/OrderByDescending are translated to ORDER BY clause with without/with DESC, for example:

{

var products = source

.OrderBy(product => product.ListPrice)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.

products.WriteLines(); // Execute query.

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// ORDER BY [product].[ListPrice]

}

{

var products = source

.OrderByDescending(product => product.ListPrice)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.

products.WriteLines(); // Execute query.

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// ORDER BY [product].[ListPrice] DESC

}

To sort with multiple keys, call OrderBy/OrderByDescending and ThenBy/ThenByDescending:

{

var products = source

.OrderBy(product => product.ListPrice)

.ThenBy(product => product.Name)

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.

products.WriteLines(); // Execute query.

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// ORDER BY [product].[ListPrice], [product].[Name]

}

In EF Core, when the key selector returns anonymous type to sort by multiple keys, the sorting is executed locally:

{

var products = source

.OrderBy(product => new { ListPrice = product.ListPrice, Name = product.Name })

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice }); // Define query.

products.WriteLines(); // Execute query.

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// ORDER BY (SELECT 1)

}

Multiple OrderBy/OrderByDescending calls are translated to SQL reversely. The following example sort all products by list price, then sort all products again by subcategory, which is equivalent to sort all products by subcategory first, then sort products in the same subcategory by list price:

{

var products = source

.OrderBy(product => product.ListPrice)

.OrderBy(product => product.ProductSubcategoryID)

.Select(product => new

{

Name = product.Name,

ListPrice = product.ListPrice,

Subcategory = product.ProductSubcategoryID

}); // Define query.

products.WriteLines(); // Execute query.

// SELECT [product].[Name], [product].[ListPrice], [product].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product]

// ORDER BY [product].[ProductSubcategoryID], [product].[ListPrice]

}

##### Conversion

Cast can work with entity type. The following example casts base entity to derived entity:

{

IQueryable<TransactionHistory> transactions = source

.Where(product => product.ActualCost > 500)

.Cast<SalesTransactionHistory>(); // Define query.

transactions.WriteLines(transaction =>

\$"{transaction.GetType().Name}: {transaction.TransactionDate}"); // Execute query.

// SELECT [product].[TransactionID], [product].[ActualCost], [product].[ProductID], [product].[Quantity], [product].[TransactionDate], [product].[TransactionType]

// FROM [Production].[TransactionHistory] AS [product]

// WHERE [product].[TransactionType] IN (N'W', N'S', N'P') AND ([product].[ActualCost] > 500.0)

}

EF Core does not support Cast for primitive type.

Queryable has an additional query, AsQueryable, which accepts IEnumerable<T> and returns IQueryable<T>. Remember Enumerable.AsEnumerable can convert more derived sequence (like List<T>, IQueryable<T>, etc.) to IEnumerable<T>. So the Queryable.AsQueryable/Eumerable.AsEnumerable queries look similar to the ParallelEnumerable.AsParallel/ParallelEnumerable.AsSequential queries, which convert between sequential and parallel local queries at any point. However, AsQueryable/AsEnumerable usually do not convert freely between local and remote queries. The following is the implementation of AsEnumerable and AsQueryable:

namespace System.Linq

{

public static class Enumerable

{

public static IEnumerable<TSource> AsEnumerable<TSource>(this IEnumerable<TSource> source) => source;

}

public static class Queryable

{

public static IQueryable<TElement> AsQueryable<TElement>(this IEnumerable<TElement> source) =>

source as IQueryable<TElement> ?? new EnumerableQuery<TElement>(source);

}

}

AsQueryable accepts an IEnumerable<T> source. If the source is indeed an IQueryable<T> source, then do nothing and just return it; if not, wrap the source into an System.Linq.EnumerableQuery<T> instance, and return it. EnumerableQuery<T> is a special implementation of IQueryable<T>. If an IQueryable<T> query is an EnumerableQuery<T> instance, when this query is executed, it internally calls System.Linq.EnumerableRewriter to translate itself to local query, then execute the translated query locally. For example, AdventureWorks.Products return IQueryable<Product>, which is actually a DbSet<T> instance, so calling AsQueryable with AdventureWorks.Products does nothing and returns the DbSet<Product> instance itself, which can have its subsequent queries to be translated to SQL by EF Core. In contrast, calling AsQueryable with a T[] array returns an EnumerableQuery<T> wrapper, which is a local mocking of remote query and can have its subsequent queries to be translated to local queries, As a result, AsEnumerable can always convert a remote LINQ to Entities query to local LINQ to Objects query, but AsQueryable cannot always convert arbitrary local LINQ to Objects query to a remote LINQ to Entities query (and logically, an arbitrary local .NET data source cannot be converted to a remote data source like SQL database). For example:

{

var remoteAndLocal = source // DbSet<T>.

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice }) // Return EntityQueryable<T>.

.AsEnumerable() // Do nothing. Directly return the EntityQueryable<T> source.

.Where(product => product.ListPrice > 0) // Enumerable.Where. Return a generator wrapping the EntityQueryable<T> source.

.AsQueryable() // Return an EnumerableQuery<T> instance wrapping the source generator.

.OrderBy(product => product.Name); // Queryable.OrderBy. Return EnumerableQuery<T>.

remoteAndLocal.WriteLines();

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

var remote = source // DbSet<T>.

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice }) // Return EntityQueryable<T>.

.AsEnumerable() // Do nothing. Directly return the EntityQueryable<T> source.

.AsQueryable() // Do nothing. Directly return the EntityQueryable<T> source.

.Where(product => product.ListPrice > 0) // Still LINQ to Entities. Return EntityQueryable<T>.

.OrderBy(product => product.Name); // Still LINQ to Entities. Return EntityQueryable<T>.

remote.WriteLines();

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 0.0

// ORDER BY [product].[Name]

}

In the first query, the LINQ to Entities source is chained with Select, then AsEnumerable returns IEnumerable<T>, so the following Where is Enumerable.Where, and it returns a generator. Then AsQueryable detects if the generator is IQueryable<T>. Since the generator is not IQueryable<T>, AsQueryable returns a EnumerableQuery<T> wrapper, which can have the following OrderBy translated to local query. So in this entire query chaining, only Select, which is before AsEnumerable, can be translated to SQL and executed remotely, all the other queries are executed locally.

· The source is a DbSet<T> instance, which implements IQueryable<T> and represents the LINQ to Entities data source - rows in remote SQL database table.

· Queryable.Select is called on DbSet<T> source,  in this case it returns a Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable<T> instance in EF Core, which implements IQueryable<T> and represents LINQ to Entities query.

· Enumerable.AsEnumerable does nothing and directly returns its source, the EntityQueryable<T> instance

· Enumerable.Where is called, since AsEnumerable returns IEnumerable<T> type. Where returns a generator wrapping its source, the EntityQueryable<T> instance.

· Queryable.AsQueryable is called. Its source, the generator from Where, implements IEnumerable<T>, not IQueryable<T>, so AsQueryable return an EnumerableQuery<T> instance wrapping the generator. As fore mentioned, EnumerableQuery<T> has nothing to do with database.

· Queryable.OrderBy is called with EnumerableQuery<T> instance, in this case it returns another EnumerableQuery<T> instance, which has nothing to do with database either.

So the first query is a hybrid query. When it is executed, only Select is remote LINQ to Entities query and is translated to SQL. After AsEnumerable, Where goes local, then AsQueryable cannot convert back to remote LINQ to Entities query anymore. So, Where and OrderBy are both local queries, and not translated to SQL.

The second query is a special case, where AsEnumerable is chained with AsQueryable right away. In this case, AsEnumerable and AsQueryable both do nothing at all. The following Where and OrderBy are both LINQ to Entities queries, and translated to SQL along with Select.

#### Value query

Queries in this category accepts an IQueryable<T> source and returns a single value. As fore mentioned, the aggregation queries can be used with GroupBy. When value queries are called at the end of a LINQ to Entities query, they executes the query immediately.

##### Element

First and FirstOrDefault execute the LINQ to Entities queries immediately. They are translated to TOP(1) filter in the SELECT clause. If a predicate is provided, the predicate is translated to WHERE clause. For example:

{

string first = source

.Select(product => product.Name)

.First() // Execute query.

.WriteLine();

// SELECT TOP(1) [product].[Name]

// FROM [Production].[Product] AS [product]

}

{

var firstOrDefault = source

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice })

.FirstOrDefault(product => product.ListPrice > 5000); // Execute query.

firstOrDefault?.Name.WriteLine();

// SELECT TOP(1) [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 5000.0

}

As discussed in LINQ to Objects, Single and SingleOrDefault are more strict. They are translated to TOP(2) filter, so that, if there are 0 or more than 1 results, InvalidOperationException is thrown. Similar to First and FirstOrDefault, if a predicate is provided, it is translated to WHERE clause:

{

var single = source

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice })

.Single(product => product.ListPrice < 50); // Execute query.

\$"{single.Name}: {single.ListPrice}".WriteLine();

// SELECT TOP(2) [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] < 50.0

}

{

var singleOrDefault = source

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice })

.SingleOrDefault(product => product.ListPrice < 1); // Execute query.

singleOrDefault?.Name.WriteLine();

// SELECT TOP(2) [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] < 1.0

}

EF Core supports Last and LastOrDefault, locally. Again, if a predicate is provided, it is translated to WHERE clause:

{

Product last = source.Last(); // Execute query.

// SELECT [p].[ProductID], [p].[ListPrice], [p].[Name], [p].[ProductSubcategoryID]

// FROM [Production].[Product] AS [p]

\$"{last.Name}: {last.ListPrice}".WriteLine();

}

{

var lastOrDefault = source

.Select(product => new { Name = product.Name, ListPrice = product.ListPrice })

.LastOrDefault(product => product.ListPrice <= 0); // Execute query.

// SELECT [product].[Name], [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] <= 0.0

(lastOrDefault == null).WriteLine(); // True

}

The above examples can read many results from remote database to locally, and try to query the last result locally, which can cause performance issue.

##### Aggregation

Count/LongCount are translated to SQL aggregate functions COUNT/COUNT_BIG. if a is provided, it is translated to WHERE clause. The following examples query the System.Int32 count of categories, and the System.Int64 count of the products with list price greater than 0:

{

int count = source.Count().WriteLine(); // Execute query.

// SELECT COUNT(*)

// FROM [Production].[ProductCategory] AS [p]

}

{

long longCount = source.LongCount(product => product.ListPrice > 0).WriteLine(); // Execute query.

// SELECT COUNT_BIG(*)

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 0.0

}

Max/Min/Sum/Average are translated to MAX/MIN/SUM/AVG functions. The following examples query the latest ModifiedDate of photos, the lowest list price of products, and the total cost of transactions, and the average ListPrice of products:

{

DateTime max = source.Select(photo => photo.ModifiedDate).Max().WriteLine(); // Execute query.

// SELECT MAX([photo].[ModifiedDate])

// FROM [Production].[ProductPhoto] AS [photo]

}

{

decimal min = source.Min(product => product.ListPrice).WriteLine(); // Execute query.

// SELECT MIN([product].[ListPrice])

// FROM [Production].[Product] AS [product]

}

{

decimal sum = source.Sum(transaction => transaction.ActualCost).WriteLine(); // Execute query.

// SELECT SUM([transaction].[ActualCost])

// FROM [Production].[TransactionHistory] AS [transaction]

// WHERE [transaction].[TransactionType] IN (N'W', N'S', N'P')

}

{

decimal average = source.Select(product => product.ListPrice).Average().WriteLine(); // Execute query.

// SELECT AVG([product].[ListPrice])

// FROM [Production].[Product] AS [product]

}

##### Quantifier

EF Core supports Contains for entity type, locally.

{

Product single = source.Single(product => product.ListPrice == 20.24M); // Execute query.

// SELECT TOP(2) [product].[ProductID], [product].[ListPrice], [product].[Name], [product].[ProductSubcategoryID]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] = 20.24

bool contains = source

.Where(product => product.ProductSubcategoryID == 7)

.Contains(single).WriteLine(); // Execute query.

// exec sp_executesql N'SELECT CASE

// WHEN @__p_0_ProductID IN (

// SELECT [product].[ProductID]

// FROM [Production].[Product] AS [product]

// WHERE [product].[ProductSubcategoryID] = 7

// )

// THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

// END',N'@__p_0_ProductID int',@__p_0_ProductID=952

}

EF Core both support Contains for primitive types. In this case, Contains is translated to EXISTS predicate:

{

bool contains = source

.Select(product => product.ListPrice).Contains(100)

.WriteLine(); // Execute query.

// exec sp_executesql N'SELECT CASE

// WHEN @__p_0 IN (

// SELECT [product].[ListPrice]

// FROM [Production].[Product] AS [product]

// )

// THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

// END',N'@__p_0 decimal(3,0)',@__p_0=100

}

Any is also translated to EXISTS. If predicate is provided, it is translated to WHERE clause:

{

bool any = source.Any().WriteLine(); // Execute query.

// SELECT CASE

// WHEN EXISTS (

// SELECT 1

// FROM [Production].[Product] AS [p])

// THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

// END

}

{

bool any = source.Any(product => product.ListPrice > 10).WriteLine(); // Execute query.

// SELECT CASE

// WHEN EXISTS (

// SELECT 1

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] > 10.0)

// THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

// END

}

All is translated to NOT EXISTS, with the predicate translated to reverted condition in WHERE clause:

{

bool all = source.All(product => product.ListPrice > 10).WriteLine(); // Execute query.

// SELECT CASE

// WHEN NOT EXISTS (

// SELECT 1

// FROM [Production].[Product] AS [product]

// WHERE [product].[ListPrice] <= 10.0)

// THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

// END

}

Text:

• ## Entity Framework Core and LINQ to Entities in Depth (3) Logging and Tracing Queries

As fore mentioned, LINQ to Entities queries are translated to database queries. To understand how EF Core work with databases, it is important to uncover the actual underlying operations to the SQL database, which can be traced or logged in C# application side and in SQL database.

• ## Entity Framework Core and LINQ to Entities in Depth (2) Modeling Database: Object-Relational Mapping

In LINQ to Entities, the queries are based on Object-relational mapping. .NET and SQL database and  have 2 different data type systems. For example, .NET has System.Int64 and System.String, while SQL database has bigint and nvarchar; .NET has sequences and objects, while SQL database has tables and rows;, etc. Object-relational mapping is a popular technology to map and convert between application data objects and database relational data.

• ## Entity Framework Core and LINQ to Entities in Depth (1) Remote Query

The previous chapters discussed LINQ to Objects, LINQ to XML, and Parallel LINQ. All of these LINQ technologies query local in-memory objects managed by .NET. This chapter discusses a different kind of LINQ technology, LINQ to Entities, which queries relational data managed by databases. LINQ to Entities was initially provided by Entity Framework (EF), a Microsoft library released since .NET Framework 3.5 Service Pack 1. Since 2016, Microsoft also released Entity Framework Core (EF Core), along with .NET Core. EF Core is based on .NET Standard, so it works cross-platform.

• ## Parallel LINQ in Depth (4) Performance

The purpose of PLINQ is to utilize multiple CPUs for better performance than LINQ to Objects However, PLINQ can also introduces performance overhead, like source partitioning and result merging. There are many aspects that impact PLINQ query performance.

• ## Parallel LINQ in Depth (3) Query Methods (Operators)

Most of the PLINQ standard queries are the parities with LINQ to Objects standard queries, with the same syntax and functionality. For the additional queries in PLINQ, AsParallel, AsSequential and ForAll has been discussed. The rest of this chapter discusses the other additional queries, overloads, and the ordering relevant queries that have different behaviour from LINQ to Objects.

• ## Parallel LINQ in Depth (2) Partitioning

The previous chapter discussed what is PLINQ and how to use PLINQ. This chapter looks into PLINQ’s internals and execution, including data processing and query performance.

• ## LINQ to XML in Depth (3) Manipulating XML

Besides creating and querying XML, LINQ to XML also provides APIs for other XML manipulations, including cloning, deleting, replacing, and updating XML structures:

• ## Installing Android 9 Pie with Microsoft apps on Nexus 7

Years ago I blogged about installing Android 6 on my old Nexus 7 tablet. Now Android 9 is there. This post shows how to install latest Android 9 with latest Microsoft apps.

• ## LINQ to XML in Depth (2) Query Methods (Operators)

As fore mentioned, LINQ to XML is just a specialized LINQ to Objects, so all the LINQ to Objects queries can be used in LINQ to XML queries. LINQ to XML provides many additional functions and queries for XML tree navigation, ordering, XPath querying, etc. The following list shows these functions and their return types:

• ## LINQ to XML in Depth (1) Modeling XML

XML (eXtensible Markup Language) is widely used to represent, store, and transfer data. .NET Standard provides LINQ to XML APIs to query XML data source. LINQ to XML APIs are located in System.Xml.XDocument NuGet package for .NET Core, and System.Xml.Linq.dll assembly for .NET Framework. LINQ to XML can be viewed as specialized LINQ to Objects, where the queried objects represent XML structures.

• ## Setup Open Live Writer and sync with Windows Live Writer cross computers

Today I am setting up a new PC. I use Windows Live Writer to write for this blog for years, and found the new installation can no longer work for my blog. I tried Open Live Writer. Fortunately Open Live Writer works.

• ## LINQ to Objects in Depth (7) Building Custom Query Methods

With the understanding of standard queries in .NET Standard and the additional queries provided by Microsoft, it is easy to define custom LINQ queries for objects. This chapter demonstrates how to define the following useful LINQ to Object queries:

• ## LINQ to Objects in Depth (6) Advanced Queries in Interactive Extensions (Ix)

The previous 2 chapters discussed the LINQ to Objects standard queries.  Besides these built-in queries provided by System.Linq.Enumerable type in .NET Standard, Microsoft also provides additional LINQ to Objects queries through the System.Interactive NuGet package (aka Interactive Extensions library, or Ix). Ix has a System.Linq.EnumerableEx type with the following queries:

• ## LINQ to Objects in Depth (5) Query Methods Implementation

Understanding of internal implementation of LINQ to Objects queries is the ultimate way to master them and use them accurately and effectively, and is also helpful for defining custom query methods, which is discussed later in the custom queries chapter. Just like the usage discussion part, here query methods are still categorized by output type, but in a different order:

• ## LINQ to Objects in Depth (4) Deferred Execution, Lazy Evaluation and Eager Evaluation

As fore mentioned, when LINQ to Objects’ collection queries and value queries are called, they start to evaluate query result. When sequence queries are called, they do not evaluate any query result, and can be viewed as defining a query.

• ## LINQ to Objects in Depth (2) Query Methods (Operators) and Query Expressions

As fore mentioned, LINQ to Objects standard query methods (also called standard query operators) are provided as static methods of System.Linq.Enumerable type, most of which are IEnumerable<T> extension methods. They can be categorized by output type:

• ## LINQ to Objects in Depth (1) Local Sequential Query

LINQ to Objects queries .NET objects in local memory of current application or service. Its data source and the queries are represented by IEnumerable<T> interface, and it is executed sequentially. This chapter introduces IEnumerable<T> interface, and discusses how to use LINQ to Object to query objects in query method syntax and query expression syntax.

• ## C# Functional Programming In-Depth (15) Pattern matching

Pattern matching is a common feature in functional languages. C# 7.0 introduces basic pattern matching in is expression and switch statement, including constant value as pattern and type as pattern, and C# 7.1 supports generics in pattern matching.

• ## C# Functional Programming In-Depth (13) Pure Function

The previous chapter discusses that functional programming encourages modelling data as immutable. Functional programming also encourages modelling operations as pure functions. The encouraged purity for function is the significant difference from method and procedure in imperative programming.

• ## C# Functional Programming In-Depth (12) Immutability, Anonymous Type, and Tuple

Immutability is an important aspect of functional programming. As discussed in the introduction chapter, imperative/object-oriented programming is usually mutable and stateful, and functional programming encourages immutability without state change. There are many kinds of immutability. In C#, the relevant features can be categorized into 2 levels: immutability of a value, and immutability of a value’s internal state. Take local variable as example,  a local variable can be immutable value, if once it is initialized to an instance, it cannot be altered it to different instance to it; a local variable can also have immutable state, if once the instance’s internal state is initialized, that instance’s internal state cannot be altered to different state.

• ## C# Functional Programming In-Depth (11) Covariance and Contravariance

In programming languages with subtyping support, variance is the ability to substitute a type with a different subtype or supertype in a context. For example, having a subtype and a supertype, can a function of subtype output substitute a function of supertype output? Can a sequence of subtype substitute a sequence of supertype? C# supports covariance/contravariance, which means to use a more/less specific  type to substitute the required type in the context of function and interface.

• ## C# Functional Programming In-Depth (10) Query Expression

C# 3.0 introduces query expression, a SQL-like query syntactic sugar for query methods composition.

• ## C# Functional Programming In-Depth (9) Function Composition and Chaining

As demonstrated in the introduction chapter, in object-oriented programming, program is modelled as objects, and object composition is very common, which combines simple objects to more complex objects. In functional programming, program is modelled as functions, function composition is emphasized, which combines simple functions to build more complex functions. LINQ query is composition of quelop.

• ## C# Functional Programming In-Depth (8) Higher-order Function, Currying and First Class Function

The delegate chapter and other previous chapters have demonstrated that in C#, function supports many operations that are available for object. This chapter discusses one more aspect, higher-order function, and how it and other functional features make function the first-class citizen in C# language.

• ## C# Functional Programming In-Depth (7) Expression Tree: Function as Data

C# lambda expression is a powerful syntactic sugar. Besides representing anonymous function, the same syntax can also represent expression tree. An expression tree is an immutable tree data structure that represents structure of code. For example:

• ## C# Functional Programming In-Depth (6) Anonymous Function and Lambda Expression

Besides named function, C# also supports anonymous function, represented by anonymous method or lambda expression with no function name at design time. Lambda expression can also represent expression tree, This chapter discusses lambda expression as a functional feature of C# language. Lambda expression is also the core concept of lambda calculus, where functional programming originates. It is revisited in the Lambda Calculus chapters.

• ## C# functional programming in-depth (5) Delegate: Function type, instance and group

C#’s delegate is an important feature to make function first class citizen just like object. C# is a strongly-typed language, where any value and any expression that evaluates to a value has a type. In C#, a value can be an object, which has type represented by class; a value can also be a function, which has type represented by delegate type. Delegate type can be instantiated to represent a single function instance, or a group of functions.

• ## C# functional programming in-depth (4) Function input and output

The previous 2 chapters discuss all the named functions in C#. This chapter looks into the input and output features of C# function.

• ## C# functional programming in-depth (3) Local Function and Closure

The previous chapter discussed named function. There is one more special kind of named function. local function. Local function can be nested in another function, and it supports an important feature closure, the ability access variable outside the local function itself.

• ## C# functional programming in-depth (2) Named function and function polymorphism

This chapter starts the deep dive in functional programming with C#. C# has named function and anonymous function. In C#, the most intuitive functions are method members of class and structure, including static method, instance method, and extension method, etc. These method members are defined with names at design time and can be called by name, so they are named functions. Some other method-like members, including static constructor, constructor, finalizer, conversion operator, operator overload, property, indexer, event accessor, are also named functions, with function name generated at compiled time. This chapter discusses named functions in C#, how these named functions are defined compiled, and called, as well as their polymorphisms. A special named function, local function, is discussed in next chapter.

• ## C# functional programming in-depth (1) C# language basics

The previous chapter demonstrates that C# is a standardized, cross-platform, and multi-paradigm language, and gives an overview that C# is very functional with rich features, including LINQ, a unified functional programming model to work with different data domains. Since this chapter, we dive into C# coding.

• ## Functional Programming and LINQ Paradigm (3) LINQ to Data Sources

As fore mentioned, LINQ is a functional programming model, consists of syntax in languages and APIs in libraries:

• ## Functional Programming and LINQ Paradigm (2) Programming Paradigms and Functional Programming

Object-oriented programming and functional programming are programming paradigms. A programming paradigm is a fundamental style or approach of programming. Paradigms are not mutually exclusive. It is common for one programming language to support multiple paradigms, and C# is such a language.

• ## Functional Programming and LINQ Paradigm (1) Cross Platform C# and .NET

C# is a functional and object-oriented programming language built by Microsoft. C# works with a family of programming frameworks crossing many platforms and devices. C# has been used by millions of people to build applications, services, mobile apps, and games, etc.

• ## End-to-End - Setup free SSL certificate to secure Azure Web App with HTTPS

It is 2019 now, and HTTP is considered as “not secure”, and HTTPS is the default. This is a end-to-end tutorial of how to setup SSL certificate to secure Azure Web App with HTTPS. It is based on “Let’s Encrypt” and “letsencrypt-webapp-renewer”. “Let’s Encrypt” is a free certificate solution, and “letsencrypt-webapp-renewer” is a great automation tool for certificate installation. It is based on another tool “letsencrypt-siteextension”. The differences are,

• “letsencrypt-webapp-renewer” does not require an Azure Storage Account, but “letsencrypt-siteextension” does.
• “letsencrypt-webapp-renewer” is a WebJob, and can run on a different Web App from the Web App to install certificate. And it can manage multiple Web Apps’ certificates as well. “letsencrypt-siteextension” is a Website extension, and can only run with the Web App which needs certificate.

# What is “Let’s Encrypt”

Let’s Encrypt” is a popular certificate authority, it can issue SSL certificate for free, and currently providing certificates for more than 115 million websites. Since July 2018, the Let’s Encrypt root, ISRG Root X1, is directly trusted by Microsoft products. So currently its root is now trusted by all mainstream root programs, including Microsoft, Google, Apple, Mozilla, Oracle, and Blackberry.

However, its free certificate expirees in every 3 months (90 days), not yearly. So a automation process will  be setup to renew and install the certificates.

# Setup Active Directory and App Registration

In Azure portal, go to the Active Directory, add a new App Registration:

Save its application id, later it will be used as “client id”:

Then go to Certificates & secretes, add a client secrete, and save it:

# Setup Resource Group

Go to resource group, In Access Control, add the above App Registration as contributor:

# Setup Azure Web App (aka Azure App Service Website)

This article assumes an existing Azure Web App. If you do not have one yet, it is very straightforward to create one from the Azure portal. Then you can deploy your website to that Azure Web App.

Save the subscription id and resource group name for later usage.

Only Basic (B1+) and above pricing tiers support SSL. The free tier (F1) and the cheapest Shared tier (D1) does not support SSL, and Microsoft has declined to enable this feature for Shared (D1). If you have a F1 or D1 web app, go to “Scale up” in the Azure portal, change the pricing tier to B1 and above, which is more than 3 times of the Shared (D1) price.

## Setup custom domain

Shared pricing tier and above support custom domain. Follow the guidelines in the portal to setup your domain.

To verify your domain ownership, Let’s Encrypt requests your-domain.com/.well-known/acme-challenge/{longId}. For example: hanziyuan.net/.well-known/acme-challenge/mftvrU2brecAXB76BsLEqW_SL_srdG3oqTQTzR5KHeA.

## Enable HTTPS in ASP.NET Core website

In your ASP.NET Core website, you may want to enable HSTS, and redirect HTTP request to HTTPS:

```public class Startup
{
public void ConfigureServices(IServiceCollection services) // Container.
{
if (this.environment.IsProduction())
{
}

// Other configuration.
}

public void Configure(IApplicationBuilder application, ILoggerFactory loggerFactory, IAntiforgery antiforgery, Settings settings) // HTTP pipeline.
{
if (this.environment.IsProduction())
{
application.UseHsts();
application.UseHttpsRedirection();
}

// Other configuration.
}
}```

You also want to look up the hyperlinks and resources (images, etc.), and replace their URIs with HTTPS.

• ## Entity Framework/Core and LINQ to Entities (9) Performance

The previous parts has discussed some aspects that can impact the performance of EF/Core and LINQ to Entities, and here is a summary:

• ## Entity Framework/Core and LINQ to Entities (8) Optimistic Concurrency

Conflicts can occur if the same data is read and changed concurrently. Generally, there are 2 concurrency control approaches:

• ## Entity Framework/Core and LINQ to Entities (7) Data Changes and Transactions

Besides LINQ to Entities queries, EF/Core also provides rich APIs for data changes, with imperative paradigm.

• ## Entity Framework/Core and LINQ to Entities (6) Query Data Loading

After translated to SQL, in LINQ to Entities, sequence queries returning IQueryable<T> implements deferred execution too.

• ## Entity Framework/Core and LINQ to Entities (5) Query Translation Implementation

The previous part demonstrated what are the SQL translations of the LINQ to Entities queries. This part discusses how the translation is implemented. Regarding different database systems can have different query languages or different query APIs, EF/Core implement a provider model to work with different kinds of databases. In EF Core, the base libraries are the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Relational NuGet packages. Microsoft.EntityFrameworkCore provides the database provider contracts as Microsoft.EntityFrameworkCore.Storage.IDatabaseProviderServices interface. And the SQL database support is implemented by the Microsoft.EntityFrameworkCore,SqlServer NuGet package, which provides Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseProviderServices type to implement IDatabaseProviderServices. There are other libraries for different databases, like Microsoft.EntityFrameworkCore.SQLite NuGet package for SQLite, etc.

• ## Entity Framework/Core and LINQ to Entities (4) Query Methods (Operators)

This part discusses how to query SQL database with the defined mapping entities. In EF/Core, LINQ to Entities supports most of the methods provided by Queryable:

• ## Entity Framework/Core and LINQ to Entities (3) Logging and Tracing Queries

EF version of this i

• ## Entity Framework/Core and LINQ to Entities (2) Modeling Database: Object-Relational Mapping

.NET and SQL database and  have 2 different data type systems. For example, .NET has System.Int64 and System.String, while SQL database has bigint and nvarchar; .NET has sequences and objects, while SQL database has tables and rows;, etc. Object-relational mapping is a popular technology to map and convert between application data objects and database relational data. In LINQ to Entities, the queries are based on Object-relational mapping.

• ## Entity Framework/Core and LINQ to Entities (1) Remote Query

The previous chapters discussed LINQ to Objects, LINQ to XML (objects), and Parallel LINQ (to Objects). All of these LINQ technologies query local in-memory objects managed by .NET. This chapter discusses a different kind of LINQ technology, LINQ to Entities, which queries relational data managed by databases. LINQ to Entities was provided by Entity Framework (EF), a Microsoft library released since .NET Framework 3.5 Service Pack 1. In 2016, Microsoft also released the cross platform version, Entity Framework Core (EF Core), along with with .NET Core 1.0. EF and EF Core both implement a provider model, so that LINQ to Entitiescan be implemented by different providers to work with different kinds of databases, including SQL Server (on-premise database) and Azure SQL Database (cloud database, aka SQL Azure), DB2, MySQL, Oracle, PostgreSQL, SQLLite, etc.

• ## C# 8.0 in-depth: Understanding index and range, and working with LINQ and IEnumerable<T>

C# 8.0 introduces index and range for array. This part discussed the index and range types, syntax, compilation, and how to apply them with LINQ for any type that implements IEnumerable<T>.

• ## C# 8.0 in-depth: Setup C# 8.0 and .NET Core 3.0

Currently, Microsoft has the second preview of C# 8.0 and .NET Core 3.0, with a lot of new features and new APIs. This part of C# 8.0 series demonstrates how to setup the environment.

• ## Functional Programming and LINQ via C#

C#, .NET Core, Azure, Functional Programming, Lambda Calculus, Category Theory, LINQ, LINQ to Objects, LINQ to XML, Parallel LINQ, LINQ to Entities, Entity Framework Core, Azure SQL Database.

• ## Linq.IndexRange: The package to enable C# 8.0 index and range featues for LINQ and IEnumerable<T> types

LINQ operators to enable C# 8.0 index and range new features working with LINQ queries and any type that implements `IEnumerable<T>`.

• ## Setup passive FTP server in Azure virtual machine

This article demonstrates how to setup a passive FTP server in a Azure virtual machine running Windows.

• ## Run Hyper-V and VMware virtual machines on Windows 10

I use Windows’ Hyper-V to run virtual machines for long time. Recently I need to run a VMware virtual machine to test something. I installed VMware Player, which is free for non-commercial usage. However, the virtual machine cannot started, with an error:

• ## Build custom Windows 10 PE

Windows PE (WinPE) is a small version of Windows, which can be used to boot up computers from CD or USB disk drive. It is very useful to deploy or repair the desktop or server edition of Windows. For many years I have tried many options to build a WinPE image and create a bootable media, including the Microsoft official approach. The easiest way I found, is to use a third party tool called “Win10PE SE project”. I am sharing this tool with a post, because it is not easy to be found when searching WinPE related keywords.