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