I am running a query and there are multiple parameters that can be used to filter the records returned. The parameters are coming from dropdown menus, so if the user doesn't choose an option, the parameter comes over in an empty string.
How can I ignore the parameters that are a ""?
Here is the query: (There might be a better way to do this in LINQ, but my SQL skills are stronger). I am building this report in MVC 5/C#.
approvalReport = (from td in db.TData
from call in
(from x in db.CallLog
where x.TID == td.TID
orderby x.CallDate descending
select new
{
x.CallStatus,
x.CallNotes,
x.CallDate
}).Take(1).DefaultIfEmpty()
from appt in
(from x in db.Appt
where x.TID == td.TID
orderby x.ApptCreateDate descending
select new
{
x.ApptStatus,
x.ApptDate
}).Take(1).DefaultIfEmpty()
where (td.Location == location && td.ApprovedBy == approvedBy && td.PCP == pcp && td.IPA == ipa)
orderby td.MemberName descending
select new ApprovalDataVM()
{
MemberName = td.MemberName,
PCP = td.PCP,
IPA = td.IPA,
Location = td.Location,
ApprovedBy = td.ApprovedBy,
CallStatus = call.CallStatus,
ApptDate = appt.ApptDate,
ApptStatus = appt.ApptStatus,
callLogCt = db.CallLog.Where(x => x.TID == td.TID).Count(),
});
I have done some research on this and tried this for the where clause, but it didn't work. The report didn't pull any data. The example below is abbreviated to 3 parameters, and "approvedBy" will never be null or "".
where (td.ApprovedBy == approvedBy && (pcp =! "' && td.PCP == pcp) && (ipa != "" && td.IPA == ipa)
I don't want to have to repeat myself for all the parameter combinations that are possible. What is the best solution for this?
The CallLog and Appt tables have a foriegn key of the td.TID. The output will be a list displaying all of the data in the view model.
View:
<div class="table-responsive">
<table class="table table-hover small">
<tr>
<th></th>
<th>Approved By</th>
<th>Member Name</th>
<th>DOB</th>
<th>PCP</th>
<th>IPA</th>
<th>Call Status</th>
<th>Call Count</th>
<th>Appt Date</th>
<th>DOS</th>
</tr>
@{ int counter = 0; }
@foreach (var item in Model.reportData)
{
counter = counter + 1;
<tr>
<td>
@counter
</td>
<td>
@Html.DisplayFor(modelItem => item.ApprovedBy)
</td>
<td>
@Html.DisplayFor(modelItem => item.MemberName)
</td>
<td>
@if (item.DateOfBirth != null)
{
@item.DateOfBirth.Value.ToShortDateString()
}
</td>
<td>
@Html.DisplayFor(modelItem => item.PCP)
</td>
<td>
@Html.DisplayFor(modelItem => item.IPA)
</td>
<td>@Html.DisplayFor(modelItem => item.CallStatus)</td>
<td>@Html.DisplayFor(modelItem => item.callLogCt)</td>
<td>@Html.DisplayFor(modelItem => item.ApptStatus)</td>
<td>
@if (item.ApptDate != null)
{
@item.ApptDate.Value.ToShortDateString()
}
</td>
</tr>
}
</table>
Form that feeds the search query:
<form class="form-inline" role="form">
<div class="form-group" style="padding-right:15px;">
<label class="control-label">*Approved By: </label>
@Html.DropDownList("approvedBy", Model.ApprovedByList, "Select One", new { @class = "input-sm" })
@Html.ValidationMessageFor(model => model.ApprovedByList, "", new { @class = "text-danger" })
</div>
<div class="form-group" style="padding-right:15px;">
<label class="control-label">*Location: </label>
@Html.DropDownList("location", (IEnumerable<SelectListItem>)ViewBag.location, "Select One", new { @class = "input-sm" })
</div>
<input type="submit" value="Run Report" class="btn-primary btn-sm" />
<hr />
<div class="form-group" style="padding-right:15px;">
<label class="control-label">PCP: </label>
@Html.DropDownList("pcp", Model.PCPList, "All", new { @class = "input-sm" })
</div>
<div class="form-group" style="padding-right:15px;">
<label class="control-label">IPA: </label>
@Html.DropDownList("ipa", Model.IPAList, "All", new { @class = "input-sm" })
</div>
<div class="form-group" style="padding-right:15px;">
<label class="control-label">Call Status: </label>
@Html.DropDownList("callStatus", Model.CallStatusList, "All", new { @class = "input-sm" })
</div>
<br /><br />
</form>
(pcp == "' || td.PCP == pcp) && (ipa == "" || td.IPA == ipa)or do this directly at root leveldb.TData.Where(td=>(pcp == "' || td.PCP == pcp) && (ipa == "" || td.IPA == ipa))