创建数据库生成计算字段CodeFirst/EntityFramework

本文关键字：CodeFirst EntityFramework 字段 计算 数据库 创建 | 更新日期: 2023-09-27 18:06:50

我正在尝试通过Initial: Migration文件在数据库中创建一个列，类似于遵循本指南。

有一个Transaction表和一个Account表。我试着生成Account属性ReconciledBalance。它应该计算IsReconciled为真且IsActive为真交易中所有余额的总和。

// Example of calculation
ReconciledBalance = Transactions.Where(t => t.IsActive == true && t.IsReconciled == true).Sum(x => x.Amount)

这就是我想在计算列中完成的。我在想我是否离目标越来越近了，或者我是否走在正确的方向上。任何帮助将不胜感激!

// Initial.cs file
public partial class Initial : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Accounts",
        c => new
        {
            Id = c.Int(nullable: false, identity: true),
            IsActive = c.Boolean(),
            Name = c.String(),
            Balance = c.Decimal(nullable: false, precision: 18, scale: 2),
            //ReconciledBalance = c.Decimal(),
            HouseholdId = c.Int(),
        })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Households", t => t.HouseholdId);
        Sql("ALTER TABLE dbo.Accounts NOT SURE WHAT TO PUT HERE");
    }
    public override void Down()
    {
        //AlterColumn("dbo.Accounts", "ReconciledBalance", c => c.Decimal(nullable: false, precision: 18, scale: 2));
        DropTable("dbo.Accounts");
    }
}
//Account Model
public class Account
{
    public Account()
    {
        this.Transactions = new HashSet<Transaction>();
    }
    public int Id { get; set; }
    public bool IsActive { get; set; }
    public string Name { get; set; }
    [Range(double.MinValue, double.MaxValue)]
    public decimal Balance { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [Range(double.MinValue, double.MaxValue)]
    public decimal ReconciledBalance{ get; set; }
    }
    //FKs
    public int HouseholdId { get; set; }
    //Virtual Properties
    public virtual Household Household { get; set; }// One to one
    public virtual ICollection<Transaction> Transactions { get; set; }
}
// Account Transaction
public class Transaction
{
    public Transaction()
    {
    }
    public int Id { get; set; }
    public bool IsActive { get; set; }
    [DisplayFormat(DataFormatString = "{0:g}", ApplyFormatInEditMode = true)]
    public DateTimeOffset Date { get; set; }
    [StringLength(150, ErrorMessage ="Description cannot exceed 150 characters.")]
    public string Description { get; set; }
    [Range(double.MinValue, double.MaxValue)]
    public decimal Amount { get; set; }
    public bool IsReconciled { get; set; }
    public bool IsExpense { get; set; }
    [Range(double.MinValue, double.MaxValue)]
    public decimal ReconciledAmount { get; set; }
    //FKs
    public int CategoryId { get; set; }
    public string EnteredById { get; set; }
    public int AccountId { get; set; }
    //Virtual Properties
    public virtual Category Category { get; set; }
    public virtual ApplicationUser EnteredBy { get; set; }
    public virtual Account Account { get; set; }
}

创建数据库生成计算字段CodeFirst/EntityFramework

您可以将实体链接到具有实体框架所期望的名称的数据库视图，而不是表:

    public override void Up()
    {
        CreateTable(
            "dbo.Accounts_BackingTable",
        c => new
        {
            Id = c.Int(nullable: false, identity: true),
            IsActive = c.Boolean(),
            Name = c.String(),
            Balance = c.Decimal(nullable: false, precision: 18, scale: 2),
            HouseholdId = c.Int(),
        })
        .PrimaryKey(t => t.Id)
        Sql(@"EXECUTE('CREATE view dbo.Accounts 
with schemabinding as (SELECT
    a.id,
    a.isactive,
    a.name,
    a.balance,
    a.householdid,
    SUM(t.amount) AS ReconciledBalance
FROM dbo.accounts_backingtable a
    LEFT JOIN dbo.transactions t
    ON t.accountid = a.id AND t.isactive = 1 AND t.isreconciled = 1
    --It's not clear what logic you need for the join
    --ON t.accountid = a.id AND (t.IsReconciled = 1 or t.IsActive = 1 or t.IsVoid=0)
)
GROUP BY a.id,
    a.isactive,
    a.name,
    a.balance,
    a.householdid')");
    }

GROUP BY子句可能意味着您的视图是不可更新的，在这种情况下，您需要有两个实体，其中一个是可更新的。或者您可以切换到使用存储过程并修改存储过程中的sql。

注意，这仍然是在动态地进行计算，只是在数据库中而不是在应用程序中。如果您希望将其存储在数据库中，则需要在创建、更新和删除事务时进行计算。

另一种方法:

public class Account
{
    public Account()
    {
        this.Transactions = new HashSet<Transaction>();
    }
    public int Id { get; set; }
    public bool IsActive { get; set; }
    public string Name { get; set; }
    [Range(double.MinValue, double.MaxValue)]
    public decimal Balance { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [Range(double.MinValue, double.MaxValue)]
    public decimal ReconciledBalance
    {
        get 
        { 
             return Transactions.Where(t => t.IsActive == true && t.IsReconciled == true).Sum(x => x.Amount) 
        }
        private set { /* needed for EF */ }
    }
    //FKs
    public int HouseholdId { get; set; }
    //Virtual Properties
    public virtual Household Household { get; set; }// One to one
    public virtual ICollection<Transaction> Transactions { get; set; }
}
希望你不需要重写Up()或Down()。(编辑:或者直接删除Sql("ALTER TABLE dbo.Accounts NOT SURE WHAT TO PUT HERE");)
相关文章：