LINQ query fails with nullable variable ormlite

Question

I'm trying to write following LINQ query using ServiceStack Ormlite.

dbConn.Select<Product>(p => p.IsActive.HasValue && p.IsActive.Value)

Here, Product is my item class and "IsActive" is Nullable Bool property in that class. When this line executes it always throws "InvalidOperationException" with the message

variable 'p' of type '' referenced from scope '', but it is not defined

I tried different variants as following but still same exception result

dbConn.Select<Product>(p => p.IsActive.HasValue == true && p.IsActive.Value == true)
dbConn.Select<Product>(p => p.IsActive != null && p.IsActive.Value == true)

But if I just write

dbConn.Select<Product>(p => p.IsActive.HasValue)

then it works.

I'm puzzled what is the problem? Is this servicestack ormlite issue?

Answer 1

This is nature of the Linq. In order to achieve what you need, you will need to use two where closes:

 dbConn.Where<Product>(p => p.IsActive.HasValue).Where(p=>p.Value==true);

Answer 2

My answer can handle Nullable value like "value" and "HasValue" with servicestack ormlite. And But also with datetime nullable ,like 'createdate.value.Year'. you must change two place.

1. modify VisitMemberAccess method:

protected virtual object VisitMemberAccess(MemberExpression m)
        {
            if (m.Expression != null)
            {
                 if (m.Member.DeclaringType.IsNullableType())
                 {
                    if (m.Member.Name == nameof(Nullable<bool>.Value))
                        return Visit(m.Expression);
                    if (m.Member.Name == nameof(Nullable<bool>.HasValue))
                    {
                        var doesNotEqualNull = Expression.NotEqual(m.Expression, Expression.Constant(null));
                            return Visit(doesNotEqualNull); // Nullable<T>.HasValue is equivalent to "!= null"
                    }
                    throw new ArgumentException(string.Format("Expression '{0}' accesses unsupported property '{1}' of Nullable<T>", m, m.Member));
                }
                if (m.Member.DeclaringType == typeof(DateTime))
                {
                    var ExpressionInfo = m.Expression as MemberExpression;
                    if (ExpressionInfo.Member.DeclaringType.IsNullableType())
                    {
                        if (ExpressionInfo.Member.Name == nameof(Nullable<bool>.Value))
                        {
                            var modelType = (ExpressionInfo.Expression as MemberExpression).Expression.Type;
                            var tableDef = modelType.GetModelDefinition();
                            var columnName = (ExpressionInfo.Expression as MemberExpression).Member.Name;
                            var QuotedColumnName = GetQuotedColumnName(tableDef, columnName);
                            if (m.Member.Name == "Year")
                            {
                                return new PartialSqlString(string.Format("DATEPART(yyyy,{0})", QuotedColumnName));
                            }
                            if (m.Member.Name == "Month")
                                return new PartialSqlString(string.Format("DATEPART(mm,{0})", QuotedColumnName));
                        }                            
                        if (ExpressionInfo.Member.Name == nameof(Nullable<bool>.HasValue))
                        {
                            var doesNotEqualNull = Expression.NotEqual(ExpressionInfo.Expression, Expression.Constant(null));
                            return Visit(doesNotEqualNull); // Nullable<T>.HasValue is equivalent to "!= null"
                        }
                    }
                    else
                    {
                        var modelType = ExpressionInfo.Expression.Type;
                        var tableDef = modelType.GetModelDefinition();
                        var columnName = ExpressionInfo.Member.Name;
                        var QuotedColumnName = GetQuotedColumnName(tableDef, columnName);
                        if (m.Member.Name == "Year")
                            return new PartialSqlString(string.Format("DATEPART(yyyy,{0})", QuotedColumnName));
                        if (m.Member.Name == "Month")
                            return new PartialSqlString(string.Format("DATEPART(mm,{0})", QuotedColumnName));
                    }
                }
                if (m.Expression.NodeType == ExpressionType.Parameter || m.Expression.NodeType == ExpressionType.Convert)
                { 
                    var propertyInfo = (PropertyInfo)m.Member;

                    var modelType = m.Expression.Type;
                    if (m.Expression.NodeType == ExpressionType.Convert)
                    {
                        var unaryExpr = m.Expression as UnaryExpression;
                        if (unaryExpr != null)
                        {
                            modelType = unaryExpr.Operand.Type;
                        }
                    }

                    var tableDef = modelType.GetModelDefinition();
                    if (propertyInfo.PropertyType.IsEnum)
                        return new EnumMemberAccess(
                            GetQuotedColumnName(tableDef, m.Member.Name), propertyInfo.PropertyType);

                    return new PartialSqlString(GetQuotedColumnName(tableDef, m.Member.Name));
                }
            }

            var member = Expression.Convert(m, typeof(object));
            var lambda = Expression.Lambda<Func<object>>(member);
            var getter = lambda.Compile();
            return getter();
        }

2. modify VisitLambda method :

protected virtual object VisitLambda(LambdaExpression lambda)
        {
            if (lambda.Body.NodeType == ExpressionType.MemberAccess && sep == " ")
            {
                MemberExpression m = lambda.Body as MemberExpression;

                if (m.Expression != null)
                {
                    string r = VisitMemberAccess(m).ToString();
                    if (m.Member.DeclaringType.IsNullableType())
                        return r;
                    return string.Format("{0}={1}", r, GetQuotedTrueValue());
                }

            }
            return Visit(lambda.Body);
        }