LINQ Fundamentals

Web page by Kevin Harris of Homer IL

Please contact Kevin Harris of Homer IL concerning this web site

LINQ Basics

Language Integrated Query (LINQ) is a common query syntax build into the .NET languages which can be used for several different data sources (e.g. databases, web services, in-memory collections, files). LINQ works with a sequence of generic types so it does not care about the different data sources or where it is relational or hierarchical data. LINQ provides this ability with strong types (compile time type checking) and intellisense. LINQ replaces the need to work with different APIs for different data sources (i.e. SQL, XPath, XSLT, Algorithms (quick sorts, binary searches), Generics, ADO.NET). LINQ provides a consistent uniform API that works across various data sources. LINQ makes the query a first-class construct by integrating it into the programming language.

Older technologies, like Structured Query Language (SQL), required specifying column names as string literals. This method provided no help in determining the column names and incorrect names would result in run time exceptions. ADO.NET improved on this by providing strongly typed data sets, but this works only with relational data. LINQ understands the underlying data from various sources and provides metadata so intellisense can help pick the correct name. However if you use an incorrect name using LINQ, the static typing will result in a compiler error instead of a run time error.

The concept of a common generic query syntax is not new. Other languages, such as FoxPro and Clipper, contained built in query capabilities. LINQ's capabilities have have been brought about by the evolution of programming abilities, specifically: anonymous types, lambda expressions, extension methods, partial methods, expression trees, object initializers, and type inference. LINQ was formally introduced in .NET 3.5 and exists in all the .NET languages with ports available for Java, PHP, and JavaScript.

The major LINQ technologies included in .NET (i.e. available out-of-the-box with Visual Studio):

  1. LINQ to Objects - query in memory objects.
  2. LINQ to XML - query XML documents.
  3. LINQ to SQL - query SQL Server 2000/2005/2008 databases only. It is a simple OR/M. Translates query expressions to T-SQL.
  4. LINQ to Entities - query database entity models (most databases). For example Entity Framework.

LINQ can work in languages which support generics and works with any objects which implements IEnumerable<T>. There over 50 "standard query operators" defined in the System.Linq namespace. The standard query operators are used for filtering, joining, partitioning, ordering, aggregating, order, projecting, etc. However you can define your own custom LINQ operators or override the standard query operators. LINQ is also extensible through the provider model, where a custom provider is configured into a project to support the querying of a certain type of datasource.

Custom LINQ providers written by other people include:

  1. LINQ to Amazon - LINQ for Amazon web service API
  2. LINQ to Flickr - LINQ for FLICKR web service API
  3. LINQ to Wikipedia -LINQ for Wikipedia API
  4. LINQ to LDAP - LINQ for working with Lightweight Directory Access Protocol (LDAP). LDAP is a standard API for accessing/maintaining directory information service over an IP network.
  5. PLINQ - parallel execution of queries. e.g. the "AsParallel" operator spreads the query work across the processor cores, without having to use any of the threading synchronization primitives.


LINQ Queries

Each LINQ query contains:

  1. an enumerable sequence of data - the data can be from a local or remote host. The data must be enumerable, that is, there must be a way to know how to move from one item of data to the next.

  2. a range variable - which is used to keep track of the items as you move through the enumeration.

  3. query operators - are processed in a pipeline fashion to create another local sequence of items.


LINQ Syntax

LINQ supports two types of syntax. The Query Syntax (a.k.a Comprehension Query Syntax) resembles SQL with the Select clause listed last instead of first. In LINQ Query Syntax, having the From clause listed first allows the IDE to find the data type and provide intellisense for the later clauses. The Query syntax contains operators familiar to SQL users, such as Select, Where, Join, GroupBy, Max, Average, etc).

The second type of LINQ syntax is the Method Syntax (a.k.a. Extension Method Syntax, or Lambda Expression Syntax) which makes use of extension methods to chain together a sequence of operations. There are some queries which can not be expressed with Query Syntax and must be expressed with Method Syntax. It is also possible to compose queries using a combination of the two types of syntax. However Query Syntax is always converted by the compiler to Method Syntax. The following code shows an equivalent query specified in both Query syntax and Method Syntax.

