c# - value - Calculated column in EF Code First




hascomputedcolumnsql (7)

Concatenating a list of strings into a single string

If you need a separator-separated string then:

public List<string> Categories { get; set; }

public string Category
{
    get
    {
        return String.Join(",", Categories);
    }
}

If just concatenate i.e. join by empty string:

public string Category
{
    get
    {
        return String.Concat(Categories);
    }
}

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?


Why I'm gettin Cannot insert the value NULL into computed column?

As mentioned here and here you can't update a calculated column because it's value is retreived from database and never sent back.

So the solution would be to create another not mapped calculated property in your model which will be used in your code and keep your Total property mapped to the database:

public int Count { get; set; }
public int Fee { get; set; }
public int Total { get; set; }

[NotMapped]
public int CalculatedTotal
{
    get { return Count*Fee; }
}

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!


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.


Try to declare public int? Total to accept null.


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.


public static string Concat(this IEnumerable<string> yourString) {
    StringBuilder category= new StringBuilder();
    foreach(string s in yourString) {
        category.Append(s);
    }
    return category.ToString();
}

if you are using .net 4.0

  String.Join(String.Empty, yourString.ToArray());




calculated-columns