Return multiple results from SQL Server

Question

Currently I can input a product name into a textbox and search that name in the SQL Server database. And return ONLY a single unique row. This works fine. But when I search for e.g. Bike. I need Blue bike AND Red Bike to be returned.

I currently have:

public List<Product> GetProductByName(string name)
{
    List <Product> productList = new List<Product>();
    using (var context = _dbContextFactory.CreateDbContext())
    {
        productList.Add(context.Product.SingleOrDefault(x => x.ProductName == name));
        return productList;
    }
}

Currently I'm getting a System.InvalidOperationException: 'Sequence contains more than one element'.

Answer 1

Don't use SingleOrDefault if you're expecting multiple rows. Perhaps instead:

using (var context = _dbContextFactory.CreateDbContext())
{
    return context.Product.Where(x => x.ProductName == name).ToList();
}