8. Detailed information: Linq in practice

Linq to objects: filter data

Linq adds a number of methods to existing .NET classes. E.g. with the First() method, you get the first element of the list, or Last() the last element of the list.

public class Employee {
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int ProjectId { get; set; }
public Project Project { get; set; }
}
  static void Main(string[] args)
        {           
            Employee e1 = new Employee() { Id = 1, FirstName = "Tom", LastName = "Peeters" };
            Employee e2 = new Employee() { Id = 2, FirstName = "Vincent", LastName = "Boeijen" };
            Employee e3 = new Employee() { Id = 3, FirstName = "Ive", LastName = "Verstappen" };
            Employee e4 = new Employee() { Id = 4, FirstName = "Jesse", LastName = "Viskens" };
            Employee e5 = new Employee() { Id = 5, FirstName = "Daan", LastName = "Geudens" };

            List<Employee> employees = new List<Employee>() { e1, e2, e3, e4, e5 };

            var firstEmployee = employees.First();
            var lastEmployee  = employees.Last(); 


You also have the possibility to add a function as parameter of the linq methods:


They assume you write a lambda expression. E.g.:

var filteredEmployee = employees.First(e => e.LastName.StartsWith("V"));

Filter to a list

Linq also has a Where() method which results in a list of data.

var filteredList = employees.Where(e => e.LastName.StartsWith("V"));


Method chaining

var filteredListChain = employees
.Where(e => e.LastName.StartsWith("V"))
.Where(e => e.FirstName.EndsWith("e"));

Combine methods

var firstOfFilteredList = employees
.Where(e => e.LastName.StartsWith("V"))
.Where(e => e.FirstName.EndsWith("e"))
.First();


Order by

var filteredList = employees.OrderBy(e => e.FirstName);
var filteredList = employees.OrderByDescending(e => e.FirstName);
var filteredList = employees
.OrderBy(e => e.LastName)
.ThenByDescending(e => e.FirstName);

You also can combine the ordering of your data, but then you have to use .ThenBy() or .ThenByDescending()

Projection of data

var specificAttributes = employees
.Where(e => e.LastName.StartsWith("V"))
.Select(e => new {e.FirstName,e.LastName});

Project only a specific number of attributes

Aggregation

int countRows = employees
.Where(e => e.LastName.StartsWith("V"))
.Count();

or

int countRows2 = employees                               
.Count(e => e.LastName.StartsWith("V"));

You also have Min(), Max, Average(),..


Any

bool hasEmployees2 = employees.Any();
bool hasEmployees2 = employees.Any(e => e.LastName.StartsWith("V"));

If you want to check the employees list is empty or not, it’s better to use Any() instead of Count().
The Count() method will iterate through the whole list while Any() will check if there is an element in the sequence. When the an element is found, it returns true. 

There is huge performance benefit of using any() instead of count() when you are checking whether a list is empty or not.

Join

var query = employees.Join(projects, e => e.Project, p => p, (e, p) => new { Employ = e, Proj = p });

https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.join?view=netframework-4.8


Linq to entities

LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command tree queries, which execute against the object context. LINQ to Entities converts Language-Integrated Queries (LINQ) queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ. 

Reference: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/linq-to-entities

Reading material:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-projection

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-filtering

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-ordering

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-aggregate-operators

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-partitioning

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-join-operators

Entity framework core

Lazy loading

When the entity is first read, related data isn’t retrieved. However, the first time you attempt to access a navigation property, the data required for that navigation property is automatically retrieved. This results in multiple queries sent to the database — one for the entity itself and one each time that related data for the entity must be retrieved. The DbContext class enables lazy loading by default.

To use lazy loading, an additional package needs to be added. Lazy loading is not available if you just use the package Microsoft.EntityFrameworkCore. For using lazy loading, the package Microsoft.EntityFrameworkCore.Proxies needs to be added to the project references. As mentioned previously, because of the lazy loading disadvantages, it’s not included by default with your EF Core projects.

Eager loading

When the entity is read, related data is retrieved along with it. This typically results in a single join query that retrieves all of the data that’s needed. You specify eager loading by using the Include method.

With eager loading you load references by specifying what references should be included when defining the query. Specifying the query, you use the Include method to define what references should be included. This is best when knowing in advance the needed references.

So using Entity Framework Core, we normally prefer to use eager loading.

 var query = context.Movies
.Include(m => m.Rating);

Explicit loading

This is similar to lazy loading, except that you explicitly retrieve the related data in code; it doesn’t happen automatically when you access a navigation property. You load related data manually by getting the object state manager entry for an entity and calling the Collection.Load method for collections or the Reference.Load method for properties that hold a single entity. (In the following example, if you wanted to load the Administrator navigation property, you’d replace Collection(x => x.Courses) with Reference(x => x.Administrator).) Typically you’d use explicit loading only when you’ve turned lazy loading off.

With explicit loading you explicitly invoke the Load method when references to single items or collections should be loaded. With lazy loading, you do not need to explicitly invoke the Load method. Instead, just accessing the property dynamically invokes a query to the database to retrieve the data for the needed references.

Extra reading

https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.aggregate?view=netframework-4.8