0

Hi I am developing MVC4 application. I am developing one search page which contains 5 textboxes and search button in which only one textbox is mandatory field. Remaining are not mandatory. I want to have AND of all 5 textbox values. My query is as below.

var logDetails = (from c in db.ts_upldlog_content
join tbl in db.ts_upld_doc on c.upld_docid equals tbl.upld_docid
join doc in db.tm_doc_type on tbl.upld_doctypeid equals doc.doc_typeid
where 
(tbl.upld_clientid == clientId && tbl.upld_employeeid == employeeID && tbl.upld_empcitizenid == citizenId && tbl.upld_doctypeid == typeofDocument && EntityFunctions.TruncateTime(c.updatedOn) >= start && EntityFunctions.TruncateTime(c.updatedOn) < end)
    select new logdetails
    {
    //Getting all properties
    }).toList();

My problem is In the above query start and end are mandatory so i will get some value from UI. Let me explain one scenario. User will supply start and end and remaining will be null. My query will not yield results because I am doing && operation and my other fields may have some value in DB. Lets conside below table.

clientID    EmployeeID   EmpCitiID  docType      Date
123         456          456        1            10/19/2016

When i Pass only Date my query will not work because I am doing && operation. so Is there any way to achieve this scenario? Any help would be appreciated. Thank you.

Niranjan Godbole
  • 2,135
  • 7
  • 43
  • 90
  • 1
    Look at the most upvoted answer to the duplicate. – Gert Arnold Oct 19 '16 at 10:13
  • If I understand correctly, some of the arguments (like `clientId`, `employeeID`, `citizenId` etc.) are optional. Please show the variables/method arguments declaration. – Ivan Stoev Oct 19 '16 at 10:14
  • @GertArnold And this is even better :) – Ivan Stoev Oct 19 '16 at 10:16
  • @GertArnold This query looks efficient , could make use of compiled query and cached query for better performance – Arun Prasad E S Oct 19 '16 at 10:18
  • Thank you for your response. Yes clientId, employeeID, citizenId are optional. I can do it by using If else. Is it good approach to do it? – Niranjan Godbole Oct 19 '16 at 10:20
  • It's a very common way to build a query with predicates. There are many many similar answers at StackOverflow. – Gert Arnold Oct 19 '16 at 10:22
  • where EntityFunctions.TruncateTime(c.updatedOn) >= start && (tbl.upld_clientid == clientId || tbl.upld_employeeid == employeeID || tbl.upld_empcitizenid == citizenId || tbl.upld_doctypeid == typeofDocument) I am not sure about this. What it will return? will it work? – Niranjan Godbole Oct 19 '16 at 10:24
  • Your question was about &&. This is an entirely different question. Look [here](http://stackoverflow.com/a/14622200/861716). – Gert Arnold Oct 19 '16 at 10:30

1 Answers1

1
tbl.upld_clientid == clientId && tbl.upld_employeeid == employeeID && tbl.upld_empcitizenid == citizenId && tbl.upld_doctypeid == typeofDocument && 

(EntityFunctions.TruncateTime(c.updatedOn) >= start && EntityFunctions.TruncateTime(c.updatedOn) < end )  )

missing parenthesis

Arun Prasad E S
  • 9,489
  • 8
  • 74
  • 87