在ASP.NET Core5中使用存储过程和表值函数的最佳方法

// 创建实体类
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// 创建DbContext
public class MyDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置存储过程映射
        modelBuilder.Entity<Customer>().HasNoKey().ToView(null);
    }
}

// 调用存储过程
public class CustomerRepository
{
    private readonly MyDbContext _dbContext;

    public CustomerRepository(MyDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public List<Customer> GetCustomers()
    {
        var customers = _dbContext.Customers.FromSqlRaw("EXECUTE dbo.GetCustomers").ToList();
        return customers;
    }
}

// 创建实体类
public class Order
{
    public int Id { get; set; }
    public string ProductName { get; set; }
    public decimal Price { get; set; }
}

// 创建DbContext
public class MyDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置表值函数映射
        modelBuilder.Entity<Order>().HasNoKey().ToView(null);
    }
}

// 调用表值函数
public class OrderRepository
{
    private readonly MyDbContext _dbContext;

    public OrderRepository(MyDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public List<Order> GetOrders(int customerId)
    {
        var orders = _dbContext.Orders.FromSqlRaw("SELECT * FROM dbo.GetOrders({0})", customerId).ToList();
        return orders;
    }
}