Wrong sql-statement in mssql

Hi,

I am having some problems with the generated code (full code is added at the end of the post):

DELETE FROM
  parent
WHERE
  sub2.section_id = 1498;

This is a part of a batch of 6 sql-statements that is executed during a delete for one entity.

The relevant part of the model looks like this:

Class A has an 1-n relation to other1 which has an 1-n relation to parent and parent has two children sub1 and sub2. sub1 has an 1-n relation to other2. I am trying to delete an entity of class A.

Regards,

Dennis

-- Had to switch accounts

DELETE FROM
  parent
WHERE
  EXISTS (
    SELECT
      other1.*
    FROM
      other1
    WHERE
      other1.other1_id = sub1.other1_id AND
      other1.section_id = 1498
  );

DELETE FROM
  parent
WHERE
  sub2.section_id = 1498;

DELETE FROM
  other2
WHERE
  EXISTS (
    SELECT
      sub1.*
    FROM
      sub1
    WHERE
      parent.parent_id = sub1.parent_id AND
      EXISTS (
        SELECT
          parent.*
        FROM
          parent
        WHERE
          parent.parent_id = other2.parent_id AND
          EXISTS (
            SELECT
              other1.*
            FROM
              other1
            WHERE
              other1.other1_id = sub1.other1_id AND
              other1.section_id = 1498
          )
      )
  );

DELETE FROM
  other2
WHERE
  EXISTS (
    SELECT
      parent.*
    FROM
      parent
    WHERE
      parent.parent_id = other2.parent_id AND
      EXISTS (
        SELECT
          other1.*
        FROM
          other1
        WHERE
          other1.other1_id = sub1.other1_id AND
          other1.section_id = 1498
      )
  );

DELETE FROM
  other2
WHERE
  EXISTS (
    SELECT
      sub2.*
    FROM
      sub2
    WHERE
      parent.parent_id = sub2.parent_id AND
      EXISTS (
        SELECT
          parent.*
        FROM
          parent
        LEFT OUTER JOIN
          sub2
        ON
          parent.parent_id = sub2.parent_id
        WHERE
          parent.parent_id = other2.parent_id AND
          sub2.section_id = 1498
      )
  );

DELETE FROM
  other2
WHERE
  EXISTS (
    SELECT
      parent.*
    FROM
      parent
    LEFT OUTER JOIN
      sub2
    ON
      parent.parent_id = sub2.parent_id
    WHERE
      parent.parent_id = other2.parent_id AND
      sub2.section_id = 1498
  )

Hi Dennis,

I am assuming you are getting back a database level error about the statements?

If you havnt already please try updating to a more recent nightly build (if you are say on the 3.11 RTM build for example) as we did fix a number of aliasing related issues post 3.11.

If that doesnt resolve it, could you please send through a small repro project along with some sample data and we can have a look furthor into whats causing it.

Jeremy

Hi Jeremy,

I haven't found a way to attach my sample project. Therefore I will describe everything that seems relevant:

1. The Model

a) Create an entity called "Parent" with a Property "Classtype" of type char.

b) Create two entities "Child1" and "Child2" that use ClassTableInheritance to class "Parent" and use the Property "Classtype" as Discriminator. I chose 'A' and 'B' as discriminator values.

c) Create an entity called "Other", with one-to-many associations to 'Child1' and 'Child2' (This is important - remove one of the associations and everything will work fine).

2. Execute the following code within a UnitOfWork:

        Other o = new Other();
        uow.Add(o);
        uow.SaveChanges();
        uow.Remove(o);
        uow.SaveChanges();

You will get an exception that contains something like this:

German: "Der mehrteilige Bezeichner 'Child1.OtherId' konnte nicht gebunden werden."

Should be in English: "The multi-part identifier 'Child1.OtherId' could not be bound"

I hope that this helps to find the bug.

Regards,

Dennis

Hi,

I forgot to mention that I updated to the nightly from the 28th without any changes to my problem.

Regards,

Dennis

Thanks for the extra detail Dennis, I will have a look to see if I can reproduce the issue locally and get back to you - we are on break at the moment so it may take a day or two to track this down but Ill get back to you once I have an update on this.

Jeremy

Hi,

ok. I added the complete SQL output of my example.

We found another situation that seems to be quite similar to the example above as inheritance seems to play a role. There are some differences and I was not able to build a small example that reproduces the situation. I am quite sure that more than four classes are involved.

Regards,

Dennis

SELECT
  KeyTable.NextId AS [KeyTable.NextId]
FROM
  KeyTable
WITH (UPDLOCK)

--> Time:   0 ms

UPDATE
  KeyTable
