[SOLVED] Is there unpivot or cross apply in ServiceStack ormlite?

Is there unpivot or cross apply in ServiceStack ormlite?

I am using ServiceStack 4.5.14. I want to pass a list of Guid to such as below query.

Table Name: Image
Columns: (Id -> Type=Guid) (ImageId -> Type=Guid) (Guid -> Type=Guid)

var result = Db.ExecuteSql("select value from image unpivot (value for col in (Id, ImageId)) un where Guid=(@param) order by Guid", 
            new { param = "5de7f247-f590-479a-9c29-2e68a57e711c" });

It returns a result which their Id and ImageId are 000.... while they are null.

Another question is: how can I send a list of Guid as parameter to above query?

Solution

To query a parameterized field you should include the Guid instead of the string, e.g:

var result = Db.ExecuteSql(
    @"select value from image unpivot (value for col in (Id, ImageId)) un 
       where Guid=(@param) order by Guid", 
    new { param = new Guid("5de7f247-f590-479a-9c29-2e68a57e711c") });

If values are null, it's likely masquerading an error, you can bubble errors with:

OrmLiteConfig.ThrowOnError = true;

Or enable debug logging with:

LogManager.LogFactory = new ConsoleLogFactory();

In v5+ you can also inspect SQL commands before they're executed with:

OrmLiteConfig.BeforeExecFilter = dbCmd => Console.WriteLine(dbCmd.GetDebugString());