CODEWITHSUNDEEP
servicestackormlite-servicestack
A_0
A_0

Reputation: 1004

Servicestack Ormlite generates invalid SQL query for custom select

I am using version 4.5.14 of Servicestack ormlite

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

but the above code generates the following SQL query for SelectExpression

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

And FromExpression is as follows

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

Amol

Upvotes: 0

Views: 291

Answers (1)

mythz
mythz

Reputation: 143319

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"

Upvotes: 1

Related Questions