how can i get the membership sold count

Hi I开发者_StackOverflow中文版 have a table called membertomship with columns...

memberToMship_Id
memberToMship_StartDate 
memberToMship_EndDate
memberToMship_JoinFee
memberToMship_ChargePerPeriod
memberToMship_InductionFee
mshipOption_Id

and i have another table called mshipoptions with columns

   mshipOption_Id
   mshipOption_Period
   mshipType_Id 

and i have another table mshiptypes

  mshipType_Id
  mshipType_Name

and my datacontext name is tsgdbcontext

how can i convert below query into linq

"SELECT mshipType_Name, COUNT('A') AS mshipssold,
                                sum(memberToMship_InductionFee+memberToMship_JoinFee+
                                  (IF(mshipOption_Period='year',
                                  TIMESTAMPDIFF (YEAR ,memberToMship_StartDate, memberToMship_EndDate),
                                  TIMESTAMPDIFF (MONTH ,memberToMship_StartDate, memberToMship_EndDate)) * memberToMship_ChargePerPeriod)) as value
                              FROM membertomships
                              inner join mshipoptions on membertomships.mshipOption_Id = mshipoptions.mshipOption_Id
                              inner join mshiptypes on mshipoptions.mshipType_Id = mshiptypes.mshipType_Id
                              WHERE memberToMship_StartDate BETWEEN '2010-09-08' AND '2011-09-06'
                              GROUP BY mshipType_Name

I have tried something like this:

Modified Code :

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

 var query = from m in tsgdbcontext.membertomship
              where m.memberToMship_StartDate  >= dateFrom && m.memberToMship_StartDate <=    dateTo 
               group m by m.mshipType_Name

I dont know exactly what i have to do next

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))
                          });

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.