Servicestack Ormlite generates invalid SQL query for custom select

Question

I am using version 4.5.14 of Servicestack ormlite

Following are my domain classes

     public class Network 
        {
            [PrimaryKey]
            public string Id { get; set; }
            public string Name { get; set; }
            [Ignore]
            public bool InMaintenance { get; set; }
        }

     public class NetworkMain
        {
            [PrimaryKey]
            public string Id { get; set; }    
            [ForeignKey(typeof(Network))]
            public string NetworkId { get; set; }
            public DateTime? EndDate { get; set; }
         }

     public class NetworkDTO
        {               
            public string Id { get; set; }
            public string Name { get; set; }            
            public bool InMaintenance { get; set; }
        }

here "InMaintenance" Property is ignored as it is not the "Network" table column in the database. I want to set the value of the InMaintenance property based on whether the "Enddate" column in the NetworkMain table has value or not. Following is the code

  var q = Db.From<Domain.Network>()
                    .LeftJoin<NetworkMain>()
                    .Select<Domain.Network, NetworkMain>(
                        (a, m) => new
                        {   a,
                            InMaintenance = m.NetworkId == a.Id && m.EndDate.HasValue ? "1" : "0"
                        }).OrderBy(x=>x.Name);

            return Db.Select<NetworkDTO>(q).ToList();

but the above code generates the following SQL query for SelectExpression

SELECT "Network"."Id",  "Network"."Name",  (CASE WHEN (("dbo"."NetworkMain"."NetworkId"="Network"."Id")AND("dbo"."NetworkMain"."EndDate"is notnull)) THEN @0 ELSE @1 END)

as we can see there is no space between the not null condition in the above expression.

And FromExpression is as follows

FROM "Network" LEFT JOIN "dbo"."NetworkMaintenance" ON
("Network"."Id" = "dbo"."NetworkMain"."NetworkId")

I know that I can use the SQL query in the select but how to resolve this issue? Thanks!

Amol

UPDATE

I have used the following code to solve the problem

 var q = Db.From<Domain.Network>()
                        .LeftJoin<NetworkMain>()
                        .Select<Domain.Network, NetworkMain>(
                            (a, m) => new
                            {   a,
                                InMaintenance = Sql.Custom("(CASE WHEN (("dbo"."NetworkMain"."NetworkId"="Network"."Id")AND("dbo"."NetworkMain"."EndDate"is not null)) THEN @0 ELSE @1 END)")                                  
                            }).OrderBy(x=>x.Name);

                return Db.Select<NetworkDTO>(q).ToList();

I want to have records in the Network domain class and not in NetworkDTO due to some reason but the value of "InMaintenance" always comes as null in the following code.

var q = Db.From<Domain.Network>()
                        .LeftJoin<NetworkMain>()
                        .Select<Domain.Network, NetworkMain>(
                            (a, m) => new
                            {   a,
                                InMaintenance = Sql.Custom("(CASE WHEN (("dbo"."NetworkMain"."NetworkId"="Network"."Id")AND("dbo"."NetworkMain"."EndDate"is not null)) THEN @0 ELSE @1 END)")                                  
                            }).OrderBy(x=>x.Name);

                return Db.Select<Network>(q).ToList();

I think it's due to the Ignore attribute, but how to achieve this?

UPDATE 2: just made the following changes and it worked.

[CustomSelect("NULL")]
                public bool InMaintenance { get; set; }

mythz mythz · Accepted Answer · 2020-12-02T06:48:43

4.5.14 is several years old, but this generates valid SQL in the latest version of OrmLite. Here's a live demo on Gistlyn you can run:

OrmLiteUtils.PrintSql();
public class Network 
{
    [PrimaryKey]
    public string Id { get; set; }
    public string Name { get; set; }
    [Ignore]
    public bool InMaintenance { get; set; }
}

public class NetworkMain
{
    [PrimaryKey]
    public string Id { get; set; }    
    [ForeignKey(typeof(Network))]
    public string NetworkId { get; set; }
    public DateTime? EndDate { get; set; }
}

public class NetworkDTO
{               
    public string Id { get; set; }
    public string Name { get; set; }            
    public bool InMaintenance { get; set; }
}

var q = db.From<Network>()
    .LeftJoin<NetworkMain>()
    .Select<Network, NetworkMain>((a, m) => new
        {   a,
            InMaintenance = m.NetworkId == a.Id && m.EndDate.HasValue ? "1" : "0"
        }).OrderBy(x=>x.Name);

var results = db.Select<NetworkDTO>(q).ToList();

Which generates:

SELECT "Network"."Id", "Network"."Name", (CASE WHEN (("NetworkMain"."NetworkId"="Network"."Id")AND("NetworkMain"."EndDate" is not null)) THEN @0 ELSE @1 END) AS InMaintenance 
FROM "Network" LEFT JOIN "NetworkMain" ON
("Network"."Id" = "NetworkMain"."NetworkId")
ORDER BY "Network"."Name"

Servicestack Ormlite generates invalid SQL query for custom select

1 Answers