The C# / Entity Framework problem: I have object
Account
{
public string AccountId { get; set; }
public string UserId { get; set; }
public string CurrencyId { get; set; }
}
then I need to return all accounts on "user A" which have same currencyId as accounts for "user B"
This is simple SQL query, but I stuck with EF. This is what I tried
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
);
}
this query works but return a lot of duplicate accounts for userA.
I tried
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
);
}
but it crash with
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.
how can I do DISTINCT on EF queries?
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)
);
}
ok, I found it. it is .Distinct()
so the answer will be
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();
So user B has zero or more Accounts
, where every Account
has a CurrencyId
.
"I need to return all accounts on "user A" which have same currencyId as the currencyIds of user B"
Apparently we need the CurrencyIds` of user B:
int idUserB = ...
var currencyIdsOfUserB = GetAccounts()
.Where(account => account.Id == idUserB)
.Select(account => account.CurrencyId)
.Distinct(); // only if you expect duplicates
All accounts of user A that have at least one of these currencyIds:
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
User [10] has Accounts 2, 5, 6, 9 with CurrencyIds 50, 51, 52, 50
User [20] has Accounts 4, 7, 8, 10 with CurrencyIds 49, 52, 51, 52
currenCyIdsOfUserB = {50, 51, 52}
Give me all Accounts with UserId equal to [10] and CurrencyId in {50, 51, 52}. The result will be the Accounts with Id 7, 8, 10
Simple comme bonjour!
User contributions licensed under CC BY-SA 3.0