C#•4mo ago

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•4mo 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•4mo 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•4mo ago

i dont understand what you're doing can you show example?

Alta•4mo 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
}

tera•4mo ago

is this code side list expected to be small like few entries or?

Alta•4mo 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•4mo ago

could just do equivalent of that in ef core then. i don't know the best approach tbh

Alta•4mo ago

i don't know the best approach tbh

eh, you showed up. That alone helped me a lot, thanks 🙂

tera•4mo ago

considering its a dynamic list for filtering, you'd need to build expressions or use something like linqkit i think

Alta•4mo ago

linqkit ? I'll look it up

tera•4mo 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•4mo 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•4mo ago

👍

Alta•4mo ago

Again, thanks a lot

tera•4mo ago

np!

Alta•4mo ago

Works like a charm 👌

tera•4mo ago

nice

MODiX•4mo 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

<@235149642445225984> from #EF Core Where on multiple fields (click here)

React with ❌ to remove this embed.

Gaming

Programming

EF Core Where on multiple fields