Guidance on sqlexpression

ServiceStack.OrmLite SQL Server
1

Hi, I am trying to string together a number of SqlExpressions. The code below is generating the correct SQL when I look at the conn.GetLastSql() - and when I execute it on the server, it returns the number of rows I would expect, but the code below is returning zero Occupancy objects.

I can of course do this via untyped, but would rather keep the compile-time checking. Any ideas?

G

                var dateExp = conn.From<Occupancy>();
                //start before window but end within window
                dateExp.Or(d => d.StartDate < request.StartDate && d.EndDate > request.StartDate && d.EndDate < request.EndDate);
                //start before window and end after window
                dateExp.Or(d => d.StartDate < request.StartDate && d.EndDate > request.EndDate);
                //start within window and end after window
                dateExp.Or(d => d.StartDate > request.StartDate && d.EndDate > request.EndDate);
                //start and end within window
                dateExp.Or(d => d.StartDate > request.StartDate && d.EndDate < request.EndDate);
                //start date before end date and no end date
                dateExp.Or(d => d.StartDate < request.EndDate && d.EndDate == null);
                var dateoccupancies = conn.Select(dateExp);

The SQL this is generating is below. This returns 7 rows when executed.

SELECT "OccupancyId", "OccupancyTypeId", "MooringId", "AccountId", "BoatId", "MooringReleaseId", "StartDate", "EndDate", "OrderId", "Notes", "CreatedOn", "ModifiedOn", "CreatedBy", "ModifiedBy", "Deleted" 
FROM "Occupancy"
WHERE ((("StartDate" < N'20160401 01:00:00.000') AND ("EndDate" > N'20160401 01:00:00.000')) AND ("EndDate" < N'20160501 01:00:00.000')) OR (("StartDate" < N'20160401 01:00:00.000') AND ("EndDate" > N'20160501 01:00:00.000')) OR (("StartDate" > N'20160401 01:00:00.000') AND ("EndDate" > N'20160501 01:00:00.000')) OR (("StartDate" > N'20160401 01:00:00.000') AND ("EndDate" < N'20160501 01:00:00.000')) OR (("StartDate" < N'20160501 01:00:00.000') AND ("EndDate" is null))
2

Got to the bottom of this - as one of the object properties was DateTime? I needed to register:

OrmLiteConfig.DialectProvider.RegisterConverter<DateTime?>(new DateTimeConverter());
3

Sounds like a bug, it shouldn’t be necessary to register converters for nullable types. Can you please provide the class definition for Occupancy?

4 
 public class Occupancy : BaseEntity
{
    [Alias("OccupancyId")]
    [PrimaryKey]
    [AutoIncrement]
    public override long Id { get; set; }

    [References(typeof(OccupancyType))]
    public long OccupancyTypeId { get; set; }

    [References(typeof(Mooring))]
    public long MooringId { get; set; }

    [References(typeof(Account))]
    public long? AccountId { get; set; }

    [References(typeof(Boat))]
    public long? BoatId { get; set; }

    [References(typeof(MooringRelease))]
    public long? MooringReleaseId { get; set; }

    public string Title { get; set; }

    public DateTime StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public long? OrderId { get; set; }
    public string Notes { get; set; }

    [Ignore]
    public Boat Boat { get; set; }

    [Ignore]
    public Mooring Mooring { get; set; }

    [Ignore]
    public OccupancyType Type { get; set; }
}