#!markdown

# Chapter 10 - Working with Data Using Entity Framework Core

- Understanding modern databases
- Setting up EF Core
- Defining EF Core models
- Querying EF Core models
- Loading patterns with EF Core
- Manipulating data with EF Core
- Working with transactions
- Code First EF Core models

#!markdown

# Setting up EF Core

[.NET Interactive with SQL](https://devblogs.microsoft.com/dotnet/net-interactive-with-sql-net-notebooks-in-visual-studio-code/)

#!csharp

#r "nuget:Microsoft.DotNet.Interactive.SqlServer,1.0.0-beta.21553.5"

#!sql

#!connect mssql -h

#!sql

#!connect mssql --kernel-name Northwind "Data Source=.;Initial Catalog=Northwind;Integrated Security=true;MultipleActiveResultSets=true;"

#!sql

#!sql-Northwind
SELECT * FROM Categories

#!sql

#!connect mssql --create-dbcontext --kernel-name Northwind "Data Source=.;Initial Catalog=Northwind;Integrated Security=true;MultipleActiveResultSets=true;"

#!markdown

## Choosing an EF Core database provider

Add package references to the EF Core data provider for both SQL Server and SQLite.

#!csharp

#r "nuget:Microsoft.EntityFrameworkCore.Sqlite,5.0.11"
#r "nuget:Microsoft.EntityFrameworkCore.SqlServer,5.0.11"

#!markdown

Define a constant to switch between providers.

#!csharp

public class ProjectConstants
{
  public const string DatabaseProvider = "SQLite"; // or "SQLServer"
}

#!csharp

using static System.Console;

#!csharp

WriteLine($"Using {ProjectConstants.DatabaseProvider} database provider.");

#!markdown

## Defining the Northwind database context class

#!csharp

using System.IO;
using Microsoft.EntityFrameworkCore; // DbContext, DbContextOptionsBuilder

#!csharp

public class Northwind : DbContext
{
  protected override void OnConfiguring(
    DbContextOptionsBuilder optionsBuilder)
  {
    if (ProjectConstants.DatabaseProvider == "SQLite")
    {
      string path = Path.Combine(
        Environment.CurrentDirectory, "Northwind.db");

      WriteLine($"Using {path} database file.");

      optionsBuilder.UseSqlite($"Filename={path}");
    }
    else
    {
      string connection = "Data Source=.;" +
        "Initial Catalog=Northwind;" +
        "Integrated Security=true;" +
        "MultipleActiveResultSets=true;";
        
      optionsBuilder.UseSqlServer(connection);
    }
  }
}

#!markdown

## Defining the Category and Product entity classes

#!csharp

#nullable enable

using System.ComponentModel.DataAnnotations; // [Required], [StringLength]
using System.ComponentModel.DataAnnotations.Schema; // [Column]

public class Category
{
  // these properties map to columns in the database
  public int CategoryId { get; set; }
  public string? CategoryName { get; set; }

  [Column(TypeName = "ntext")]
  public string? Description { get; set; }

  // defines a navigation property for related rows
  public virtual ICollection<Product> Products { get; set; }

  public Category()
  {
    // to enable developers to add products to a Category we must
    // initialize the navigation property to an empty collection
    this.Products = new HashSet<Product>();
  }
}

public class Product
{
  public int ProductId { get; set; } // primary key

  [Required]
  [StringLength(40)]
  public string ProductName { get; set; } = null!;

  [Column("UnitPrice", TypeName = "money")]
  public decimal? Cost { get; set; } // property name != column name

  [Column("UnitsInStock")]
  public short? Stock { get; set; }

  public bool Discontinued { get; set; }

  // these two define the foreign key relationship
  // to the Categories table
  public int CategoryId { get; set; }
  public virtual Category Category { get; set; } = null!;
}