C
C#ā€¢3mo ago
Alta

EF Core Where on multiple fields

Initial question Hi! I'm currently running into an issue where I try and do something along the lines of
void SomeMethod(string argBar, int argBuzz){
//...
efCoreContext.FooTable.Where(f => new { bar = f.bar, buzz = f.buzz } == new { bar = argBar, buzz = argBuzz })
//...
}
void SomeMethod(string argBar, int argBuzz){
//...
efCoreContext.FooTable.Where(f => new { bar = f.bar, buzz = f.buzz } == new { bar = argBar, buzz = argBuzz })
//...
}
The whole idea behind that is to check {f.bar, f.buzz} against a list of {argBar, argBuzz} with something like Where(f => args.Contains(new { bar = f.bar, buzz = f.buzz })) I can't find anything online and I'm quite stuck... (But I'm pretty sure I'm phrasing my google query wrong) A workaround our senior dev provided was to concatenate bar and fuzz into a string Although that works, the query time went absurdly high... If anyone could lead me into the correct direction, that would be highly appreciated šŸ™ More context with example and better exaplanation I've got a table with 2 cols Foo and Bar My database has those records
| Foo | Bar |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| Foo | Bar |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
code side, I have a list looking like
| Foo | Bar |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| Foo | Bar |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
I'd like to fetch every record from my database where a corresponding record is found in my list In this example, only
{
Foo : 1,
Bar : 1
}
{
Foo : 1,
Bar : 1
}
would be fetched And .Where(x => x.Foo == argFoo && x.Bar == argBar) would work just fine if I only one pair of argument to match the records from But here, I'd like something more akin to a list.Contains(dbRecord) Answer With LinQKit https://discord.com/channels/143867839282020352/1231977108239290418/1231985809209688188
18 Replies
tera
teraā€¢3mo ago
...
.Where(x => x.Foo == argFoo)
.Where(x => x.Bar == argBar)
...
.Where(x => x.Foo == argFoo)
.Where(x => x.Bar == argBar)
? šŸ˜„ or in a single
.Where(x => x.Foo == argFoo && x.Bar == argBar)
.Where(x => x.Foo == argFoo && x.Bar == argBar)
A workaround our senior dev provided was to concatenate bar and fuzz into a string Although that works, the query time went absurdly high...
šŸ˜¬ dont do that.. say goodbye to indexes and db-side filtering
Alta
Altaā€¢3mo ago
That works just fine! Until I try and match against records from a list where my db record should be equal to at least a record from my list That's where I'm hitting a wall yeah, although it works, that really sounds like a bad option, ngl
tera
teraā€¢3mo ago
i dont understand what you're doing can you show example?
Alta
Altaā€¢3mo ago
I'll give a bit more context I've got a table with 2 cols Foo and Bar My database has those records
| Foo | Bar |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| Foo | Bar |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
code side, I have a list looking like
| Foo | Bar |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| Foo | Bar |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
I'd like to fetch every record from my database where a corresponding record is found in my list In this example, only
{
Foo : 1,
Bar : 1
}
{
Foo : 1,
Bar : 1
}
would be fetched And .Where(x => x.Foo == argFoo && x.Bar == argBar) would work just fine if I only one pair of argument to match the records from But here, I'd like something more akin to a list.Contains(dbRecord)
tera
teraā€¢3mo ago
is this code side list expected to be small like few entries or?
Alta
Altaā€¢3mo ago
thousands of records in the database About 1 to 50ish list items depending on user input (most case should be 1 to 3 list item) those two fields are part of the primary key which is composed of 4 fields total I may just generate a raw sql query with a foreach on the list, lmao for something like
WHERE (Foo = @Foo AND Bar = @Bar)
OR (Foo = @Foo2 AND Bar = @Bar2)
OR -- ...
WHERE (Foo = @Foo AND Bar = @Bar)
OR (Foo = @Foo2 AND Bar = @Bar2)
OR -- ...
doesn't sound a lot better than the string concatenation our colleague told us to do šŸ¤” (well, it will at least keep the indexes. But that doesn't look like the cleanest nor safest)
tera
teraā€¢3mo ago
could just do equivalent of that in ef core then. i don't know the best approach tbh
Alta
Altaā€¢3mo ago
i don't know the best approach tbh
eh, you showed up. That alone helped me a lot, thanks šŸ™‚
tera
teraā€¢3mo ago
considering its a dynamic list for filtering, you'd need to build expressions or use something like linqkit i think
Alta
Altaā€¢3mo ago
linqkit ? I'll look it up
tera
teraā€¢3mo ago
PredicateBuilder and chain .Or expresions like this example
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.New<Product>();

foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.New<Product>();

foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
but again idk if this is best solution tbh
Alta
Altaā€¢3mo ago
oh, wow. Didn't know about that. That's cool! Well, I'll at least check it out šŸ™‚ That sounds like a good idea I'll let the thread open, check out the solution, post my answer and close it if someone ever has the same kind of problem
tera
teraā€¢3mo ago
šŸ‘
Alta
Altaā€¢3mo ago
Again, thanks a lot
tera
teraā€¢3mo ago
np!
Alta
Altaā€¢3mo ago
Works like a charm šŸ‘Œ
tera
teraā€¢3mo ago
nice
MODiX
MODiXā€¢3mo ago
tera
like this example
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.New<Product>();

foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.New<Product>();

foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
Quoted by
React with āŒ to remove this embed.