1
2
3
4
5
6
7
8
9
        //Query syntax:
        IEnumerable<int> numQuery1 = 
            from num in numbers
            where num % 2 == 0
            orderby num
            select num;

        //Method syntax:
        IEnumerable<int> numQuery2 = numbers.Where(num => num % 2 == 0).OrderBy(n => n);


Equivalent Query in Query Syntax and Method Syntax

As mentioned, the query syntax is really just syntax sugar as the query syntax is converted to the method syntax by the compiler. Also the query syntax does not provide all the needed function, so you may need to surround the query with parenthesis and add on a function method, a shown in the code below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
using System;
using System.Linq;
using System.Collections.Generic;

namespace Examples
{
    class Program
    {
        public static void Main(string[] args)
        {

            List<kevin> kevins = new List<kevin>()
            {
                new kevin {LastName = "Harris", Votes = 1},
                new kevin {LastName = "Costner", Votes = 22},
                new kevin {LastName = "Bacon", Votes = 21},
                new kevin {LastName = "Nealon", Votes = 15},
                new kevin {LastName = "Kline", Votes = 18}
            };

            // Query syntax needing help from method to return first value
            kevin query1 =
                (from k in kevins
                 where k.LastName == "Harris"
                 orderby k.Votes
                 select k).First();

            // Evivalent to query above, but using Method syntax
            kevin query2 =
                kevins.Where(k => k.LastName == "Harris")
                      .OrderBy(k => k.LastName)
                      .Select(k => k)  // Select is optional in method syntax
                      .First();
                     
        }
    }
    public class kevin
    {
        public string LastName { get; set; }
        public int Votes { get; set; }
    }
}


Query syntax needs help from first() method.


LINQ to Objects

The following code uses LINQ to Objects. Since Kevin Smith is included in the output, it proves that deferred execution is used. Deferred execution is the execution of the query at the point it is used, not at the point it is defined. Since this query uses all lazy operators the executions is deferred. If the query used one or more of the greedy operators (e.g. ToList(), Sum(), FirstOrDefault(), Count()) the executions would instead be immediate and Kevin Smith would NOT appear in the list.

Deferred execution or immediate execution of a LINQ statement depends upon the operators used (greedy, or lazy). The standard LINQ operators can be overridden by making extensions methods. If you wish to make your overridden operator lazy, it should use the yield keyword to set up a continuation which creates an execution point for the instruction pointer to return to on subsequent execution of the method.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;

namespace LinqExamples
{
    class Program
    {
        public static void Main(string[] args)
        {
            QueryKevins();
        }

        private static void QueryKevins()
        {
            List<kevin> kevins = new List<kevin>()
            {
                new kevin {LastName = "Harris", Votes = 1},
                new kevin {LastName = "Costner", Votes = 22},
                new kevin {LastName = "Bacon", Votes = 21},
                new kevin {LastName = "Nealon", Votes = 15},
                new kevin {LastName = "Kline", Votes = 18}
            };

            IEnumerable<kevin> orderedKevins =
                from k in kevins
                where k.Votes > 10
                orderby k.Votes descending
                select k;

            // This line proves execution is deferred
            kevins.Add(new kevin { LastName = "Smith", Votes = 14 });

            foreach (var k in orderedKevins)
            {
                Console.WriteLine($"Kevin {k.LastName} , Votes: {k.Votes}");
            }
            Console.ReadLine();
        }
    }

    public class kevin
    {
        public string LastName { get; set; }
        public int Votes { get; set; }
    }
}
.LINQ to Objects - Deferred Execution


LINQ to Objects - Deferred Execution


Extension Methods

Inorder to support LINQ, Microsoft introduced extension methods which are static methods which are used as if they were instance methods. Extension methods are public static methods in a public static class where the first parameter is the "this" keyword and the second parameter is the type being extended. Extension methods can be used to extend any type, even sealed classes. Below is a comparison of Static Methods and Extension Methods.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
using System;

namespace LinqExamples
{
    class Program
    {
        public static void Main(string[] args)
        {
            // Call static methods
            string theName = StringUtils.AddKevin("Costner");
            theName = StringUtils.AlternateCaps(theName);
            Console.WriteLine(theName);

            // Call extension method
            string theName2 = "Costner".AddKevin().AlternateCaps();
            Console.WriteLine(theName2);

            Console.ReadLine();
        }
    }


