从 Linq 查询中删除联接

Question

我有两个这样的实体：

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

想将以下查询转换为无需连接即可工作，因为帐户实体具有 accountOwners 且 accounOwner 具有帐户导航属性（主键/外键关系）

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

正如 Sergi 建议的那样，我发现了这一点：

var sum = (from account in context.Accounts
           from owner in account.AccountOwners
           where (owner.AccountOwnerRegistryId == ownerRegistryId
               && !excludeTypes.Contains(account.AccountType)
               && (account.StateChangeDate == null ||
                   (account.StateChangeDate.Month - DateTime.Now.Month)
                       <= maxStateChangeMonth)
               && account.AccountStatusId != excludeStatusId
               && (includeMortgage.Contains(account.AccountType) || 
                    account.AccountType.Contains("Mortgage")))
            select account.MinimumInstallment)
               .Sum(minimumInstallment => Math.Abs(minimumInstallment));

我添加了另一个 & ;&子句

 && (includeMortgage.Contains(account.AccountType) ||
    account.AccountType.Contains("Mortgage") 

，但现在我得到：

转换为值类型“Decimal”失败，因为物化值为空。结果类型的泛型参数或查询必须使用可为 null 的类型。

谢谢

Answer 1

正如您所提到的，您可以使用导航属性而不是联接，因此您可以尝试类似的操作：

var sum = (from account in context.Accounts
           from owner in account.AccountOwners
           where (owner.AccountOwnerRegistryID == ownerRegId
               && !excludeTypes.Contains(account.AccountType)
               && (account.StateChangeDate == null || 
                  (account.StateChangeDate.Month - DateTime.Now.Month)
                       <= maxStateChangeMonth)
               && account.AccountStatusID != excludeStatusId)
           select account.MinimumInstallment)
               .Sum(minimumInstallment => Math.Abs(minimumInstallment));

两个“连接”from 子句相当于 SelectMany Linq 中的方法。