1

in my .net core service I have a list as follows:

List<string> list = new(){
    110234,
    231798,
    445978
}

The database table has the following columns: int1(2), int(3), int3(1). Now I want to query all records where the concat string maches. But with following example I get a LINQ/Ef-Core exception that this expression can't be translated:

 myentities = myentities
    .Where(e => list.Contains($"{e.int1.ToString("00")}{e.int2.ToString("000")}{e.int3.ToString("0")}"))
    .ToList();

Does anyone has an idea how I can handle that?

Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
SNO
  • 793
  • 1
  • 10
  • 30
  • 1
    That will result in an extremely slow SQL query that won't be able to use any indexes on those fields. It will have to scan the entire table. It will probably return bad results too - `23` will become `"023"` even though the field contains only `23`. Compare the fields normally instead: `Where(e=>e.Field1==11 && e.field2==2 && e.field3==34)`. If and ONLY if that concatenated string makes *business* sense, you could create a computed, persisted and indexed column with that value in the table. In that case the `Contains` clause will be very fast – Panagiotis Kanavos Nov 04 '21 at 14:13
  • For example, an air ticket number is the airline code and a document number. The printed ticket and reports use the combined ticket number though. Since these two are used a *lot*, it's very common to store them combined eg `123-123456787890` or `123123456789090` in a field that's indexed. – Panagiotis Kanavos Nov 04 '21 at 14:16
  • Querying the fields separately with ```Where(e=>e.Field1==11 && e.field2==2 && e.field3==34)```, how can I loop through my list with the or-clause? – SNO Nov 04 '21 at 14:29
  • 1
    Use [this solution](https://stackoverflow.com/questions/67666649/lambda-linq-with-contains-criteria-for-multiple-keywords/67666993#67666993). I hope it is straightforward how to apply filtering in your case. `query = query.FilterByItems(list, (e, x) => e.Field1 == x.Id1 && e.Field2 == x.Id2, true)` – Svyatoslav Danyliv Nov 04 '21 at 15:15

1 Answers1

0

The solution suggested from @Svyatoslav Danyliv solved my issue:

Use this solution. I hope it is straightforward how to apply filtering in your case. query = query.FilterByItems(list, (e, x) => e.Field1 == x.Id1 && e.Field2 == x.Id2, true)

SNO
  • 793
  • 1
  • 10
  • 30