3

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?

KJCM
  • 33
  • 3

1 Answers1

2

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"));
mythz
  • 141,670
  • 29
  • 246
  • 390
  • 2
    Thanks, your explanation helped to clarify. I couldn't get my head around why there was no way of adding the TableAlias directly to the where clause, but it makes sense that it needs to be against a specific column. – KJCM Feb 25 '19 at 04:34
  • I don't see any examples in your code of how you would do string comparisons. I've managed to get everything working except that. For example: q.Where(u => Sql.TableAlias(u.LastName, "Leader").StartsWith("Mac")); q.Where(u => Sql.TableAlias(u.LastName.StartsWith("Mac"), "Leader")); Neither of these options work. Is there a way to do string comparisons? – KJCM Feb 26 '19 at 02:35