C# EF Code First 中的计算列

Calculated column in EF Code First

提问by CodeDemen

I need to have one column in my database calculated by database as (sum of rows) - (sum of rowsb). I'm using code-first model to create my database.

我需要在我的数据库中有一列由数据库计算为（行总和）-（行总和）。我正在使用代码优先模型来创建我的数据库。

Here is what I mean:

这就是我的意思：

public class Income {
      [Key]
      public int UserID { get; set; }
      public double inSum { get; set; }
}

public class Outcome {
      [Key]
      public int UserID { get; set; }
      public double outSum { get; set; }
}

public class FirstTable {
      [Key]
      public int UserID { get; set; }
      public double Sum { get; set; } 
      // This needs to be calculated by DB as 
      // ( Select sum(inSum) FROM Income WHERE UserID = this.UserID) 
      // - (Select sum(outSum) FROM Outcome WHERE UserID = this.UserID)
}

How can I achieve this in EF CodeFirst?

如何在 EF CodeFirst 中实现这一目标？

采纳答案by Gert Arnold

You can create computed columnsin your database tables. In the EF model you just annotate the corresponding properties with the DatabaseGeneratedattribute:

您可以在数据库表中创建计算列。在 EF 模型中，您只需使用属性注释相应的DatabaseGenerated属性：

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public double Summ { get; private set; } 

Or with fluent mapping:

或者使用流畅的映射：

modelBuilder.Entity<Income>().Property(t => t.Summ)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)

As suggested by Matija Grcicand in a comment, it's a good idea to make the property private set, because you'd probably never want to set it in application code. Entity Framework has no problems with private setters.

正如Matija Grcic和评论中所建议的那样，创建 property 是个好主意private set，因为您可能永远不想在应用程序代码中设置它。实体框架对私有 setter 没有问题。

Note:For EF .NET Core you should to use ValueGeneratedOnAddOrUpdatebecause HasDatabaseGeneratedOption doesnt exists, e.g.:

注意：对于 EF .NET Core，您应该使用ValueGeneratedOnAddOrUpdate因为 HasDatabaseGeneratedOption 不存在，例如：

modelBuilder.Entity<Income>().Property(t => t.Summ)
    .ValueGeneratedOnAddOrUpdate()

回答by Linus Caldwell

One way is doing it with LINQ:

一种方法是使用 LINQ：

var userID = 1; // your ID
var income = dataContext.Income.First(i => i.UserID == userID);
var outcome = dataContext.Outcome.First(o => o.UserID == userID);
var summ = income.inSumm - outcome.outSumm;

You may do it within your POCO object public class FirstTable, but I would not suggest to, because I thinkit's not good design.

您可以在 POCO 对象中执行此操作public class FirstTable，但我不建议这样做，因为我认为这不是好的设计。

Another way would be using a SQL view. You can read a view like a table with Entity Framework. And within the view code, you may do calculations or whatever you want. Just create a view like

另一种方法是使用 SQL 视图。您可以使用实体框架读取像表一样的视图。在视图代码中，您可以进行计算或任何您想要的操作。只需创建一个视图

-- not tested
SELECT FirstTable.UserID, Income.inCome - Outcome.outCome
  FROM FirstTable INNER JOIN Income
           ON FirstTable.UserID = Income.UserID
       INNER JOIN Outcome
           ON FirstTable.UserID = Outcome.UserID

回答by Matija Grcic

public string ChargePointText { get; set; }

public class FirstTable 
{
    [Key]
    public int UserID { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]      
    public string Summ 
    {
        get { return /* do your sum here */ }
        private set { /* needed for EF */ }
    }
}

References:

参考：

• Bug in EF 4.1 DatabaseGeneratedOption.Computed
• Calculated Columns in Entity Framework Code First Migrations
• Working with Computed Columns

• EF 4.1 DatabaseGeneratedOption.Computed 中的错误
• Entity Framework Code First 迁移中的计算列
• 使用计算列

回答by craigvl

I would go about this by just using a view model. For example rather than have the FirstTable class as a db entity would you not be better just having a view model class called FirstTable and then have a function that is used to return this class that would include the calculated sum? For example your class would just be:

我会通过使用视图模型来解决这个问题。例如，与其将 FirstTable 类作为 db 实体，您是否最好只拥有一个名为 FirstTable 的视图模型类，然后拥有一个用于返回此类的函数，该类将包括计算出的总和？例如，您的课程将是：

public class FirstTable {
  public int UserID { get; set; }
  public double Sum { get; set; }
 }

And then you would have a function that you call that returns the calculated sum:

然后你会有一个你调用的函数来返回计算的总和：

public FirsTable GetNetSumByUserID(int UserId)
{
  double income = dbcontext.Income.Where(g => g.UserID == UserId).Select(f => f.inSum);
  double expenses = dbcontext.Outcome.Where(g => g.UserID == UserId).Select(f => f.outSum);
  double sum = (income - expense);
  FirstTable _FirsTable = new FirstTable{ UserID = UserId, Sum = sum};
  return _FirstTable;
}

Basically the same as an SQL view and as @Linus mentioned I don't think it would be a good idea keeping the computed value in the database. Just some thoughts.

基本上与 SQL 视图相同，正如@Linus 提到的，我认为将计算值保留在数据库中不是一个好主意。只是一些想法。

回答by Patrick Michalina

I stumbled across this question when trying to have an EF Code First model with a string column "Slug", be derived from another string column "Name". The approach I took was slightly different but worked out well so I will share it here.

当我试图让一个带有字符串列“Slug”的 EF Code First 模型从另一个字符串列“名称”派生时，我偶然发现了这个问题。我采取的方法略有不同，但效果很好，所以我将在这里分享。

private string _name;

public string Name
{
    get { return _name; }
    set
    {
        _slug = value.ToUrlSlug(); // the magic happens here
        _name = value; // but don't forget to set your name too!
    }
}

public string Slug { get; private set; }

What is nice about this approach is you get the automatic slug generation, while never exposing the slug setter. The .ToUrlSlug() method isn't the important part of this post, you could use anything in its place to do the work you need done. Cheers!

这种方法的好处是您可以自动生成 slug，而从不暴露 slug setter。.ToUrlSlug() 方法不是这篇文章的重要部分，您可以在其位置使用任何东西来完成您需要完成的工作。干杯!

回答by Yennefer

As of 2019, EF core allows you to have computed columns in a clean way with the fluent API:

截至 2019 年，EF 核心允许您使用 fluent API 以干净的方式计算列：

Suppose that DisplayNameis the computed column you want to define, you have to define the property as usual, possibly with a private property accessor to prevent assigning it

假设这DisplayName是您要定义的计算列，您必须像往常一样定义属性，可能使用私有属性访问器来防止分配它

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    // this will be computed
    public string DisplayName { get; private set; }
}

Then, in the model builder, address it with the column definition:

然后，在模型构建器中，使用列定义对其进行寻址：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .Property(p => p.DisplayName)
        // here is the computed query definition
        .HasComputedColumnSql("[LastName] + ', ' + [FirstName]");
}

For further information, have a look at MSDN.

有关更多信息，请查看MSDN。

回答by Fernando Vieira

In EF6, you can just configure the mapping setting to ignore a calculated property, like this:

在 EF6 中，您可以将映射设置配置为忽略计算属性，如下所示：

Define the calculation on the get property of your model:

在模型的 get 属性上定义计算：

public class Person
{
    // ...
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName => $"{FirstName} {LastName}";
}

Then set it to ignore on the model configuration

然后将其设置为忽略模型配置

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //...
    modelBuilder.Entity<Person>().Ignore(x => x.FullName)
}