    public static class StringUtils
    {
        public static string AddKevin(string name)
        {
            return "Kevin " + name;
        }

        public static string AlternateCaps(string name)
        {
            name = name.ToLower();
            string newName = null;
            int i = 0;
            foreach (char c in name)
            {
                if (++i % 2 == 0)
                    newName += c.ToString().ToUpper();
                else
                    newName += c.ToString();
            }
            return newName;
        }
    }

    public static class StringExtensionMethods
    {
        public static string AddKevin(this string name)
        {
            return "Kevin " + name;
        }

        public static string AlternateCaps(this string name)
        {
            name = name.ToLower();
            string newName = null;
            int i = 0;
            foreach (char c in name)
            {
                if (++i % 2 == 0)
                    newName += c.ToString().ToUpper();
                else
                    newName += c.ToString();
            }
            return newName;
        }
    }
}


Comparison of Static Method and Extension Method

LINQ uses extension methods to extend the Enumerable type and provides for the method chaining ability used in LINQ expressions.
Extension methods must be defined as public static methods inside a non-generic public static class. Extension methods can not hide or replace instance methods. The compiler looks for instance methods first, before looking for extension methods. The namespace containing the extension methods must be in scope (i.e. through the "using" statement) before than can be used. Below is part of the LINQ extension methods residing in the System.Linq namespace extending the Enumerable class.

. LINQ extension methods residing in the System.Linq namespace extending the Enumerable class


LINQ extension methods residing in the System.Linq namespace extending the Enumerable class


Lambda Expressions

Lambda is a short-hand syntax for creating anonymous methods. They were introduced in C# 3.0 and are used in LINQ expressions. The following example shows how the C# syntax has evolved from requiring name methods, to allowing anonymous methods, to using lambda expressions to concisely define anonymous methods.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;

namespace LinqExamples
{
    class Program
    {
        public static void Main(string[] args)
        {
            // Array of kevin objects
            kevin[] kevins = new kevin[]
            {
                new kevin {LastName = "Harris", Votes = 1},
                new kevin {LastName = "Costner", Votes = 22},
                new kevin {LastName = "Bacon", Votes = 21},
                new kevin {LastName = "Nealon", Votes = 15},
                new kevin {LastName = "Kline", Votes = 18}
            };


            // In C# 1.0 had to use a named method as the predicate
            //kevin theKevin = Array.Find(kevins, FindKevinPredicate);

            // In C# 2.0 predicate is an anonymous methods which is specified through delegates
            kevin theKevin2 = Array.Find(kevins,
                                        delegate (kevin k)
                                        {
                                            return k.LastName == "Harris";
                                        });

            // In C# 3.0 lambda expressions could be used to specify the predicate
            kevin theKevin3 = Array.Find(kevins, k => k.LastName == "Harris");                                        

            Console.WriteLine($"theKevin is: {theKevin3.LastName}");
            Console.ReadLine();
        }

        //public static bool FindKevinPredicate(kevin k)
        //{
        //    return k.LastName == "Harris";
        //}


        public class kevin
        {
            public string LastName { get; set; }
            public int Votes { get; set; }
        }
    }
}


Evolution in C# from named methods, anonymous methods, and lambda expressions.


Built-in Func and Action Delegates

Lambda Expressions where introduced in .NET 3.5 and are at the core of LINQ. Lambda expressions are a concise notation for defining a delegate. Delegates are function pointers which allow code to be passed around like data. Before delegates can be passed to a method, or consumed directly in code, they must be declared. Once a delegate is declared, any method which has a signature string which matches with the delegate declaration, can be used with the delegate. This is similar to other types, such as integer. You must first declare an integer variable (int i;), then you can assign a value to the variable (i=5;). With delegates you must first declare the delegate, then you can assign any signature-matching method to the delegate. Once the delegate is assigned a method, it can be passed into a method or invoked directly.

