如何在模型中添加多个条件取决于参数的值？ [英] How to add multiple Condition to model Depends on value of parameter ?

问题描述

嗨....

此方法从表'tbl_Books'返回日期

i需要添加where where condition取决于参数值

如果参数Category_ID> 0为列Category_ID添加条件

和if参数PriceFrom> 0添加条件PriceFrom

和参数PriceTo> 0为列添加条件PriceTo



i使用C＃，VS 2013，MVC5，实体框架

我的代码



BooksEntities db = new BooksEntities（）;

public ActionResult ShowBooks（int Category_ID = 0，decimal PriceFrom = 0，decimal PriceTo = 0）

{

返回查看（db.tbl_Books）;

}





谢谢

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

推荐答案

以下是实现相同的选项：



选项1 ：使用动态LINQ库

要实现相同的功能，您需要从Nuget下载动态包库。下载后，您需要Dynamic Linq库中定义的方法。以下是一些可以帮助您的链接：

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/ [ ^ ]



选项2 ：使用存储过程

这是实现相同的另一种选择。创建一个存储过程（SP）并将3个参数传递给过程。在SP中，实现自己的逻辑来获取数据，就像Category_ID大于0一样。然后执行动态SQL。



选项3 ：使用原始SQL

如果您对上述选项不感兴趣，那么这是最后一个选项 - 即原始SQL。您需要根据需要生成原始SQL查询。然后通过SqlQuery（）方法执行原始SQL查询。以下是代码：

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:

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

这里 BookEntity 是实体，您需要根据需要进行更改。此选项的一个缺点是SQL漏洞。确保传入的数据正确，以免影响您的数据库。

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.

这篇关于如何在模型中添加多个条件取决于参数的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！