Effects of IEnumerable and IQueryable on Relational Database and Non-Relational Database

Question

I have started to learn Linq recently, So I came across IQueryable and IEnumerable. I have understood the difference and where to use what. But I have a small doubt will IEnumerable and IQueryable have the same effect across all the databases let's say we have PostgreSQL and MongoDB will it have same effect on PostgreSql a relational DB and MongoDb a non-relational database? Could someone please help me with this? Any extra information is appreciated. Thanks in Advance.

Answer 1

The .NET drivers that allow you to use LINQ expressions against SQL and NO-SQL databases have their limitations.

For instance, in mongo's most popular driver for c# website (MongoDB C# Driver), we are recommended to use an analyser to tell us which LINQ expressions are valid or not:

However, and answering your other question, this is only valid for the IQueryable interface, because it is the only one among the two where your LINQ expressions will be translated into Mongo queries. For the IEnumerable interface, you're always safe, as the query will only take place in memory once the data is fetched from the database.

Remember that both IQueryable and IEnumerable support deferred execution. The difference is that with IEnumerable when you instanciate the list (.ToList) you'll be querying the database and ONLY THEN apply your filter (in memory). When you instanciate an IQueryable, it will 'apply' your filter in the database query itself (that's why it is faster and usually recommended when dealing with databases - you'll fetch potentially less data from the DB).

The differences between IQueryable and IEnumerable are very well described here: Returning IEnumerable vs. IQueryable