gpt4 book ai didi

c# - 我怎样才能得到成员(member)销售数量

转载 作者:行者123 更新时间:2023-11-30 22:35:42 25 4
gpt4 key购买 nike

您好,我有一个名为 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/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com