MySQL Error: Unknown column 't2.UserId' in 'on clause'

Find the code in question attached.

The objective of the query is to obtain a person for a given user identifier. When I perform the following query:

var query = new Query(typeof(Person));
query.Distinct = true;
query.Mappings.Add<Person>("t0");
query.Mappings.Add<User>("t1");
query.Join = Join.Inner<Party, User>(Person.UserIdField, "Id");
query.QueryExpression = Entity.Attribute<User>(User.IdentifierField) == userIdentifier;
var person = unitOfWork.FindOne<Person>(query);
Console.Out.WriteLine("Person EntityProperty1: {0}", person.EntityProperty1);

This query generates an invalid sql statement. Notice the "ON" clause has a t2 alias which was never defined.

SELECT
  t0.Id AS "t0.Id",
  t0.Discriminator AS "t0.Discriminator",
  t0.Identifier AS "t0.Identifier",
  t0.EntityProperty1 AS "t0.EntityProperty1",
  t0.UserId AS "t0.UserId"
FROM
  Party t0
INNER JOIN
  User t1
ON
  t2.UserId = t1.Id
WHERE
  (t1.Identifier = '3f6270bbe3af4d30ad1399e66081794a' AND t0.Discriminator = 'Person')

If I omit the mapping so the query looks as follows:

var query = new Query(typeof(Person));
query.Distinct = true;
query.Join = Join.Inner<Party, User>(Person.UserIdField, "Id");
query.QueryExpression = Entity.Attribute<User>(User.IdentifierField) == userIdentifier;
var person = unitOfWork.FindOne<Person>(query);
Console.Out.WriteLine("Person EntityProperty1: {0}", person.EntityProperty1);

It generates the following invalid SQL (notice the t1.UserId):

SELECT
  Party.Id AS "Party.Id",
  Party.Discriminator AS "Party.Discriminator",
  Party.Identifier AS "Party.Identifier",
  Party.EntityProperty1 AS "Party.EntityProperty1",
  Party.UserId AS "Party.UserId"
FROM
  Party
INNER JOIN
  User t1
ON
  t0.UserId = t1.Id
WHERE
  (t1.Identifier = '3f6270bbe3af4d30ad1399e66081794a' AND Party.Discriminator = 'Person')

The SQL I was looking for looks is the following:

SELECT DISTINCT parties.Identifier
              , parties.CreatedOn
              , parties.UpdatedOn
              , parties.LockVersion
              , parties.UserId
              , parties.Discriminator
              , parties.Id
FROM
  parties
INNER JOIN users
ON parties.UserId = users.Id
WHERE
  parties.Discriminator = "Person"
  AND users.Identifier = "1336656502b44fb89d7d8deedd76c1f5"

I can only conclude that there is a problem with the way LightSpeed is generating the query.

I'd appreciate any help to get the query to work.

Thanks, Werner

Is there a particular reason you are specifying the join as:

query.Join = Join.Inner<Party, User>(Person.UserIdField, "Id");

as opposed to

query.Join = Join.Inner<Person, User>(Person.UserIdField, "Id");

Given that the query is oriented around the Person and the join is defined on Person? The reason for the additional alias being in the mix is that the Person entity is considered (and internally given an alias) which is ending up in the query because the join has been explicitly declared on Party and not Person. This ends up with the confusion leading to the invalid query.

I would consider the join from Person to User to be more correct since that is actually what you are dealing with.

Jeremy

Thanks mate. That sorted it.

Werner