Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
5.00/5 (3 votes)
See more: , +
C#
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; }
}
Posted

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

C#
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
SQL
 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
 
Share this answer
 
v2
Comments
Maciej Los 18-Oct-14 16:29pm    
Looks promisingly, +5!
 
Share this answer
 
Comments
Maciej Los 18-Oct-14 16:26pm    
Very interesting link, King_Fisher vel Nels140812!
+5!
King Fisher 20-Oct-14 0:18am    
Thank you :)
You might give this[^] a try.
Cheers
Andi
 
Share this answer
 
v2
Comments
[no name] 18-Oct-14 7:04am    
Cool ;) 5. Regards, Bruno
Maciej Los 18-Oct-14 16:27pm    
Agree!
Andreas Gieriet 18-Oct-14 17:09pm    
Thanks for your 5!
Cheers
Andi
Andreas Gieriet 18-Oct-14 17:05pm    
Hello Bruno,
by accident, I copied the wrong link. Fixed now.
Thanks anyways for your 5!
Cheers
Andi
[no name] 19-Oct-14 6:56am    
Hello Andi
But please can you post the other link again as a comment, it was really cool.
I only bookmarked the answer and so I lost the cool link ;)
Thanks, regards, Bruno

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