Codecademy Logo

ASP.NET Data Models

Related learning

  • Course

    Learn ASP.NET

    Learn how to build fast, secure, and maintainable web apps with ASP.NET and the Razor Pages architecture.
    • With Certificate
    • Intermediate.
      17 hours
  • Skill path

    Build Web Apps with ASP.NET

    Jumpstart your career with this skill path, first by learning the C# language, then building web apps with ASP.NET Core and the Razor Pages.
    • Includes 7 Courses
    • With Certificate
    • Intermediate.
      41 hours

Database Model

Entity Framework uses C# classes to define the database model. This is an in-memory representation of data stored in a database table. Several model classes combine to form the schema for the database.

Each property maps to a column in a database table. The bottom line in the example shows a type of Continent which implies a relationship to another table.

using System;


public class Country
{
  public string ID { get; set; }
  public string ContinentID { get; set; }
  public string Name { get; set; }
  public int? Population { get; set; }
  public int? Area { get; set; }
  public DateTime? UnitedNationsDate { get; set; }
        
  public Continent Continent { get; set; }
}

Database Context

The Entity Framework database context is a C# class that provides connectivity to an external database for an application. It relies on the Microsoft.EntityFrameworkCore library to define the DB context which maps model entities to database tables and columns.

The DbContextOptions are injected into the context class via the constructor. The options allow configuration changes per environment so the Development DB is used while coding and testing but the Production DB would be referenced for real work.

The DbSet is an in-memory representation of a table or view which has a number of member methods that can return a List<T> of records or a single record.

using Microsoft.EntityFrameworkCore;


public class CountryContext : DbContext
{
  public CountryContext(DbContextOptions<CountryContext> options)
      : base(options)
  {
  }


  public DbSet<Country> Countries { get; set; }
  public DbSet<Continent> Continents { get; set; }


  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Country>().ToTable("Country");
    modelBuilder.Entity<Continent>().ToTable("Continent");
  }
}

DbSet Type

The Entity Framework type DbSet represents a database table in memory. It is typically used with a <T> qualifier. The type, or T, is one of your database model classes. The ModelBuilder binds each database table entity to a corresponding DbSet.

DbSet has a number of member methods that can return a List<T> of records or a single record.

using Microsoft.EntityFrameworkCore;


public class CountryContext : DbContext
{
  public CountryContext(DbContextOptions<CountryContext> options)
      : base(options)
  {
  }


  public DbSet<Country> Countries { get; set; }
  public DbSet<Continent> Continents { get; set; }


  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Country>().ToTable("Country");
    modelBuilder.Entity<Continent>().ToTable("Continent");
  }
}

Database Connection String

The Entity Framework context depends on a database connection string that identifies a physical database connection. It is typically stored in appsettings.json. You can define multiple connection strings for different environments like Development, Test, or Production.

Each database product has specific requirements for the syntax of the connection string. This might contain the database name, user name, password, and other options.

{
  "ConnectionStrings": {
    "CountryContext": "Data Source=Country.db"
  }
}

EF Setup Packages

To set up Entity Framework in a C# project, three essential packages are needed. These packages add SQLite support, design-time features, and developer tools for project management. Ensure these packages are correctly installed to enable seamless database interactions with EF Core.

// Command line installation commands:
dotnet add package Microsoft.EntityFrameworkCore.SQLite
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools

Finding Records

The Entity Framework context DbSet member provides the Find() and FindAsync() methods to retrieve an existing record from the in-memory representation of the database table. Assign the result of this method to a local member in the page model.

This method generates the appropriate SQL syntax needed to access the record in the database table. 

// Assuming Country is of type Country
// Assuming _context is of a type inheriting DbSet


public async Task<IActionResult> OnGetAsync(string id)
{
  if (id == null)
  {
    return NotFound();
  }


  Country Country = await _context.Countries.FindAsync(id);
  
  return Page();
}

LINQ Queries

The Entity Framework DbSet entities can manage complex queries using C# LINQ syntax. This is referenced from the System.Linq library.

All of the Where() and OrderBy() clauses are evaluated in the final statement that calls ToListAsync(). EF evaluates all options and generates a SQL SELECT statement with corresponding WHERE and ORDERBY clauses.

using System.Linq;


var countries = from c in _context.Countries
          select c;


countries = countries.Where(c => c.Name.Contains("Russia"));


countries = countries.Where(c => c.ContinentID == 3);


countries = countries.OrderBy(c => c.Name);


List<Country> Countries = await countries.ToListAsync();

Learn more on Codecademy

  • Course

    Learn ASP.NET

    Learn how to build fast, secure, and maintainable web apps with ASP.NET and the Razor Pages architecture.
    • With Certificate
    • Intermediate.
      17 hours
  • Skill path

    Build Web Apps with ASP.NET

    Jumpstart your career with this skill path, first by learning the C# language, then building web apps with ASP.NET Core and the Razor Pages.
    • Includes 7 Courses
    • With Certificate
    • Intermediate.
      41 hours
Previous
Next