c# - 我如何获得成员(member)出售数量

嗨，我有一个名为membertomship的表，带有列...

memberToMship_Id
memberToMship_StartDate
memberToMship_EndDate
memberToMship_JoinFee
memberToMship_ChargePerPeriod
memberToMship_InductionFee
mshipOption_Id

我有另一个表称为mshipoptions的列

   mshipOption_Id
   mshipOption_Period
   mshipType_Id

我还有另一个表mshiptypes

  mshipType_Id
  mshipType_Name

我的数据上下文名称是tsgdbcontext

我如何将下面的查询转换为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

我已经尝试过这样的事情：

修改后的代码：

  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

我不知道下一步该怎么做

最佳答案

假设您已经建立了带有关联的类，例如（使用CodeFirst EF）。如果使用设计器，请使用已定义的关联和类。

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

现在，我们可以利用这些关系来帮助形成查询。

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

关于c# - 我如何获得成员(member)出售数量，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7370839/