SET
  NextId = 11

--> Time:   6 ms

INSERT INTO Other
(
  Id
)
VALUES
(
  1
)

--> Time:   0 ms

SELECT
  Child1.Id AS [Child1.Id]
FROM
  Child1
LEFT OUTER JOIN
  Parent
ON
  Child1.Id = Parent.Id
WHERE
  (Child1.OtherId = 1 AND Parent.Classtype = 'A')

--> Time:   3 ms

SELECT
  Child2.Id AS [Child2.Id]
FROM
  Child2
LEFT OUTER JOIN
  Child1
ON
  Child2.Id = Child1.Id
LEFT OUTER JOIN
  Parent
ON
  Child2.Id = Parent.Id
WHERE
  (Child1.OtherId = 1 AND Parent.Classtype = 'B')

--> Time:   2 ms

DELETE FROM
  Parent
WHERE
  EXISTS (
    SELECT
      Parent.*
    FROM
      Child1
    WHERE
      Child1.OtherId = 1 AND
      Child1.Id = Parent.Id
  );

DELETE FROM
  Parent
WHERE
  EXISTS (
    SELECT
      Parent.*
    FROM
      Child1
    WHERE
      Child1.OtherId = 1 AND
      Child1.Id = Parent.Id
  );

DELETE FROM
  Child1
WHERE
  Child1.OtherId = 1;

DELETE FROM
  Child2
WHERE
  Child1.OtherId = 1

--> Time:   0 ms

Hi,

I have now reduced the other problem to 6 classes, which include a situation as described above. The situation is different in one aspect as these four classes only need to exist and only one of the other two classes has to be inserted into and removed from the database.

I would like to give you more details, but I have already spend a lot of time into tracking down these problems. If you want to have more information I can send you the sample projects via email.

Regards,

Dennis

Hi Dennis,

I have reproduced the issue as described with your earlier sample model (Parent/Child1+2/Other) and included a fix for this which will be available in tonights nightly build (20110110) - let us know how you get on with this.

Thanks!

Jeremy

Your changes seem to fix the described issue and a problem with a scenario with six classes. I still have some issues with wrong sqls which seem to be related, but I have to identify exact situation.

New example for Nightly 20110110:

1. extend the model described above:

  a) Create a new Entity "ABC"

  b) Create a one-to-many-association between Parent (1) and ABC (many)

2. Run the example

3. The run will fail with the following sql:

DELETE FROM
  Parent
WHERE
  Child1.OtherId = 1;

DELETE FROM
  Parent
WHERE
  Child1.OtherId = 1;

DELETE FROM
  ABC
WHERE
  EXISTS (
    SELECT
      Parent.*
    FROM
      Parent
    LEFT OUTER JOIN
      Child1
    ON
      Parent.Id = Child1.Id
    WHERE
      Parent.Id = ABC.ParentId AND
      Child1.OtherId = 1
  );

DELETE FROM
  ABC
WHERE
  EXISTS (
    SELECT
      Child1.*
    FROM
      Child1
    WHERE
      Parent.Id = Child1.Id AND
      EXISTS (
        SELECT
          Parent.*
        FROM
          Parent
        LEFT OUTER JOIN
          Child1
        ON
          Parent.Id = Child1.Id
        WHERE
          Parent.Id = ABC.ParentId AND
          Child1.OtherId = 1
      )
  );

DELETE FROM
  ABC
WHERE
  EXISTS (
    SELECT
      Parent.*
    FROM
      Parent
    LEFT OUTER JOIN
      Child1
    ON
      Parent.Id = Child1.Id
    WHERE
      Parent.Id = ABC.ParentId AND
      Child1.OtherId = 1
  );

DELETE FROM
  ABC
WHERE
  EXISTS (
    SELECT
      Child2.*
    FROM
      Child2
    WHERE
      Parent.Id = Child2.Id AND
      EXISTS (
        SELECT
          Parent.*
        FROM
          Parent
        LEFT OUTER JOIN
          Child1
        ON
          Parent.Id = Child1.Id
        WHERE
          Parent.Id = ABC.ParentId AND
          Child1.OtherId = 1
      )
  );

DELETE FROM
  Child1
WHERE
  Child1.OtherId = 1;

DELETE FROM
  Child2
WHERE
  Child2.OtherId = 1

Thanks for detailing the updated scenario, this is a seperate issue and we are currently looking into this. Unfortunately its a bit more complicated than the previous one so it will be a bit longer to get this fixed. Will update the thread once we have made progress on resolving this.

Jeremy

This problem is getting quite urgent. Our project is getting into reviews of our customers and they are not very interested why they cannot delete anything. Do you have a date when we can expect a fix for the problem?

