Understanding LINQ Standard Query Operators

LINQ standard query operators are the methods which help you to write LINQ query. Most of these query methods operate on sequences or collection whose implement the IEnumerable<T> interface or the IQueryable<T> interface. These operators provide query capabilities including filtering, projection, aggregation, sorting and much more.

LINQ Standard Query Operators Sets

LINQ provides two sets of standard query operators, one set operate on objects of type IEnumerable<T> and the other set operate on objects of type IQueryable<T>. Also, these query methods (standard query operators) are static members of the Enumerable and Queryable static classes They are defined as extension methods of the type on which they operate. Hence, they are called either by using static method syntax or instance method syntax.

The query methods which are extended from IEnumerable<T>, operate on in-memory collections. The query methods which are extended from IQueryable<T>, operate on out-of-process memory collections and build an expression tree that represents the query to be performed.

Example for LINQ Query Method Where() Sets

In LINQ to Objects (in-memory collection), LINQ query methods would be used as anonymous method, like Where():

public static IEnumerable<TSource> Where<TSource>(
 this IEnumerable<TSource> source, Func<TSource, bool> predicate)

While in LINQ to SQL (out-memory collection), mostly LINQ query methods would be used as expression tree, like Where():

public static IQueryable<TSource> Where<TSource>(
 this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate) 

LINQ Query Method Execution

The standard query operators are executed based on the value they return. The query methods that return a singleton value (like Average, Sum, Min, Max etc.) then execute immediately. The query methods that return a sequence or collection defer the query execution. The query methods can be chained together in one query.

Writing LINQ Query with the help of Standard Query Operators (LINQ Query Methods)

string sentence = "The quick brown fox jumps over the lazy dog";
 
// Split the string into individual words to create a collection. 
string[] words = sentence.Split(' ');
 
// Using query expression syntax. 
var query = from word in words
 group word.ToUpper() by word.Length into gr
 orderby gr.Key
 select new { Length = gr.Key, Words = gr };
 
//You can also write above query by using method-based query syntax. 
var query1 = words.
 GroupBy(w => w.Length, w => w.ToUpper()).
 Select(g => new { Length = g.Key, Words = g }).
 OrderBy(o => o.Length);
 
//use either query or query1 variable to get result
foreach (var obj in query) 
{
 Console.WriteLine("\n Words of length {0}:", obj.Length);
 foreach (string word in obj.Words)
 Console.WriteLine(word);
}
 
/*output: 
 
 Words of length 3: 
 THE 
 FOX 
 THE 
 DOG 
 
 Words of length 4: 
 OVER 
 LAZY 
 
 Words of length 5: 
 QUICK 
 BROWN 
 JUMPS 
*/

Different types of standard query operators

Operator Type

Operator Name

Aggregate

Aggregate, Average, Count, LongCount, Max, Min, Sum

Concatenation

Concat

Conversions

Cast, OfType, ToList, ToArray, ToLookup, ToDictionary, AsEnumerable

Element

Single, SingleOrDefault, First, FirstOrDefault, Last, LastOrDefault, ElementAt, ElementAtOrDefault

Equality

SequenceEqual

Generation

Repeat, Range, Empty

Grouping

GroupBy

Join

Join, GroupJoin

Ordering

OrderBy, OrderByDescending, ThenBy, ThenByDescending, Reverse

Partitioning

Skip, SkipWhile, Take, TakeWhile

Projection

Select, SelectMany

Quantifier

Contains, All, Any

Restriction

Where

Set

Union, Intersect, Except, Distinct

What do you think?

I hope you will enjoy LINQ Standard Query Operators while programming with LINQ. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Difference between ADO.NET and LINQ to SQL

As you know LINQ provides a common query syntax to query any data source and ADO.NET allows you to execute query against any RDBMS like SQL Server, Oracle etc. In this article, I am sharing my view on LINQ and ADO.NET.

ADO.NET

LINQ to SQL

It is a part of .NET Framework since .NET Framework 1.0

It is a part of .NET Framework since .NET Framework 3.5

SqlConnection/OleDbConnection is used for database connectivity.

We can use context for database connectivity.

Difficult to debug and cause syntax errors at run-time.

Easy to debug and cause syntax errors at compile-time.

It has full type checking at run-time and not IntelliSense support in Visual Studio, since it used the T-SQL to query the database.

It has full type checking at compile-time and IntelliSense support in Visual Studio, since it used the .NET Framework languages like C# and VB.

It used T-SQL to query the data to query the database and some other syntax for querying the other data source.

It used LINQ to query the data which provides the uniform programming model (means common query syntax) to query the various data sources.

What do you think?

I hope you will enjoy LINQ and ADO.NET while querying data source. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

LINQ Inner Join with AND and OR condition

LINQ has a JOIN query operator that provides SQL JOIN like behavior and syntax. As you know, Inner join returns only those records or rows that match or exists in both the tables. The simple inner join example is given below:

DataContext context = new DataContext();
var q = (from pd in context.Products
 join od in context.Orders on pd.ProductID equals od.ProductID
 orderby od.OrderID
 select new
 {
 od.OrderID,
 pd.ProductID,
 pd.Name,
 pd.UnitPrice,
 od.Quantity,
 od.Price,
 }).ToList();

Inner Join with AND condition

Sometimes, you need to apply inner join with and condition. To write query for inner join with and condition you need to make two anonymous types (one for left table and one for right table) by using new keyword and compare both the anonymous types as shown below:

DataContext context = new DataContext();
var q=from cust in context.tblCustomer
 join ord in context.tblOrder
// Both anonymous types should have exact same number of properties having same name and datatype
 on new {a=(int?)cust.CustID, cust.ContactNo} equals new {a=ord.CustomerID, ord.ContactNo}
 select new 
 {
 cust.Name,
 cust.Address,
 ord.OrderID,
 ord.Quantity
 };

// Generated SQL
SELECT [t0].[Name], [t0].[Address], [t1].[OrderID], [t1].[Quantity]
FROM [tblCustomer] AS [t0]
INNER JOIN [tblOrder] AS [t1] ON (([t0].[CustID]) = [t1].[CustomerID]) AND ([t0].[ContactNo] = [t1].[ContactNo])

Note

1. Always remember, both the anonymous types should have exact same number of properties with same name and Datatype otherwise you will get the compile time error "Type inference failed in the call to Join".
2. Both the comparing fields should define either NULL or NOT NULL values.
3. If one of them is defined NULL and other is defined NOT NULL then we need to do typecasting of NOT NULL field to NULL data type like as above

Inner Join with OR condition

Sometimes, you need to apply inner join with or condition. To write query for inner join with or condition you need to use || operator in where condition as shown below:

DataContext context = new DataContext();
var q=from cust in context.tblCustomer
 from ord in context.tblOrder
 where (cust.CustID==ord.CustomerID || cust.ContactNo==ord.ContactNo)
 select new 
 {
 cust.Name,
 cust.Address,
 ord.OrderID,
 ord.Quantity
 };

// Generated SQL
SELECT [t0].[Name], [t0].[Address], [t1].[OrderID], [t1].[Quantity]
FROM [tblCustomer] AS [t0], [tblOrder] AS [t1]
WHERE (([t0].[CustID]) = [t1].[CustomerID]) OR ([t0].[ContactNo] = [t1].[ContactNo])

What do you think?

I hope you will enjoy LINQ query with AND and OR conditions while programming with LINQ. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.