c# - Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Data.Entity.DbSet'

Question

I'm new in Linq and so I have these situation below.

Now below error during compilation, says Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Data.Entity.DbSet'.

var query = _db.Products;
if (bool) {
  query = query.Where(p => p.Id == id);
}

And so I tried to change var to IQueryable and it works.

IQueryable<Product> query = _db.Products;
if (bool) {
  query = query.Where(p => p.Id == id);
}

But then, I tried to change it again (see below) and it works.

var query = from product in products
            select product;
if (bool) {
  query = query.Where(p => p.Id == id);
}

And I just want to know why the other one works, but the other's not.

A good explanation with example might help. Thanks

Answer 1

The reason why the first scenario does not work is that the System.Linq.IQueryable is an interface which is implemented, among others, by the System.Data.Entity.DbSet class. In C#, if class C implements interface I, when it comes to transitions between types, you may as well treat I as C's base class (even the semantics class C : I suggest such an approach). And since you cannot implicitly (i.e. not verbosely) cast a class (or interface) to one of it's descendant classes, you get a compile-time error when trying to do it. You can do the opposite, that is implicitly cast a descendant class to it's base class (or interface). That's exactly what happens in the second scenario.

In your case, you could trick the compiler by casting explicitly:

query = (DbSet<Customer>) query.Where(p => p.Id == id);

but I would strongly advise you not to since you'll end up with a messy exception, because the result of query.Where(p => p.Id == id) is not in fact an instance of DbSet<Customer>, but rather some class representing the result of a query performed on a DbSet<Customer>, which implements the IQueryable interface.

So, to sum up, let's go through all the scenarios:

Scenario 1:

//query is of type DbSet<Customer>
var query = _db.Products; 
if (bool) {
  //here you're trying to assign a value of type IQueryable<Customer>
  //to a variable of it's descendant type DbSet<Customer>
  //hence the compile-time error
  query = query.Where(p => p.Id == id); 
}

Scenario 2:

//here you implicitly cast value of type DbSet<Customer>
//to IQueryable<Customer>, which is OK
IQueryable<Customer> query = _db.Products; 
if (bool) {
  //here you're assigning a value of type IQueryable<Customer>
  //to a variable of the same type, which is also OK
  query = query.Where(p => p.Id == id); 
}

Scenario 3:

//I assume you have the following line in your code
var products = _db.Products;
//query is of type IQueryable<Customer>, because you perform
//a query on the DbSet<Product>
var query = from product in products
            select product;
if (bool) {
  //here you're assigning a value of type IQueryable<Customer>
  //to a variable of the same type, which is OK
  query = query.Where(p => p.Id == id); 
}

EDIT

It's been a while since I answered this question, and even though the merit of it still stands, I tend to use a slightly different approach (which might have not been available at the time of original answer, I'm not sure).

The simplest (and I believe safest) way of casting an object implementing IQueryable<T> to IQueryable<T> is this:

var query = _db.Products.AsQueryable();

This simply returns the subject of the call to its IQueryable<T> interface implementation. It should not produce any overhead when executing the query. Now, there are comments suggesting to use some tricks, using which I believe might be a bad idea.

One example of such trick is to use this:

var queryable = query.Select(x => x);

While being (almost) completely benign when querying objects, it can do some harm when dealing with some implementations of IQueryable<T>. Namely, when the query is translated to, for example, a SQL query, it most likely will add a redundant "SELECT * FROM ..." to the executed query. That's the best case scenario - in the most probable scenario it will add something greatly more tedious - something like "SELECT x.P1, x.P2, ... FROM ... AS x". Of course, you might be OK with it, but you should be aware of it. Aware of the fact that, depending on implementation, calls like that might not be "free", even though appearing to do nothing.

Another example:

query.Where(x => true)

will potentially add a WHERE 1=1 to your SQL query.