C#•5mo ago

EF Core - Where clause on multiple field in object list

Hi everybody I'm using sql through EF Core, here at work, and I have a problem querying multiple results from a table In SQL, I've got that table of Foo with an index on field a and b In my code, I've got a list of Bar containing, quite conveniently, two field a and b I'd like to do something along the line of

sqlContext.Foo.Where(f => f.a == bar.a && f.b == bar.b);

sqlContext.Foo.Where(f => f.a == bar.a && f.b == bar.b);

But where foo could match with any element of an enumerable of bar My colleague suggest something looking like this

IEnumerable<string> stringifiedBar = bars.Select(bar => $"{bar.a}{bar.b}");
sqlContext.Foo.Where(foo => stringifiedBar.Contains(foo.a+foo.b));

IEnumerable<string> stringifiedBar = bars.Select(bar => $"{bar.a}{bar.b}");
sqlContext.Foo.Where(foo => stringifiedBar.Contains(foo.a+foo.b));

Super, that works But... It's slow as heck Our guess is that it try out every row in the table foo? Like, he can't really know the result of foo.a + foo.b so it does it for every single row before checking it for a match inside of stringifiedBar? How would one do that query properly?

11 Replies

Angius•5mo ago

.Where(f => bar.Contains(f.A) && bar.Contains(f.B))

.Where(f => bar.Contains(f.A) && bar.Contains(f.B))

? Ah, wait, an enumerable of Bars

.Where(f => bars.Any(b => b.A == f.A) && bars.Any(b => b.B == f.B))

.Where(f => bars.Any(b => b.A == f.A) && bars.Any(b => b.B == f.B))

Something like that

AltaOP•5mo ago

Yes, I may have miss explained the problem Both A and B should be equal for a matching record Here, if I read that line correctly, the A from Foo could match with a first Bar and the B from Foo could match with the A of a second Bar, wouldn't it? I'll give it a shot anyway, thank you 🙂

Angius•5mo ago

Right, so Foo's A and B should match those of any Bar in a list?

.Where(f => bars.Any(b => b.A == f.A && b.B == f.B))

.Where(f => bars.Any(b => b.A == f.A && b.B == f.B))

then

MODiX•5mo ago

Angius

REPL Result: Success

record Foo (int A, int B);
record Bar (int A, int B);

var bars = new Bar[]{ new(1, 2), new(3, 4), new(5, 6) };
var foos = new Foo[]{ new (3, 4), new (5, 6), new(7, 8) };

foos.Where(f => bars.Any(b => b.A == f.A && b.B == f.B))

record Foo (int A, int B);
record Bar (int A, int B);

var bars = new Bar[]{ new(1, 2), new(3, 4), new(5, 6) };
var foos = new Foo[]{ new (3, 4), new (5, 6), new(7, 8) };

foos.Where(f => bars.Any(b => b.A == f.A && b.B == f.B))

Result: WhereArrayIterator<Foo>

[
  {
    "a": 3,
    "b": 4
  },
  {
    "a": 5,
    "b": 6
  }
]

[
  {
    "a": 3,
    "b": 4
  },
  {
    "a": 5,
    "b": 6
  }
]

Compile: 495.947ms | Execution: 91.285ms | React with ❌ to remove this embed.

AltaOP•5mo ago

mmmmmm, that's actually great.. I wonder why we didn't do that anywhere in our code base rather that the ugly stringified trick 🤔 Thanks a lot @ZZZZZZZZZZZZZZZZZZZZZZZZZ 🙏 I'll try that out and close the thread once it works

Angius•5mo ago

I have an idea as to why, but I don't want to be rude to your coworkers 😛

AltaOP•5mo ago

mmmm, it seems like ef core can't translate the query or am I just bad at writting?

