score:2

Accepted answer

Let's assume that you have the classes set up with associations, such as (using CodeFirst EF). If using the designer, then use the associations and classes as you've defined them.

public class MemberToMembership
{
    [Key] // maybe also DatabaseGenerated.Identity?
    public virtual int Id { get; set; }
    public virtual DateTime StartDate { get; set; }
    public virtual DateTime StartDate { get; set; }
    public virtual decimal JoinFee { get; set; }
    public virtual decimal ChargePerPeriod { get; set; }
    public virtual decimal InductionFee { get; set; }
    public virtual int OptionId { get; set; }

    [ForeignKey("OptionId")]
    public virtual MembershipOption Option { get; set; }
}

public class MembershipOption
{
    [Key]
    public virtual int Id { get; set; }

    public virtual string Period { get; set; }

    public virtual int TypeId { get; set; }

    [ForeignKey("TypeId")]
    public virtual MembershipType Type { get; set; }

    public virtual ICollection<MemberToMembership> MemberMap { get; set; }
}

public class MembershipType
{
     [Key]
     public virtual int Id { get; set; }

     public virtual string Name { get; set; }

     public virtual ICollection<MembershipOption> Options { get; set; }
}

Now we can take advantage of the relationships to help form the query.

var dateFrom = new DateTime(2010, 9, 8); // start of day we care about
var dateTo = new DateTime(2011, 9, 6).AddDays(1); // end of day we care about
var query = tgsdbcontext.MemberToMemberships
                        .Where( mm => mm.StartDate > dateFrom && mm.StartDate < dateTo )
                        .GroupBy( mm => mm.Option.Type.Name )
                        .Select( g => new
                         {
                             Period = g.Key,
                             Count = g.Count(),
                             Value = g.Sum( e => e.JoinFee
                                                   + e.InductionFee
                                                   + (e.Option.Period == "year"
                                                        ? EntityFunctions.DiffYears(e.StartDate,e.EndDate) * e.ChargePerPeriod
                                                        : EntityFunctions.DiffMonths(e.StartDate,e.EndDate) * e.ChargePerPeriod))
                          });

score:1

Try something like this:

DateTime dateFrom = new DateTime(2010, 9, 8); 
DateTime dateTo = new DateTime(2001, 9, 6);

var query = from t1 in tsgdbcontext.membertomship
    join t2 in tsgdbcontext.mshipoptions on t1.mshipOption_Id equals t2.mshipOption_Id
    join t3 in tsgdbcontext.mshiptypes on t1.mshipType_Id equals t3.mshipType_Id
    where t1.memberToMship_StartDate  >= dateFrom && 
        t1.memberToMship_StartDate <= dateTo 
    group t1 by t1.mshipType_Name into g
    select new { 
        mshipType_Name = g.Key, 
        mshipssold = g.Count(), 
        value = (from x in g select memberToMship_InductionFee +        
            memberToMship_JoinFee + ((mshipOption_Period = 'year' ? 
                memberToMship_EndDate.Year - memberToMship_StartDate.Year : 
                ConvertTimeSpanToMonths(memberToMship_EndDate - 
                memberToMship_StartDate)) * memberToMship_ChargePerPeriod)
        ).Sum()
    }

NOTE: The code above is untested so might need tweaking, but this should give you the general idea of how to do this

You'll need to write or copy one of the available solutions for ConvertTimeSpanToMonths method to convert a TimeSpan to months. Here a link to something you can use: Date Subtraction Examples.


Related Articles