最近研究 Entity Framework 直接使用 SQL 語法的方法，順便做了筆記。

Model 的宣告要注意欄位是否允許 null，以北風資料庫為例，Employee 的宣告如下：

public class Employee
{
    public int? EmployeeID { get; set; }
    public string? LastName { get; set; } = string.Empty;
    public string? FirstName { get; set; } = string.Empty;
    public string? Title { get; set; } = string.Empty;
    public string? TitleOfCourtesy { get; set; } = string.Empty;
    public DateTime? BirthDate { get; set; }
    public DateTime? HireDate { get; set; }
    public string? Address { get; set; } = string.Empty;
    public string? City { get; set; } = string.Empty;
    public string? Region { get; set; } = string.Empty;
    public string? PostalCode { get; set; } = string.Empty;
    public string? Country { get; set; } = string.Empty;
    public string? HomePhone { get; set; } = string.Empty;
    public string? Extension { get; set; } = string.Empty;
    public byte[]? Photo { get; set; }
    public string? Notes { get; set; } = string.Empty;
    public int? ReportsTo { get; set; }
    public string? PhotoPath { get; set; } = string.Empty;
}

db context 的宣告：

public partial class BaseDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    private string _connectionStr { get; set; } = string.Empty;

    public BaseDbContext()
    {
        _connectionStr = Tools.DBTools.ConnectionString;
    }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(_connectionStr);
    }    
}

直接執行 SQL：

var list = Context.Employees.FromSqlRaw("select * from Employees;");

lambda function：

public Employee? GetById(int id)
{
    Employee? emp = Context.Employees.Where(e => e.EmployeeID == id).FirstOrDefault();

    return emp;
}

LINQ：

public Employee? GetEmployee(int id) 
{
    Employee? emp = (from e in Context.Employees
                     where e.EmployeeID == id
                     select e).FirstOrDefault();

    return emp;
}

新增及查詢單筆：

public void Add(Employee emp)
{
    Context.Employees.Add(emp);
    Context.SaveChanges();
}

public Employee? GetSingle(Func<Employee?, bool> predicate)
{
    return Context.Employees.SingleOrDefault(predicate);
}

參考資料