Problems with converting List to IQueryable in Linq

Question

EDIT

Actually this question should be more general: How to modify query to DB if Linq with IQueryable gives errors?

The correct answer is as far as I understand — to get as much of the query done at the database level. Because in this particular case my complicate query just can not be transform from Linq to sql.

So I just wrote a raw sql query with FromSqlRaw() method and errors have gone. Moreover I wrote query in the way that does not take all entries (with filtering) as opposed to ToList() method, so I have less doubt about performance (though I did not measure it).

---

Need some help with understanding how to use linq with converting List to IQueryable.

What I had: A three tables in DB with IQueryable-based queries to one of them.

What I need: To create a query that combine data from three tables by Linq and give me resulting specific column with data for every element of one of table with function of filtering by this column.

What I try: Supplement IQueryable-based query. But I found problems with List to IQueryable converting. Method AsQueryable() gives errors.

What I achieve: I rewrite queries with List-based logic in Linq and it gives me what I need. But I do not understand:

• Is this practice good?
• Why should I often must make ToList() conversion for avoiding errors?
• Is the speed of my solution worse than IQueryable-based approach?

Here is fiddle with my exercises: https://dotnetfiddle.net/BAKi6r

What I need I get in listF var.

I totally replace CreateAsync method in it with Create method for List. Is it good? I also try to use hardcoded Lists with CreateAsync method /items2moq, items3moq/, but they with filtered List-based query give The provider for the source IQueryable doesn't implement IAsyncQueryProvider error. Also I got Argument types do not match error when I use IQueryable for NamesIQ instead of List for NamesList. What exactly the source of this errors?

Answer 1

Why should I often must make ToList() conversion for avoiding errors?

I often think about Linq queries in three "levels":

1. IQueryable - there are designed to translate a Linq query into an equivalent database (or whatever data source you're using) query. Many Linq and non-Linq operations just can't be translated into its SQL or other equivalent, so this layer would throw an error. Even operations that seem simple (like splitting a string) are difficult if not impossible to do in SQL

2. IEnumerable - in this layer, Linq queries are done in memory, so there's much more flexibility to do custom operations. To get from the IQueryable layer to the IEnumerable layer, the AsEnumerable() call is the most straightforward. That separates the part of the query that gets raw data from the part that can create custom objects, do more complex filtering and aggregations, etc. Note that IEnumerable still uses "deferred execution", meaning that at this stage, the query is just a query - the results don;t actually get computed until you enumerate it, either with a foreach loop or by advancing to the next layer:

3. List/Array/etc. This is where queries are executed and turned into concrete collections. Some of the benefits of this layer are serializability (you can't "serialize" an enumerator) and eager-loading (as opposed to deferred execution described above).

So you're probably getting an error because you have some part of your query that can't be translated by the underlying Queryable provider, and using ToList is a convenient way to materialize the raw data into a list, which allows you to do more complex operations. Note that AsEnumerable() would do the same thing but would maintain deferred execution.

Is this practice good?

It can be, but you might easily be getting more data than you need by doing filtering at the list level rather than at the database level. My general practice is to get as much of the query done at the database level, and only moving to the enumerable/list level when there's no known way to translate the rest of the query to SQL.

Is the speed of my solution worse than IQueryable-based approach?

The only way to know is to try it both ways and measure the difference. But it's a pretty safe bet that if you get more raw data than you need and filter in memory that you'll have worse performance.