I'm quite new to Linq,
I'm just trying to do a routine return which is usually done in SQL and cannot seem to get a straight answer from viewing many forums with hours of browsing.
Ok, I have a table Customers with two columns, CustomerID and CustomerName which belong to a CustomersDataBase
I have successfully been able to return single string from an integer query using the following method (in its basic form):
public string getCustomerName(int custID)
{
CustomerDataBase cdb = new CustomerDataBase();
string custName = (
from c in cdb.Customers
where c.CustomerID == custID
select c.CustomerName)
.SingleOrDefault();
return custName;
}
Ok that's fine but when I try to inverse the situation with trying to return the CustomerID it returns an Exception: System.InvalidCastException: Specified cast is not valid.
This is my code:
public int getCustomerID(string custName)
{
CustomerDataBase cdb = new CustomerDataBase();
int custID = (
from c in cdb.Customers
where c.CustomerName == custName
select c.CustomerID)
.SingleOrDefault();
return custID;
}
I've also tried thinking SQL terms to make the custName string a char array:
public int getCustomerID(string custName)
{
CustomerDataBase cdb = new CustomerDataBase();
int custID = (
from c in cdb.Customers
where c.CustomerName == "'"+custName+"'"
select c.CustomerID)
.SingleOrDefault();
return custID;
}
The exception disappears but only seem to be returning a "0" for the CustomerID even though I have hundreds of entries.
So in SQL I would just use the following:
Select CustomerID
from Customers
where CustomerName="'"+custName+"'"
Any help would be much appreciated! Thanks
//Edits to code to make it work (following answer from Jon Skeet):-
Ok This works:
[Table(Name = "TableCustomers")]
public class Customers
{
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public long CustomerID { get; set; }
[Column]
public string CustomerName { get; set; }
[Column]
}
//for accomadating for BigInt in SQL Database
public long getCustomerID(string custName)
{
CustomerDatabase cdb = new CustomerDatabase();
long custID = (from c in cdb.Customers
where c.CustomerName == custName
select c.CustomerID).SingleOrDefault();
return custID;
}
}
Best Answer
Your final approach is definitely wrong - you don't need to manually "quote" parameters; basically you're searching for a value with the quotes, and it's returning
0
fromSingleOrDefault
because it hasn't found the entry. (That's whatSingleOrDefault
does - it returns the default value for the type (int
, here) if there are no results.)I suspect that your
CustomerID
column is nullable, or something like that - but it's hard to tell without knowing more about your schema. You should check whether your LINQ to SQL model matches your database schema.You should also consider what you want to happen if there are multiple customers with the same name - currently this will throw an exception; is that what you want?
EDIT: Okay, now you've finally told us what the schema has, it's clear.
BigInt
is a 64-bit integer, but you're using a 32-bit property in the LINQ model. Simply changeCustomerID
from anint
tolong
in your LINQ model, change your method to return along
as well, and it should all be fine.