Debugging LINQ Query in Visual Studio 2010 with LINQPad

I found out that we can use LINQPad (LINQPad Visualizer) to debug LINQ queries in Visual Studio 2010. To be able to do this we need to:
- install LINQPad

- download LINQPad Visualizer

- copy these files from LINQPad folder (eg. Program Files (x86)\LINQPad4): LINQPad.exe and LINQPad.exe.config
and from the downloaded LINQPad Visualizer folder: linqpadvisualizer.dll, Newtonsoft.Json.dll and Newtonsoft.Json.xml
to Documents\Visual Studio 2010\Visualizers (create the folder if not exists)

- copy LINQPad.exe and LINQPad.exe.config to the same folder as devenv.exe (eg. C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE)

- restart Visual Studio

Then we can put a breakpoint on a LINQ query and start debugging. When we want to inspect a variable, put this on the Name column on Watch window

new System.WeakReference([variable_to_inspect])

then click the magnifier glass icon on the Value column, the visualizer should appear.


Further reading:
https://www.simple-talk.com/dotnet/.net-framework/linq-secrets-revealed-chaining-and-debugging/

Left and Right Outer Join in LINQ Query

Say we have Staff and Department entities as below:

List<Staff> staffs = new List<Staff>() { 
 new Staff { FullName = "Person 1", DepartmentId =1 },
 new Staff { FullName = "Person 2", DepartmentId =1 },
 new Staff { FullName = "Person 3", DepartmentId =1 },
 new Staff { FullName = "Person 4", DepartmentId =2 },
 new Staff { FullName = "Person 5", DepartmentId =2 },
 new Staff { FullName = "Person 6", DepartmentId =3 },
 new Staff { FullName = "Person 7", DepartmentId =3 }
};

List<Department> departments = new List<Department>() {
 new Department { DepartmentId = 1, Name = "Dept One"},
 new Department { DepartmentId = 3, Name = "Dept Three"},
 new Department { DepartmentId = 4, Name = "Dept Four"},
 new Department { DepartmentId = 5, Name = "Dept Five"}
};

To do Left Outer Join Staff with Department is:

var leftJoinQuery = 
  from staff in staffs
  join dept in departments on staff.DepartmentId equals dept.DepartmentId into joinedStaffDept
  from r in joinedStaffDept.DefaultIfEmpty()
  //select r;  // this returns 'dept' list
  select new { 
    staff.FullName, 
    DeptName = r != null ? r.Name : null
    //DepartmentName = dept != null ?dept.Name : null  // using 'dept' here does not work
  };

Note that the combined result 'joinedStaffDept' actually consists of the first entity only 'Staff'. It doesn't include 'Department'.
Below is the result:


To do Right Outer Join, we need to swap the order of the entities joined:

var rightJoinQuery = 
  from dept in departments
  join staff in staffs on dept.DepartmentId equals staff.DepartmentId into joinedDeptStaff
  from r in joinedDeptStaff.DefaultIfEmpty()
  //select r; // this returns 'staff' list
  select new {
    FullName = r != null? r.FullName : null, // using 'staff' here does not work
    dept.Name
 };

The result:

Deferred Execution in LINQ Query Expression or Standard Query Operators Extension Methods

An IQueryable and IEnumerable type variable can be used to specify a query in query or method syntax (query syntax is also known as query expression while method syntax as standard query operators extension methods; see this MSDN article for definition). However the execution will take place when the items are required.

Methods such as Count(), ToList(), ToArray(), ToDictionary() or ToLookup() will iterate through items thus executing the query definition. Other methods such as Average(), Sum(), Max() and Min() will also execute the query definition.

If one of those methods mentioned above are called twice then the query definition will be executed again to get the result. The execution can be very expensive as the result may be retrieved from database, network, etc. This is done to get fresh result every time from the source.

To avoid re-executing the query definition in a query expression or standard query operators extension methods, store the result into a collection. When obtaining result from the collection, the query definition won't be run again rather the cached/stored result will be used.

Below is an example to clarify:

static void Main(string[] args)
{
    List<int> numbers = new List<int> { 1, 2, 3, 4, 5 };

    int invocationsCount = 0;
    Func<int, int> func = number =>
    {
        invocationsCount++;
        return number;
    };

    // 1. define the query expression or extension methods (method invocations)
    IEnumerable<int> selection = from n in numbers
                                    select func(n);
    //IEnumerable<int> selection = numbers.Select(n => func(n));
    Console.WriteLine("1. invocationsCount = {0}", invocationsCount);

    // 2. do a loop
    foreach (var item in selection)
    {
    }
    Console.WriteLine("2. invocationsCount = {0}", invocationsCount);

    // 3. do a count
    selection.Count();
    Console.WriteLine("3. invocationsCount = {0}", invocationsCount);

    // 4. do an average
    selection.Average();                
    Console.WriteLine("4. invocationsCount = {0}", invocationsCount);

    // 5. do another loop
    foreach (var item in selection)
    {
    }
    Console.WriteLine("5. invocationsCount = {0}", invocationsCount);

    // 6. do ToList() and cache it to a collection
    List<int> collection = selection.ToList();
    Console.WriteLine("6. invocationsCount = {0}", invocationsCount);

    // 7. do the loop on the cache collection
    foreach (var item in collection)
    {
    }
    Console.WriteLine("7. invocationsCount = {0}", invocationsCount);


    Console.ReadLine();
}

