0

Right now I am doing this in Entity Framework to filter results:

public ActionResult Index(string productName, string productDesc, string productQty)
{
    var products = from p in db.Products select p;

    if (!String.IsNullOrEmpty(productName))
    {
          products = products.Where(p=>p.ProductName.Contains(productName));
    }

    // similarly for other parameters and returning to view as:
    return View(products);
}

I want to write a stored procedure to filter the products in database itself, before fetching it to the application.

The stored procedure is:

CREATE PROCEDURE [dbo].[usp_FilterProducts]
    @ProductName varchar = null,
    @ProductDesc varchar  = null,
    @Quantity int = null
AS
BEGIN
    SET NOCOUNT ON

    SELECT *
    FROM [Products]
    WHERE (@ProductName IS NULL OR [ProductName] LIKE @ProductName)
      AND (@ProductDesc IS NULL OR [ProductDesc] LIKE @ProductDesc)
      AND (@Quantity IS NULL OR [Quantity] = @Quantity)
END

I have a corresponding model class as Product.

Is this procedure correct? If not, how should it be?

And, how do I call it from controller?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
b.g
  • 411
  • 1
  • 3
  • 19
  • This is too broad. What's your *specific* question? – Gert Arnold Oct 21 '16 at 09:06
  • Specific question : How do I execute this procedure in Entity Framework, retrieve filtered results if parameters passed to controller are not null, (else retrieve all rows) and then assign to this `product` model and pass it to the view? – b.g Oct 21 '16 at 09:10

2 Answers2

0

Just call the DbSet's SqlQuery method: https://msdn.microsoft.com/en-us/library/gg696460(v=vs.113).aspx. In this case, do something like this:

var products = from p in db.Products.SqlQuery("exec usp_FilterProducts @productName = {0}, @productDesc = {1}, @quantity = {2}", productName, productDesc, productQty).ToList();
Ricardo Peres
  • 13,724
  • 5
  • 57
  • 74
0

Looks fine, except for the fact that .NET .Contains() is not entirely equal to SQL LIKE.
This is what it should be to achieve the same:

SELECT * FROM [Products]
WHERE ( @ProductName is null or [ProductName] LIKE '%' + @ProductName + '%')
AND   ( @ProductDesc is null or [ProductDesc] LIKE '%' + @ProductDesc + '%')
AND   ...

Then again, by applying one or more .Where clauses in LINQ, you are essentially building a dynamic SQL query which is only generated and then executed when the collection is iterated - in this case inside the View as it starts iterating over the IQueryable object, and retrieving only filtered results.

Peter B
  • 22,460
  • 5
  • 32
  • 69
  • now i tried `var products = from p in db.Products.SqlQuery<>("exec usp_FilterProducts @productName = {0}, @productDesc = {1}, @quantity = {2}", productName, productDesc, productQty);` to call it but the syntax is incorrec – b.g Oct 21 '16 at 09:24
  • This is *not* the same as I said! You shouldn't have <>, please stick to what I wrote. – Ricardo Peres Oct 21 '16 at 09:28
  • @b.g "have a stored procedure already" -> then you should change that sp, otherwise it will not act the same as when using .NET Contains() (which is the essence of my answer) – Peter B Oct 21 '16 at 09:34
  • i changed the procedure and now the page displays none of the items if the filter fields are not set – b.g Oct 21 '16 at 09:55
  • Did you add the `%` signs? They are essential, without them `LIKE` behaves as `=` (so only equal strings are found). – Peter B Oct 21 '16 at 10:29
  • Perhaps not a fix, but several things remain: [1] in SP definition you must specify a length, use e.g. `varchar(80)` otherwise it will use `varchar(1)`. [2] consider using `nvarchar(x)` instead of `varchar(x)`, to support special characters. [3] the C# version will skip a parameter if it is empty string, but the SP does not, it only skips condition if value is NULL, but it will never be NULL if you are calling it using the `@productName = '{0}'` approach. – Peter B Oct 21 '16 at 12:11