Click here to Skip to main content
15,891,689 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi ....
this method return date from table 'tbl_Books'
i need to add where condition Depends on value of parameter
if parameter Category_ID > 0 add condition for column Category_ID
and if parameter PriceFrom > 0 add condition for column PriceFrom
and if parameter PriceTo > 0 add condition for column PriceTo

i use C# , VS 2013 , MVC5 , entity framework
My Code

BooksEntities db = new BooksEntities();
public ActionResult ShowBooks(int Category_ID = 0,decimal PriceFrom = 0,decimal PriceTo = 0)
{
return View(db.tbl_Books);
}


thank you
Posted
Comments
Krunal Rohit 2-Oct-15 0:24am    
What conditions ? Where would you add them ?

-KR

1 solution

Here are options to implement the same:

Option 1 : Using Dynamic LINQ Library
To implement the same you need to download Dynamic package library from Nuget. After downloading you need the methods defined in Dynamic Linq library. Here are some links that will help you:
http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library[^]

http://www.c-sharpcorner.com/UploadFile/deveshomar/dynamic-linq-library-in-C-Sharp/[^]

Option 2 : Using Stored Procedure
This is another option to implement the same. Create a stored procedure(SP) and pass 3 params to procedure. In SP, implement your own logic to fetch data like if Category_ID greater than 0. Then execute the dynamic SQL.

Option 3 : Using Raw SQL
This is last option if you are not interested in above options - that is raw SQL. You need to generate raw SQL query as per requirement. Then execute raw SQL query through SqlQuery() method. Here is the code:
C#
string query = "";
	
if(Category_ID > 0)
{
	query += "Category_ID = " + Category_ID;
}

if(PriceFrom  > 0)
{
	if(query.Length > 0){
		query += " AND PriceFrom = " + PriceFrom ;
	}else{
		query += "PriceFrom = " + PriceFrom;
	}		
}

if(PriceTo > 0)
{
	if(query.Length > 0){
		query += " AND PriceTo = " + PriceTo;
	}else{
		query += "PriceTo = " + PriceTo;
	}
}

if(query.Length > 0){
	query = "SELECT Name FROM tbl_Books WHERE " + query;
}else{
	query = "SELECT Name FROM tbl_Books";
}

using (var context = new BooksEntities()) 
{	
    var bookNames = context.Database.SqlQuery<bookentity>(query).ToList(); 
}

Here BookEntity is entity, you need to change as per your need. One disadvantage in this option is SQL vulnerability. Make sure that incoming data is correct so that it won't affect your Database.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900