Grouping Data with LINQ

To group data in LINQ, we can use group ... by ... clause in query syntax or GroupBy() in method syntax. We will go through some examples and explanations along this post.


SIMPLE GROUPING
Let's start with simple grouping, below is an example:

// query syntax
var groupedData = from c in context.Customers
                  group c by c.Country;
// method syntax
var groupedData = context.Customers.GroupBy(c => c.Country);

Grouping in LINQ will result in an object of type IEnumerable<IGrouping<TKey,TSource>> which in this case is IEnumerable<IGrouping<String,Customer>>. IGrouping is a special class that has two properties; a key property and an IEnumerable<TSource> property that holds all the items corresponding to the key.

If we try to debug the 'groupedData' object, we will get something like this (you may need to click the image below to make it displayed bigger):

As we can see there's a 'Key' property and another property that contains some items corresponding to the 'Key' value.

To print out these items on screen:

foreach(var groupedItems in groupedData)
{
    Console.WriteLine(string.Format("Key: {0}", groupedItems.Key));
    foreach (var item in groupedItems)
    {
        Console.WriteLine(string.Format("{0} - {1}", item.CompanyName, item.Country));
    }
    Console.WriteLine("----------------------------------");
}

GROUPING WITH MORE THAN ONE KEY
If we want to have a grouping using two keys, we could use group x by new { x.Key1, x.Key2 } in query syntax or GroupBy( x => new { x.Key1, x.Key2 } ) in method syntax. Below is an example:

// query syntax
var groupedData2 = from c in context.Customers
                   group c by new { c.Country, c.City };
// method syntax
var groupedData2 = context.Customers.GroupBy(c => new {c.Country, c.City});

foreach (var groupedItems in groupedData2)
{
    //note that the Keys' names now become part of Key properties; ie. Key.Country and Key.City
    Console.WriteLine(string.Format("Key: {0} - {1}", groupedItems.Key.Country, groupedItems.Key.City));
    foreach (var item in groupedItems)
    {
        Console.WriteLine(string.Format("{0} - {1} - {2}", item.CompanyName, item.City, item.Country));
    }
    Console.WriteLine("----------------------------------");
}

PROJECTION
Here is an example of projecting the result into anonymous type objects:

// query syntax
var groupedData3 = from c in context.Customers
                   group c by c.Country into grp
                   select new
                   {
                       Key = grp.Key,
                       Items = grp.Select(g => new { g.CompanyName, g.Country })
                   };
// method syntax
var groupedData3 = context.Customers.GroupBy(c => c.Country).
                   Select(grp => new {
                                       Key = grp.Key, 
                                       Items = grp.Select(g => new {g.CompanyName, g.Country})
                                     }
                   );

foreach (var groupedItems in groupedData3)
{
    Console.WriteLine(string.Format("Key: {0}", groupedItems.Key));
    foreach (var item in groupedItems.Items)
    {
        Console.WriteLine(string.Format("{0} - {1}", item.CompanyName, item.Country));
    }
    Console.WriteLine("----------------------------------");
}

Below is another example that projects the result into strong typed objects.
The classes (made simple for demonstration purpose):

public class CompanyViewModel
{
    public string Name { get; set; }
    public string Country { get; set; }
}

public class GroupedCompanies
{
    public string CountryKey { get; set; }
    public IEnumerable<CompanyViewModel> Companies { get; set; }
}

Then the query:

var groupedData4 = from c in context.Customers
                   group c by c.Country into grp
                   select new GroupedCompanies
                   {
                       CountryKey = grp.Key,
                       Companies = grp.Select(g => new CompanyViewModel { Name = g.CompanyName, Country = g.Country })
                   };
foreach (GroupedCompanies groupedItems in groupedData4)
{
    Console.WriteLine(string.Format("Key: {0}", groupedItems.CountryKey));
    foreach (CompanyViewModel item in groupedItems.Companies)
    {
        Console.WriteLine(string.Format("{0} - {1}", item.Name, item.Country));
    }
    Console.WriteLine("----------------------------------");
}

GROUPING WITH MORE THAN ONE KEY + PROJECTION
Finally this example shows a combination of grouping with two keys and projection:

// query syntax
var groupedData5 = from c in context.Customers
                   group c by new { c.Country, c.City } into grp
                   select new
                   {
                       Key = grp.Key,
                       Items = grp.Select(g => new { g.CompanyName, g.City, g.Country })
                   };
// method syntax
var groupedData5 = context.Customers.GroupBy( c => new {c.Country, c.City} ).
                   Select( grp => new {
                                       Key = grp.Key, 
                                       Items = grp.Select(g => new {g.CompanyName, g.City, g.Country})
                                      }
                   );