CODEWITHSUNDEEP
c#ormlite-servicestackpredicatebuilder
KJCM
KJCM

Reputation: 33

ServiceStack ORMLite JoinAlias on a Where clause

I'm trying to add a Where clause to a table joined with a JoinAlias, but there doesn't appear to be a way to specify the JoinAlias on the where clause.

I'm trying to join to the same table multiple times, then add a variable number of where clauses to the join, based on user input:

  var userFilterList = new List<Expression<Func<LocationDb, LocationAttributesDateTimeDb, bool>>>();

  Expression <Func<LocationDb, LocationAttributesDateTimeDb, bool>> joinPredicate = (loc, ext) =>
            loc.LocationId == ext.LocationId && ext.AttributeId == attributeId;

  query = query.Join<LocationAttributesDateTimeDb>(joinPredicate, ctx.JoinAlias($"ext{attributeId}"));


  foreach (var item in userFilterList)
  {
       query = query.Where<LocationDb, LocationAttributesDateTimeDb>(item);
  }

The main problem is, there doesn't appear to be a way to add the JoinAlias onto the Where clause. If I try to run the query as is, I get an exception regarding the missing alias.

If I try the following code, I get a compile exception:

query = query.Where<LocationDb, LocationAttributesDateTimeDb>(item, ctx.JoinAlias($"ext{attributeId}"));

Is there a way to add the JoinAlias to a where clause without resorting to writing the Where clauses as manual SQL?

Or, is there an alternative method I can use to stitch my multiple requests together into the single Join predicate?

Upvotes: 3

Views: 462

Answers (1)

mythz
mythz

Reputation: 143359

Note that in the latest v5.4.1 pre-release on MyGet JoinAlias() has been deprecated and replaced with TableAlias() which uses a different implementation that substitutes the alias whilst walking the expression tree whilst generating SQL Statements whereas JoinAlias() worked by post string substitution on the generated SQL which was more fragile.

There's no TableAlias() in WHERE statements as it wouldn't be possible to determine where the alias should be used, instead here are some examples of how to use TableAlias in WHERE conditions:

q.Where<Team, Teamuser>((t, u) => t.Id == Sql.TableAlias(u.TeamId, "Leader"));
q.Where<Teamuser>(u => Sql.TableAlias(u.Id, "Leader") == 1);
q.Where<Team, Teamuser>((t, u) => Sql.TableAlias(t.Id, q.DialectProvider.GetQuotedTableName(ModelDefinition<Team>.Definition)) == Sql.TableAlias(u.TeamId, "Leader")); // Workaround, but only works for fields, not constants
q.Where<Team, Teamuser>((user, leader) => Sql.TableAlias(user.Id, "Teamuser") < Sql.TableAlias(leader.Id, "Leader"));

Upvotes: 2

Related Questions