您好我有一个名为membertomship与列的表...

Hi I have a table called membertomship with columns...

memberToMship_Id
memberToMship_StartDate 
memberToMship_EndDate
memberToMship_JoinFee
memberToMship_ChargePerPeriod
memberToMship_InductionFee
mshipOption_Id

和我有另一个表称为mshipoptions的列

and i have another table called mshipoptions with columns

   mshipOption_Id
   mshipOption_Period
   mshipType_Id 

和我有另一个表mshiptypes

and i have another table mshiptypes

  mshipType_Id
  mshipType_Name

和我的datacontext的名字是 tsgdbcontext

and my datacontext name is tsgdbcontext

我怎么能转换下面的查询到LINQ

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:

修改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

推荐答案

让我们假设你已经设置了关联的类，如（C $ cFirst EF使用$）。如果使用的是设计师，然后使用协会和类为你定义它们。

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