return product price using LINQ, what return type to use in function?

I'm just getting started with LINQ and I'm trying to select and return a product price from database, like so:

public int GetPricePerKg(Product prod)
{
    var result = from p in dc.Products
                 where p.pk_product_id == prod.pk_product_id
                 select p.product_price_kg;
    return result;
}

This gives me the error:

Cannot implicitly convert type 'System.Linq.IQueryable<int?>' to 'int'

What's the best way to handle this? I need the price (which is an int in this case) and do some calculations with it i开发者_高级运维n another place

Well, you've selected a query - that could in theory match 0, 1 or more records. What do you want to do in each situation? Also, it looks like product_price_kg is int? - what do you want to do if it's null?

You might want:

public int GetPricePerKg(Product prod)
{
    return dc.Products.Where(p => p.pk_product_id == prod.pk_product_id)
                      .Select(p => p.product_price_kg)
                      .Single()
                      .Value;
}

... but that will throw an exception if either there isn't exactly one matching product or the price property is null.

You can still use a query expression if you want - but I tend not to for simple cases where you then want to add a method call at the end. The query expression equivalent is:

return (from p in dc.Products
        where p.pk_product_id == prod.pk_product_id
        select p.product_price_kg).Single().Value;

Alternatively, you can use the version of Single() which takes a predicate, like this:

return dc.Products.Single(p => p.pk_product_id == prod.pk_product_id)
                  .product_price_kg.Value;

There are no doubt other variations - they'll all do the same thing - pick whichever one you find most readable.

EDIT: Other options instead of Single are:

• First (cope with 1 or more)
• FirstOrDefault (cope with 0, 1 or more)
• SingleOrDefault (cope with 0 or 1)

For the OrDefault versions you'd need to work out what to do if you didn't match any products.

you need to do (if you only want the first match back)

public int GetPricePerKg(Product prod)
    {
        var result = (from p in dc.Products
                     where p.pk_product_id == prod.pk_product_id
                     select p.product_price_kg).FirstOrDefault();
        return result;
    }

Both John Skeet and saj are right in their answers. Here's just another alternate syntax more in line with the code you posted:

public int GetPricePerKg(Product prod)
{
    var result = from p in dc.Products
                 where p.pk_product_id == prod.pk_product_id
                 select p.product_price_kg;
    return result.FirstOrDefault();
}

you have to use FristOrDefault() extension method

public int GetPricePerKg(Product prod)
{
    var result = (from p in dc.Products
                 where p.pk_product_id == prod.pk_product_id
                 select p.product_price_kg).FirstOrDefault();
    return result;
}