The original C# method of declaring a delegate used the "Delegate" keyword, as shown in the following code. The calcPercentage method takes a delegate declared as type calPercentageDelegate as input. The code contains three methods which match the signature declared in the calcPercentageDelegate delegate, so any of the three can be passed into the calcPercentage method.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
using System;

namespace DelegateExamples
{
    class Program
    {
        // Delegate declaration
        public delegate double calcPercentageDelegate(double amount);

        public static void Main(string[] args)
        {
            bool loop = true;

            while (loop)
            {
                double dblCheckAmount;
                string strCheckAmount;

                Console.Write("Enter amount of check: ");
                strCheckAmount = Console.ReadLine();
                if (double.TryParse(strCheckAmount, out dblCheckAmount))
                {
                    Console.WriteLine($"10% tip is: {calcPercent(dblCheckAmount, calcTenPercent)}");
                    Console.WriteLine($"15% tip is: {calcPercent(dblCheckAmount, calcFifteenPercent)}");
                    Console.WriteLine($"20% tip is: {calcPercent(dblCheckAmount, calcTwentyPercent)}");
                    loop = false;
                }
                else
                {
                    Console.WriteLine("Check amount must be numeric!\n");
                }
            }
            Console.ReadLine();
        }

        // Method which takes a delegate as input
        public static double calcPercent(double amount, calcPercentageDelegate calcDelegate)
        {
            return calcDelegate(amount);
        }

        // Calculation Methods used with delegate
        public static double calcTenPercent(double amount)
        {
            return Math.Round(amount * .10, 2);
        }

        public static double calcFifteenPercent(double amount)
        {
            return Math.Round(amount * .15, 2);
        }

        public static double calcTwentyPercent(double amount)
        {
            return Math.Round(amount * .20, 2);
        }

    }
}


Declare delegates with "delegate" keyword. Then pass the delegate into a method.

Along with all the other changes released in .NET 3.5 to support LINQ, the Func keyword was introduced for declaring generic delegates. This eliminated the need to declare delegates with the "delegate" keyword. Instead of having several "delegate" declarations listed in a section of the code, using Func allows the delegate to be declared in-line at the point of usage. The Action can also be used to declare a generic delegate. The difference between the two is Func returns a value, while Action does not. Now with the built-in Func<T TResult>and Action<T>, it is rare that you need to define delegates with the "delegate" keyword. The code below is equivalent to the above code, but the delegate declaration has been replaced by func.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
using System;

namespace DelegateExamples
{
    class Program
    {
        // Delegate declaration replaced by Func<double, double>
        // Delegate declaration
        //public delegate double calcPercentageDelegate(double amount);

        public static void Main(string[] args)
        {
            bool loop = true;

            while (loop)
            {
                double dblCheckAmount;
                string strCheckAmount;

                Console.Write("Enter amount of check: ");
                strCheckAmount = Console.ReadLine();
                if (double.TryParse(strCheckAmount, out dblCheckAmount))
                {
                    Console.WriteLine($"10% tip is: {calcPercent(dblCheckAmount, calcTenPercent)}");
                    Console.WriteLine($"15% tip is: {calcPercent(dblCheckAmount, calcFifteenPercent)}");
                    Console.WriteLine($"20% tip is: {calcPercent(dblCheckAmount, calcTwentyPercent)}");
                    loop = false;
                }
                else
                {
                    Console.WriteLine("Check amount must be numeric!\n");
                }
            }
            Console.ReadLine();
        }

        // Method which takes a delegate as input
        // Delegate declaration replaced by Func<double, double>
        //public static double calcPercent(double amount, calcPercentageDelegate calcDelegate)
        public static double calcPercent(double amount, Func<double, double> calcDelegate)
        {
            return calcDelegate(amount);
        }

        // Calculation Methods used with delegate
        public static double calcTenPercent(double amount)
        {
            return Math.Round(amount * .10, 2);
        }

        public static double calcFifteenPercent(double amount)
        {
            return Math.Round(amount * .15, 2);
        }

        public static double calcTwentyPercent(double amount)
        {
            return Math.Round(amount * .20, 2);
        }

    }
}


C equivalent to the previous code, but the delegate declaration has been replaced by func.


Expressions

