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

All about LINQ operators

A short list of LINQ operators.

Using IN clause

This is similar to database IN keyword;

var myInClause = new string[] {"One", "Two", "Three"};

var results = from x in MyTable
              where myInClause.Contains(x.SomeColumn)
              select x;
// OR
var results = MyTable.Where(x => myInClause.Contains(x.SomeColumn));

Using ALL operator

Working with simple types

//does all numbers are greater than 10
int[] IntArray = { 11, 22, 33, 44, 55 };
var Result = IntArray.All(x => x > 10);
Console.WriteLine("Is All Numbers are greater than 10 : " + Result);

//does all names has characters greater than three
string[] stringArray = { "Khan", "Ali", "Adam", "Eve", "Joe" };
Result = stringArray.All(name => name.Length > 3);
Console.WriteLine("Is All Names are greater than 3 Characters : " + Result);

var letterResult = stringArray.All(name => name.StartsWith("A"));
Console.WriteLine("Is All Names start with letter A : " + letterResult);

//all numbers can be divided by three
int[] numbers = { 3, 6, 9, 12};
bool iSNumbersDivided = numbers.All(number => number % 3 == 0);
Console.WriteLine($"Numbers are divisible by three = {iSNumbersDivided}");

Working with complex types

//Check whether age of all animals in the zoo is greater than 1 year
bool response = animalData.All(x => x.AnimalAge > 1);
Console.WriteLine($"Is All Animals are greater than 1 years old : {response}");

//get all animas who are feed by milk
var zooSubSet = animalData.Where(x => x.Food.All(y => y.FoodType == "Milk"));
foreach(var item in zooSubSet)
{
    Console.WriteLine($"Animal Name: {item.AnimalName}");
}

Resources

https://stackoverflow.com/questions/959752/where-in-clause-in-linq

https://coderedirect.com/questions/644629/linq-nested-list-contains

KeyValue pair class

The KeyValue pair class stores a pair of values in a single list.

It’s super easy to create a list of single value. Here is an example;

List<string> firstList =  new List<string> {"'cover page$'", "'i# milestones$'", "'ii# tasks$'" };
List<string> secondList = new List<string> { "'cover page$'", "'i# milestones$'", "'ii# tasks$'" };
var exceptList = secondList.Except(firstList);
Console.WriteLine($"\nsingle string: Value in second list that are not in first List");
foreach (var val in exceptList)
{
     Console.WriteLine($"single string: {val}");
}

What if we want to store pair of values instead of creating any custom classes? We can use KeyValue pair class;

var parentList = new List<KeyValuePair<string, string>>()
{
    new KeyValuePair<string, string>("v2-2021", "'cover page$'"),
    new KeyValuePair<string, string>("v2-2021", "'i# milestones$'"),
    new KeyValuePair<string, string>("v2-2021", "'ii# tasks$'"),
    new KeyValuePair<string, string>("v2-2021", "'iii# spendplan$'"),
};
var parentSubList = new List<KeyValuePair<string, string>>()
{
    new KeyValuePair<string, string>("v2-2021", "'cover page$'"),
    new KeyValuePair<string, string>("v2-2021", "'i# milestones$'"),
    new KeyValuePair<string, string>("v2-2021", "'ii# tasks$'"),
};
var exceptList1 = parentSubList.Except(parentList);
Console.WriteLine($"\nparentSubList->parentList: Value in second list that are not in first List");
foreach (var val in exceptList1)
{
    Console.WriteLine($"{val}");
}
IsASubset = parentSubList.All(i => parentList.Contains(i));
Console.WriteLine($"\nparentSubList->parentList: all members of subset (parentSubList) exists in list1 (parentList): {IsASubset}");
}

KeyValue pair class can also be used like this;

var myList = new List<KeyValuePair<string, string>>();
//add elements now
myList.Add(new KeyValuePair<string, string>("v2-2021", "'cover page$'"));
myList.Add(new KeyValuePair<string, string>("v2-2021", "'i# milestones$'"));
myList.Add(new KeyValuePair<string, string>("v2-2021", "'ii# tasks$'"));
foreach (var val in myList)
{
    Console.WriteLine($"Another style: {val}");
}

LINQ methods, for example Except can be used without implementing any Comparer classes.