Generate DateTime Series in Months using C# and Entity Framework

Question

I am a newbie. This is my entity model

''' public virtual DateTime StartDate { get; set; }

    [Required]
    public virtual string Name { get; set; }
    [Column(TypeName = "money")]
    public virtual decimal SalesAmount { get; set; }
    [Column(TypeName = "money")]
    public virtual decimal CostofSales { get; set; }


    [Column(TypeName = "money")]
    public virtual decimal GrossProfit { get { return SalesAmount - CostofSales; } }

    public virtual int Months { get; set; }

    public virtual DateTime EndDate { get {return StartDate.AddMonths(Months); } } '''

Now I want to ensure that when a user enters their start date and the months they want to project for then, my database will be filled with months between start date and end date. This means I need a way to make a monthly date time series which they can then use to generate sales projections on a monthly basis over the period they set. Please help or point me to a similar solution that can help

See if this helps https://stackoverflow.com/questions/3738748/create-an-array-or-list-of-all-dates-between-two-dates — auburg, May 28 '20 at 10:43

score 0 · Accepted Answer · answered May 31 '20 at 06:36

You shouldn't put the logic in the EF models. EF models are just used to map objects to the database tables.

I am thinking you want something like this:

public class SalesProjection
{
    public int Id { get; set; }

    public string Name { get; set; }

    public decimal SalesAmount { get; set; }

    public decimal CostofSales { get; set; }

    //Mark only the navigation properties with the keyword 'virtual'    
    public virtual IEnumerable<ProjectionMonth> ProjectionMonths { get; set; }
}

public class ProjectionMonth
{
     public int Id { get; set; }

     public int SalesProjectionId { get; set; }

     public int Month { get; set; }

     [ForeignKey("SalesProjectionId ")] //EF attribute to denote foreign key.
     public virtual SalesProjection SalesProjection { get; set; }
}

The entity models should not contain any business logic such as calculations (e.g GrossProfit). GrossProfit can be calculated on the fly instead of saving it to the database. All business logic should be done in Data Transfer Objects (DTO). Create another set of models (DTO) that will be used to transform the data based on business rules. These are the models you can return to the client:

    public class SalesProjectionDTO
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public decimal SalesAmount { get; set; }

        public decimal CostofSales { get; set; }

        public decimal GrossProfit { get; set; }
    }

Then, assuming you are using Entity Framework:

public List<SalesProjectionDTO> GetProjectionsByDateRange(int startMonth, int endMonth) 
{
     var projections = _context.SalesProjection
       .Where(sp => sp.SalesProjectionMonths
                      .Any(m => m.Month >= startMonth && m.Month <= endMonth))
       .Select(sp => new SalesProjectionDTO {
               Name = sp.Name,
               SalesAmount = sp.SalesAmount,
               CostOfSales = sp.CostOfSales,
               GrossProfit = sp.SalesAmount - sp.CostOfSales
         }).ToList();

     return projections;
}

public void SaveProjections(SalesProjectionDTO projection, int startMonth, int endMonth)
{
     //check if projection exists
     var entity= _context.SalesProjection.Where(sp => sp.Id == projection.Id)
           .FirstOrDefault() ?? new SalesProjection();

     if (entity== null)
         _context.Add(projection);

     entity.Name = projection.Name;
     entity.SalesAmount = projection.SalesAmount;
     entity.CostOfSales = projection.CostOfSames;

     for (var i = startMonth; i <= endMonth; i++){
          entity.ProjectionMonths.Add(
               new ProjectionMonth { 
                    Month = i;
               }
     }

     _context.SaveChanges();
}

Generate DateTime Series in Months using C# and Entity Framework

1 Answers1