将两个IQueryable连接起来

Question

C# / Entity Framework 问题： 我有一个对象

Account 
{
   public string AccountId { get; set; }
   public string UserId { get; set; }
   public string CurrencyId { get; set; }
}

我需要返回“用户A”的所有帐户，这些帐户与“用户B”的帐户具有相同的currencyId。这是一个简单的SQL查询，但我在EF上遇到了困难。以下是我的尝试：

public IQueryable<Account> Test(string userA, string userB)
{
    var accountsA = GetAccounts().Where(x => x.UserId == userA);
    var accountsB = GetAccounts().Where(x => x.UserId == userB);

    return accountsA.Join(
            accountsB,
            acc1 => acc1.CurrencyId,
            acc2 => acc2.CurrencyId,
            (acc1, acc2) => acc1
        );
}

这个查询有效，但返回了很多重复的用户A账户。

我尝试过：

public IQueryable<Account> Test(string userA, string userB)
{
    var accountsA = GetAccounts().Where(x => x.UserId == userA);
    var accountsB = GetAccounts().Where(x => x.UserId == userB);

    return accountsA.GroupJoin(
            accountsB,
            acc1 => acc1.CurrencyId,
            acc2 => acc2.CurrencyId,
            (acc1, acc2) => acc1
        );
}

但它会崩溃。

System.InvalidOperationException
  HResult=0x80131509
  Message=Processing of the LINQ expression 'DbSet<Account>
    .Where(x => x.UserId == "userA").GroupJoin(
        outer: DbSet<Account>
            .Where(x => x.UserId == "userB"), 
        inner: acc1 => acc1.CurrencyId, 
        outerKeySelector: acc2 => acc2.CurrencyId, 
        innerKeySelector: (acc1, acc2) => acc1)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

我该如何在EF查询中使用DISTINCT关键字？

- Dmitry Sazonov

Answer 1

public IQueryable<Account> Test(string userA, string userB)
{
    var accountsA = GetAccounts().Where(x => x.UserId == userA);
    var accountsB = GetAccounts().Where(x => x.UserId == userB);

    return accountsA.Where(accountA => 
         accountsB.Any(accountB => accountB.CurrencyId == accountA.CurrencyId)
    );
}

- Milney

Answer 2

用户B有零个或多个“账户”，每个“账户”都有一个“CurrencyId”。需要返回所有与用户B的CurrencyId相同的“用户A”的账户。显然，我们需要用户B的CurrencyIds。

int idUserB = ...
var currencyIdsOfUserB = GetAccounts()
    .Where(account => account.Id == idUserB)
    .Select(account => account.CurrencyId)
    .Distinct();                              // only if you expect duplicates

用户A的所有帐户中，至少有一个以下货币ID之一：

int idUserA:
var result = GetAccounts.Where(account => account.Id == idUserB
             && currencyIdsOfUserB.Contains(account.CurrencyId);

Accounts
Id UserId CurrencyId
01   09      18
02   10      50
03   11      19
04   20      49
05   10      51
06   10      52
07   20      52
08   20      51
09   10      50
10   20      52

• 用户[10]拥有账户2、5、6、9，币种ID分别为50、51、52、50

• 用户[20]拥有账户4、7、8、10，币种ID分别为49、52、51、52

• currenCyIdsOfUserB = {50, 51, 52}

• 给我所有UserID等于[10]且CurrencyId在{50, 51, 52}中的账户。结果将是ID为7、8、10的账户。

简单得像打招呼！

- Harald Coppoolse

Answer 3

好的，我找到了。它是 .Distinct()

所以答案将会是

    var a1 = accountContext.Account.Where(x => x.UserId == "userA");
    var a2 = accountContext.Account.Where(x => x.UserId == "userB");
    var result = a1.Join(
            a2,
            acc1 => acc1.CurrencyId,
            acc2 => acc2.CurrencyId,
            (acc1, acc2) => acc1
        )
        .Distinct();

- Dmitry Sazonov