Problems with LINQ to SQL

Question

 

I have started to use LINQ some time ago, and in general I think it is great. However I run into some unexpected behaviour and wondered if someone could explain it.

 

I pass a Table<T> from a LINQ to SQL DataContext to a function, losing its type, so I have to cast it to be able to query it. The following query, where data is the table cast to object works:

 

from order in (IQueryable<PurchaseOrder>)data

where order.Code == searchText

select order

 

However, if I change IQueryable into IEnumerable the query no longer produces results (an empty result set). This while, as far as I know, from ... in ... operates on IEnumerables.

 

Another problem I encountered is that an entity class A that has a one to many association with entity class B has a property of type EntitySet<B>. This type implements IEnumerable, which I can convert into an IQueryable by using the AsQueryable method. However if I then query this (as in the example below), I get no results when I should.

 

IQueryable<Order> orders = customer.Orders.AsQueryable();

var result=

from order in orders

where order.Amount>100

select order;

 

Anyone got a clue what I am missing?

Answer 1

Taco H. in den Bosch wrote:

I have started to use LINQ some time ago, and in general I think it is great. However I run into some unexpected behaviour and wondered if someone could explain it.   I pass a Table<T> from a LINQ to SQL DataContext to a function, losing its type, so I have to cast it to be able to query it. The following query, where data is the table cast to object works:   from order in (IQueryable<PurchaseOrder>)data where order.Code == searchText select order   However, if I change IQueryable into IEnumerable the query no longer produces results (an empty result set). This while, as far as I know, from ... in ... operates on IEnumerables.   Another problem I encountered is that an entity class A that has a one to many association with entity class B has a property of type EntitySet<B>. This type implements IEnumerable, which I can convert into an IQueryable by using the AsQueryable method. However if I then query this (as in the example below), I get no results when I should.   IQueryable<Order> orders = customer.Orders.AsQueryable(); var result= from order in orders where order.Amount>100 select order;   Anyone got a clue what I am missing?

 

I'm not sure why you lose your type when you pass a table to a method.  You can either type the method parameter as Table<PurchaseOrder> or IQueryable<PurchaseOrder>.  Either will work just fine.

 

Passing the value as an IEnumerable<T> should still produce results when enumerated, however, all query operations applied to the IEnumerable will be executed locally instead of in the database.  That's why you should pass the table as either Table<T> or IQueryable<T>.

 

L2S represents assocation sets using the EntitySet<T> class.  This is not a queryable proxy to a table like Table<T> is, it is an honest to goodness collection of instances.  Converting an EntitySet<T> to IQueryable<T> via AsQueryable() is not going to change that.  Your query, whether as IQuerayble<T> or EntitySet<T> will be processed locally against the items in the collection.  If you are not getting any results, then you may not have any objects in the collection or none that pass the filter you are applying in the query.

Answer 2

Matt,

 

I lose the type of the table because the method is called through a generic delegate. Since this delegate has to be able to refer to several query methods on various tables, the type of the parameter used to pass the table must be a common base class or interface. I have chosen IQueryable for this.

This, however, is not really the point of my first question. Any necessity of casting aside, I think it strange that performing a query on a table cast to IQueryable<T> will compile but not work at run-time.

 

Regarding my second question: I know that the entity set is an in-memory collection. And it does contain items that pass the filter, they just are not returned by the query for some reason.

Answer 3

I have just found out some more about the query. It turs out that queries on Table<T> instances are case insensitive, wherease queries on EntitySet<T> are case sensitive. This is probably because table queries are performed using SQL and entity set queries are performed in-memory by the LINQ runtime. Still this is rather unintuitive.

 

Taco.