Unable to cast object of type 'System.Data.Linq.DataQuery`1[System.Int32]' to type 'System.IConvertible'

Question

I'm trying to insert the data into my database which has 2 tables

Products
(ProductID): 1
(IDNumber) : 200900110
(ProductName) : Pepsi

Order
(OrderID): 1 (Auto Increment by 1)
(ProductID):1
(Date): 1/1/2009

The code is this:

var db = new ProductOrder();
var idNum = from p in db.Product
            where p.IDNumber == 200900110 
            select p.ProductID; 

var order = new Order();
            order.productID = Convert.ToInt32(idNum);
            order.Date = DateTime.Now;
db.Order.InsertOnSubmit(nTime);
db.SubmitChanges();

After I run it gives me the error like this:

Unable to cast object of type 'System.Data.Linq.DataQuery`1[System.Int32]' to type 'System.IConvertible'

Answer 1

Your query:

from p in db.Product
where p.IDNumber == 200900110
select p.ProductID

does not return a single result but a list of results. In your case this will be a list containing a single product id. You should modify it to this:

(from p in db.Product
 where p.IDNumber == 200900110
 select p.ProductID).Single()

If you run your code in the debugger and you hover your mouse over the idNum variable, you'll see that it is a DataQuery instance.