Unfortunately no we cannot give any specific dates for fixes, needless to say we are still working on this.

In the meantime is it possible for you to look at an application level workaround for this?

e.g. If you turn off CascadeDeletes on your Other entity and then manually removed the Child1's and Child2's from your Other instance prior to removing it (which will cause the same sequence of events, just minus the Other entity in the cascade deletion).

Jeremy

Happy to report that Ivan has crafted a fix for this and we will have this included in the next nightly build (20110202).

Update: Unfortunately it would appear this wont make the nightly because there are still some lingering issues as a result of the fix, but I would be keen to send you a small private build with the specific fix in place to verify it if this would be useful. Hopefully we can get this in for tommorows nightly.

Jeremy

Hi Dennis,

Another update on this.  We now have a candidate fix for the lingering issues Jeremy mentioned, and it will be in the nightly build, but we would still like to test it with you to ensure it resolves your problem (and that it doesn't introduce any new ones!).  We have been testing it based on your description of your model and usage, but we are concerned that the fix is fairly brittle and we want to work with you to make sure it copes with your real model.

Furthermore, you will probably need to make a small change to your model.  As I understand it, you have two derived classes, Child1 and Child2, each of which has an association called Other.  We have a known issue when two derived classes contain an association with the same name.  You will therefore need to change the name of one of the associations, e.g. to Other2.  (You can do this without affecting your database schema by using ColumnAttribute in the code or Backreference Column Name in the designer to keep the same foreign key column name e.g. OtherId.)

Hi,

first of all I want to thank you and to promise that I will be more patient next time.

My first checks show that we can delete more Objects than before but still not all. I am currently checking your suggestions throughout the model. I will tell you more about this later.

Regards,

Dennis

Hi,

I just had the chance to test the current build 20110206 with the following result: It does not work, but I think the error is now slightly different.

I will try to give you a description of my model (I would also send you my example-project - that would make things a lot easier):

Entity --> Other1 --> Child1 --X Parent --> ABC

Entity --> Child2 --X Parent

A --> B: one-to-many relations (A has may Bs)

A --X B: are inheritances (A is a child of B) and B has a column called "type" of type "char". Discriminator values are the ending digits of the children.

The code is:

      using (ModelUnitOfWork uow = DatabaseHelper.Context.CreateUnitOfWork()) {
        Entity u = new Entity();
        uow.Add(u);
        uow.SaveChanges();
        uow.Remove(u);
        uow.SaveChanges(); // Exception is thrown here
      }

Regards,

Dennis

--

DELETE FROM
  Parent
WHERE
  EXISTS (
    SELECT
      Other1.*
    FROM
      Other1
    WHERE
      Other1.Id = Child1.Other1Id AND
      Other1.EntityId = 1
  );

DELETE FROM
  Parent
WHERE
  EXISTS (
    SELECT
      Child2.*
    FROM
      Child2
    WHERE
      Child2.EntityId = 1 AND
      Parent.Id = Child2.Id
  );

DELETE FROM
  ABC
WHERE
  EXISTS (
    SELECT
      Parent.*
    FROM
      Parent
    LEFT OUTER JOIN
      Child2
    ON
      Parent.Id = Child2.Id
    WHERE
      Parent.Id = ABC.ParentId AND
      Child2.EntityId = 1
  );

DELETE FROM
  ABC
WHERE
  EXISTS (
    SELECT
      Parent.*
    FROM
      Parent
    WHERE
      Parent.Id = ABC.ParentId AND
      EXISTS (
        SELECT
          Other1.*
        FROM
          Other1
        WHERE
          Other1.Id = Child1.Other1Id AND
          Other1.EntityId = 1
      )
  );

DELETE FROM
  Child2
WHERE
  Child2.EntityId = 1;

DELETE FROM
  Child1
WHERE
  EXISTS (
    SELECT
      Other1.*
    FROM
      Other1
    WHERE
      Other1.Id = Child1.Other1Id AND
      Other1.EntityId = 1
  );

DELETE FROM
  Other1
WHERE
  Other1.EntityId = 1

Yes, please send a project (and ideally a sample database) that we can use to repro the issue(s).  The hierarchy and associations you describe are not quite what we understood from your previous message so a definitive working model would be invaluable!  A console or NUnit test project would be ideal if possible -- thanks!

I just send you a sample console project which is stripped down to about the minimum what is required. I hope I guessed the right email to send it to.

Thanks for the repro.  We were able to reproduce the issue and have committed a candidate fix which will be in tonight's nightly build (available from about 1200 GMT).  Let us know if this finally sorts things out!

Thanks. This part works fine now.