Are ORMLite typed Subqueries possible?

#1

I’m trying to replace most the stored procedures in our sql server database with code in our C# app, especially for simple data collection.

I looked for similar, previous posts and answers but I didn’t see anything recent so thought I’d pose the question again incase this is now in servicestack…

I have a fairly simple query:

SELECT a.*
FROM Table1 a
WHERE NOT EXISTS (SELECT NULL FROM Table2 b WHERE a.Id = b.Table1Id)

I’m struggling to type this into an efficient ORMLite query, that I’m happy with the resulting t-sql.

How should I go about this in ORMLite?

#2

No it’s not fully supported, best you can do is combine separate queries together, e.g:

var subQ = db.From<Table2>(db.TableAlias("b"))
  .Where<Table1,Table2>((a,b) => 
        Sql.TableAlias(a.Id,"a") == Sql.TableAlias(b.Table1Id,"b"))
  .Select(Sql.Custom("null"));

var q = db.From<Table1>(db.TableAlias("a"))
  .UnsafeWhere($"NOT EXISTS ({subQ.ToSelectStatement()})");

var results = db.Select(q);
1 Like
#3

I’ve made this a little nicer with the new WhereExists/WhereNotExists SqlExpression methods which will let you do:

var q = db.From<Table1>(db.TableAlias("a")).WhereNotExists(
  db.From<Table2>(db.TableAlias("b"))
    .Where<Table1,Table2>((a,b) => 
      Sql.TableAlias(a.Id,"a") == Sql.TableAlias(b.Table1Id,"b"))
    .Select(Sql.Custom("null")));

var results = db.Select(q);

This change is available from v6.7.1+ that’s now available on MyGet.

1 Like
#4

That is nice, thanks!
Are the TableAliases purely because they were in my script or are they required?

#5

They’re required if you want to use table aliases, which are typically used by sub selects to reference outer tables.

1 Like
#6

FYI I’ve decided to rename the new methods to WhereExists and WhereNotExists to better represent the SQL that’s used.

So without the unnecessary 2nd table alias, this example would now look like:

var q = db.From<Table1>(db.TableAlias("a"))
    .WhereNotExists(db.From<Table2>()
        .Where<Table1,Table2>((a,b) => 
                b.Table1Id == Sql.TableAlias(a.Id, "a"))
        .Select(Sql.Custom("null")));

var results = db.Select(q);

This change is now available from the latest v6.7.1+ on MyGet, you’ll need to clear packages cache to get the latest v6.7.1.

1 Like
#7

Yeah, this reads better and fits in with the rest of the api. Thanks for the quick turn around!

1 Like