I went a little nuts on this simple console program. I wanted to demonstrate different ways to use the Join operator with in-memory objects from a collection and pulling information from a database. In the process of wanting to leverage anonymous types in the query I was playing around with different ways to display the data. Do I just iterate thru the query and display it. Or do I take that data, create objects, and populate another collection type with the data. Again, your personal preference might be different. I was just playing around with what ways would work.
I broke the code up into different repositories for the in-memory data and database data. I also created a WriteResults method in an attempt to use a central class to display data. I know lots of work for a simple console program. However I think it shows some cool core concepts with LINQ queries.
You will notice that the one common class that’s the same from other examples is the Employee and Department classes along with their respective repository classes. I’ve added a couple more for the database data which I’ll go over below.
MovieAndReviewRepo.cs Code Contents
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyLinq
{
/// <summary>
/// Class used as a repository for the MovieAndReview class object data.
/// </summary>
public class MovieAndReviewRepo
{
// Public Read-Only Properties
//
public List<MovieAndReview> MovieAndReviewList { get; private set; }
// Constructors
//
public MovieAndReviewRepo()
{
MovieAndReviewList = new List<MovieAndReview>();
}
// Public Methods
//
public void AddToRepo(MovieAndReview movieObject)
{
MovieAndReviewList.Add(movieObject);
}
}
}
I created this repository class to help with storing any anonymous types from the LINQ queries against the database. What this class is doing is creating a list of the MovieAndReview type and storing the objects via the add method. The list is created when the class is instantiated via the constructor.
EmployeeAndDepartmentRepo.cs Code Contents
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyLinq
{
/// <summary>
/// Class used as a repository for the EmployeeAndDepartment class object data.
/// </summary>
public class EmployeeAndDepartmentRepo
{
// Public Read-Only Properties
//
public List<EmployeeAndDepartment> EmployeeDepartmentList { get; private set; }
// Constructors
//
public EmployeeAndDepartmentRepo()
{
EmployeeDepartmentList = new List<EmployeeAndDepartment>();
}
// Public Methods
//
public void AddToRepo(EmployeeAndDepartment employeeObject)
{
EmployeeDepartmentList.Add(employeeObject);
}
}
}
This class is performing similar actions as the MovieAndReviewRepo class. It’s holding a list of EmployeeAndDepartment objects. The list is created via the constructor upon class instantiate. Objects are added to the list via the Add method.
WriteResults.cs Code Contents
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyLinq
{
public static class WriteResults
{
// Public Static Methods
//
// Method that displays the employee name and department.
public static void Write(List<EmployeeAndDepartment> list)
{
foreach (var item in list)
{
Console.WriteLine("Name: {0} - Department: {1}", item.EmployeeName, item.EmployeeDepartment);
}
Console.WriteLine();
}
// Method that displays the movie title and review.
public static void Write(List<MovieAndReview> list)
{
foreach (var item in list)
{
Console.WriteLine("Title: {0} - Review: {1}", item.MovieTitle, item.MovieReview);
}
Console.WriteLine();
}
// Method that displays the movie title and review from a strongly types object.
public static void Write(IEnumerable<MovieAndReview> list)
{
foreach (var item in list)
{
Console.WriteLine("Title: {0} - Review: {1}", item.MovieTitle, item.MovieReview);
}
Console.WriteLine();
}
// Method that displays employee name and department from a strongly typed object.
public static void Write(IEnumerable<EmployeeAndDepartment> list)
{
foreach (var item in list)
{
Console.WriteLine("Name: {0} - Department: {1}", item.EmployeeName, item.EmployeeDepartment);
}
Console.WriteLine();
}
}
}
This class is my attempt to contain all the way you can write results from the code into one class. I made it a static class so I didn’t have to create an object just to call on the various Write methods in the class. I wanted the ability to call directly on them
EmployeeRepo.cs Code Contents
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyLinq
{
/// <summary>
/// Class used as a repository for employee objects.
/// </summary>
public class EmployeeRepo
{
// Public Methods
//
// Method that returns the private employee list collection.
public IEnumerable<Employee> GetAll()
{
return _employees;
}
// Private Methods
//
// Creating a simple in memory list of employees.
List<Employee> _employees = new List<Employee>
{
new Employee(1, "Daniel", 1, new DateTime(2009, 12, 22)),
new Employee(2, "Sara", 2, new DateTime(2012, 3, 12)),
new Employee(3, "John", 4, new DateTime(2010, 5, 10)),
new Employee(4, "Debbie", 2, new DateTime(2005, 1, 28)),
new Employee(5, "Poonam", 2, new DateTime(2002, 2, 22)),
new Employee(6, "Jack", 3, new DateTime(2001, 6, 13)),
new Employee(7, "Ben", 4, new DateTime(2015, 5, 2)),
new Employee(4, "Debbie", 2, new DateTime(2005, 1, 28)),
new Employee(5, "Poonam", 2, new DateTime(2002, 2, 22)),
new Employee(6, "Jack", 3, new DateTime(2001, 6, 13))
};
}
}
This class is almost the same as previous examples. With one minor difference. You will notice in the list that’s being returned you see some duplicate data. This will come into play in later in this example.
EmployeeAndDepartment.cs Code Contents
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyLinq
{
/// <summary>
/// Class used to create objects that hold employee name and employee department data.
/// </summary>
public class EmployeeAndDepartment : IEquatable<EmployeeAndDepartment>
{
// Public Read-Only Properties
//
public string EmployeeName { get; private set; }
public string EmployeeDepartment { get; private set; }
// Constructors
//
public EmployeeAndDepartment(string employeeName, string employeeDepartment)
{
this.EmployeeName = employeeName;
this.EmployeeDepartment = employeeDepartment;
}
// IEquatable Interface Implementation Methods.
//
public bool Equals(EmployeeAndDepartment other)
{
if (EmployeeName == other.EmployeeName && EmployeeDepartment == other.EmployeeDepartment)
return true;
return false;
}
public override int GetHashCode()
{
int hashEmployeeName = EmployeeName == null ? 0 : EmployeeName.GetHashCode();
int hashEmployeeDepartment = EmployeeDepartment == null ? 0 : EmployeeDepartment.GetHashCode();
return hashEmployeeName ^ hashEmployeeDepartment;
}
}
}
I need to make a mention on this class. You will notice that I’m inheriting the IEquatable interface. The reason for this is because in my attempts to convert the data from the queries anonymous type in the Program.cs class you will lose the ability to run the Distinct() method properly. That is unless you inherit and implement the Equals and GetHashCode methods from IEquatable interface. Why am I doing this? In the EmployeeRepo class I mentioned there was duplicate data. The Distinct method takes care of the duplicate data if you aren’t doing any kind of checks on this anywhere else in the code. For example I’m not doing on the database data because I have safeguards built into the database that I won’t need to. However if you run into areas using the Distinct method will come in handy. And if you’re converting it to another data type you will need to implement the interface.
Program.cs Code Contents
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Xml.Linq;
using System.Diagnostics;
using System.Collections;
namespace MyLinq
{
class Program
{
/// <summary>
/// Demonstrating examples of different ways to use the join operator methods.
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
executeJoinOnInMemoryObjects();
executeGroupJoinOnInMemoryObjects();
executeDistinctQueries();
executeJoinsOnDatabaseObjects();
Console.ReadLine();
}
//Private Methods
//
private static void executeJoinOnInMemoryObjects()
{
// Creating the employee object, department object, and getting all the elements to store in memory.
var employeeRepo = new EmployeeRepo();
var departmentRepo = new DepartmentRepo();
// You can comment these out to see that the alternate combined methods work.
var employees = employeeRepo.GetAll();
var departments = departmentRepo.GetAll();
// Executing a comprehension query on the in-memory objects.
// This is used to display only the departments with employees including duplicates.
var inMemoryComprehensionQuery = from d in departments
join e in employees on d.ID equals e.DepartmentID
select new { DepartmentName = d.Name, EmployeeName = e.Name };
// Another way is to call on the GetAll() method directly from the query.
var combinedInMemoryComprehensionQuery = from d in departmentRepo.GetAll()
join e in employeeRepo.GetAll() on d.ID equals e.DepartmentID
select new { DepartmentName = d.Name, EmployeeName = e.Name };
// Executing a extension method query on the in-memory objects.
var inMemoryExtensionQuery = departments.Join(employees, d => d.ID, e => e.DepartmentID,
(d, e) => new { DepartmentName = d.Name, EmployeeName = e.Name });
// Another way is to call on the GetAll() method directly from the query.
var combinedInMemoryExtensionQuery = departmentRepo.GetAll().Join(employeeRepo.GetAll(), d => d.ID, e => e.DepartmentID,
(d, e) => new { DepartmentName = d.Name, EmployeeName = e.Name });
// Results Display
//
// Displaying the results of the data of the comprehension query that contains the anonymous type.
Console.WriteLine("****In Memory Comprehension Query Details****");
var inMemoryComprehensionQueryDetails = new EmployeeAndDepartmentRepo();
foreach (var item in inMemoryComprehensionQuery)
{
var employeeAndDepartment = new EmployeeAndDepartment(item.EmployeeName, item.DepartmentName);
inMemoryComprehensionQueryDetails.AddToRepo(employeeAndDepartment);
}
WriteResults.Write(inMemoryComprehensionQueryDetails.EmployeeDepartmentList);
// Displaying the results of the data of the extension query that contains the anonymous type.
Console.WriteLine("****In Memory Extension Query Details****");
var combinedInMemoryExtensionQueryDetails = new EmployeeAndDepartmentRepo();
foreach (var item in combinedInMemoryExtensionQuery)
{
var employeeAndDepartment = new EmployeeAndDepartment(item.EmployeeName, item.DepartmentName);
combinedInMemoryExtensionQueryDetails.AddToRepo(employeeAndDepartment);
}
WriteResults.Write(combinedInMemoryExtensionQueryDetails.EmployeeDepartmentList);
}
private static void executeGroupJoinOnInMemoryObjects()
{
// Creating the employee object, department object, and getting all the elements to store in memory.
var employeeRepo = new EmployeeRepo();
var departmentRepo = new DepartmentRepo();
// You can comment these out to see that the alternate combined methods work.
var employees = employeeRepo.GetAll();
var departments = departmentRepo.GetAll();
// Executing a comprehension query on the in-memory objects.
// The into keyword is what makes this a group join.
var inMemoryComprehensionQuery = from d in departments
join e in employees on d.ID equals e.DepartmentID
into eg
select new { DepartmentName = d.Name, Employees = eg };
var combinedInMemoryComprehesionQuery = from d in departmentRepo.GetAll()
join e in employeeRepo.GetAll() on d.ID equals e.DepartmentID
select new { DepartmentName = d.Name, EmployeeName = e.Name };
// Executing a extension method query on the in-memory objects.
var inMemoryExtensionQuery = departments.GroupJoin(employees, d => d.ID, e => e.DepartmentID,
(d, e) => new { DepartmentName = d.Name, Employees = e });
var combinedInMemoryExtensionQuery = departmentRepo.GetAll().GroupJoin(employeeRepo.GetAll(), d => d.ID, e => e.DepartmentID,
(d, e) => new { DepartmentName = d.Name, EmployeeName = e });
}
private static void executeDistinctQueries()
{
// Here I'm showing some different ways to use the Distinct method on comprehension and extension method queries.
var employeeRepo = new EmployeeRepo();
var departmentRepo = new DepartmentRepo();
// One way to create something like the Distinct method is to use the into keyword in the query.
// The into keyword not only acts as a group join and does what the distinct method function does in a similar fashion.
// The into keyword acts as similar to the Distinct() method call.
var distinctComprehensionQueryExample = from d in departmentRepo.GetAll()
join e in employeeRepo.GetAll() on d.ID equals e.DepartmentID
into eg
select new { DepartmentName = d.Name, EmployeeName = eg };
// Another way is to wrap the entire query in () and call the Distinct method directly.
var distinctComprehensionQueryExampleTwo = (from d in departmentRepo.GetAll()
join e in employeeRepo.GetAll() on d.ID equals e.DepartmentID
select new { DepartmentName = d.Name, EmployeeName = e.Name }).Distinct();
// Example showing how to use an extension method query and calling the Distinct method.
var distinctExtensionQueryExample = departmentRepo.GetAll().Join(employeeRepo.GetAll(), d => d.ID, e => e.DepartmentID,
(d, e) => new { DepartmentName = d.Name, EmployeeName = e.Name }).Distinct();
// Another way is to use the strongly typed class for the type. This gives the advantage of being
// able to IEnumerable.
var stronglyTypedObjectQuery = departmentRepo.GetAll().Join(employeeRepo.GetAll(), d => d.ID, e => e.DepartmentID,
(d, e) => new EmployeeAndDepartment(e.Name, d.Name)).Distinct();
// Results Display
//
// Displaying the results of the data of the comprehension query that contains the anonymous type.
Console.WriteLine("****Distinct In Memory Extension Query Details****");
var distinctExtensionQueryDetails = new EmployeeAndDepartmentRepo();
foreach (var item in distinctExtensionQueryExample)
{
var employeeAndDepartment = new EmployeeAndDepartment(item.EmployeeName, item.DepartmentName);
distinctExtensionQueryDetails.AddToRepo(employeeAndDepartment);
}
WriteResults.Write(distinctExtensionQueryDetails.EmployeeDepartmentList);
// Displaying the results of the strongly typed query object.
Console.WriteLine("****Displaying The Results Of The Strongly Typed Distinct Query****");
WriteResults.Write(stronglyTypedObjectQuery);
}
private static void executeJoinsOnDatabaseObjects()
{
MovieReviewsDataContext movieReviewsContext = new MovieReviewsDataContext();
// Example of a comprehension query to pull movie review data from a database.
// The database has no table associations to the query will perform a join to pull the information needed.
var comprehensionMovieReviews = from m in movieReviewsContext.Movies
join r in movieReviewsContext.Reviews on m.MovieID equals r.ReviewID
select new { Movie = m.Title, MovieReview = r.Summary };
// Example of an extension query pulling the movie review data from a database.
var extensionMovieReviews = movieReviewsContext.Movies.Join(movieReviewsContext.Reviews, m => m.MovieID, r => r.ReviewID,
(m, r) => new { MovieTitle = m.Title, MovieReview = r.Summary });
// Another way is to use the strongly typed class for the type. This gives the advantage of being
// able to IEnumerable.
var stronglyTypedObjectQuery = movieReviewsContext.Movies.Join(movieReviewsContext.Reviews, m => m.MovieID, r => r.ReviewID,
(m, r) => new MovieAndReview(m.Title, r.Summary));
// Results Display
//
Console.WriteLine("****Movie Review Extension Method Details****");
var extensionMovieReviewsDetails = new MovieAndReviewRepo();
foreach (var item in extensionMovieReviews)
{
var movieAndReview = new MovieAndReview(item.MovieTitle, item.MovieReview);
extensionMovieReviewsDetails.AddToRepo(movieAndReview);
}
WriteResults.Write(extensionMovieReviewsDetails.MovieAndReviewList);
// Displaying the results of the strongly typed object.
Console.WriteLine("****Strongly Typed Movie Review Details****");
WriteResults.Write(stronglyTypedObjectQuery);
}
}
}
Wow all this for a console program huh? 🙂 There seems to be a lot going on in this class that falls under proof of concept stuff. What I’ve done is separate out into methods the different join methods on the in-memory objects and the database. I’m showing more examples on the in-memory objects however the same concepts would apply to the database data.
For the in-memory data you will notice that I’m showing both Query and Method syntax for querying the data. To be able to write out the results I’m iterating thru the query that contains the anonymous type, creating the appropriate object and its properties, and adding it to a collection. Then I’m letting the write method do what it needs to in displaying the results.
In the executeDistinctQueries method I’m showing how the use of the inheritance in the EmployeeAndDepartment class come into play. I’m showing both types of queries. But in the query instead of creating an anonymous type I’m creating a new type of EmployeeAndDepartment and using the constructor to create the object. Doing it this way makes it so I don’t have to add more code to iterate thru a collection with an anonymous type, convert the type, then display what results I need. I’m not sure if I like it this way but this is how I got this to work. I’m still on the lookout for simplifying this even more. So stay tuned for more examples later on.
For the database data my query is joining the movie and review data tables by their respective ID properties. I’m showing both Query and Method LINQ queries to perform these tasks. No need for the Distinct method because I have safeguards in the database to keep from having duplicate data. I’m not doing inheritance in the MovieAndReview class. So to give the ability to use the Write class I need to convert the data to a type then call on the method that
iterates thru and displays the results.
Like I’ve said a few times. This is a lot for a simple console program. What it does show is some core concepts of seperating things out and how data conversion can happen. Hope you enjoyed this one.. 🙂
The Pragmatic Blogosphere 