Servicestack Ormlite generates invalid SQL query for custom select

Question

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

mythz · Accepted Answer · 2020-12-02 06:48:43Z

1

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"

answered Dec 2, 2020 at 6:48

mythz

144k31 gold badges253 silver badges400 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mythz Over a year ago

@amol I'm only testing the latest version which treats the anonymous InMaintenance property as a column alias. If you want to stick with the old v4 version you'll likely need to specify the entire Select SQL fragment, e.g. .Select(selectSql)

A_0 Over a year ago

Thanks! @mythz I think I have figured out how to achieve it

A_0 Over a year ago

could you please help me with stackoverflow.com/questions/65129435/… @mythz

mythz Over a year ago

@amol No I only run & support the latest version, if you have custom SQL Queries you want to run you can use the Custom SQL APIs, they've been around for years but don't know which of the APIs were available in v4 libraries, e.g. in v5 you can .From() and .CustomJoin() for custom JOINs, in v4 you'll likely need to run entire query in .SqlList(). If you want to explore what features & APIs are available in the latest release you can use a Trial License Key.

A_0 Over a year ago

Ok. Thanks, but for now I am stuck with v4, can't change it now. Is there any way to get the SqlExpression from the sql query so that I can apply conditional filters later instead of having different queries for each filter?

|

Collectives™ on Stack Overflow

Servicestack Ormlite generates invalid SQL query for custom select

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related