List<EntityLienCommandeClientCommandeFournisseur> foo = await this._ctx.LienCommandeClientCommandeFournisseur.AsNoTracking()
    .Where(l => lignesClients.Any(c =>
        l.NumeroJournalClient == c.NumeroJournal
        && l.NumeroPieceClient == c.NumeroPiece
        && l.NumeroLigneClient == c.NumeroLigne)
    ).ToListAsync();

List<EntityLienCommandeClientCommandeFournisseur> foo = await this._ctx.LienCommandeClientCommandeFournisseur.AsNoTracking()
    .Where(l => lignesClients.Any(c =>
        l.NumeroJournalClient == c.NumeroJournal
        && l.NumeroPieceClient == c.NumeroPiece
        && l.NumeroLigneClient == c.NumeroLigne)
    ).ToListAsync();

That looks close enough, though

Angius•5mo ago

Huh, it should work What's the database you're using? You could try splitting it into three .Where(...Any())s

Monsieur Wholesome•5mo ago

I was gonna say You expect passing a list of complex objects to EFC works? 😄 It works for Linq yee, but cannot be expressed in sql

Angius•5mo ago

Nah, it can work using IN [1, 2, 3, 4, 5] or whatever the syntax is Depends on the db though Not every db will support that

Monsieur Wholesome•5mo ago

That's not a complex type though Ofc you can do it with ints, strings and what not, but not with a complex type where 1 element consists of 3 columns / properties Edit: Though with SQL Server json columns and json functions (OPENJSON() and stuff) it could with some black magic be possible maybe, though I imagine not I can think of two ways 1 (Slow, not really nice): If its a small list of lignesClients of like 2 - 5 elements, you could build Expressions at runtime that will basically become

SELECT *
FROM LienCommandeClientCommandeFournisseur
WHERE (NumeroJournalClient = 'A1' AND NumeroPieceClient = 'B1' AND NumeroLigneClient = 'C1')
OR (NumeroJournalClient = 'A2' AND NumeroPieceClient = 'B2' AND NumeroLigneClient = 'C2')
OR (NumeroJournalClient = 'A3' AND NumeroPieceClient = 'B3' AND NumeroLigneClient = 'C3')

SELECT *
FROM LienCommandeClientCommandeFournisseur
WHERE (NumeroJournalClient = 'A1' AND NumeroPieceClient = 'B1' AND NumeroLigneClient = 'C1')
OR (NumeroJournalClient = 'A2' AND NumeroPieceClient = 'B2' AND NumeroLigneClient = 'C2')
OR (NumeroJournalClient = 'A3' AND NumeroPieceClient = 'B3' AND NumeroLigneClient = 'C3')

2 (Fast, pretty cool, yo!): Hoping that your lignesClients is an IEnumerable (or array or list) that comes from an IQueryable query, and making sure it stays an IQueryable, so that you can make it a sub-query within your query here with what @ZZZZZZZZZZZZZZZZZZZZZZZZZ suggested, which should translate into something like

SELECT LCCCF.*
FROM LienCommandeClientCommandeFournisseur AS LCCCF
WHERE EXISTS (
    -- Your Sub-Query
  SELECT 1
  FROM WhereverYouGetYourLignesClientsFrom AS S
  WHERE 
      LCCCF.NumeroJournalClient = S.NumeroJournalClient 
      AND LCCCF.NumeroPieceClient = S.NumeroJournalClient
      AND LCCCF.NumeroLigneClient = S.NumeroJournalClient
)

SELECT LCCCF.*
FROM LienCommandeClientCommandeFournisseur AS LCCCF
WHERE EXISTS (
    -- Your Sub-Query
  SELECT 1
  FROM WhereverYouGetYourLignesClientsFrom AS S
  WHERE 
      LCCCF.NumeroJournalClient = S.NumeroJournalClient 
      AND LCCCF.NumeroPieceClient = S.NumeroJournalClient
      AND LCCCF.NumeroLigneClient = S.NumeroJournalClient
)

Gaming

Programming

EF Core - Where clause on multiple field in object list