LINQ Operators

LINQ to Objects extends any type that inherits from IEnumerable (which is almost every collection class in .NET, from simple Arrays to List<T>) to support query operations similar to those available in SQL. We can write queries using any of the built-in Standard Query Operators, or add our own operators if we need to. The standard operators cover a wide variety of categories, at present there are over fifty that form the backbone of LINQ. To get an idea of their scope, here is a list of those operators available to us –

Operator TypeOperator Name
AggregationAggregate, Average, Count, LongCount, Max, Min, Sum
ConversionCast, OfType, ToArray, ToDictionary, ToList, ToLookup, ToSequence
ElementDefaultIfEmpty, ElementAt, ElementAtOrDefault, First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault
EqualityEqualAll
GenerationEmpty, Range, Repeat
GroupingGroupBy
JoiningGroupJoin, Join
OrderingOrderBy, ThenBy, OrderByDescending, ThenByDescending, Reverse
PartitioningSkip, SkipWhile, Take, TakeWhile
QuantifiersAll, Any, Contains
RestrictionWhere
SelectionSelect, SelectMany
SetConcat, Distinct, Except, Intersect, Union


Most of the operators should be familiar if you have ever worked with a relational database writing queries in SQL.

One important distinction between writing SQL queries and LINQ queries is that the operator order is reversed. If you are used to Select-From-Where-OrderBy, it might take some time to overcome the muscle memory and move to From-Where-OrderBy-Select.

Joining Operator: GroupJoin

The GroupJoin operator performs the same task as Join operator except that GroupJoin returns a result in group based on specified group key. The GroupJoin operator joins two sequences based on key and groups the result by matching key and then returns the collection of grouped result and key.

GroupJoin requires same parameters as Join.

let’s understand GroupJoin using following Student and Standard class where Student class includes StandardID that matches with StandardID of Standard class.

public class Student{ 
    public int StudentID { get; set; }
    public string StudentName { get; set; }
    public int StandardID { get; set; }
}

public class Standard{ 
    public int StandardID { get; set; }
    public string StandardName { get; set; }
}

Consider the following GroupJoin query example.

IList<Student> studentList = new List<Student>() { 
    new Student() { StudentID = 1, StudentName = "John", StandardID =1 },
    new Student() { StudentID = 2, StudentName = "Moin", StandardID =1 },
    new Student() { StudentID = 3, StudentName = "Bill", StandardID =2 },
    new Student() { StudentID = 4, StudentName = "Ram",  StandardID =2 },
    new Student() { StudentID = 5, StudentName = "Ron" } 
};

IList<Standard> standardList = new List<Standard>() { 
    new Standard(){ StandardID = 1, StandardName="Standard 1"},
    new Standard(){ StandardID = 2, StandardName="Standard 2"},
    new Standard(){ StandardID = 3, StandardName="Standard 3"}
};

var groupJoin = standardList.GroupJoin(studentList,  //inner sequence
                                std => std.StandardID, //outerKeySelector 
                                s => s.StandardID,     //innerKeySelector
                                (std, studentsGroup) => new // resultSelector 
                                {
                                    Students = studentsGroup,
                                    StandarFulldName = std.StandardName
                                });

foreach (var item in groupJoin)
{ 
    Console.WriteLine(item.StandarFulldName );

    foreach(var stud in item.Students)
        Console.WriteLine(stud.StudentName);
}

Reference

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.groupjoin?view=net-7.0

LINQ Distinct. How To Get Distinct Values From A Collection Using LINQ.

LINQ pronounced as “Link” is a .NET component that enables the processing of the native queries directly into C# and VB.NET languages. LINQ has Distinct() function which provides the unique list of values from a single data source. 

Example Class – LINQ Distinct by Property or Field Using Distinct function

The Distinct() method is used to remove the duplicate values from the list and gives the unique values. Let us consider the example in which you need to have unique values from Book List, and you have the following model:

public class Book
{
        public int BookID { get; set; }
        public string Title { get; set; }
        public string Author { get; set; }
}

List of dummy data of the books. In real word scenario, the list is could be from live database or any other data source.


