Linq returning single integer value based on string stored in SQL

Question

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;
    }
}

The last query will return zero records, the OrDefault() part will return int 0. That's why you don't get a cast exception. What is the type of CustomerId in your code? — Wouter de Kort
[Column(IsPrimaryKey = true, IsDbGenerated = true)] public int CustomerID { get; set; } Customer ID is non-Nullable and BigInt in the SQL Database as well as Primary Key — amundell
Format I am using: [Table(Name = "TableCustomers")] public class Customers { [Column(IsPrimaryKey = true, IsDbGenerated = true)] public int CustomerID { get; set; } [Column] public string CustomerName { get; set; }} — amundell
Code doesn't work well in comments. You're allowed to edit your question to add more information. — Damien_The_Unbeliever

Jon Skeet Jon Skeet · Accepted Answer · 2012-01-20T10:47:49

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 from SingleOrDefault because it hasn't found the entry. (That's what SingleOrDefault 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 change CustomerID from an int to long in your LINQ model, change your method to return a long as well, and it should all be fine.

Linq returning single integer value based on string stored in SQL

1 Answers