CODEWITHSUNDEEP
c#asp.net-coreentity-framework-corelinq2db
Learner
Learner

Reputation: 571

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

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)

Upvotes: 0

Views: 71

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

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();

Upvotes: 2

MakePeaceGreatAgain
MakePeaceGreatAgain

Reputation: 37000

In general when R# informs you about that warning, that means that you're iterating the same collection of items multple times. This might be okay sometimes, e.g. when your collection already is materialized into an array or a List<T>. However when your collection is an IQueryable<T>, you're performing the query again whenever you call IEnumerator.MoveNext() - which is done implictely e.g. by calling First, or foreach on said queryable.

In your case the warning comes from IEnumerable ids = Enumerable.Range(1, 10) .Select(x => {Console.WriteLine(x); return x;});, which isn't a materialized collection. So best would be to call ToArray or ToList on it and the warning disappears.

On the other side as that collection is pretty small and doesn't depend on any external (potentially heavy) dependencies, you can ignore the warning.

Btw.: I hope your ids-variable is just for testing purposes. You should generally avoid linq-expressions that have side-effects like Console.WriteLine. While it works, it somehow contradicts the purpose of LINQ which is about querying, not about modifiying. Better use a classig loop in these cases.

Upvotes: 1

Related Questions