Basic LinqToSql question: Why won't this compile?

I've been introducing myself to LinqToSQL lately through a poorly-made project at work. I'm curious as to why this works:

var territories = db.Territories.Where(t => t.PendingUserCount > 0);

But this results in a compilation error:

var territories = db.Territories;
if (someCondition)
    territories = territories.Where(t => t.PendingUserCount > 0);
// Cannot implicitly convert 'System.Linq.IQueryable<Territory> to System.Data.Linq.Table<Territory>

I've also tried to call db.Territories.ToList(), but to no avail.

I'm sure it's just a misunderstanding of how Linq works, but I'd be appreciative if someone could help me out.

From stackoverflow

• db.Territories returns a table object. Hence the 'var' will be of type System.Data.Linq.Table. Later you try (based on some condition) to assign something of type System.Linq.IQueryable to the variable. As .NET is strongly typed, the compiler throws an error.

  Variables of type var will be assigned a type when they get assigned first. That's how I try to remember myself.

  Matt Grande : Okay, I see what's happening there. How can I get around this? Ideally I'd like to be able to call `.Where` on `territories`, so what do I have to do to make db.Territories return an IQueryable?

  flatline : Why are you trying to assign a value to db.Territories? Do you want to post an update back to the table? If not, will need a separate variable to assign your linq expression to...

• Because you've typed "var" as a Table<Territory> and then try to reassign it as a IQueryable<Territory>.

  This is equivalent to saying

    var i = 0
  
    i = "a string";
  

  EDIT: To clarify, var is implicitly strong typed at compile time not run time, unlike dynamically typed scripting language.

• Your var territories is typed as a System.Data.Linq.Table<Territory> initially and then you are trying to assign the results of a Where clause (which is of type System.Linq.IQueryable<Territory>) to it.

  Remember that the compiler infers the type of a var at assignment so once it is assigned the type cannot be changed.

  Try something like this:

  System.Linq.IQueryable<Territory> territories;
  if (someCondition)
      territories = db.Territories.Where(t => t.PendingUserCount > 0);
  

• You need to use the IQueryable tyupe as others have suggested:

  also this linq query maybe also work:

  var query = from territories in db.Territories
              where territories.SomeProperty == SomeCondition
              where territories.PendingUserCount > 0
              select territories;
  

• For this type of cumulative Where, you need to tell the compiler to use IQueryable<T>:

  IQueryable<Territory> territories = db.Territories;
  if (someCondition)
      territories = territories.Where(t => t.PendingUserCount > 0);
  ... etc
  

  Andrew Hare : This won't work as "db.Territories" is the Table itself.

  Matt Grande : There's no problems assigning it like that. I just tried.

  Andrew Hare : My mistake, you are correct! Table implements IQueryable so (+1) to you. :)

• You can't re-assign a var to a different type after you've declared it. So your var declaration has already typed it to System.Data.Linq.Table.

  It's a fire once thing.

• One of the potentially confusing things about "var" is that its type is determined at compile time, so you can't assign a range of different types to it. People with experience of dynamic languages, like Python, sometimes get confused by this, at first.

  Matt Grande : Yep, I'm coming from Ruby, so I think that's where I got confused.

• change to this

  var territories = db.Territories;
  

  to

  IQueryable territories = db.Territories.Where(t => t.PendingUserCount > 0);
  

  The reasoning is that by calling db.Territories, you are getting all the data back, returning it in a linq.table object. Db.Territores.where(... will return an IQueryable object instead.

  leppie : But now you have to figure out the type, not good.

• Alternative:

  var territories = db.Territories.AsQueryable();
  if (someCondition)
      territories = territories.Where(t => t.PendingUserCount > 0);