public List GetBooks() 
{
            List books = new List();

            books.Add(new Book { BookID = 1, Title = "Book Title 1", Author = "Author 1" });
            books.Add(new Book { BookID = 2, Title = " Book Title 2", Author = "Author 2" });
            books.Add(new Book { BookID = 3, Title = " Book Title 3", Author = "Author 1" });
            books.Add(new Book { BookID = 4, Title = " Book Title 4", Author = "Author 2" });
            books.Add(new Book { BookID = 5, Title = " Book Title 5", Author = "Author 8" });
            books.Add(new Book { BookID = 6, Title = " Book Title 4", Author = "Author 2" });
            books.Add(new Book { BookID = 7, Title = " Book Title 6", Author = "Author 4" });
            books.Add(new Book { BookID = 8, Title = " Book Title 8", Author = "Author 2" });
            books.Add(new Book { BookID = 9, Title = " Book Title 3", Author = "Author 3" });
            books.Add(new Book { BookID = 10, Title = " Book Title 5", Author = "Author 1" });

            return books;
}

LINQ Distinct() using Property

The Distinct() function in LINQ can be applied by the properties. You can make groups and get the first object from each group or you can use DistinctBy function to achieve the required result.

LINQ DistinctBy() On a Property

DistinctBy() apply on a specified property to get unique values from a list of objects. If you need a distinct list based on one or more properties, you can use the following code:

List bookList = GetBooks().DistinctBy(book => new { book.BookID, book.Title });

Using GroupBy and Select on a Property

By taking the above sample data, you can get the distinct author list by using the following code, as it will group the similar author named books first, and then select the first of every group in list.

List bookList = GetBooks().GroupBy(book => book.Author).Select(x => x.First()) .ToList();

LINQ Distinct By – Field

The Distinct() function in LINQ can be applied on the fields of tables also. This will group the similar data of the given field and return the unique list by selecting the first or default of the group depending on the requirement.

yourTable.GroupBy(x => x.TableFieldColumn).Select(x => x.FirstOrDefault());

When you use reference type object, LINQ will treat the values as unique even the property values are same. To overcome this situation, you can use either of the following ways to have distinct values:

LINQ Group By and Select Operators

In this method, GroupBy function is used to group the list which can either be a static list or a dynamic list. The Select operator is used to fetch the results from a list or a grouped list.

In this example, grouping is done by using the groupby operator to group Authors and Title and then Select is used to get the first result of a grouped list.

using System;
using System.Text;
using System.Linq; 
namespace MyFirstApp{
     public class LINQProgram {
              public static void Main(String[] args) {
                      List bookList = GetBooks()
                                       .GroupBy(book => new { book.Author, book.Title })
                                       .Select(book => book.FirstOrDefault());
             }
     } 
}

Distinct with IEqualityComparer

You can give an instance of IEqualityComparer to an overloaded method of the Distinct method. For that, you need to create a new class “BookComparer” that must be implementing the IEqualityComparer to overload it.

using System;
using System.Text;
using System.Linq; 

namespace MyFirstApp{

     public class BookComparer : IEqualityComparer   // Implements interface
     {
            public bool Equals(Book x, Book y) {
                if (Object.ReferenceEquals(x, y)) 
                    return true;

                if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
                    return false;

                return x.Author == y.Author && x.Title == y.Title;
            }

            public int GetHashCode(Book book) {
                if (Object.ReferenceEquals(book, null)) 
                       return 0;

                int hashBookName = book.Author == null ? 0 : book.Author.GetHashCode();
                int hashBookCode = book.Title == null ? 0 : book.Title.GetHashCode();
                return hashBookName ^ hashBookCode;

            }
     } 

     public class LINQProgram {

              public static void Main(String[] args) {

                      List bookList = GetBooks()
                                      .Distinct(new BookComparer());
             }
     }
}

Using Select and Distinct operators

You can use the Select and Distinct functions to get rid of the repeated values from a list of objects. In the following example, select and distinct operators are used to get the unique values from Books list.

using System;
using System.Text;
using System.Linq; 

