SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll (2.0.0.60911) SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll (2.0.0.60722)

LLBLGenPro 2, ASP.Net 2.0, C#, using Adapter.

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(HoldingplanBankAccountFields.PortfolioCode == portfolioCode);
// filter.PredicateExpression.Add(HoldingplanBankAccountFields.EffectiveTo == DateTime.MinValue).AddWithOr(HoldingplanBankAccountFields.EffectiveTo > DateTime.Now);

If you uncomment the last line, the following exception occurs:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

during .DataBind()

I know why this is occurring, because it's converting Nulls in the column of to SqlDateTime(DateTime.MinValue), and then trying to do the comparison(s).

How do I get around this to achieve the equivelant of this SQL?

select * from holdingplanbankaccount where portfoliocode = '10000127' and ((effectiveto is null) or (effectiveto >= '2006-10-20'))

holdingplanbankaccount is a SQLServer View, and null is a valid value for effectiveto as it indicates the plan is effective until cancelled.

I've seen some other posts about this exception, but they were about saving values back to the database.

DateTime.MinValue is 1/1/0000 So that's causing the overflow. You shouldn't compare with DateTime.MinValue, as that's not a valid value in the DB. The predicate is constructed to be run on the DB. If you want to compare with null, just do HoldingplanBankAccountFields.EffectiveTo == DBNull.Value

Otis wrote:

DateTime.MinValue is 1/1/0000 So that's causing the overflow. You shouldn't compare with DateTime.MinValue, as that's not a valid value in the DB. The predicate is constructed to be run on the DB. If you want to compare with null, just do HoldingplanBankAccountFields.EffectiveTo == DBNull.Value

I've ran into this problem as well... Is it possible to have the LLBLGenPro (since it's a database persistence tool) behind the scenes automatically convert DataTime.MinValue to SqlDataType.DateTime.MinValue (or whatever database template we are using) so that from the development side we could continue to use DateTime.MinValue?

Everything is possible But could you explain to me please why you would want to keep on using DateTime.MinValue in this context? It has no meaning IMHO to use it for saving data.

Otis wrote:

Everything is possible But could you explain to me please why you would want to keep on using DateTime.MinValue in this context? It has no meaning IMHO to use it for saving data.

Hi Otis,

Thanks for the feedback, I could have sworn I'd tried that, and that I was still getting the error when trying things using just the > comparison as well...?!?

The MinValue stuff was there as a hopeful attempt to avoid the error - I was already checking for MinValue's after binding and replacing them with a string "Open Ended"

Will have to put it down to Friday Afternoonitis.....maybe none of my recompiles actually ended up being executed or something?

Just as a reference for anyone who cares, the code below works fine:

 public static RelationPredicateBucket BuildPlanFilter(string portfolioCode, PlanType planType)
        {
            RelationPredicateBucket filter = new RelationPredicateBucket();

            IPredicateExpression portfolioFilter = new PredicateExpression(HoldingplanBankAccountFields.PortfolioCode == portfolioCode);
            IPredicateExpression dateFilter = BuildPlanEffectiveDateFilter();
            IPredicateExpression planTypeFilter = BuildPlanTypeFilter(planType);

            filter.PredicateExpression.Add(portfolioFilter).AddWithAnd(dateFilter).AddWithAnd(planTypeFilter);

            return filter;
        }
...

 private static IPredicateExpression BuildPlanEffectiveDateFilter()
        {
            return new PredicateExpression(
                (HoldingplanBankAccountFields.EffectiveTo > DateTime.Now.AddDays(-1)) |
                (HoldingplanBankAccountFields.EffectiveTo == DBNull.Value)
                );
        }

Thanks for the feedback and the code reference