c# - "dot notation"中的 LINQ to Entities 查询

Question

我的 ASP.Net MVC3 应用程序中有两个实体。我正在使用 EF 4.1

[Table("tblAccount")]
public class Account
{      
    [Key]
    [Column("Creditor Registry ID", Order = 0)]
    public int CreditRegistryId { get; set; }

    [Key]
    [Required]
    [Column("Account No", Order = 1)] 
    public int AccountNo { get; set; }

    [Column("Minimum Installment")]
    public decimal MinimumInstallment { get; set; }

    [Column("Account Status Date")]
    public DateTime AccountStatusDate { get; set; }


    [Required]
    [Column("Account Type")]
    public string AccountType { get; set; }

    public virtual ICollection<AccountOwner> AccountOwners { get; set; }
}

和

[Table("tblAccountOwner")]
public class AccountOwner
{
    [Key]
    [ForeignKey("Account")]
    [Column("Creditor Registry ID", Order = 0)]   
    public int CreditorRegistryId { get; set; }


    [Key]
    [ForeignKey("Account")]
    [Column("Account No", Order = 1)]           
    public int AccountNo { get; set; }

    [Key]
    [Column("Account Owner Registry ID", Order = 2)] 
    public long AccountOwnerRegistryId { get; set; }

    public virtual Account Account { get; set; }
}

我需要使用扩展方法“点”表示法将以下查询转换为 LINQ to Entities 查询:

SELECT Sum(ABS([Minimum Installment])) AS SumOfMonthlyPayments
    FROM tblAccount 
    INNER JOIN tblAccountOwner ON
        tblAccount.[Creditor Registry ID] = tblAccountOwner.[Creditor Registry ID] AND
        tblAccount.[Account No] = tblAccountOwner.[Account No] 
    WHERE (tblAccountOwner.[Account Owner Registry ID] = 731752693037116688) AND
          (tblAccount.[Account Type] NOT IN  ('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04')) AND
          (DATEDIFF(mm, tblAccount.[State Change Date], GETDATE()) <= 4 OR tblAccount.[State Change Date] IS NULL) AND
          (tblAccount.[Account Status ID] <> 999)

我尝试了以下查询:

var minIns = context.Accounts
                    .Where(x=>x.CreditRegistryId == x.AccountOwners.Any(z=>z.AccountOwnerRegistryId)
                    .Sum(p => Math.Abs(p.MinimumInstallment));

但它不起作用。怎么写呢？

Answer 1

var ownerRegId = 731752693037116688L;
var excludeTypes = new[]
{ 
    "CA00", "CA01", "CA03", "CA04", "CA02", "PA00", "PA01", "PA02", "PA03", "PA04"
};
var maxStateChangeMonth = 4;
var excludeStatusId = 999;

var SumOfMonthlyPayments = context.AccountOwners
       .Where(ao => ao.AccountOwnerRegistryId == ownerRegId
           && !excludeTypes.Contains(ao.Account.AccountType)
           && (ao.Account.StateChangeDate == null
               || ao.Account.StateChangeDate.Month - DateTime.Now.Month <= maxStateChangeMonth)
           && ao.Account.AccountStatusID != excludeStatusId)
        .Sum(ao => Math.Abs(ao.Account.MinimumInstallment));

或

var SumOfMonthlyPayments =
    (from ao in context.AccountOwners
     let a = ao.Account
     where ao.AccountOwnerRegistryId == ownerRegId
         && !excludeTypes.Contains(a.AccountType)
         && (a.StateChangeDate == null
             || a.StateChangeDate.Month - DateTime.Now.Month <= maxStateChangeMonth)
         && a.AccountStatusID != excludeStatusId)
     .Sum(ao => Math.Abs(ao.Account.MinimumInstallment));