namespace MyFirstApp{
     public class LINQProgram {
              public static void Main(String[] args) {
                      List bookList = GetBooks()
                                        .Select(book => new { book.Author, book.Title })
                                       .Distinct();
             }
     } 
}

LINQ Distinct by Field

If you want to achieve the distinct values for a specific field in the list, you can use the following two methods:

1. Using GroupBy and Select functions

In this approach, you need to use two LINQ functions i.e., GroupBy and Select to get the list of unique field values. You can use the following code to have groupby and select in one query.


using System;
using System.Text;
using System.Linq; 

namespace MyFirstApp{

     public class LINQProgram {

              public static void Main(String[] args) {

                      List bookList = GetBooks()
                                                             .GroupBy(o => o.Author)
                                                             .Select(o => o.FirstOrDefault());
             }
     } 
}

2. Using Select and Distinct functions

In the second approach, you need to use two LINQ functions i.e. Select and Distinct, to achieve the list of unique field values.

using System;
using System.Text;
using System.Linq; 

namespace MyFirstApp{

     public class LINQProgram {

              public static void Main(String[] args) {

                      List bookList = GetBooks()
                                                             .Select(o => new { Author = o.Author } )
                                                             .Distinct();
             }
     } 
}

Sources

https://stackoverflow.com/questions/19548043/select-all-distinct-values-in-a-column-using-linq

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.distinct?view=net-7.0

Method vs Linq based query syntax example

I need to make inner join on 3 tables; LedgerTypes, LedgerControl and Ledger.

Method based query example

//method based query
            
// your starting point - table in the "from" statement
var list = LedgerTypes
  //the source table of the inner join
  .Join(LedgerControl,
     //primary key (first part of sql "join" statement)
     t => t.Id,
     //foreign key (the second part of the "on" clause)
     lc => lc.ControllerTypeId, 
  (t, lc) => new { t, lc })   //new join
  // third table in the join clause
  .Join(Ledger, 
    //third table foreign key
    tc => tc.lc.Id, 
    //second table primary key
    l => l.ControllerID, 
  (tc, l) => new { tc, l })
      .Select(result => new {          //selection
       LedgerTypeId = result.tc.t.Id,
       LedgerTypeName = result.tc.t.ControllerTypeName,
       LedgerControlId = result.tc.lc.Id,
       LedgerControlName = result.tc.lc.ControllerCodeFullName,
       LedgerId = result.tc.lc.Id,
       LedgerName = result.tc.lc.ControllerCodeFullName
       // other assignments
});

Linq based query example

 //linq based query
var list = from t in LedgerTypes
    join lc in LedgerControl on t.Id equals lc.ControllerTypeId
    join l in Ledger on lc.Id equals l.ControllerID
    select new
       {
             LedgerTypeId = t.Id,
             LedgerTypeName = t.ControllerTypeName,
             LedgerControlId = lc.Id,
             LedgerControlName = lc.ControllerCodeFullName,
             LedgerId = l.Id,
             LedgerName = l.LedgerCodeFullName
            // other assignments
       };

I prefer query syntax because it’s readable and maintainable.

Resources

For more info read here and here

Search string array in collection using LINQ

LINQ behavior is that LINQ wouldn’t return null when results are empty rather it will return an empty enumerable. We can check this with .Any() method;

if (!YourResult.Any())

This is a LinqPad example;

var lst = new List<int>() { 1, 2, 3 };
var ans = lst.Where( i => i > 3 );

(ans == null).Dump();  // False
(ans.Count() == 0 ).Dump();  // True

Let’s go through another example where I have this string array to search;
{“dog”,”cat”};

in this string;
“This is a string and may or may not contain a word we are looking for like cat”

string input = "This is a string and may or may not contain a word we are looking for like cat";
List<string> search = new List<string>() { "dog", "cat"};
bool found = input.Split(' ').Any(x => search.Contains(x));

It works like this: the string gets split into an array of words. Then Any checks whether there is an x in this array where search.Contains(x).

Enumerable.Any(TSource) Method (IEnumerable(TSource)) (System.Linq)

Reference

What does linq return when the results are empty

Find all items in list which exist in another list using linq