“点表示法”中的 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));

Answer 2

为什么你写的形式很重要？查询表示法与“点表示法”一样好。无论如何，这是我的尝试。我相信这里使用的所有内容都应该是可翻译的。

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.Accounts
           .Join(context.AccountOwners,
                 a => new { CreditorRegistryId = a.CreditRegistryId, a.AccountNo },
                 ao => new { ao.CreditorRegistryId, ao.AccountNo },
                 (a, ao) => new { Account = a, AccountOwner = ao })
           .Where(x => x.AccountOwner.AccountOwnerRegistryID == ownerRegId
                    && !excludeTypes.Contains(x.Account.AccountType)
                    && (x.Account.StateChangeDate == null || x.Account.StateChangeDate.Month - DateTime.Now.Month <= maxStateChangeMonth)
                    && x.Account.AccountStatusID != excludeStatusId)
           .Sum(x => Math.Abs(x.Account.MinimumInstallment));

这里没有显式使用连接：

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