0

Input data comes with a list of multiple (5) properties and I would like to fetch records from the DB where one row matches any row from the input list like so:

class MyClassDto
{
   public string Value1 { get; set; }
   public string Value2 { get; set; }
   public string Value3 { get; set; }
   public int Value4 { get; set; }
   public int Value5 { get; set; }
}

DB entity also has those 5 values.

public IEnumerable<MyClassDb> GetData(IEnumerable<MyClassDto> data)
{
    _context.MyClassDb.Where(x => data.Contains(x.Value1) && data.Contains(x.Value2) && ...
}

Would return data that contains any combination from the input data and not exactly a row that matches one input row 1 for 1.

I tried lookging at the examples given in Query where multiple columns have to match a value set simultaneously in EF Core but I'm unsure how to produce expressions while matching 5 values.

Also, whenever I try to build expressions something like in this example, however it always produces a stackoverflow exception.

GeorgeR
  • 149
  • 9
  • Your query won't work because `data.Contains` is not translatable to SQL (and reference-equality is meaningless in this context anyway). Linq-to-Objects is totally different to Linq-to-Entities: https://stackoverflow.com/questions/7192040/linq-to-entities-vs-linq-to-objects-are-they-the-same – Dai Mar 30 '23 at 07:25
  • What is `MyClassDto` and `MyClassDb`? – Dai Mar 30 '23 at 07:27
  • MyClassDb is an entity in the database, and dto is its dto object. Both have same properties. – GeorgeR Mar 30 '23 at 07:29
  • Option 1: For each `MyClassDto` in `data`, issue a single query to your `DbContext.MyClassDb` (so for 5 objects in `data` you'll issue 5 queries). Option 2: Issue a single query that uses a runtime-built `OR` predicate - but you'll need to bring-in the LinqKit library to do this as Linq doesn't have in-box support for building `OR` predicates, only `AND`. – Dai Mar 30 '23 at 07:32
  • thank you, could you provide an example? – GeorgeR Mar 30 '23 at 07:34
  • What are you trying to do and what is the problem? EF is an ORM, not a database driver. It retrieves and allows you to work with objects instead of tables. Querying works by translating LINQ queries to SQL. `someList.Contains(x.Property1)` gets translated to `WHERE Property1 IN (@list1, @list2, @list3,...)` with all the values of the list passed to the `IN` clause – Panagiotis Kanavos Mar 30 '23 at 07:35
  • `IN` works with simple database values though, eg integers, strings, dates. It doesn't work with types, arrays or tables. Many databases don't even have complex parameter types. Since `data` is a `IEnumerable` even a database that does have complex types wouldn't know what to do with `Where Property1 in (@myCustomType1, @myCustomType2). What would it compare `Property1` with? – Panagiotis Kanavos Mar 30 '23 at 07:40

1 Answers1

1

Option 1: N-separate queries:

  • Pro: simple, works
  • Cons: wasteful
public async Task< List<MyClassDb> > GetDataAsync( IEnumerable<MyClassDto> data, CancellationToken cancellationToken = default )
{
    if( data is null ) throw new ArgumentNullException(nameof(data));

    List<MyClassDb> list = new List<MyClassDb>();

    foreach( MyClassDto dto in data )
    {
        MyClassDb? single = await this.dbContext.MyClassDb
            .Where( m =>
                m.Value1 == dto.Value1 &&
                m.Value2 == dto.Value2 &&
                m.Value3 == dto.Value3 &&
                m.Value4 == dto.Value4 &&
                m.Value5 == dto.Value5
            )
            .SingleOrDefaultAsync( cancellationToken )
            .ConfigureAwait(false);

        if( single != null ) list.Add( single );
    }
    
    return list;
}

Option 2: Build OR query at runtime

public async Task< List<MyClassDb> > GetDataAsync( IEnumerable<MyClassDto> data, CancellationToken cancellationToken = default )
{
    if( data is null ) throw new ArgumentNullException(nameof(data));

    var filter = PredicateBuilder.False<MyClassDb>();

    foreach( MyClassDto dto in data )
    {
        filter = filter.Or( m =>
            m.Value1 == dto.Value1 &&
            m.Value2 == dto.Value2 &&
            m.Value3 == dto.Value3 &&
            m.Value4 == dto.Value4 &&
            m.Value5 == dto.Value5
        );
    }

    return await this.dbContext.MyClassDb
        .Where( filter )
        .ToListAsync( cancellationToken )
        .ConfigureAwait(false);
}
Dai
  • 141,631
  • 28
  • 261
  • 374
  • Thank you for the answer, I still get stackoverflow exception for some reason. Even though 1st solution is quite slow, I guess it still is the only way to go. – GeorgeR Mar 30 '23 at 08:17
  • @GeorgeR We can't really help you with a `StackoverflowException` until you share the stack-trace and other pertinant details. – Dai Mar 30 '23 at 08:18
  • Something wrong here `IQueryable q = `, I'm pretty sure `PredicateBuilder` do not return IQueryable. Better use `var`. – Svyatoslav Danyliv Mar 30 '23 at 08:24
  • My bad. SO exception happens because I have incoming 5 thousand entities, and oracle (my provider) only accepts 999 maximum values in its queries. @SvyatoslavDanyliv you are right, minor change. Still the answer is correct, thank you for contributing. – GeorgeR Mar 30 '23 at 08:27
  • 1
    If you have thousand records, this is not solution. You need extension which supports temporary tables. – Svyatoslav Danyliv Mar 30 '23 at 08:38
  • @SvyatoslavDanyliv You're right - I checked and it's `Expression>` – Dai Mar 30 '23 at 08:48