How to convert EF properties into SQL Server's computed columns?

Question

I'd like to transform these Entity Framework's properties into SQL Server's computed columns. Is it possible? All other properties specified are table fields. Moreover, considering that I am using Code First, how I should specify the computed columns inside my model?

public enum Severity : int { INTIME = 0, B = 1, M = 2, A = 3, TIMEOUT = 4 };
public enum StatiTT : int { ND_INT = 1, ND_REP = 2, INT = 3, DI = 4, CH = 5, RV = 6, AN = 7 };
private const float TRESH_B = (float)0.5;
private const float TRESH_M = (float)0.3;
private const float TRESH_A = (float)0.2;

A)

 public int MinutiAllaScadenza
   {
       get
       {
           int mm = 0;
           DateTime Ora = DateTime.Now;

           mm = (DataObiettivo - Ora).Days*1440 + (DataObiettivo - Ora).Hours * 60 + (DataObiettivo - Ora).Minutes;

           if (StatoTicketID > (int)StatiTT.DI && mm < 0) mm = 10000000; 

           return mm;
       }
   }

B)

public int Sev 
   {
       get
       {
           int sev = 0; 
           float perctres = PercentualeTempoResiduo; 

           if (StatoTicketID < (int)StatiTT.CH)
           {
               if (MinutiAllaScadenza < 0) { sev = (int)Severity.TIMEOUT; return sev; }
               if (perctres < TRESH_A) { sev = (int)Severity.A; return sev; }
               if (perctres < TRESH_M) { sev = (int)Severity.M; return sev; }
               if (perctres < TRESH_B) { sev = (int)Severity.B; return sev; }
           }
           return sev;
       }
   }

C)

public float PercentualeTempoResiduo
   {
       get
       {

           if (StatoTicketID > (int)StatiTT.DI) return 999;

           float perc = 0;
           float mm2scad = (float)MinutiAllaScadenza;
           float mmtot = (float)TempoTotaleInizio_Obiettivo;

           if (MinutiAllaScadenza > 0)

               perc = (float)1 - (mmtot-mm2scad) / mmtot;

           return perc;
       }
   }

D)

   public string Alert
   {
       get
       {
           string alert = "";
           float perctres = PercentualeTempoResiduo; 

           if (StatoTicketID < (int)StatiTT.CH) {
               if (perctres < TRESH_A) { alert = "A"; return alert; }
               if (perctres < TRESH_M) { alert = "M"; return alert; }
               if (perctres < TRESH_B) { alert = "B"; return alert; }
           }
           return alert;
       }
   }

Thank you very much!

Answer 1

The answer to your main question is yes, these C# routines can be converted to computed columns. But there's one condition: the relevant information needed to compute the result must be found in the table itself.

For your Alert property, here's how your computed column would look:

alter table <TBLNAME> add <COLNAME>  AS (
  case 
    when StatoTicketID < (int)StatiTT.CH AND perctres < .2 then 'A'
    when StatoTicketID < (int)StatiTT.CH AND perctres < .3 then 'M'
    when StatoTicketID < (int)StatiTT.CH AND perctres < .5 then 'B'
    else ''
  end)

If your computed result needs to use information from other tables then consider binding EF to a view for reading and then using plain EF objects for your write operations.

As for code first, you cannot write a computed column in C# and expect EF to translate that into a SQLServer computed column for you. Write the computed column in the DB after you've done your model first generating and then go back and wire it into your EF object.