Database Calculations - MVC 3 and CodeFirst

I'm trying t开发者_如何学Co write a method that generates multiple commission slips. This is for a college, where clients are enrolled with tutors in a class called Enrollments. With this method, I am trying to accumulate the monthly fee of the tutors' clients multiplied by their commission percentages, as tutors earn a certain commission on the lessons they give. Here is my code for this:

public ActionResult CreateBulkCommissions()
{
var month = DateTime.Now.ToString("MMMM");

var enrolments = db.Enrollments.ToList();

var newCommissions = from enrolment in enrolments
                     select new TutorCommission()
                     {
                         CommissionAmount = enrolment.MonthlyFee,
                         CommissionMonth = month,  // string constant 
                         CommissionStatus = "Unpaid",
                         Tutor = enrolment.Tutor
                     };
foreach (var newCommission in newCommissions)
{
    List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
    {
        CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage,
        TutorNoID = s.Key.TutorNoID

    }).ToList();

    db.TutorCommission.Add(newCommission);
    db.SaveChanges();
}

return RedirectToAction("Index");
}

The problem is that TutorCommission entries are created for each individual enrollment, instead of one entry per tutor (for the month) with the total commission amount. I.e. Ashley has 3 clients and therefore 3 enrollments and currently 3 TutorCommission entries are being created for him. I want to add up the enrollments amounts for one entry. In addition, the amount is not being multiplied by the commission percentage, so it is just saving as the full enrollment monthly fee. Relevant classes are:

public class Enrollment
{
    [Key]
    [Display(Name = "Enrollment ID Number")]
    public long EnrollmentIDNumber { get; set; }
    [Display(Name = "Client ID Number")]
    public long ClientNumberID { get; set; }
    [Display(Name = "Tutor ID Number")]
    public long TutorNoID { get; set; }
    [Display(Name = "Course Name")]
    public string CourseName { get; set; }
    [Display(Name = "Lesson Time")]
    public string LessonTime { get; set; }
    [Display(Name = "Lesson Day")]
    public string LessonDay { get; set; }
    [Display(Name = "Lesson Location")]
    public string LessonLocation { get; set; }
    [Display(Name = "Lesson Type")]
    public string LessonType { get; set; }
    [Display(Name = "Lesson Level")]
    public string LessonLevel { get; set; }
    [Display(Name = "Monthly Fee")]
    public long MonthlyFee { get; set; }

    public virtual Client Client { get; set; }
    public virtual Tutor Tutor { get; set; }

}

public class TutorCommission
{
    [Key]
    [Display(Name = "Commission ID")]
    public long CommissionID { get; set; }
    [Display(Name = "Commission Month")]
    public string CommissionMonth {get; set;}
    [Display(Name = "Commission Amount")]
    public double CommissionAmount { get; set; }
    [Display(Name = "Commission Status")]
    public string CommissionStatus { get; set; }
    [Display(Name = "Tutor ID Number")]
    public long TutorNoID { get; set; }

    public virtual Tutor Tutor { get; set; }
    public virtual ICollection<CommissionPayments> CommissionPayments { get; set; }

}

public class Tutor
{
    [Key]
    [Display(Name = "Tutor ID Number")]
    public long TutorNoID { get; set; }
    [Required]
    [StringLength(50, ErrorMessage="First name must be less than 50 characters")]
    [Display(Name = "First Name")]
    public string TutorFirstName { get; set; }
    [StringLength(50, ErrorMessage = "Last name must be less than 50 characters")]
    [Display(Name = "Last Name")]
    public string TutorLastName { get; set; }
    [DisplayFormat(DataFormatString = "{0:d}", ApplyFormatInEditMode = true)]
    [Display(Name = "Birth Date")]
    public DateTime? TutorBirthDate { get; set; }
    [Display(Name = "Cellphone Number")]
    public string TutorCellphoneNumber { get; set; }
    [Display(Name = "Home Number")]
    public string TutorHomeNumber { get; set; }
    [RegularExpression("^[a-z0-9_\\+-]+(\\.[a-z0-9_\\+-]+)*@[a-z0-9-]+(\\.[a-z0-9-]+)*\\.([a-z]{2,4})$", ErrorMessage = "Not a valid email address")]
    [Display(Name = "Email Address")]
    public string TutorEmailAddress { get; set; }
    [Display(Name = "Street Address")]
    public string TutorStreetAddress { get; set; }
    [Display(Name = "Suburb")]
    public string TutorSuburb { get; set; }
    [Display(Name = "City")]
    public string TutorCity { get; set; }
    [Display(Name = "Postal Code")]
    public string TutorPostalCode { get; set; }
    [Display(Name="Full Name")]
    public string FullName
    {
        get
        {
            return TutorFirstName + " " + TutorLastName;
        }
    }
    [Display(Name="Commission Percentage")]
    [Required]
    public double TutorCommissionPercentage { get; set; }

    public virtual ICollection<Enrollment> Enrollments { get; set; }
    public virtual ICollection<TutorCommission> TutorCommissions { get; set; }

}

Thanks, Amy

---

You can try moving the grouping logic outside the foreach loop, then iterating on the grouped list. So instead of

foreach (var newCommission in newCommissions)
{
    List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
    {
        CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage,
        TutorNoID = s.Key.TutorNoID

    }).ToList();

    db.TutorCommission.Add(newCommission);
    db.SaveChanges();
}

try

List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
    {
        CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage,
        TutorNoID = s.Key.TutorNoID

    }).ToList();

foreach (var tutorCom in TutorComs)
{    
    db.TutorCommission.Add(tutorCom);
    db.SaveChanges();
}

See if that is closer to the desired result.