Support JOIN with LIKE in OrmLite

ServiceStack.OrmLite SQL Server
1

In SQL it is possible to join with LIKE. Perhaps not the best idea always, but certainly useful.

This query:

var q = db.From<Tag>().Join<TagAccess>((t, a) => a.TagPath.StartsWith(t.Text))
            .Where<TagAccess>(a => a.Username == username);

When I do .ToSqlStatementMergedParams it gives me the incorrect SQL:

SELECT "Tag"."Id", "Tag"."Text", "Tag"."Type", "Tag"."Comment" 
FROM "Tag" INNER JOIN "TagAccess" ON "Tag"."Text" like '"TagAccess"."TagPath"%'

When the correct SQL would be

SELECT "Tag"."Id", "Tag"."Text", "Tag"."Type", "Tag"."Comment" 
FROM "Tag" INNER JOIN "TagAccess" ON "Tag"."Text" like CONCAT("TagAccess"."TagPath",%)

or

SELECT "Tag"."Id", "Tag"."Text", "Tag"."Type", "Tag"."Comment" 
FROM "Tag" INNER JOIN "TagAccess" ON "Tag"."Text" like CONCAT("TagAccess"."TagPath",'%')

SELECT "Tag"."Id", "Tag"."Text", "Tag"."Type", "Tag"."Comment" 
FROM "Tag" INNER JOIN "TagAccess" ON "Tag"."Text" like "TagAccess"."TagPath"+'%'

Would this be possible to get fixed?

I don’t think the classes are necessary for you in this case, but I’m joining on the column called Text:

public class Tag {
 public string Text { get;set; } // /tag/looks/like/this
}
public class TagAccess {
 public string TagPath { get;set; } // tag/looks/
 // of course more, e.g. userid or something
}
2

StartsWith currently expects a constant or resolvable value to compare rather than a partial SQL statement, so at the moment you will need to handle this use case with a different approach to handle a SQL expression.

This can be seen in the SqlExpression class when converting from an expression to SQL when using the StartsWith method. To support this above use case, the handling of StartsWith I believe would also need to handle generically a PartialSqlString.

Alternatively, you can customized this behavior with your own Dialect providing your own SqlExpression override to VisitColumnAccessMethod, this can be seen with the SqliteExpression.

This can be useful for also taking advantage of your specific SQL provider features as well that aren’t yet in OrmLite.

1 Like
3

Ok, at least for now this is a dead end then. Good to know so I won’t waste time. Perhaps I’ll just put in hand-written SQL instead. I often write the SQL first, then backport to ORMlite anyway.

But it would be a nice feature I think, and the syntax would look good as well. Perhaps one day.

Implementing SqlExpression it will be a challenge for me, perhaps during a holiday, as I would learn how those classes work etc.