Databases
StackOverflow2013
Postsdbo
POIssue with LINQ IN clause when filter value is empty but works with filter values
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIssue with LINQ IN clause when filter value is empty but works with filter values
    text
    copied!<p>I have a filter called serviceEntryFilter with a property System which could have values for instance EP1, EP2 OR EP1 and sometimes this filter would be null. If there are multiple values or a single value then the query (IN) clause runs fine . If the filter value is null then I get the following error:</p> <p><strong>Unable to create a constant value of type 'System.String[]'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.</strong></p> <pre><code>[HttpPost] public ActionResult List(string ServiceEntryStatus, string ServiceEntryReconciled, string ServiceEntryReliabilityRecord, string ActiveServiceEntry, int PageNo, ServiceEntryFilter serviceEntryFilter = null) { string[] systems = null; var list = (from se in db.ServiceEntry join r in db.RunLogEntry on se.RunLogEntryID equals r.ID into joinRunLogEntry from r2 in joinRunLogEntry.DefaultIfEmpty() join u in db.User on se.TechnicianID equals u.ID join s in db.System1 on se.SystemID equals s.ID where ( ((se.RunLogEntryID == 0 || se.RunLogEntryID != null)) &amp;&amp; ((serviceEntryFilter.ID.HasValue == false) || (se.ID == serviceEntryFilter.ID.Value &amp;&amp; serviceEntryFilter.ID.HasValue == true)) &amp;&amp; ((serviceEntryFilter.ServiceDateTime.HasValue == false) || (EntityFunctions.TruncateTime(se.ServiceDateTime) == EntityFunctions.TruncateTime(serviceEntryFilter.ServiceDateTime) &amp;&amp; serviceEntryFilter.ServiceDateTime.HasValue == true)) &amp;&amp; ((serviceEntryFilter.RunDate.HasValue == false) || (EntityFunctions.TruncateTime(r2.RunDate) == EntityFunctions.TruncateTime(serviceEntryFilter.RunDate) &amp;&amp; serviceEntryFilter.RunDate.HasValue == true)) &amp;&amp; ((serviceEntryFilter.Technician == null) || (u.FullName.Contains(serviceEntryFilter.Technician.Trim()) &amp;&amp; serviceEntryFilter.Technician != null)) &amp;&amp; ( ((ServiceEntryStatus == "O" &amp;&amp; se.ServiceRequestClosed == false) || (ServiceEntryStatus == "C" &amp;&amp; se.ServiceRequestClosed == true) || (ServiceEntryStatus == "A") ) ) &amp;&amp; ( ((ServiceEntryReliabilityRecord == null) || (ServiceEntryReliabilityRecord == "N" &amp;&amp; se.ReliabilityRecord == false) || (ServiceEntryReliabilityRecord == "Y" &amp;&amp; se.ReliabilityRecord == true) || (ServiceEntryReliabilityRecord == "A") ) ) &amp;&amp; ( ((ServiceEntryReconciled == null) || (ServiceEntryReconciled == "N" &amp;&amp; se.Reconciled == false) || (ServiceEntryReconciled == "Y" &amp;&amp; se.Reconciled == true) || (ServiceEntryReconciled == "A") ) ) &amp;&amp; ( ((ActiveServiceEntry == null) || (ActiveServiceEntry == "N" &amp;&amp; se.Active == false) || (ActiveServiceEntry == "Y" &amp;&amp; se.Active == true) || (ActiveServiceEntry == "A") ) ) &amp;&amp; ( (s.PlatformID == platformID) || (platformID == 0) ) &amp;&amp; ((serviceEntryFilter.System == null) || ((serviceEntryFilter.System != null) &amp;&amp; systems.Contains(s.SystemFullName))) ) orderby se.ID descending select new ServiceSearchEntry() { ID = se.ID, ServiceDateTime = se.ServiceDateTime, Technician = u.FullName, System = s.SystemFullName, ReasonForFailure = se.ReasonForFailure, RunDate = (r2 == null ? (DateTime?)null : r2.RunDate) }); var listData = list.Skip((page - 1) * PageSize).Take(PageSize); ServiceEntriesListViewModel viewModel = new ServiceEntriesListViewModel() { ServiceSearchEntry = listData, PagingInfo = new PagingInfo { CurrentPage = page, ItemsPerPage = PageSize, TotalItems = list.Count() } }; } </code></pre> <p><strong>The Issue:</strong></p> <p>The following clause is throwing an error when SystemFilter.System is NULL. It is null at times when users do not select values for it. Sample values are as follows:</p> <p>EP1, EP2 EP1 TP2, TP3, TP4</p> <p><strong>&amp;&amp; ((serviceEntryFilter.System == null) || ((serviceEntryFilter.System != null) &amp;&amp; systems.Contains(s.SystemFullName)))</strong></p> <p>If it has a value, then I put it in an array and its works like a charm, its just when its null.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload