2/24/25, 1:58 PM [Link] I: ASP.
NET: Databases Cheatsheet | Codecademy
Cheatsheets / [Link] I
[Link]: Databases
Database Model
Entity Framework uses C# classes to define the database using System;
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. public class Country
Each property maps to a column in a database table. The {
bottom line in the example shows a type of Continent
public string ID { get; set; }
which implies a relationship to another table.
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; }
}
[Link] 1/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Database Context
The Entity Framework database context is a C# class that using [Link];
provides connectivity to an external database for an
application. It relies on the
[Link] library to define the public class CountryContext : DbContext
DB context which maps model entities to database tables {
and columns.
public
The DbContextOptions are injected into the context
class via the constructor. The options allow configuration CountryContext(DbContextOptions<CountryCon
changes per environment so the Development DB is used text> options)
while coding and testing but the Production DB would be
: base(options)
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.
public DbSet<Country> Countries { get;
set; }
public DbSet<Continent> Continents {
get; set; }
protected override void
OnModelCreating(ModelBuilder modelBuilder)
{
[Link]<Country>
().ToTable("Country");
[Link]<Continent>
().ToTable("Continent");
}
}
[Link] 2/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
DbSet Type
The Entity Framework type DbSet represents a using [Link];
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 public class CountryContext : DbContext
entity to a corresponding DbSet . {
DbSet has a number of member methods that can public
return a List<T> of records or a single record.
CountryContext(DbContextOptions<CountryCon
text> options)
: base(options)
{
}
public DbSet<Country> Countries { get;
set; }
public DbSet<Continent> Continents {
get; set; }
protected override void
OnModelCreating(ModelBuilder modelBuilder)
{
[Link]<Country>
().ToTable("Country");
[Link]<Continent>
().ToTable("Continent");
}
}
[Link] 3/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Entity Framework Configuration
In [Link] Core, a database may be connected to a web
app using Entity Framework. There are four common
steps for any setup:
1. Define one or more database model classes and
annotate them
2. Define a database context class that uses DbSet to
map entities to tables
3. Define a database connection string in
[Link]
4. Add the Entity Framework service in
[Link]()
Database Connection String
The Entity Framework context depends on a database {
connection string that identifies a physical database
"ConnectionStrings": {
connection. It is typically stored in [Link]. You
can define multiple connection strings for different "CountryContext": "Data
environments like Development, Test, or Production. Source=[Link]"
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. }
Creating the Schema
Entity Framework provides command-line tools that help dotnet ef migrations add InitialCreate
manage the connected database. Use these commands in
dotnet ef database update
the bash shell or Windows command prompt to create an
initial database file and schema. This will read the context
class and evaluate each database model represented by a
DbSet . The SQL syntax necessary to create all schema
objects is then generated and executed.
[Link] 4/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Saving Changes
The Entity Framework context DbSet member provides // Assuming Country is of type Country
the Attach() method to update an existing record, the
// Assuming _context is of a type
Add() method to insert a new record, and the
Remove() method to delete an existing record. Any inheriting DbSet
combination of multiple records can batched before
saving. public async Task<IActionResult>
Use the EF context SaveChanges() or
OnPostAsync(string id)
SaveChangesAsync() methods to persist all inserted,
updated, and deleted records to the database table. {
// update
_context.Attach(Country).State =
[Link];
// insert
await
_context.[Link](Country);
// delete
Country Country = await
_context.[Link](id);
if (Country != null)
{
_context.[Link](Country);
}
// all three methods must be followed by
savechanges
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}
[Link] 5/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Deleting Records
The Entity Framework context DbSet member provides // Assuming Country is of type Country
the Remove() method to delete an existing record from
// Assuming _context is of a type
the in-memory representation of the database table. Any
combination of multiple record deletions can be batched inheriting DbSet
before saving.
Use the EF context SaveChanges() or
public async Task<IActionResult>
SaveChangesAsync() methods to persist all deletions
to the database table. OnPostAsync(string id)
{
if (id == null)
{
return NotFound();
}
Country Country = await
_context.[Link](id);
if (Country != null)
{
_context.[Link](Country);
}
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}
[Link] 6/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
LINQ Queries
The Entity Framework DbSet entities can manage using [Link];
complex queries using C# LINQ syntax. This is referenced
from the [Link] library.
All of the Where() and OrderBy() clauses are var countries = from c in
evaluated in the final statement that calls _context.Countries
ToListAsync() . EF evaluates all options and generates a select c;
SQL SELECT statement with corresponding
WHERE and ORDERBY clauses.
countries = [Link](c =>
[Link]("Russia"));
countries = [Link](c =>
[Link] == 3);
countries = [Link](c =>
[Link]);
List<Country> Countries = await
[Link]();
DisplayNameFor Helper
The @[Link]() tag helper is used to <!-- In .cshtml file -->
display the friendly name for a property in a database
<div>
model. By default, this will match the property name. If a
[Display(Name = "Code")] annotation is applied to @[Link](model =>
the property in the model class, that string is used instead [Link])
</div>
// Example database model
public class Continent
<!-- Rendered HTML in browser -->
{
<div>
[Display(Name = "Code")]
Code
public int ID { get; set; }
</div>
}
[Link] 7/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Model Binding
In [Link] Core, model binding is a feature that simplifies
capturing and storing data in a web app. The process of
model binding includes retrieving data from various
sources, converting them to collections of .NET types,
and passing them to controllers/page models. Helpers
and attributes are used to render HTML with the contents
of bound page models. Client- and server-side validation
scripts are used to ensure integrity during data entry.
Adding Records
The Entity Framework context DbSet member provides // Assuming Country is of type Country
the Add() and AddAsync() methods to insert a new
// Assuming _context is of a type
record into the in-memory representation of the
corresponding database table. A batch of multiple inheriting DbSet
records can also be added in this fashion.
The record is passed from the browser in the <form>
public async Task<IActionResult>
post back. In this case a Country member is declared
OnPostAsync(string id)
with a [BindProperty] attribute so the entire record is
passed back to the server. {
Use the EF context SaveChanges() or if (![Link])
SaveChangesAsync() methods to persist all new
{
records to the database table.
return Page();
}
await
_context.[Link](Country);
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}
[Link] 8/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Finding Records
The Entity Framework context DbSet member provides // Assuming Country is of type Country
the Find() and FindAsync() methods to retrieve an
// Assuming _context is of a type
existing record from the in-memory representation of the
database table. Assign the result of this method to a local inheriting DbSet
member in the page model.
This method generates the appropriate SQL syntax
public async Task<IActionResult>
needed to access the record in the database table.
OnGetAsync(string id)
{
if (id == null)
{
return NotFound();
}
Country Country = await
_context.[Link](id);
return Page();
}
[Link] 9/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Updating Records
The Entity Framework context DbSet member provides // Assuming Country is of type Country
the Attach() method to update an existing record in the
// Assuming _context is of a type
in-memory representation of the corresponding
database table. A batch of multiple records can also be inheriting DbSet
updated in this fashion.
The record is passed from the browser in the <form>
public async Task<IActionResult>
post back. In this case a Country member is declared
with a [BindProperty] attribute so the entire record is
OnPostAsync(string id)
passed back to the server. {
Use the EF context SaveChanges() or if (![Link])
SaveChangesAsync() methods to persist all updated
{
records to the database table.
return Page();
}
_context.Attach(Country).State =
[Link];
await _context.SaveChangesAsync();
return RedirectToPage("./Index");
}
[Link] 10/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Valid Model State
Entity Framework database models accept annotations public async Task<IActionResult>
that drive data validation at the property level. If you are
OnPostAsync()
using the asp-validation-for or asp-validation-
summary HTML attributes, validation is handled client- {
side with JavaScript. The model is validated and the if (![Link])
<form> post back won’t occur until that model is valid.
{
Sometimes the client-side validation will not be available
so it is considered best practice to also validate the return Page();
model server-side, inside the OnPostAsync() method. }
This example checks for [Link] and
returns the same page if it is false. This effectively keeps
_context.[Link](Continent);
the user on the same page until their entries are valid.
If the model is valid, the insert, update, or delete can
proceed followed by SaveChangesAsync() to persist await _context.SaveChangesAsync();
the changes.
return RedirectToPage("./Index");
}
[Link] 11/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Select List Items Attribute
<select asp-for="[Link]
The asp-items attribute in a <select> element
generates <option> tags according to the model
property specified. It works in conjunction with the asp-
for attribute to display the matching option and set the
underlying value on a change.
using [Link]
public SelectList Continents { get;
public async Task<IActionResult> On
{
Continents = new SelectList(_cont
}
The SelectList type is declared in the page model code
and assigned to a new SelectList() where each record
in Continents grabs the ID as the <option> value
and Name as the <option> display text.
The included <select> in the example would render as
this HTML:
<select class="valid" id="Country_C
<option value="NA">North America<
<option value="SA">South America<
<option value="EU">Europe</option
<!-- etc -->
</select>
[Link] 12/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Validation Attribute
The asp-for attribute in an <input> element will <div>
render HTML and JavaScript that handle the display and
<label asp-for="[Link]"></label>
data entry for a field based on the model annotations.
The JavaScript will set the valid flag on the field. <div>
The asp-validation-for attribute in a <span> element <input asp-for="[Link]" />
will display any error message generated when the
<span asp-validation-
property annotations are not valid.
In this example, the <span> be rendered as this HTML: for="[Link]"></span>
</div>
<span class="field-validation-valid </div>
[Display] Attribute
The [Display] attribute specifies the caption for a label, using
textbox, or table heading.
[Link];
Within a Razor Page, the @[Link]()
helper defaults to the property name unless the
[Display] attribute overrides it. In this case, public class Country
Continent is displayed instead of the more technical {
ContinentID .
[Display(Name = "Continent")]
public string ContinentID { get; set; }
}
[DisplayFormat] Attribute
The [DisplayFormat] attribute can explicitly apply a C# using
format string. The optional ApplyFormatInEditMode
[Link];
means the format should also apply in edit mode.
[DisplayFormat] is often used in combination with the
[DataType] attribute. Together they determine the public class Country
rendered HTML when using the @[Link]() {
helper.
[DisplayFormat(DataFormatString = "
{0:N0}", ApplyFormatInEditMode = true)]
public int? Population { get; set; }
}
[Link] 13/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
[DataType] Attribute
The [DataType] attribute specifies a more specific data using
type than the database column type. In this case, the
[Link];
database table will use a DateTime column but the
render logic will only show the date.
The @[Link]() helper knows about types public class Country
and will render a default format to match the type. In this
{
case, the HTML5 browser date picker will appear when
editing the field. [DataType([Link])]
[DisplayFormat(DataFormatString = "
{0:yyyy-MM-dd}", ApplyFormatInEditMode =
true)]
public DateTime? UnitedNationsDate {
get; set; }
}
[Required] Attribute
The [Required] attribute can be applied to one or more using
properties in a database model class. EF will create a
[Link];
NOT NULL column in the database table for the
property.
The client-side JavaScript validation scripts will ensure public class Country
that a non-empty string or number is valid before posting
{
the record from the browser on inserts and updates.
[Required]
public string Name { get; set; }
}
[Link] 14/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
[RegularExpression] Attribute
The [RegularExpression] attribute can apply detailed using
restrictions for data input. The match expression is
[Link];
evaluated during data entry and the result returns true or
false. If false, the model state will not be valid and the
optional ErrorMessage will display. public class Country
In a Razor page, the @[Link]() helper only
{
shows the data in the field. The asp-validation-for
[RegularExpression(@"[A-Z]+",
attribute on a <span> tag displays the ErrorMessage .
ErrorMessage = "Only upper case characters
are allowed.")]
public string CountryCode { get; set; }
}
[StringLength] Attribute
The [StringLength] attribute specifies the maximum using
length of characters that are allowed in a data field and
[Link];
optionally the minimum length. The model will not be
flagged as valid if these restrictions are exceeded. In this
case, the ContinentID must be exactly 2 characters in public class Country
length.
{
In a Razor Page, the @[Link]() helper only
shows the data in the field. The client-side JavaScript [StringLength(2, MinimumLength = 2)]
validation scripts use the asp-validation-for attribute public string ContinentCode { get; set;
on a <span> tag to display a default error message. }
}
[Range] Attribute
The [Range] attribute specifies the minimum and using
maximum values in a data field. The model will not be
[Link];
flagged as valid if these restrictions are exceeded. In this
case, Population must be greater than 0 and less than
the big number! public class Country
In a Razor page, the @[Link]() helper only
{
shows the data in the field. The client-side JavaScript
validation scripts use the asp-validation-for attribute [Range(1, 10000000000)]
on a <span> tag to display a default error message. public int? Population { get; set; }
}
[Link] 15/16
2/24/25, 1:58 PM [Link] I: [Link]: Databases Cheatsheet | Codecademy
Print Share
[Link] 16/16