How many times query will be sent and executed in a database with non-materialized parameters?

Question

I am sorry if this question is silly, but I am eager to know the answer.

We are using linq2db. I believe that this IQueryable will be executed one time in the database. But I am not sure whether it is true because of Resharper warning which says Possible Multiple enumeration.

My linq2db query looks like this:

IEnumerable ids = Enumerable.Range(1, 10)
    .Select(x => {Console.WriteLine(x); return x;});

IQueryable<PersonDto> query =
    from person in dbContext.GetTable<DbPerson>()           
    where ids.Contains(person.Id)  // here warning `Possible Multiple enumeration`
        || ids.Contains(person.DepartmentId) // here warning `Possible Multiple
                                             //  enumeration`
    select
        new PersonDto
        {
            Name = person.Name,
            Id = person.Id,
            DepartmentId = person.DepartmentId
        }).Distinct();

await query.ToListAsync()
            

I know that it can be easily fixed by calling .ToList(). But I just want to know whether it is true that query will be sent to database 20 times? (because of Enumerable.Range(1, 10) and ids is used twice in where condition)

Answer 1

First, Linq2DB isn't related to EF Core, it's an entirely different ORM.

Second, There won't be multiple executions. The Resharper warning doesn't refer to multiple SQL query executions at all, it refers to multiple iterations of the ids IEnumerable and is correct.

Just use this instead:

var ids = Enumerable.Range(1, 10).ToArray();

or

var ids = Enumerable.Range(1, 10).ToList();

non-materialized parameters isn't an EF, Linq2DB or SQL concept. EF Core or Linq2DB providers convert the IEnumerable<T>.Contains(someProp) clause to the SQL condition prop in (@value1, @value2, ....). When that happens the provider has to iterate over all the values. While that costs nothing with a list or array, it forces re-evaluation of any enumerables.

You gain nothing by using an IEnumerable<T>, much less a typeless IEnumerable. You may even cause extra boxing as the generated integers are boxed into objects.

In EF you can get the generated SQL if you use the ToQueryString, eg:

var sql=query.ToQueryString();

In Linq2DB I think you can just use ToString() but I haven't found a reference yet. So try this to see what you get:

var sql=query.ToString();