ASP.NET MVC5 Entity Framework 6 get bool = true and bool = false LINQ

Question

I have a table that I am filtering on. There is a filter for values 'include' which can be true or false.

I have a filter that has 3 options: true, false, & all.

So, when the filter is true, it should return rows where include = 'true'; when the filter is 'false', return where include = false; and when 'all' return where include = true or false.

Here is my code, that is not working, but I think it should be.

private ICollection<AggregationEntityViewModel> getEntities(AggregationPracticeDetailsViewModel apdvm)
{
    bool? filterInclude = Convert.ToBoolean(apdvm.Filter_IncludeValue);

    var a = (from e in _repository.GetAll<Entity>()                         
        where e.include == filterInclude != null ? (bool)filterInclude : (true || false)                     
        select e
     return a;
}

It is currently returning 0 rows when filter is set to 'All' or 'False', and returning all rows when set to 'Yes'.

FYI, I have ommitted lots of code for clarity's sake.

Please help...thanks!

*EDIT: I've displayed all the code, so you can see why I want to keep it all in linq query. Thanks for all the offered solutions. I see that most solutions involve using Linq Extension methods. Is there anyway to do it in inline linq query? *

bool? filterInclude = Convert.ToBoolean(apdvm.Filter_IncludeValue);
            var a = (from e in _repository.GetAll<Entity>()
                     from u in e.Users
                     where (e.AuditQuestionGroupId != null ? e.AuditQuestionGroupId : 0) == this.LoggedInEntity.AuditQuestionGroupId
                     && e.BatchNumber != null && e.BatchNumber.StartsWith(apdvm.Filter_BatchNumber == null ? "" : apdvm.Filter_BatchNumber)
                     && e.Name != null && e.Name.ToLower().StartsWith(apdvm.Filter_EntityName.ToLower())
                     && e.EntityState != null && e.EntityState.ToLower().Contains(apdvm.Filter_StateValue == null ? "" : apdvm.Filter_StateValue.ToLower())
                     && u.NIAMembershipId != null && u.NIAMembershipId.Contains(apdvm.Filter_MemberNo == null ? "" : apdvm.Filter_MemberNo)
                     from p in e.PracticeProfiles.DefaultIfEmpty()
                     join ea in _repository.GetAll<EntityAggregate>() on e.EntityId equals ea.EntityId into eas
                     from ea in eas.DefaultIfEmpty()                     
                     where ea.include == filterInclude != null ? (bool)filterInclude : (true || false)
                     group e by new { entity = e, profile = p, ea = ea } into newGroup
                     orderby newGroup.Key.entity.Name
                     select new AggregationEntityViewModel()
                     {
                         Id = newGroup.Key.ea == null ? 0 : newGroup.Key.ea.Id,
                         EntityId = newGroup.Key.entity.EntityId,
                         Include = newGroup.Key.ea == null ? (true || false) : (bool)newGroup.Key.ea.include,
                         BHAddress = newGroup.Key.profile == null || newGroup.Key.profile.soloOffice == null ? false : (bool)newGroup.Key.profile.soloOffice,
                         Incorporated = newGroup.Key.profile == null || newGroup.Key.profile.company == null ? false : (bool)newGroup.Key.profile.company,
                         MajorityOwned = newGroup.Key.profile == null || newGroup.Key.profile.capital == null ? false : (bool)newGroup.Key.profile.capital,
                         MajorityVoting = newGroup.Key.profile == null || newGroup.Key.profile.votingRights == null ? false : (bool)newGroup.Key.profile.votingRights,
                         Name = newGroup.Key.entity.Name,
                         Partnership = newGroup.Key.profile == null || newGroup.Key.profile.partnership == null ? false : (bool)newGroup.Key.profile.partnership,
                         PublicAccountant = newGroup.Key.profile == null || newGroup.Key.profile.publicAccountant == null ? false : (bool)newGroup.Key.profile.publicAccountant,
                         Trust = newGroup.Key.profile == null || newGroup.Key.profile.operatingTrust == null ? false : (bool)newGroup.Key.profile.operatingTrust,
                         TrustDeed = newGroup.Key.profile == null || newGroup.Key.profile.deed == null ? false : (bool)newGroup.Key.profile.deed
                     }).ToList();
            return a;

What is the type of `AggregationPracticeDetailsViewModel .Filter_IncludeValue`? — MarcinJuraszek, Aug 12 '14 at 06:21
`(true || false)` evaluates to `true`. This means that when your filter is null, the query always looks for `where e.include == true`. — Andrei V, Aug 12 '14 at 06:22
AggregationPracticeDetailsViewModel .Filter_IncludeValue is a string. I am using null to be ALL as it seemed the easiest, but it sounds like it may not be. — Rhys Stephens, Aug 12 '14 at 06:39

score 4 · Answer 1 · answered Aug 12 '14 at 06:24

4

Convert.ToBoolean returns bool, not bool?, so there is no way filterInclude != null is true.

You should use following pattern instead of ternary operator within where clause:

var query = _repository.GetAll<Entity>();

if (apdvm.Filter_IncludeValue == "true")
    query = query.Where(x => x.include == true);
else if (apdvm.Filter_IncludeValue == "false")
    query = query.Where(x => x.include == false);

return query;

I assumed apdvm.Filter_IncludeValue is a string (and that's why you tried to call Convert.ToBoolean on it).

answered Aug 12 '14 at 06:24

MarcinJuraszek

124,003
15
196
263

+1. Most people are not aware that you can construct a LINQ query in multiple steps. You can have multiple where clauses. This is a very nice construction. – TomTom Aug 12 '14 at 06:43
Yes it is, except for the fact my query is a lot more complicated, as per my edit. Having the complication of my query with this pattern seems like it would become way too messy. – Rhys Stephens Aug 12 '14 at 06:45
1

Well, you could convert the entire query to the fluent syntax. This would make the assembly more straightforward. – Christopher Stevenson Aug 12 '14 at 06:49
OMG. The query is a disaster. You could get it much more clear by constructing the conditions in steps like the one I described. And you'd get better performance, because unnecessary conditions (like ` myColumn LIKE '%'`` which will be generated from `myColumn.Contains("")` would never hit the database. – MarcinJuraszek Aug 12 '14 at 06:50
Okay, thanks for the comments. The syntax works okay for my brain, because its so like sql. – Rhys Stephens Aug 12 '14 at 06:59
Just to confirm, will the query only actually be run once the view tries to display the results or a resolving method (like ToList(), AsEnumerable() etc) is called? I was worried about breaking the query into parts would add unnecessary SQL commands and decrease performance. – Rhys Stephens Aug 18 '14 at 03:03
It will only actually send the SQL to server when results are needed, e.g. when `ToList` is called. – MarcinJuraszek Aug 18 '14 at 03:19

score 1 · Accepted Answer · edited May 23 '17 at 12:31

You could use

private ICollection<AggregationEntityViewModel> getEntities(
              AggregationPracticeDetailsViewModel apdvm)
{
    bool? filterInclude = apdvm.Filter_IncludeValue.ConvertToNullable<bool>();

    var a = (from e in _repository.GetAll<Entity>()                         
             where !filterInclude.HasValue || ea.include == filterInclude.Value                     
             select new AggregationEntityViewModel()
             {
                Include = newGroup.Key.ea == null 
                          ? (true || false) 
                          : (bool)newGroup.Key.ea.include,
             }
    return a;
}

just remove your (true||false) and add filterInclude == null in the where

For Nullable Value (taken from Convert string to nullable type (int, double, etc...))

public static T? ConvertToNullable<T>(this String s) where T : struct 
{
    try
    {
        return (T?)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(s);
    }
    catch (Exception)
    {
        return null;
    }
}

(bool?)apdvm.Filter_IncludeValue; gives an error: cannot convert type 'string' to 'bool'. This is what I am trying to do though, convert the string to a nullable bool. — Rhys Stephens, Aug 12 '14 at 06:48
Maybe you should dev a method that converts your values (yes,no|true,false|e.g.) to a nullable bool. than this pattern works for all variations. — S.L., Aug 12 '14 at 07:16

score 1 · Answer 3 · edited May 23 '17 at 11:45

There is an other solution:

var query = from e in _repository.GetAll<Entity>();

if (filterInclude.HasValue)
{

    // when filterInclude is null (it means **ALL**), 
    // do not filter otherwise - check the flag
    query = query.Where(entity => entity.Include == filterInclude.Value);
}

// or one-line:
// query = query.Where(entity => filterInclude == null
//                      || entity.Include == filterInclude.Value);

var a = query.Select(entity => new AggregationEntityViewModel { .... });

return a;

Other problem is that Convert.ToBoolean never returns null. You should create own method to parse apdvm.Filter_IncludeValue.

In order to convert to nullable type, you colud use the generic method:

public static Nullable<T> ToNullable<T>(this string s) where T: struct
{
    Nullable<T> result = new Nullable<T>();
    try
    {
        if (!string.IsNullOrEmpty(s) && s.Trim().Length > 0)
        {
            TypeConverter conv = TypeDescriptor.GetConverter(typeof(T));
            result = (T)conv.ConvertFrom(s);
        }
    }
    catch { } 
    return result;
}

Source.

Usage:

var filterInclude = apdvm.Filter_IncludeValue.ToNullable<bool>();

Egor · Answer 4 · 2014-08-12T06:32:46.137

You can make it easier with fluent syntax like this:

private ICollection<AggregationEntityViewModel> getEntities(AggregationPracticeDetailsViewModel apdvm)
{
    var query = _repository.GetAll<Entity>();
    if(apdvm.Filter_IncludeValue != 'all')
    {
        var value = Convert.ToBoolean(apdvm.Filter_IncludeValue);
        query = query.Where(q => q.include == value)
    }
    return query.Select(q => new AggregationEntityViewModel {...}).ToArray();
}

no need to evaluate string to nullable bool or smth. Same as no need to do strange boolean expressions.

ASP.NET MVC5 Entity Framework 6 get bool = true and bool = false LINQ

4 Answers4