0

I am working with Entity Framework code-first and ASP.NET MVC in C#. I have a table:

public class Project
{
    public Guid Id { get; set; }
    public Guid UserId { get; set; }
    [Required]
    public DateTime CreationDate{ get; set; }
    [Required]
    public DateTime EndDate { get; set; }        
    [Required]
    public bool Active{ get; set; }

}

My question is, given the above fields, how do I update the Active field, after the EndDate passed?

For instance, the end date is 08/04/2018 and today is 08/05/2018, then Active=false.

Please take into account that performance is important in this case. Each user creates a project that other users can see.

Thank you.

ev vk
  • 345
  • 1
  • 4
  • 18
  • 1
    Are you aiming at some automated/scheduled manner to update this field? – Gert Arnold Aug 04 '18 at 17:02
  • Yes dear Arnold, I would like it to be automated or scheduled. – ev vk Aug 04 '18 at 17:04
  • Then you should be more specific. There are many ways to do that. It would be great if you could share some first attempts at achieving this. – Gert Arnold Aug 04 '18 at 17:06
  • 1
    **IMHO**, some (simpler) scheduled task (as commented above) to say, run a `sproc`, sounds good (no need to force/complicate things with some EF/app bound way). – EdSF Aug 04 '18 at 17:11
  • 1
    If you don't really need the value of `Active` in the database, you can turn it into a read-only property (by removing the setter) and make it return `if (EndDate > DateTime.Now ? true : false);` or just `(EndDate > DateTime.Now);`. – 41686d6564 stands w. Palestine Aug 04 '18 at 17:13
  • @AhmedAbdelhameed the Active value is used in the database to filter the active projects – ev vk Aug 04 '18 at 17:19
  • @EdSF can you please suggest some kind of stored procedure in the answer? I am using entity framework for this project. I know I can import a sproc, but can I make it run in an automated fashion? – ev vk Aug 04 '18 at 17:21
  • Use a computed column in the database to return either 0 or 1 based on `GetDate` or `GetUtcDate` (or equivalent) compared to `EndDate`. See also [Calculated column in EF Code First](https://stackoverflow.com/q/15585330/1260204) – Igor Aug 04 '18 at 17:31
  • can you please write an answer? so I can vote it. Thank you – ev vk Aug 04 '18 at 17:38
  • this doesn't sound like something you'd want to be done by the database, since this field can change at any time. I would simply ignore this property in the database model and write the getter to implement the desired behavior. – DevilSuichiro Aug 04 '18 at 18:28

2 Answers2

2

This isn't the only way. Use one that suits your needs/resources (what you have access to - e.g. SQL Server Agent). This takes things away from EF/application (everything in this sample is done in SQL Server).

A simplistic stored procedure could be:

// Expire after current date
UPDATE [insert your table name here] 
SET Active = 0 
WHERE DATEDIFF(DAY, EndDate, GETDATE()) > 0;

Yes, you can schedule a stored procedure to run as a JOB

So assuming you named the stored procedure UpdateStatus, your job command would be EXEC dbo.UpdateStatus.

Hth...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
EdSF
  • 11,753
  • 6
  • 42
  • 83
1

Try making Active field a computed column. 

[DatabaseGenerated(DatabaseGenerationOption.Computed)]
public bool Active { get { return [condition goes here]; } }
Ugo Okoro
  • 164
  • 6
  • You're right about database-generated, but not about the condition in the getter. It should be a standard getter because, well, the database generates the value. – Gert Arnold Aug 04 '18 at 21:05
  • hello, I like what you have here, however the active field will be used for some entity framework queries. For instance, filter where active is true – ev vk Aug 06 '18 at 02:15
  • @GertArnold can you specify more about this? – ev vk Aug 06 '18 at 02:16
  • The database field `Active` should be a [computed column](https://stackoverflow.com/a/15585492/861716). Then all EF does is read its value. – Gert Arnold Aug 07 '18 at 07:33