Remote LINQ providers which do not work with in-memory data, such as database management systems, do not work with the Func and Action delegates directly. Instead they work with Expressions from the System.Linq.Expressions namespace. You surround the Func or Action with an Expression to change the delegate type to a expression variable type. The expression statement converts the Func/Action into meta-data which is used by the remote provider to write the code for accessing the data against their particular data store.

Notice below that when in debug mode you can only see the signature of a Func. But when the Func is wrapped to create an expression, then the internals which make up the delegate are visible. When the .NET compiler sees an expression, it no compiles the Func/Action into intermediate code. Instead it creates an Expression Tree for the Func/Action which provides details used by remote providers to create the code to access their data store. Expressions can be compiled (myFunc.Compile()) and then they can be invoked, but the main reason for using expressions is to provide the meta-data about the Func/Action for remote providers at run time.

.Func wrapped in an Expression


Func wrapped as an expression reveals the internals for the delegate.


IEnumerable vs IQueryable

The compiler must determine when the LINQ statement needs to be used as a Func against in-memory objects (LINQ to Objects), or when it needs to be used as meta-data for a remote provider (LINQ to Entities). Once the compiler determines whether the data is in-memory or remote, it chooses the appropriate interface to use, either: IEnumerable and IQueryable.

IEnumerable is used with in-memory collection (e.g. a List, Array, or some enumerable collection already on the heap).

IQueryable is used with remote data providers (e.g. databases), IQueryable no longer take delegates, but instead take expressions to be treated as data to remotely build the data access code.


Anonymous Types

Anonymous type were introduced in C# 3.0 and provides a convenient way encapsulate a set of read-only properties into a single object without having to define a type first.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
using System;
using System.Linq;
using System.Collections.Generic;

namespace Examples
{
    class Program
    {
        public static void Main(string[] args)
        {
            // Create an Anonymous Type called Customer
            var customer = new { Name = "Kevin", Id = 34 };

            Console.WriteLine($"Customer Name: {customer.Name} with an ID of {customer.Id}");                     
        }
    }
}


Creating an Anonymous Type

You can also project an anonymous type inside a LINQ expression, as shown below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
using System;
using System.Linq;
using System.Collections.Generic;

namespace Examples
{
    class Program
    {
        public static void Main(string[] args)
        {

            List<kevin> kevins = new List<kevin>()
            {
                new kevin {LastName = "Harris", Votes = 1, State="IL"},
                new kevin {LastName = "Costner", Votes = 22},
                new kevin {LastName = "Bacon", Votes = 21},
                new kevin {LastName = "Nealon", Votes = 15},
                new kevin {LastName = "Kline", Votes = 18}
            };


            // Project an anonymous type
           var query2 = kevins.Where(k => k.LastName != "Harris")
                              .OrderBy(k => k.LastName)
                              .ThenByDescending(k => k.State)
                              .Select(k => new {k.LastName, k.State });   
            
            foreach (var k in query2)
                Console.WriteLine(k.LastName, k.State);
            Console.ReadLine();
        }
    }

    public class kevin
    {
        public string LastName { get; set; }
        public int Votes { get; set; }
        public string State { get; set; }
    }
}


Projecting an Anonymous type inside LINQ.

Tags: 
Error | ASP.NET Developer

Error

Error message

  • Warning: Cannot modify header information - headers already sent by (output started at /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/common.inc:2748) in drupal_send_headers() (line 1232 of /srv/disk9/1218369/www/kcshadow.net/aspnet/includes/bootstrap.inc).
  • PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user '1218369_b2cf'@'185.176.40.58' for table 'watchdog': INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 0 [:db_insert_placeholder_1] => cron [:db_insert_placeholder_2] => Attempting to re-run cron while it is already running. [:db_insert_placeholder_3] => a:0:{} [:db_insert_placeholder_4] => 4 [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => http://www.kcshadow.net/aspnet/?q=linqfundamentals [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => 54.198.41.76 [:db_insert_placeholder_9] => 1534839435 ) in dblog_watchdog() (line 160 of /srv/disk9/1218369/www/kcshadow.net/aspnet/modules/dblog/dblog.module).
The website encountered an unexpected error. Please try again later.