如何在where子句中创建具有多个条件的动态查询？ [英] How to create dynamic query with multiple conditions in where clause?

问题描述

  public   object  SearchInfo（DateTime duedate， int  customerid， int  billtypeid， int  providerid，字符串 cityname，字符串 isaname，字符串 buildingname）
 {
 尝试 
 {
 
 context =  new  MGLEntities（）; 
  var  query = 来自 cb   context.tbl_CustomerBills 
  join  c  in  context.tbl_Customer on cb.CustomerID equals c。 CustomerID 
 其中  //  这里我想要根据搜索信息中的参数添加条件。假设，用户只输入duedate，条件应为cb.DueDate == duedate 
如果用户输入customerid和billtypeid，则条件应为  c.CustID == customerid&& cb.Billtypeid == billtypeid 
查询应该更改动态根据收到的参数。请如果任何人都知道解决方案然后建议我。谢谢 提前
 
 
 
 
 选择  new  
 {
 c.CName，
 cb.DueDate，
 cb.CustomerID 
} ; 
 
  return 查询; 
} 
  catch （例外）{ return  空; } 
} 

解决方案

你可以通过两个步骤完成任务



1.将默认值传递给未使用的过滤器参数

2.在SQL查询中，您可以轻松地进行OR跳过特定过滤器的条件



场景：



表单UI（用户界面），您搜索到期日期然后函数参数必须是



函数参数

_CustomerID = 0; _BillTypeID = 0; _DueDate ='2014-01-01'



表单UI（用户界面），您按CustomerID和帐单类型ID搜索，然后功能参数必须是



函数参数

_CustomerID = 5; _BillTypeID = 115; _DueDate =''



Linq代码将是

 EmployeeDataContext objDB =  new  EmployeeDataContext（）; 
  int  CustomerID =  0 ; 
  int  BillID =  0 ; 
  string  DueDate =  ; 
  var  res = 来自项  objDB.tblBills 其中（（item.BillID == BillID || BillID ==  0 ）& ;&（item.CustomerID == CustomerID || CustomerID ==  0 ）&&（DueDate ==    || Convert.ToDateTime（DueDate）== item.DueDate））选择项; 
 

查询将是

 从 tblBill  *  -keyword> where  
（CustomerID = _CustomerID 或 _CustomerID =  0 ） 和 
（BillTypeID = _BillTypeID 或 _BillTypeID =  0 ）和 
（_DueDate = ' ' 或 DueDate = 转换（ date ，_ DueDate））

谢谢，

Siva Rm K

参考：



https://www.simple-talk.com/dotnet/.net-framework/dynamic-linq-queries-with-expression-trees/ [ ^ ]

你可以给这个 [< a href =http://www.codeproject.com/Articles/355513/Invent-your-own-Dynamic-LINQ-parsertarget =_ blanktitle =New Window> ^ ]试一试。

干杯

Andi

public object SearchInfo(DateTime duedate, int customerid, int billtypeid, int providerid, string cityname, string areaname, string buildingname)
{
try
{

context = new MGLEntities();
var query = from cb  in context.tbl_CustomerBills
join c in context.tbl_Customer on cb.CustomerID equals c.CustomerID
where // here I want to add condition according to parameters in search info.Suppose, only duedate is entered by user then condition should be "cb.DueDate == duedate"
If user enters customerid and billtypeid then condition should be "c.CustID == customerid && cb.Billtypeid == billtypeid"
query should change dynamically according to received parameters.Please if anyone knows the solution then suggest me. Thanks in advance




select new
{
c.CName,
cb.DueDate,
cb.CustomerID
};

return query;
}
catch (Exception) { return null; }
}

解决方案

Hi,

You can Achieve the task by doing two steps

1. Pass Default Value to unused Filter Parameter
2. In SQL query you can easily make OR Condition to skip particular filter

Scenario :

Form UI(User Interface) , you searched by Due Date then Function Parameter Must be

Function Parameters
_CustomerID = 0 ; _BillTypeID =0 ; _DueDate='2014-01-01'

Form UI(User Interface) , you searched by CustomerID and Bill Type ID then Function Parameter Must be

Function Parameters
_CustomerID = 5 ; _BillTypeID =115 ; _DueDate=''

Linq Code Will Be

EmployeeDataContext objDB = new EmployeeDataContext();
   int CustomerID = 0;
   int BillID = 0;
   string DueDate = "";
   var res = from item in objDB.tblBills where((item.BillID== BillID || BillID == 0) && (item.CustomerID==CustomerID || CustomerID ==0) && (DueDate=="" || Convert.ToDateTime(DueDate)==item.DueDate)) select item;

Query will be

 Select * from tblBill where 
(CustomerID = _CustomerID or _CustomerID = 0) and 
(BillTypeID = _BillTypeID or _BillTypeID = 0 )and
(_DueDate ='' or DueDate= Convert(date,_DueDate) )

Thank You,
Siva Rm K

Refer this :

https://www.simple-talk.com/dotnet/.net-framework/dynamic-linq-queries-with-expression-trees/[^]

You might give this[^] a try.
Cheers
Andi

这篇关于如何在where子句中创建具有多个条件的动态查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！