EF 代码中的计算列优先

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

我的意思是:

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)
}

如何在 EF CodeFirst 中实现这一点？

110878 次浏览
小开

One way is doing it with 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 think it's not good design.

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

-- 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
小开
最佳答案

You can create computed columns in your database tables. In the EF model you just annotate the corresponding properties with the DatabaseGenerated attribute:

[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 Grcic and 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.

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

modelBuilder.Entity<Income>().Property(t => t.Summ)
.ValueGeneratedOnAddOrUpdate()
小开 
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:

小开

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: 

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.

小开

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.

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!

小开

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

Suppose that DisplayName is 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

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.

小开

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

Define the calculation on the get property of your model:

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)
}

提交答案