Query returns SQL expression and not value from database?

I have an integer co开发者_如何学Pythonlumn in my table in the database, and I want to select it but it give me exception that can't convert from int to string

Example I want to select CategoryID from Category table where CategoryName="Cat"

categoryID is int & categoryName is string

my linq query which I tried

string Category_ID = (from p in StaticVa.new_data_context.Categories
                              where p.CategoryName == Category_Name
                              select p.CategoryID).ToString();

It returned "SELECT [t0].[CategoryID]\r\nFROM [dbo].[Category] AS [t0]\r\nWHERE [t0].[CategoryName] = @p0\r\n"

try

int Category_ID = (from p in StaticVa.new_data_context.Categories
                              where p.CategoryName == Category_Name
                              select p.CategoryID).FirstOrDefault();

LINQ is trying to return a IQueryable of every id which matches the where clause, if you only want one you need to specify it as single or first

(from p in StaticVa.new_data_context.Categories
                              where p.CategoryName == Category_Name
                              select p.CategoryID)

represents an expression, that when executed, will return a set of items.

so you say:

var catIds = (from p in StaticVa.new_data_context.Categories
                              where p.CategoryName == Category_Name
                              select p.CategoryID);

then you get the for first one (ie. force the above expression to be execute and start returning values) by:

int? Category_ID = catIds.FirstOrDefault();

you need the int? in case there is no first row returned in which case FirstOrDefault() will return a null.

However if you're sure it return atleast one row then you can say

int Category_ID = catIds.First();

And if you're sure it return only one row then you can say

int Category_ID = catIds.Single();