[LINQ via C# series]

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

For a certain language, like C#, there is only 1 set of LINQ query syntax working with many LINQ API sets, and each API set works with a specific data domain. Here are examples of these API sets:

·        In .NET Standard, Microsoft provides:

o    LINQ to Objects: a set of LINQ APIs for .NET objects in memory

o     Parallel LINQ: another set of LINQ APIs also for .NET objects in memory, with parallel query capability

o    LINQ to XML: a set of LINQ APIs for XML data objects in memory

·        Microsoft also provides other libraries based on .NET Standard:

o    LINQ to Entities: a set of LINQ APIs in Entity Framework Core (EF Core) library for databases, including Microsoft SQL Server, Microsoft Azure SQL Database (aka SQL Azure), as well as SQLite, Oracle, MySQL, PostgreSQL, etc.

o    LINQ to NoSQL: a set of LINQ APIs for Azure CosmosDB, the Microsoft NoSQL database service. For convenience these APIs are called LINQ to NoSQL in this book.

·        In .NET Framework for Windows, Microsoft provides:

o    LINQ to DataSets: a set of LINQ APIs for data cached in data sets

o    LINQ to SQL: a set of LINQ APIs for relational data in Microsoft SQL Server

·        There are also third-party LINQ libraries, for example:

o    LINQ to JSON, s set of LINQ APIs for JSON data in memory

o    LINQ to Twitter, a set of LINQ APIs for Twitter data in Twitter’s services.

One language for different data domains

C# developer can use a single LINQ language syntax to work with different data. At compile time, the LINQ syntax can be compiled to different API calls according to different contexts. At runtime, these specific API calls work with specific data domains. To use LINQ to work with data, there are usually 3 steps:

1.      Get the data source for LINQ query

2.      Define the LINQ query

3.      Execute the LINQ query

LINQ to Objects

LINQ to Objects queries .NET objects in memory. The following example queries positive integers from the integer array in memory, and get the integers’ square roots in ascending order:

internal static void LinqToObjectsWithQueryExpression()

{

   IEnumerable<int> source = new int[] { 4, 3, 2, 1, 0, -1 }; // Get source.

   IEnumerable<double> query =

        from int32 in source

        where int32> 0

        orderby int32

        select Math.Sqrt(int32); // Define query.

    foreach (double result in query) // Execute query.

    {

        Trace.WriteLine(result);

    }

}

Here the data source is a sequence of integers in memory. The query is built declaratively in native C# language keywords (where, orderby, select, etc.), which is called query expression:

·        The from clause specifies data source

·        The where clause filters the data source and keeps the integers greater than 0,

·        The orderby clause sort the filtered integers in ascending order

·        The select clause maps the sorted integers to their square roots.

Building the query does not execute it. Later, when pulling the results from the query with a foreach loop, the query is executed.

Besides above query expression syntax. There is another query method call syntax to build LINQ query:

internal static void LinqToObjectsWithQueryMethods()

{

    IEnumerable<int>source = new int[] { 4, 3, 2, 1, 0, -1 }; // Get source.

    IEnumerable<double> query = source

        .Where(int32 => int32 > 0)

        .OrderBy(int32 => int32)

        .Select(int32 => Math.Sqrt(int32)); // Define query.

    foreach (double result in query) // Execute query.

    {

        Trace.WriteLine(result);

    }

}

These 2 versions of query are identical. The query expression is compiled to query method calls, which is discussed in detail in the Functional Programming and LINQ to Objects chapters.

Parallel LINQ

The above LINQ to Object query executes sequentially. The filter-sort-map computation are executed for all integers with a single thread, and the query results are produced one by one in a deterministic order. Parallel LINQ (to Objects) is the parallel version of the LINQ to Objects APIs. It also works with objects in memory but can execute the query in parallel with multiple threads, in order to utilize multiple processor cores and improve the LINQ query performance. The following are the parallel version of the above queries:

internal static void ParallelLinq()

{

    int[] values = { 4, 3, 2, 1, 0, -1 };

    ParallelQuery<int>source = values.AsParallel(); // Get source.

    ParallelQuery<double> query =

        from int32 in source

        where int32 > 0

        orderby int32

        select Math.Sqrt(int32); // Define query.

    // Equivalent to:

    // ParallelQuery<double> query = source

    //   .Where(int32 => int32 > 0)

    //    .OrderBy(int32 => int32)

    //   .Select(int32 => Math.Sqrt(int32));

    query.ForAll(result => Trace.WriteLine(result)); // Execute query.

}

The query creation syntax is exactly the same as sequential LINQ to Objects. The query execution syntax is different. In the previous LINQ to Objects query execution, a foreach loop is used to pull the results one by one sequentially. Here Parallel LINQ provides a special ForAll method to execute the pulling in parallel.  Since the results are computed in parallel, the query results can be produced in nondeterministic order.

LINQ to XML

LINQ to XML queries XML data. The ASP.NET blog RSS feed https://weblogs.asp.net/dixin/rss is XML and can be the source:

<?xml version="1.0" encoding="utf-8"?>

<rss version="2.0">

  <channel>

    <title>Dixin's Blog</title>

    <link>https://weblogs.asp.net:443/dixin/</link>

    <description>https://weblogs.asp.net:443/dixin/</description>

    <item>

      <title>EntityFramework.Functions: Code First Functions for Entity Framework</title>

      <link>https://weblogs.asp.net/dixin/entityframework.functions</link>

      <description><!-- Description. --></description>

      <pubDate>Mon Dec 17, 2015 06:27:56 GMT</pubDate>

      <guid isPermaLink="true">https://weblogs.asp.net/dixin/entityframework.functions</guid>

      <category>.NET</category>

      <category>LINQ</category>

      <category>Entity Framework</category>

      <category>LINQ to Entities</category>

      <category>Code First</category>

    </item>

    <!-- More items. -->

  </channel>

</rss>

The following example queries the items with permalink from the feed and get the items’ titles in ascending order of the items’ publish dates:

internal static void LinqToXml()

{

    XDocument feed = XDocument.Load("https://weblogs.asp.net/dixin/rss");

    IEnumerable<XElement>source = feed.Descendants("item"); // Get source.

    IEnumerable<string> query =

        from item in source

        where (bool)item.Element("guid").Attribute("isPermaLink")

        orderby (DateTime)item.Element("pubDate")

        select (string)item.Element("title"); // Define query.

    // Equivalent to:

    // IEnumerable<string> query = source

    //    .Where(item => (bool)item.Element("guid").Attribute("isPermaLink"))

    //    .OrderBy(item => (DateTime)item.Element("pubDate"))

    //    .Select(item => (string)item.Element("title"));

    foreach (string result in query) // Execute query.

    {

        Trace.WriteLine(result);

    }

}

In this example, the data source is XML data loaded in memory. It queries all <item> elements in the XML document, filter them and only keep the <item> elements with child< guid> elements, whose isPermaLink attributes have the value true, then sort the <item> element by the time represented by the child< pubDate> elements in descending order; then get <item> elements’ child <title> elements’ values. Again, later when pulling the results from the query with a foreach loop, the query is executed.

LINQ to DataSets

.NET Framework provides System.Data.DataSet type to cache tabular data from relational database. When working with relational database, this book uses Microsoft SQL database and Microsoft AdventureWorks sample database. In the following example, data is read from the AdventureWorks database’s Production.Product table, and cached in a DataSet instance. The following example queries the products in the specified subcategory, and get the products’ names, in ascending order of products’ list prices.

internal static void LinqToDataSets(string connectionString)

{

    using (DataSet dataSet = new DataSet())

    using (DataAdapter dataAdapter = new SqlDataAdapter(

        @"SELECT [Name], [ListPrice], [ProductSubcategoryID] FROM [Production].[Product]", connectionString))

    {

        dataAdapter.Fill(dataSet);

        EnumerableRowCollection<DataRow> source = dataSet.Tables[0].AsEnumerable(); // Get source.

        EnumerableRowCollection<string> query =

            from product in source

            where product.Field<int>("ProductSubcategoryID") == 1

            orderby product.Field<decimal>("ListPrice")

            select product.Field<string>("Name"); // Define query.

        // Equivalent to:

        // EnumerableRowCollection<string> query = source

        //    .Where(product => product.Field<int>("ProductSubcategoryID") == 1)

        //    .OrderBy(product => product.Field<decimal>("ListPrice"))

        //    .Select(product => product.Field<string>("Name"));

        foreach (string result in query) // Execute query.

        {

            Trace.WriteLine(result);

        }

    }

}

Here the query is created to filter the products in the DataSet object, and only keeps the products under the specified subcategory, then sort the products by their list price fields, then get the products’ name fields. Later, when pulling the results from the query with a foreach loop, the query is executed.

LINQ to Entities

Microsoft Entity Framework Core provides LINQ to Entities to enable LINQ queries directly working with data in database.  The AdventureWorks sample database includes the following 3 related tables:

The following example queries Production.Product table for the products under the specified category, and get the products’ names in the order of their list prices:

internal static void LinqToEntities()

{

    using (AdventureWorks adventureWorks = new AdventureWorks())

    {

        IQueryable<Product>source = adventureWorks.Products; // Get source.

        IQueryable<string> query =

            from product in source

            where product.ProductSubcategory.ProductCategory.Name == "Bikes"

            orderby product.ListPrice

            select product.Name; // Define query.

        // Equivalent to:

        // IQueryable<string> query = source

        //   .Where(product => product.ProductSubcategory.ProductCategory.Name == "Bikes")

        //    .OrderBy(product => product.ListPrice)

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

        foreach (string result in query) // Execute query.

        {

            Trace.WriteLine(result);

        }

    }

}

Here the data source is the relational data stored in the remote database table, not local .NET objects in memory. The above AdventureWorks type is the LINQ to Entities data context and represents the database, and its Products property represents the table. The query is created to filter the products in the table, and only keeps the products under the specified category, then sort the products by their list prices, and get the products’ names. Later, when pulling the results from the query with a foreach loop, the query is executed to read from the database.

LINQ to SQL

LINQ to SQL is a lightweight database access technology provided by .NET Framework. As the name suggests, LINQ to SQL only works with Microsoft SQL Server. Its APIs are similar to LINQ to Entities APIs. So, if the above queries are implemented by LINQ to SQL, the code can have the same looking:

#if NETFX

internal static void LinqToSql()

{

    using (AdventureWorks adventureWorks = new AdventureWorks())

    {

        IQueryable<Product>source = adventureWorks.Products; // Get source.

        IQueryable<string> query =

            from product in source

            where product.ProductSubcategory.ProductCategory.Name == "Bikes"

            orderby product.ListPrice

            select product.Name; // Define query.

        // Equivalent to:

        // IQueryable<string> query = source

        //    .Where(product => product.ProductSubcategory.ProductCategory.Name == "Bikes")

        //    .OrderBy(product => product.ListPrice)

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

        foreach (string result in query) // Execute query.

        {

            Trace.WriteLine(result);

        }

    }

}

#endif

Here the AdventureWorks type is a LINQ to SQL data context, which is different from the LINQ to Entities data context. So, the pulling execution on the query triggers LINQ to SQL API calls, which read data from the database.

LINQ to NoSQL

Microsoft provides LINQ APIs in client library to work with its  non-relational database (aka NoSQL database) service, CosmosDB. To setup a data source for LINQ, create a free account, then follow the Microsoft documents to import some JSON documents representing some stores with addresses:

[

    {

        "id": "1424",

        "Name": "Closeout Boutique",

        "Address": {

            "AddressType": "Main Office",

            "AddressLine1": "1050 Oak Street",

            "Location": {

                "City": "Seattle",

                "StateProvinceName": "Washington"

            },

            "PostalCode": "98104",

            "CountryRegionName": "United States"

        }

    },

    // More documents.

]

Here the source is the database’s Store collection. The following example queries the stores in the specified city, and get their names in the alphabetic order:

internal static void LinqToNoSql(string key)

{

    using (DocumentClient client = new DocumentClient(

        new Uri("https://dixin.documents.azure.com:443/"), key))

    {

        IOrderedQueryable<Store>source = client.CreateDocumentQuery<Store>(

            UriFactory.CreateDocumentCollectionUri("dixin", "Store")); // Get source.

        IQueryable<string> query = from store in source

                                    where store.Address.Location.City == "Seattle"

                                    orderby store.Name

                                    select store.Name; // Define query.

        // Equivalent to:

        // IQueryable<string> query = source

        //   .Where(store => store.Address.CountryRegionName == "United States")

        //    .OrderBy(store => store.Address.PostalCode)

        //   .Select(store => store.Name);

        foreach (string result in query) // Execute query.

        {

            Trace.WriteLine(result);

        }

    }

}

The query is created to filter the products in the collection, and only keeps the stores in the specified city, then sort the stores by their names, then get the stores’ names.

LINQ to JSON

LINQ to JSON is a third party set of APIs enabling LINQ for JSON data. Tumblr provides APIs returning JSON data, which can be a data source:

{

  "meta": {

    "status": 200,

    "msg": "OK"

  },

  "response": {

    "posts": [

      {

        "type": "photo",

        "blog_name": "dixinyan",

        "id": 94086491678,

        "post_url": "http://dixinyan.tumblr.com/post/94086491678/microsoft-way-microsoft-campus-microsoft-campus",

        "slug": "microsoft-way-microsoft-campus-microsoft-campus",

        "date": "2014-08-07 19:11:43 GMT",

        "timestamp": 1407438703,

        "state": "published",

        "format": "html",

        "reblog_key": "FZQVzcFD",

        "tags": [ "Microsoft" ],

        "short_url": "https://tmblr.co/Z_W6Et1Nd-UuU",

        "summary": "Microsoft Way, Microsoft Campus  Microsoft Campus is the informal name of Microsoft's corporate headquarters, located at One...",

        "recommended_source": null,

        "recommended_color": null,

        "note_count": 4,

        "caption": "<h2>Microsoft Way, Microsoft Campus </h2><p>Microsoft Campus is the informal name of Microsoft&rsquo;s corporate headquarters, located at One Microsoft Way in Redmond, Washington. Microsoft initially moved onto the grounds of the campus on February 26, 1986. <a href=\"http://en.wikipedia.org/wiki/Microsoft_Redmond_Campus\" target=\"_blank\">en.wikipedia.org/wiki/Microsoft_Redmond_Campus</a>\n\n<a href=\"https://www.flickr.com/dixin\" target=\"_blank\"></a></p>",

        "image_permalink": "http://dixinyan.tumblr.com/image/94086491678",

        "can_like": true,

        "can_reblog": true,

        "can_send_in_message": true,

        "can_reply": false,

        "display_avatar": true

        // More post info.

      },

      // More posts.

    ],

    "total_posts": 20

  }

}

The following example queries the posts with specified tag, and get their summary in the order of items’ publish dates:

internal static void LinqToJson(string apiKey)

{

    using (WebClient webClient = new WebClient())

    {

        string feedUri = $"https://api.tumblr.com/v2/blog/dixinyan.tumblr.com/posts/photo?api_key={apiKey}";

        JObject feed = JObject.Parse((webClient.DownloadString(feedUri)));

        IEnumerable<JToken>source = feed["response"]["posts"]; // Get source.

        IEnumerable<string> query =

            from post in source

            where post["tags"].Any(tag => "Microsoft".Equals((string)tag, StringComparison.OrdinalIgnoreCase))

            orderby (DateTime)post["date"]

            select (string)post["summary"]; // Define query.

        // Equivalent to:

        // IEnumerable<string> query = source

        //   .Where(post => post["tags"].Any(tag =>

        //        "Microsoft".Equals((string)tag, StringComparison.OrdinalIgnoreCase)))

        //   .OrderBy(post => (DateTime)post["date"])

        //   .Select(post => (string)post["summary"]);

        foreach (string result in query) // Execute query.

        {

            Trace.WriteLine(result);

        }

    }

}

It queries all posts in the JSON document, filter them and only keep the items with the specified tag, then sort the posts by their publish dates, then get the items’ titles.

LINQ to Twitter

LINQ to Twitter is another third-party library enabling LINQ queries for Twitter data. To access Twitter as a data source, registering an app with Twitter to get the consumer key, consumer secrete, OAuth token, and OAuth token secrete. The following example queries the tweets with specified search keyword:

internal static void LinqToTwitter(

    string consumerKey, string consumerSecret, string oAuthToken, string oAuthTokenSecret)

{

    SingleUserAuthorizer credentials = new SingleUserAuthorizer()

    {

        CredentialStore = new InMemoryCredentialStore()

        {

            ConsumerKey = consumerKey,

            ConsumerSecret = consumerSecret,

            OAuthToken = oAuthToken,

            OAuthTokenSecret = oAuthTokenSecret

        }

    };

    using (TwitterContext twitter = new TwitterContext(credentials))

    {

        IQueryable<Search>source = twitter.Search; // Get source.

        IQueryable<List<Status>> query =

            from search in source

            where search.Type == SearchType.Search && search.Query == "LINQ"

            orderby search.SearchMetaData.Count

            select search.Statuses; // Define query.

        // Equivalent to:

        // IQueryable<List<Status>> query = source

        //   .Where(search => search.Type == SearchType.Search && search.Query == "LINQ")

        //    .OrderBy(search => search.SearchMetaData.Count)

        //    .Select(search => search.Statuses);

        foreach (List<Status> search in query) // Execute query.

        {

            foreach (Status status in search)

            {

                Trace.WriteLine(status.Text);

            }

        }

    }

}

Sometimes the query result could be fun. For example, a casino in Las Vegas is named LINQ, and a Japanese idol girls’ music group is also named LinQ (Love in Qshu), etc.

Productivity

When LINQ was first released with .NET Framework 3.5, MSDN describes it as:

Traditionally, to work with a specific data domain, a domain specific language and a set of domain specific APIs are used. For example, the following example is equivalent to above LINQ to XML query logic, implemented in traditional programming model, which calls XML APIs to execute query expression in XPath language:

internal static void Xml()

{

    XPathDocument feed = new XPathDocument("https://weblogs.asp.net/dixin/rss");

    XPathNavigator navigator = feed.CreateNavigator();

    XPathExpression selectExpression = navigator.Compile("//item[guid/@isPermaLink='true']/title/text()");

    XPathExpression sortExpression = navigator.Compile("../../pubDate/text()");

    selectExpression.AddSort(sortExpression, Comparer<DateTime>.Default);

    XPathNodeIterator nodes = navigator.Select(selectExpression);

    foreach (object node in nodes)

    {

        Trace.WriteLine(node);

    }

}

For SQL database, the traditional programming model implements the above LINQ to Entities query logic by calling ADO.NET data access APIs to execute query statement in SQL language:

internal static void Sql(string connectionString)

{

    using (DbConnection connection = new SqlConnection(connectionString))

    using (DbCommand command = connection.CreateCommand())

    {

        command.CommandText =

            @"SELECT [Product].[Name]

            FROM [Production].[Product] AS [Product]

            LEFT OUTER JOIN [Production].[ProductSubcategory] AS [Subcategory]

                ON [Subcategory].[ProductSubcategoryID] = [Product].[ProductSubcategoryID]

            LEFT OUTER JOIN [Production].[ProductCategory] AS [Category]

                ON [Category].[ProductCategoryID] = [Subcategory].[ProductCategoryID]

            WHERE [Category].[Name] = @categoryName

            ORDER BY [Product].[ListPrice] DESC";

        DbParameter parameter = command.CreateParameter();

        parameter.ParameterName = "@categoryName";

        parameter.Value = "Bikes";

        command.Parameters.Add(parameter);

        connection.Open();

        using (DbDataReader reader = command.ExecuteReader())

        {

            while (reader.Read())

            {

                string productName = (string)reader["Name"];

                Trace.WriteLine(productName);

            }

        }

    }

}

Similarly, for Twitter data, there are network APIs to query Twitter’s REST endpoints, etc. LINQ implements a unified and consistent language syntax and programming model for many different data domains. Above examples demonstrated the same C# syntax builds filter-sort-map query flows for .NET objects, XML data, cached tabular data, SQL database, NoSQL database, JSON, Twitter data. This capability makes LINQ a powerful and productive solution for working with data.

C# is a strongly typed language. In C#, any value has a type, including any value in LINQ query. And any expression is evaluated to a type, including LINQ query expressions. Any method has a type for each parameter and a type for return value, including LINQ query methods. So, LINQ queries are checked by compiler and runtime for type safety, which is great help for productivity, unless dynamic typing is used to bypass the compiler check:

internal static void Dynamic()

{

    IEnumerable<int> source = new int[] { 4, 3, 2, 1, 0, -1 }; // Get source.

    IEnumerable<dynamic> query =

        from dynamic value in source

        where value.ByPass.Compiler.Check > 0

        orderby value.ByPass().Compiler().Check()

        select value & new object(); // Define query.

    foreach (dynamic result in query) // Execute query.

    {

        Trace.WriteLine(result);

    }

}

Strong typing also enables IntelliSense for tools, which also improves the productivity:

LINQ also supports deferred execution. Usually, LINQ query is executed only when the results are pulled from the query. This enables creating query with arbitrary complexity. In above examples, during the composition of filter-sort-map, no execution is triggered. Later, when the results are pulled, the entire filter-sort-map query executes is triggered. This is also important for productivity. Take above LINQ to Entities query as example, when the query is executed against the SQL database, the entire filter-sort-map query logic is submitted to database as a single database query.

LINQ is not only about data query. Many LINQ libraries provide rich APIs to manipulate and change the data, like LINQ to XML, LINQ to SQL, and EF Core, and LINQ to NoSQL, etc. Parallel LINQ is a special set of LINQ APIs, it can significantly improve the query performance for .NET objects, it also provides a simple programming model for general parallel computing.

Local query vs. remote query

Generally, there are 2 kinds of LINQ technologies:

·        Local query: The data source for local query is .NET objects in local memory of current .NET application or service. Apparently, (sequential) LINQ to Objects queries, and Parallel LINQ (to Objects) queries are local queries. LINQ to XML have XML data loaded to memory as specialized .NET objects representing the XML data structure, then query these objects, so LINQ to XML queries are also local queries too. Similarly, LINQ to DataSets and LINQ to JSON queries are local queries too. As demonstrated above, the local sequential LINQ data source and query is represented by System.Collections.Generics.IEnumerable<T> interface, and the local parallel LINQ data source and query is represented by System.Linq.ParallelQuery<T> type.

·        Remote query: The data source for remote query is not in the local memory. For example, LINQ to Entities queries the data stored in a relational database, apparently the data source is not available as .NET objects in the memory of current .NET application or service. So, LINQ to Entities queries are remote queries. So are LINQ to SQL, LINQ to DocumentDB and LINQ to Twitter. As demonstrated above, the remote LINQ data source and query is represented by System.Linq.IQueryable<T> interface.

There are so many LINQ technologies, it is infeasible and also unnecessary to have one book for all of them. This book covers C# language's LINQ features, and the most used LINQ APIs: LINQ to Object (sequential local queries), LINQ to XML (specialized local queries), Parallel LINQ (parallel local queries), as well as EF/Core (remote queries). With the unified and consistent LINQ programming model, mastering these LINQ knowledge enables developers working any other local or remote LINQ technologies, understanding the internal implementation of these LINQ technologies also enables developer to build custom LINQ APIs to for other local or remote data scenarios.

Summary

This chapter introduces the brief history and basic concept of .NET, C#, .NET Standard, and demonstrate how to setup tools to start coding on Windows, macOS, and Linux. It also introduces programming paradigms, and explains what is declarative/functional programming by comparing to imperative/object-oriented programming. It also explains what is LINQ, and how LINQ works with many different data domains with a unified programming model. The next chapter discusses more concepts of C# programming and give a overview of C#’s basic syntax used through this book.