Inside our business logic, we can use LINQ to help us implement most of the logic.
For more on LINQ see this 101 LINQ samples in C#
There are two ways to write LINQ statements: using Query syntax or using Method syntax:
// Query syntax
var result = from c in _courses.All()
where c.Semester == "20143"
orderby c.Name
select c;
// The same query, using Method syntax:
var result = _courses.All().Where(c => c.Semester == "20143").OrderBy(c => c.Name);
//The same query but in with MS SQL syntax
SELECT * FROM courses WHERE Semester = '20143' ORDER BY Name asc;Both queries are equivalent, and will result in identical queries being executed. There are however cases where it will be more convenient to use one method over the other, so it is probably not a good idea to reject one or the other entirely.
A LINQ statement will usually result in a query object:
var result = _courses.All().Where(c => c.Semester == "20143");The result variable will now contain a query object. This object will however not be materialized (i.e. the query won't be executed), until the data is actually needed. There are several ways that could happen:
- Some code will try to enumerate the result, by calling foreach on the collection.
- The result is converted to a list, using result.ToList() or result.ToArray()
- We try to access a given value from the result, using First()/FirstOrDefault()/Single()/SingleOrDefault()
- We try to use some of the values in the result, by calling aggregate functions such as Count()/Min()/Max()/Average() etc.
This must be kept in mind at all times, since unneccesary materialization might result in a lot of data being fetched needlessly.
We will often need to access a single record from a table. For example in functions like
public Student getStudentByID(int ID){
//Some code that returns a single Student
}We have 4 basic ways to do this: First(), FirstOrDefault(), Single() and SingleOrDefault(). These functions all return a single record from a queryable ojbect, but they all have different usage details:
- First() returns the first record from a collection, and throws an exception if the collection is empty.
- FirstOrDefault() returns the first record from a collection or null if the collection is empty.
- Single() returns the first record from a collection, and throws an exception if the collection is empty OR if there are more than one record that match the query string.
- SingleOrDefault() returns the first record from a collection or null if the collection is empty and throws an exception if there are more than one record that match the query string.
Examples of usage:
public Student getStudentByID(int ID){
return _students.All().SingleOrDefault(s => s.ID == ID);
}or
public Student getStudentByID(int ID){
return (from s in _students.All()
where s.ID equals ID
select s).SingleOrDefault();
}Sometimes we want to select only x many records but not all of them. For this we have four actions.
Please note that these function are often used with the orderby keyword
- Take(int x) returns x first records it finds, and throws an exception if the source is null.
- Skip(int x) skips the first x records but returns the rest in the collection, and throws an exception if the source is null.
- TakeWhile() Keeps taking element until a specific condition is met, and throws an exception if the source or predicate is null.
- SkipWhile() Skips all element until a specific condition is met, and throws an exception if the source or predicate is null.
Examples of usage:
public IEnumerable<Student> getFirstFiveStudents(){
return (from s in _students.All()
orderby s.Name
select s).Take(5);
}
public IEnumerable<Student> getAllButFirstFive(){
return (from s in _students.All()
orderby s.Name
select s).Skip(5);
}
public IEnumerable<Student> getAllStudentsOverFive(){
return (from s in _students.All()
orderby s.Grade desc
select s).TakeWhile(s.Grade >= 5);
}
public IEnumerable<Student> getAllStudentsBelowFive(){
return (from s in _students.All()
orderby s.Grade desc
select s).SkipWhile(s.Grade >= 5);
}We are going to need to join data from two or more tables in any moderately complex application. For instance, assume we've got course instances in one table, and course templates in another. The name of each course would be stored in the course template, since we would have data duplication if the name was stored for each instance of a course. However, when getting a list of course instances (such as by semester), we will probably need to include the name of each course instance, which requires a join:
var result = from ci in _courseInstances.All()
join ct in _courseTemplates.All() on ci.CourseID equals ct.ID
where ci.Semester == "20143"
select new
{
ID = ci.ID,
Name = ct.Name
// etc.
};This will result in an INNER JOIN query being executed, it will select all course instances for semester "20143" (autumn 2014), and we will include the name of each course which we fetch from the course template.
Notice the use of the keyword equals in the join statement.
If necessary, we can join more than two tables together in a given LINQ statement as well. For instance, assume that a given course instance can have 0 or more teachers, and that our data contains some courses with 0 teachers, some with 1 and some with more than one. Given this design, we would have a table containing all teachers (which we can access via _teachers), and another table containing teacher registrations (accessible via the _teacherRegistrations IRepository). The following query would return a list of all teachers which are teaching a course in fall semester 2014, and the name of the course each teacher is teaching:
var result = from t in _teachers.All()
join tr in _teacherRegistrations.All() on t.SSN equals tr.SSN
join c in _courses.All() on c.ID equals tr.CourseID
where c.Semester == "20143"
select new
{
Name = t.Name,
SSN = t.SSN,
CourseName = c.Name
// etc.
};Do note that if a given teacher is teaching more than one course, (s)he will appear equally often in the result.
In some cases, we need other join types. Again, assume multiple teachers for a given course, and we want a list of all courses in a given semester, including the main teacher for each course. A regular join statement would simply skip those course instances where a teacher hasn't been registered. What we would need in that case is a left outer join. In this case, we use the into keyword to select all teachers in a given course into a collection which we can then manipulate further.
It is almost certain that we will need to use the same or similar code in more than one place. A typical scenario is that we need to load some entity (a course, a teacher etc.) by ID, and if the entity cannot be found, we would like to throw a particular exception with a given error message (more on error handling later...):
var result = from t in _teachers.All()
where t.ID == id
select new TeacherDTO
{
Name = t.Name,
SSN = t.SSN,
// etc.
};
if (result == null)
{
// Note: not necessarily the best way to emit an exception, will be covered in more detail later
throw new ArgumentException("Teacher with the given ID was not found");
}Assume we need to use a code similar to this in more than one place. One possible method is to create extension methods for our repositories:
public static class TeacherExtensions
{
public static TeacherDTO GetTeacherByID(this IRepository<Teacher> repo, int id )
{
var result = from t in repo.All()
where t.ID == id
select new TeacherDTO
{
Name = t.Name,
SSN = t.SSN,
// etc.
};
if (result == null)
{
// Note: not necessarily the best way to emit an exception, will be covered in more detail later
throw new ArgumentException("Teacher with the given ID was not found");
}
return result;
}
}These extension methods can then be called, as if they were member functions in the repositories:
// This will throw an exception if there is no teacher with the given ID:
var teacherDTO = _teachers.GetTeacherByID(id);As with other extension methods, to be able to use them you must add the appropriate using statement to the files where they should be accessible.