Summer’s over — what’s your move? Join Pro for 50% off using code [MAKEITHAPPEN](https://www.codecademy.com/checkout?periods=year&plan_id=proGoldAnnualV2&discountCode=MAKEITHAPPEN)


LINQ syntax is used for more demanding data retrieval and manipulation. This complements the basic Entity Framework methods we used on the DBSet instances of Continents and Countries

Data with LINQ

We just used a `SelectList` to filter Continents on the list page. We can use a similar approach on the Country Edit page. We will replace the Continent `<input>` text box with a `SelectList`. This has the added value of ensuring that only a valid Continent is selected.

We fill the Continents select list from the EF context DBSet and specify the key and display value. This allows the user to see the full Continent name but the database will properly store the Continent ID:
```cs
Continents = new SelectList(_context.Continents, nameof(Continent.ID), nameof(Continent.Name));
```
The `<select>` uses the `asp-for attribute` the same way `<input>` does. It has an additional `asp-items` attribute for the source of the dropdown list:
```html
<select asp-for="Country.ContinentID" asp-items="Model.Continents" class="form-control" ></select>
```
There is no need for validation since only values in the list are acceptable. We will remove the validation requirements from the Country model and rely on the `SelectList`.

Add a Select List for Continents

We start by expanding our sample data to include all known continents and countries. This is done by replacing the `DBInitializer` code with `DBInitializerFull`. This will make any filtering and searching we do more interesting.

Now that we have over 200 countries in the list, we need to add some code to search and filter. We do this by adding a input text box for a search string. A button can post back a form with this string. To make this work, the SearchString must use the `[BindProperty]` annotation with special qualifier of `SupportsGet`. This basically tells the `OnGetAsync()` method that a SearchString may exist when the page is ready to load. That way the SearchString could be added as a query parameter in a bookmarked URL:
```cs
[BindProperty(SupportsGet = true)]
public string SearchString { get; set; }
```
`OnGetAsync()` then uses a conditional statement to add the `SearchString` value to a new LINQ query. The var countries is first assigned a LINQ statement that returns all rows by default. If a `SearchString` is present, the query is modified to include a where clause that filters only by Name.
```cs
var countries = from c in _context.Countries
  select c;
           
if (!string.IsNullOrEmpty(SearchString))
{
  countries = countries.Where(c => c.Name.Contains(SearchString));
}
```
The LINQ query is not turned into a SQL database query until the `ToListAsync()` method is called:
```cs
Countries = await countries.ToListAsync();
```
This is an important concept in LINQ. Any number of modifications to a query are allowed until the final evaluation to a list.



Add a Search Filter to the Country List

LINQ can take advantage of navigational properties coded into the entity models. This is a C# way of supporting one to many and many to many data relationships. 

We start by adding lines to our models so that Entity Framework knows that the two tables are related. Continents have a collection of Countries:
```cs
public ICollection<Country> Countries { get; set; }
```
And Countries have a related Continent:
```cs
public Continent Continent { get; set; }
``` 

We can enhance our Continent Detail page by including a list of related Countries. The LINQ record retrieval statement for a single Continent can add the `Include()` chain statement. EF loads all related Countries each time it loads a `Continent`. The `AsNoTracking()` is a hint that says we will not be updating the `Countries`. This improves performance:
```cs
Continent = await _context.Continents
     .Include(c => c.Countries)
     .AsNoTracking()
     .FirstOrDefaultAsync(m => m.ID == id);
```
Markup is then added to the page to display the list of member Countries. This can easily be copied from the Countries Index page made earlier.

Show Related Records with Include

We replaced the `FindAsync(ID)` call with a LINQ type syntax statement. The `FirstOrDefaultAsync()` method returns a single record that matches the lambda expression in the parameter. We are not limited to ID in this regard and could as easily search by Name.

We added some code to search and filter. A button posts back a form with a search string. The SearchString uses the `[BindProperty]` annotation with special qualifier of `SupportsGet`. This tells the `OnGetAsync()` method that a SearchString may exist when the page is ready to load. 

`OnGetAsync()` uses a conditional statement to add the `SearchString` value to a new LINQ query. The var countries is first assigned a LINQ statement that returns all rows by default. If a `SearchString` is present, the query is modified to include a where clause that filters only by Name. The LINQ query is not turned into a SQL database query until the `ToListAsync()` method is called. This is an important concept in LINQ. Any number of modifications to a query are allowed until the final evaluation to a list.

We added sorting using a `<select>` tag to hold the column names in a series of `<option>` tags. We added an `onChange()` event that posts the form each time a change is made.

We also limited our searches by continent using a select list. LINQ is used to gather all continents while sorting then alphabetically. The result of the query is assigned to a `SelectList` component during `OnGetAsync()`. The component added the All option as part of the markup. Multiple modifiers allows us to filter by a continent and include a country name search string.

We replaced the Continent `<input>` text box with a `SelectList`. This has the added value of ensuring that only a valid Continent is selected. The `<select>` uses the `asp-for attribute` the same way `<input>` does. It has an additional `asp-items` attribute for the source of the dropdown list.

LINQ can take advantage of navigational properties coded into the entity models. This is a C# way of supporting one to many and many to many data relationships. We added lines to our models so that Entity Framework knows that the two tables are related. Continents have a collection of Countries 
and Countries have a related Continent.

We enhanced our Continent Detail page by including a list of related Countries. The LINQ record retrieval statement for a single Continent added the `Include()` chain statement. EF loads all related Countries each time it loads a `Continent`. The `AsNoTracking()` is a hint that says we will not be updating the `Countries`. This improves performance. Markup was added to the page to display the list of member Countries.

We used inheritance to make a new data model called **CountryStat.cs**. It has an additional member called Density. We retrieved a single `CountryStat` object instead of a Country and added a new field in the page markup.









Review

Our last look at LINQ demonstrates the ability to create calculated columns. We know that each Country has an Area and a Population. The Density would be Population divided by Area to get people per square mile. This match could be done client side in JavaScript or server side with some C# syntax. Instead, we will make a new data model that inherits from **Country.cs** called **CountryStat.cs**. It will have an additional member called Density.

In the **Pages/Countries/Detail.cshtml.cs** page, we will retrieve a single `CountryStat` object instead of a Country. The final step will be the addition of a new field in the page markup.


Add a Calculated Count Column

Language-Integrated Query (LINQ) integrates query capabilities directly into the C# language. Having LINQ as a first class citizen in the C# language provides strong type checking and intellisense. LINQ can operate over many types of data including objects, XML, JSON, and databases. 

So far we have accessed data through the Entity Framework context using the `ToListAsync()` or `FindAsync()` methods of a DbSet. The former returns all records in a table and the other returns a single record by ID. 

This lesson covers several approaches where all or a single record by ID are not enough. We start by replacing the `FindAsync(ID)` call with a LINQ type syntax statement:
```cs
Variable = await _context.SomeDbSet.FirstOrDefaultAsync(m => m.ID == id);
```
The `FirstOrDefaultAsync()` method returns a single record that matches the lambda expression in the parameter. This is functionally equivalent to `FindAsync()` since we are matching on the ID. But, we are not limited to ID in this regard and could as easily search by Name.


Entity Framework Context with LINQ

Searching is now working. Let's add sorting as well. We will use a `<select>` tag to hold the column names in a series of `<option>` tags. This will be linked to a member property that tracks the current sort column. We will add an `onChange()` event that posts the form each time a change is made:
```html
<select class="p-2" asp-for="SortField" onchange="this.form.submit();">
  <option value="ID">Code</option>
  <option value="ContinentID">Cont</option>
  <option value="Name">Name</option>
</select>
```
The LINQ query can be modified multiple times before it is evaluated. Sorting is managed by the `OrderBy()` clause. A switch statement determines which column name to include in the LINQ query:
```cs
switch (SortField)
{
  case "ID": 
    countries = countries.OrderBy(c => c.ID); 
    break;
  case "Name": 
    countries = countries.OrderBy(c => c.Name); 
    break;
  case "ContinentID": 
    countries = countries.OrderBy(c => c.ContinentID);
    break;
}
```


Add Column Sorting to the Country List

We now have a simple way of searching and sorting our countries. We might also want to limit our searches by continent. We can accomplish this with another select list.

LINQ is used to gather all continents while sorting them alphabetically:
```cs
IQueryable<string> continentQuery = from c in _context.Continents
      orderby c.ID
      select c.ID;
```
The result of the query is assigned to a `SelectList` component during `OnGetAsync()`. The component adds the All option as part of the markup.

We use another LINQ query modifier that checks for a selected continent and adds that condition. Multiple modifiers allows us to filter by a continent and include a country name search string:
```cs
if (!string.IsNullOrEmpty(SelectedContinent))
{
  countries = countries.Where(c => c.ContinentID == SelectedContinent);
}
```