The multi-part identifier "t0.FieldName" could not be bound.

Hi Guys,

Lightspeed seems to be creating some SQL incorrectly, it seems to use an alias but never actually set it up. I am using V5.0.2621.0

SELECT
  tblEmail.Email AS [tblEmail.Email]
FROM
  tblEmail
INNER JOIN
  tblDummy t1
ON
  t0.EntityId = t1.Dummy_id
INNER JOIN
  aspnet_Membership t2
ON
  t1.UserId = t2.UserId
INNER JOIN
  aspnet_Users t3
ON
  t2.UserId = t3.UserId
WHERE
  (EXISTS (
    SELECT
      t1.*
    FROM
      tblDummy t1
    WHERE
      t1.Dummy_id = tblEmail.EntityId AND
      EXISTS (
        SELECT
          t2.*
        FROM
          aspnet_Membership t2
        WHERE
          t2.UserId = t1.UserId AND
          EXISTS (
            SELECT
              t3.*
            FROM
              aspnet_Users t3
            WHERE
              t3.UserId = t2.UserId AND
              t3.UserName = 'theusernameinhere'
          )
      )
  ) AND tblEmail.EntityTypeId = 4)

So, on line 8 "t0.EntityId = t1.Dummy_id" it is trying to reference t0 - which is not defined, yet on the last line "tblEmail.EntityTypeId" - this is referenced correctly using the table name rather than t0.

The model looks like this...

        +-------+       1 to 1     +------------------+      1 to 1    +------------+
        | Dummy |------------------| AspnetMembership |----------------| AspnetUser |
        +-------+                  +------------------+                +------------+
            |
            |
            |
            |
            V
      +------------+
      | DummyEmail | This inherits from EmailAddress 
      +------------+
            |
            | SingleTableInheritance - EntityTypeId is int Disciminator
            V
+-------------------------+
| EmailAddress (tblEmail) | This has a Base Entity Class
+-------------------------+

The Base Enity class only has the following code...

public void StartAuditing()
{
    ChangeTracker.TrackingMode = ChangeTrackingMode.ChangesOnly;
}

And the Linq used to generate this is...

var emails = _uow.DummyEmails
    .Where(d => d.Dummy.AspnetMembership.User.UserName == currentUserName)
        .Select(d => d.Address).ToList();

I don't think I'm doing anything wrong, and if I replace the t0 with tblEmail in the SQL manually it works.

Is there something I can do to make this work, or does something need to be tweaked in LS :)

Cheers, Sean

Hi Sean,

This looks to be a bug, Im working on reproducing this locally so we can work on a fix. I will let you know if we need any more info or a repro project otherwise I will update this once we have made some progress on tracking this down.

Jeremy

Hi Sean,

Ive tried reproducing this here based on the description above, however I am seeing the statement being correctly generated - e.g..

SELECT
  t0.Email AS [t0.Email] 
FROM
  tblEmail t0
INNER JOIN
  tblDummy t1
ON
  t0.DummyId = t1.Id

...

Are you able to send us through a small repro project which triggers this as presumably there may be something slightly different in your actual model which I am missing. You can email this through to us at support at mindscape.co.nz or attach it to the forum post if you would prefer.

Jeremy

Thanks Jeremy - I have sent a repro project to support @

Cheers, Sean

Thanks Sean,

Ive have received this and will be having a look into this tomorrow for you.

Jeremy

Hi Sean,

The problem is that you have assigned a Key Property Reference value on the Practitioner -> PractitionerEmail relationship. This is not a valid mapping and its causing our query translation to incorrectly assume this is part of a composite key based relationship (where this value is used) which in turn is causing it to mis the alias needed on that join. You will need to remove that value which presumably has been assigned because you assigned a Column Name on that entity which collides with the existing entity property on EmailAddress. You will also need to remove that property and have it managed via the relationship (you will need to do the update in 2 parts for this, first allow the deletion of the existing column, and then the update which re-adds it back in).

After making those changes the query is being generated correctly.

Jeremy

That's great Jeremy, Thanks!

I have this somewhat working, but may need a little extra help...

• I have removed the Key Property Ref (EntityId) from the Practitioner Entity
• Removed the EntityId field From the EmailAddress Entity.
• Deleted the one to many join from Practitioner to PractionerEmail, and re-created and entered the column name as "EntityId"

Now the SQL is generated as expected and the results come back - but I cannot access EntityId field from the base EmailAddress Entity now, I can only access via the PractitionerEmail Entity.

While the join goes to PractitionerEmail, this is based on EmailAddress - so shouldn't the EntityId be based in EmailAddress ?

This is where I need it due to other code, I can change my code - but I wanted to check to make sure everything is still working as expected.

Cheers, Sean

The FK Id property sits on the entity where it is associated, so in this case this is PractitionerEmail, if the intention is to have this joined on EmailAddress then you will want to move the association accordingly. In practice the column is obviously part of the EmailAddress table, but logically its only part of the derived class when that is the one which is associated which is why the property resides there.

Jeremy

Thanks Jeremy,

I am changing my code slightly that was in the EmailAddress entity, as I actually like the way this is works with the FK being on the derived class as it means all the derived Entitys will have nice FK names instead of EntityId

Now understand how it's all working and happy : )

Cheers, Sean