Hello,

I have an error I'm not able to find. I sollicitate any generous people to try to help me out with this fuzzy bug.

The code is "as is".

In the following code crash at the commented line "Error: unique key...". The database unique condition help me detect the exception that I try to insert an entity of type "evLocLocRes" twice. But what is weird, it is that I deleted that same record few lines before (see: passedByHere variable wich is true when the error happen).

Any help would be appreciate !!!

        public bool UpdateEvenementLoc(Int32 localisationResultatId)
        {
            Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "UpdateEvenementLoc");
            try
            {
                LocalisationResultatEntity loc;

                PrefetchPath prefetchPathLoc = new PrefetchPath((int)EntityType.LocalisationResultatEntity);
                prefetchPathLoc.Add(LocalisationResultatEntity.PrefetchPathTopologie);

                LocalisationResultatCollection locs = new LocalisationResultatCollection();
                trans.Add(locs);

                locs.GetMulti(LocalisationResultatFields.LocalisationResultatId == localisationResultatId, prefetchPathLoc);
                if (locs.Count == 0)
                {
                    Log.WriteError("Unable to update EvenementLoc because no LocalisationResultat exists with localisationResultatId = " + localisationResultatId);
                    return false;
                }

                loc = locs[0];

                Interval locInterval = GetLocInterval(loc);
                if (locInterval == null)
                {
                    Log.WriteError("Invalid mesures for a localisation. Untouching measures for LocalisationResultat: " + localisationResultatId);
                    return false;
                }

                EvenementLocCollection evLocs = new EvenementLocCollection();
                trans.Add(evLocs);
                evLocs.GetMulti(
                    EvenementLocFields.LigneId == loc.Topologie.LigneId &
                    EvenementLocFields.IntervalFin >= locInterval.DateDeb & 
                    EvenementLocFields.IntervalDebut <= locInterval.DateFin);

                bool passedByHere = false;
                trans.Add(loc.EvenementLocLocRes);
                foreach (EvenementLocLocResEntity evLocLocRes in loc.EvenementLocLocRes)
                {
                    trans.Add(evLocLocRes);
                    evLocLocRes.Delete();
                    passedByHere = true;
                }

                if (evLocs.Count == 0)
                {
                    EvenementLocEntity evLoc = new EvenementLocEntity();
                    trans.Add(evLoc);
                    evLoc.IntervalDebut = locInterval.DateDeb;
                    evLoc.IntervalFin = locInterval.DateFin;
                    evLoc.LigneId = loc.Topologie.LigneId;
                    evLoc.Save();
                    // Debug.Print(evLoc.EvenementLocId.ToString());

                    EvenementLocLocResEntity evLocLocres = new EvenementLocLocResEntity();
                    trans.Add(evLocLocres);
                    evLocLocres.EvenementLoc = evLoc;
                    evLocLocres.LocalisationResultat = loc;
                    evLocLocres.Save();

                    SetEvLocBestLoc(evLoc);
                }
                else
                {
                    foreach (EvenementLocEntity evLoc in evLocs)
                    {
                        EvenementLocLocResEntity evLocLocRes = new EvenementLocLocResEntity();
                        
                        trans.Add(evLocLocRes);
                        evLocLocRes.EvenementLocId = evLoc.EvenementLocId;
                        evLocLocRes.LocalisationResultatId = loc.LocalisationResultatId;
                        evLocLocRes.Save(); // Error: unique key... already exists and passedByHere == true

                        trans.Add(evLoc);
                        if (locInterval.DateDeb > evLoc.IntervalDebut)
                        {
                            evLoc.IntervalDebut = locInterval.DateDeb;
                        }
                        if (locInterval.DateFin < evLoc.IntervalFin)
                        {
                            evLoc.IntervalFin = locInterval.DateFin;
                        }
                        evLoc.Save();

                        SetEvLocBestLoc(evLoc);
                        SetMeteoIfAvailable(evLoc);
                    }
                }
    
                trans.Commit();
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                trans.Dispose();
            }

            return true;
        }

Thanks.

Exception:

An exception was caught during the execution of an action query: ORA-00001: violation de contrainte unique (MILE.EVENEMENT_LOC_LOC_RES_UK1). Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

I'm having trouble trying to work out what the code is trying to achieve - could you give us a commented pseudo-code version that explains what is going on...?

Matt

Yes for sure...

Mainly, there is 3 tables (entities): LocalisationResultat, EvenementLoc wich are 2 tables in a relation m:n through EvenementLocLocRes.

Those 3 entities are the results of a program that calculates location when electric problem arise on an electric distribution network. LocalisationResultat is the result of this calculation. We improve our calculation algorithm once in a while. We then modifiy existing LocalisationResultat entities.

We need to keep EvenementLoc (never destroy any of those records). When we modifiy LocalisationResultat, we then have to rejoin it to sometimes different evenementLoc. To do this we ensure that any existing relations between the newly recalculated LocalisationResultat and any EvenementLoc are deleted first; and then recreate relations with appropriate EvenementLoc.

What happen is that deletetion of existing relations (EvenementLocLocRes) never occurs (see delete near line "passedByHere = true;"). Traces do not show any deletions.

Just to make it simplier, real interesting code begin at the first "foreach" (the loop where I try to delete relations (EvenementLocLocRes entities)).

Please try the following to prefetch the EvenementLocLocRes:

                PrefetchPath prefetchPathLoc = new PrefetchPath((int)EntityType.LocalisationResultatEntity);
                prefetchPathLoc.Add(LocalisationResultatEntity.PrefetchPathTopologie);
                prefetchPathLoc.Add(LocalisationResultatEntity.PrefetchPath.EvenementLocLocRes);

                LocalisationResultatCollection locs = new LocalisationResultatCollection();
                trans.Add(locs);

                locs.GetMulti(LocalisationResultatFields.LocalisationResultatId == localisationResultatId, prefetchPathLoc);

And then instead of:

            bool passedByHere = false;
            trans.Add(loc.EvenementLocLocRes);
            foreach (EvenementLocLocResEntity evLocLocRes in loc.EvenementLocLocRes)
            {
                trans.Add(evLocLocRes);
                evLocLocRes.Delete();
                passedByHere = true;
            }

Please try:

                bool passedByHere = false;
                trans.Add(loc.EvenementLocLocRes);
                int deletedRecords = loc.EvenementLocLocRes.DeleteMulti();

And examine the value of deletedRecords, and check the generated SQL for the Delete statments.

Thanks a lot Walaa.

I've been implemented it yesterday. I've put an Assert and restart all the process but everything went ok (all the night). I'm in a middle of a formation (Wednesday, yesterday and today) all day long. I do not have enough time to test properly now. I will get back to you on monday (I add a reminder on outlook for monday morning about it). Thanks.

Have a good weekend !

Hello Walaa,

I've just made all the modifications mentionned. I still have the same problem. There is no sql statements produced (executed) on the database for the DeleteMulti. I added portion of the traces where I think the delete should happen.

Index of added entity: 0 Method Exit: CollectionCore.PerformAdd Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery Method Exit: DaoBase.PerformGetMultiAction Method Enter: EntityCollectionBase.PerformDeleteMulti Method Enter: CollectionCore.Remove Entity to Remove Description: Entity: HQ.Mile.Data.EntityClasses.EvenementLocLocResEntity. ObjectID: 82b10bac-19b4-43c3-b0a4-990a1ca88476 Method Exit: CollectionCore.Remove Method Exit: EntityCollectionBase.PerformDeleteMulti Method Enter: EntityBase.CheckForRefetch Method Exit: EntityBase.CheckForRefetch: state is not OutOfSync Method Enter: EntityBase.CheckForRefetch Method Exit: EntityBase.CheckForRefetch: state is not OutOfSync Method Enter: EntityBase.CheckForRefetch Method Exit: EntityBase.CheckForRefetch: state is not OutOfSync Method Enter: EntityBase.CheckForRefetch Method Exit: EntityBase.CheckForRefetch: already refetching or isdirty. Method Enter: EntityBase.Save(2) Active Entity Description: Entity: HQ.Mile.Data.EntityClasses.EvenementLocLocResEntity. ObjectID: 114d00d3-a663-4ad3-b76e-59c8309df88b Method Enter: DaoBase.PersistQueue

Any other idea ?

int deletedRecords = loc.EvenementLocLocRes.DeleteMulti();

So what was the value of deletedRecords? Also if you can check the value of loc.EvenementLocLocRes.Count perior to the above line.

int count = loc.EvenementLocLocRes.Count = 1 int deletedRecords = loc.EvenementLocLocRes.DeleteMulti() = 1

I do not regularly check what LLBLGen traces looklikes. But I'm expecting to see the "Delete from ...." sql command in the traces (output pane while debug (diagnostics) level is set to 4). Shouldn't it be there ???

Wally wrote:

I do not regularly check what LLBLGen traces looklikes. But I'm expecting to see the "Delete from ...." sql command in the traces (output pane while debug (diagnostics) level is set to 4). Shouldn't it be there ???

You should have something like:

<system.diagnostics>
  <switches>
    <add name="SqlServerDQE" value="4" />
  </switches>
</system.diagnostics>

I¿m new in this thread, but I think a couple of thing could simplify the resolution:

• What LLBLGen Runtime libraries version are you using? (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

• Simplify the code (5-10 lines) so it shows the issue.

• Check the generated sql

• Debug in the foreach part, look if nothing weird is happening when you delete() in the loop. You also could collect the IDs in the for and then perform a DeleteEntitiesDirectly.

Hello David,

Thanks for the help.

LLBLGen version is: 2.6 Final, July 27th, 2009

I'm using Oracle, not SqlServer:

<system.diagnostics>
    <switches>
        <add name="SqlServerDQE" value="0"/>
        <add name="AccessDQE" value="0"/>
        <add name="OracleDQE" value="4"/>
        <add name="FirebirdDQE" value="0"/>
        <add name="MySqlDQE" value="0"/>
        <add name="DB2DQE" value="0"/>
        <add name="PostgeSqlDQE" value="0"/>
        <add name="SybaseAsaDQE" value="0"/>
        <add name="SybaseAseDQE" value="0"/>
        <add name="ORMGeneral" value="4"/>
        <add name="ORMStateManagement" value="4"/>
        <add name="ORMPersistenceExecution" value="4"/>
        <!-- 4 = verbose  3 = Info  -->


        <!--<add name="System.Net" value="31" />
        <add name="System.Net.Sockets" value="Error" />
        <add name="System.Net.Cache"  value="Verbose" />-->
    </switches>

I already got all traces, see below on previous message for part of it.

I got all "select" but no "delete" in the output pane. It appears that for any reason the "delete" statement is never called over the database for the DeleteMulti method.

I can simplify but it will take very very long to create another projet on a database with SqlServer and It will not be Oracle... If I really have to, I can do it... But... do you think to anything else before I go with a sample that will take very long create ??? Do you know a database that I can download that LLBL use as reference ?

daelmo wrote:

• Debug in the foreach part, look if nothing weird is happening when you delete() in the loop. You also could collect the IDs in the for and then perform a DeleteEntitiesDirectly.

Did you check this? (inspect the loop and see what happens to the delete method? Anyway, I will try to reproduce your scenario

I reduced a little bit your code to understand what are you doing in there. I remove the first unnecessary LocalisationResultatCollection. Also, remove some unnecessary adds to the transaction. Please try this:

public bool UpdateEvenementLoc(Int32 localisationResultatId)
{
    Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "UpdateEvenementLoc");
    
    // result loc - prefetch path
    PrefetchPath prefetchPathLoc = new PrefetchPath((int)EntityType.LocalisationResultatEntity);
    prefetchPathLoc.Add(LocalisationResultatEntity.PrefetchPathTopologie);
    prefetchPathLoc.Add(LocalisationResultatEntity.PrefetchPathEvenementLocLocRes);

    // fetch the loc result with prefetchPath
    LocalisationResultatEntity loc = new LocalisationResultatEntity(localisationResultatId, prefetchPathLoc);
    
    // no loc result available
    if (loc.IsNew)
    {
        Log.WriteError("Unable to update EvenementLoc because no LocalisationResultat exists with localisationResultatId = " + localisationResultatId);
        return false;
    }

    // get some interval object
    Interval locInterval = GetLocInterval(loc);
    if (locInterval == null)
    {
        Log.WriteError("Invalid mesures for a localisation. Untouching measures for LocalisationResultat: " + localisationResultatId);
        return false;
    }

    // get locs filtered by interval and topologie lignedid
    EvenementLocCollection evLocs = new EvenementLocCollection();                       
    evLocs.GetMulti(
        EvenementLocFields.LigneId == loc.Topologie.LigneId &
        EvenementLocFields.IntervalFin >= locInterval.DateDeb &
        EvenementLocFields.IntervalDebut <= locInterval.DateFin);

    try
    {
        // delete all locloc associated
        // this should delete associated loclocRes 
        trans.Add(loc.EvenementLocLocRes);
        int deletedRecords = loc.EvenementLocLocRes.DeleteMulti();              

        // no filtered locs found
        if (evLocs.Count == 0)
        {
            // create new
            EvenementLocEntity evLoc = new EvenementLocEntity();                    
            evLoc.IntervalDebut = locInterval.DateDeb;
            evLoc.IntervalFin = locInterval.DateFin;
            evLoc.LigneId = loc.Topologie.LigneId;
            trans.Add(evLoc);
            evLoc.Save();                   

            // create a new locloc
            EvenementLocLocResEntity evLocLocres = new EvenementLocLocResEntity();                  
            evLocLocres.EvenementLoc = evLoc;
            evLocLocres.LocalisationResultat = loc;
            trans.Add(evLocLocres);
            evLocLocres.Save();

            // custom biz...
            SetEvLocBestLoc(evLoc);
        }

        // there are filtered locs found
        else
        {
            // navigate through locs
            foreach (EvenementLocEntity evLoc in evLocs)
            {
                // create a new locloc                      
                // review this part....                 
                EvenementLocLocResEntity evLocLocRes = new EvenementLocLocResEntity();
                evLocLocRes.EvenementLocId = evLoc.EvenementLocId; // set the loc
                evLocLocRes.LocalisationResultatId = loc.LocalisationResultatId; // set the result loc
                trans.Add(evLocLocRes);
                evLocLocRes.Save(); // Error: unique key... already exists and passedByHere == true

                // set some fields on loc                       
                if (locInterval.DateDeb > evLoc.IntervalDebut)
                {
                    evLoc.IntervalDebut = locInterval.DateDeb;
                }
                if (locInterval.DateFin < evLoc.IntervalFin)
                {
                    evLoc.IntervalFin = locInterval.DateFin;
                }
                trans.Add(evLoc);
                evLoc.Save();

                // custom biz
                SetEvLocBestLoc(evLoc);
                SetMeteoIfAvailable(evLoc);
            }
        }

        // commit changes
        trans.Commit();
    }
    catch (Exception)
    {
        trans.Rollback();
        throw;
    }
    finally
    {
        trans.Dispose();
    }

    return true;
}

The delete should take place, unless of course, there is nothing to delete. In such case, the problem must be on the Save of the new entity (EvenementLocLocResEntity).

Also, you have custom methods in your code snippet. Just check that those aren't doing anything weird in your entities. If you experiment problems again, maybe you should attach your file to see the whole picture.

If you have problems, please post the Generated SQL. I know you have the trace ready but just for double-check. It should be like:

<system.diagnostics>
  <switches>
    <add name="OracleDQE" value="4" />
    <add name="ORMPersistenceExecution" value="4" />
  </switches>
</system.diagnostics>

Thanks for the help.

I cannot remove lines of code you ask me to do. They are needed. If I remove them, I could be in a situation where I work on an entity that has been deleted in the meantime.

I already set debug traces properly as mentionned earlier.

To simplify my problem for a specific case where the problem happen:

loc.EvenementLocLocRes.Count is egal to "1".

The code execute : "

int deletedRecords = loc.EvenementLocLocRes.DeleteMulti();

Then I have deletedRecords egals to "1" and traces for that method are:

Method Enter: EntityCollectionBase.PerformDeleteMulti
Method Enter: CollectionCore.Remove
Entity to Remove Description: 
    Entity: HQ.Mile.Data.EntityClasses.EvenementLocLocResEntity. ObjectID: 4026ef0e-074e-403b-a428-0876fe2ae49a
Method Exit: CollectionCore.Remove
Method Exit: EntityCollectionBase.PerformDeleteMulti

As you can see, no delete method are executed on the database. It only remove the record from the collection in memory, why ??? Is this a bug ?

Do I need to call "Save()" after DeleteMulti() ????? If yes why ?

CollectionX.DeleteMulti() does only delete in memory.

CollectionX.DeleteMulti(null) do delete every "CollectionXEntity" records. Not only records records in CollectionX collection. ie: if I do employeeCollection.GetMulti(EmployeeFields.BossId = 2); employeeCollection.DeleteMulti(null); Then I will delete all and every employees completely !!! WOW !!! What the hell the designer of this method has thinking of ??????? ...What is the purpose of an object if you apply methods over all the entities, not only the ones being in the collection ? Why not having implement that method as static for the entityCollection class ???

The add a cherry on the sunday, the documentation says that DeleteMulti(with params) will be part of its own transaction.

Am I silly or there is no way to delete a record that are part of a transaction ??? How could I delete elements of a collection and have the delete being part of a transaction ???

Wally wrote:

The add a cherry on the sunday, the documentation says that DeleteMulti(with params) will be part of its own transaction.

Am I silly or there is no way to delete a record that are part of a transaction ??? How could I delete elements of a collection and have the delete being part of a transaction ???

Quoting the manual:

All DeleteMulti*() methods work directly on the persistent storage except one, the DeleteMulti() method which does not take any parameters. That one works with the objects inside the collection and deletes them one by one from the persistent storage using an own transaction if the current collection isn't part of an existing transaction. (See for more information about transactions the section Transactions).

All DeleteMulti() overloads that accept parameters work directly on the database, and hence there is no implicit transaction used for one Delete command.

But the one without parameters works on all entities inside the collection, and it deletes them one by one, and hence the use of a transaction, to roll back entities if the database failed to delete one of them.

As for the DeleteMulti(null) design part, please check this post for a good explanation: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9689&StartAtMessage=0&#54027

Thanks a lot Walaa for your support.

The info in the recommended link is very fine. Like Frans said in http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9689&StartAtMessage=0&#54027 and also in the doc:

...the DeleteMulti() method which does not take any parameters. That one works with the objects inside the collection and deletes them one by one from the persistent storage using an own transaction if the current collection isn't part of an existing transaction.

DeleteMulti() without args is suppose to delete on the persitent storage. Then it is suppose to send a "Delete... " sql command to the database.

Look at the video I attached and see that the GetMulti does send a "select" to the database but deleteMulti does nothing, absolutely nothing to the database... or there is something I don't understand ????????

Althought not necessary, I think the video talk by itself (very self explanatory with traces for each lines). I upload the video here because I can't (or I haven't found how) in the previous message.

This is the offending code and its related traces

                EvenementLocCollection evLocs = new EvenementLocCollection();
                trans.Add(evLocs);
                evLocs.GetMulti(
                    EvenementLocFields.LigneId == loc.Topologie.LigneId &
                    EvenementLocFields.IntervalFin >= locInterval.DateDeb & 
                    EvenementLocFields.IntervalDebut <= locInterval.DateFin);
                bool passedByHere = false;
                //foreach (EvenementLocLocResEntity evLocLocRes in loc.EvenementLocLocRes)
                //{
                //  trans.Add(evLocLocRes);
                //  evLocLocRes.Delete();
                //  passedByHere = true;
                //}
                //loc.EvenementLocLocRes.Clear();

                // 3- Walaa
                trans.Add(loc.EvenementLocLocRes);
                int count = loc.EvenementLocLocRes.Count;
                Debug.Print(count.ToString());
                int deletedRecords = loc.EvenementLocLocRes.DeleteMulti();

Method Enter: CollectionCore.Clear Method Exit: CollectionCore.Clear Method Enter: DaoBase.PerformGetMultiAction Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT "MILE"."EVENEMENT_LOC"."EVENEMENT_LOC_ID" AS "EvenementLocId", "MILE"."EVENEMENT_LOC"."MEILLEUR_LOC_RES_ID" AS "MeilleurLocResId", "MILE"."EVENEMENT_LOC"."INTERVAL_DEBUT" AS "IntervalDebut", "MILE"."EVENEMENT_LOC"."INTERVAL_FIN" AS "IntervalFin", "MILE"."EVENEMENT_LOC"."LIGNE_ID" AS "LigneId", "MILE"."EVENEMENT_LOC"."SUIVI_ID" AS "SuiviId", "MILE"."EVENEMENT_LOC"."METEO_TEMPERATURE" AS "MeteoTemperature", "MILE"."EVENEMENT_LOC"."METEO_VENT_MAX" AS "MeteoVentMax", "MILE"."EVENEMENT_LOC"."METEO_RAFALE_MAX" AS "MeteoRafaleMax", "MILE"."EVENEMENT_LOC"."METEO_PLUIE" AS "MeteoPluie", "MILE"."EVENEMENT_LOC"."METEO_NEIGE" AS "MeteoNeige", "MILE"."EVENEMENT_LOC"."METEO_VERGLAS" AS "MeteoVerglas", "MILE"."EVENEMENT_LOC"."DATE_MODIFICATION" AS "DateModification" FROM "MILE"."EVENEMENT_LOC" WHERE ( ( ( "MILE"."EVENEMENT_LOC"."LIGNE_ID" = :LigneId1 AND "MILE"."EVENEMENT_LOC"."INTERVAL_FIN" >= :IntervalFin2) AND "MILE"."EVENEMENT_LOC"."INTERVAL_DEBUT" <= :IntervalDebut3)) Parameter: :LigneId1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 26. Parameter: :IntervalFin2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2005-09-29 08:48:18. Parameter: :IntervalDebut3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2005-09-29 08:48:22.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery Executed Sql Query: Query: SELECT "MILE"."EVENEMENT_LOC"."EVENEMENT_LOC_ID" AS "EvenementLocId", "MILE"."EVENEMENT_LOC"."MEILLEUR_LOC_RES_ID" AS "MeilleurLocResId", "MILE"."EVENEMENT_LOC"."INTERVAL_DEBUT" AS "IntervalDebut", "MILE"."EVENEMENT_LOC"."INTERVAL_FIN" AS "IntervalFin", "MILE"."EVENEMENT_LOC"."LIGNE_ID" AS "LigneId", "MILE"."EVENEMENT_LOC"."SUIVI_ID" AS "SuiviId", "MILE"."EVENEMENT_LOC"."METEO_TEMPERATURE" AS "MeteoTemperature", "MILE"."EVENEMENT_LOC"."METEO_VENT_MAX" AS "MeteoVentMax", "MILE"."EVENEMENT_LOC"."METEO_RAFALE_MAX" AS "MeteoRafaleMax", "MILE"."EVENEMENT_LOC"."METEO_PLUIE" AS "MeteoPluie", "MILE"."EVENEMENT_LOC"."METEO_NEIGE" AS "MeteoNeige", "MILE"."EVENEMENT_LOC"."METEO_VERGLAS" AS "MeteoVerglas", "MILE"."EVENEMENT_LOC"."DATE_MODIFICATION" AS "DateModification" FROM "MILE"."EVENEMENT_LOC" WHERE ( ( ( "MILE"."EVENEMENT_LOC"."LIGNE_ID" = :LigneId1 AND "MILE"."EVENEMENT_LOC"."INTERVAL_FIN" >= :IntervalFin2) AND "MILE"."EVENEMENT_LOC"."INTERVAL_DEBUT" <= :IntervalDebut3)) Parameter: :LigneId1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 26. Parameter: :IntervalFin2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2005-09-29 08:48:18. Parameter: :IntervalDebut3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2005-09-29 08:48:22.

Method Enter: CollectionCore.PerformAdd Entity to Add Description: Entity: HQ.Mile.Data.EntityClasses.EvenementLocEntity. ObjectID: 9cac466b-a993-4d46-9fd6-54c4ee71c702 PrimaryKey field: EvenementLocId. Type: System.Int32. Value: 49350 Index of added entity: 0 Method Exit: CollectionCore.PerformAdd Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery Method Exit: DaoBase.PerformGetMultiAction 1 Method Enter: EntityCollectionBase.PerformDeleteMulti Method Enter: CollectionCore.Remove Entity to Remove Description: Entity: HQ.Mile.Data.EntityClasses.EvenementLocLocResEntity. ObjectID: baefe87d-b2a6-4d2a-8c7c-d96383269480 Method Exit: CollectionCore.Remove Method Exit: EntityCollectionBase.PerformDeleteMulti

My experience with LLBL tells me that no cmd "delete" have been sent to Oracle. If yes, the trace is not there. If no, why ?

Hope this time it will be fine

Code to create database and populate tables

  CREATE TABLE "MILE"."ANALYST" 
   (    "ANALYST_ID" NUMBER(9,0) NOT NULL ENABLE, 
    "NAME" VARCHAR2(20 BYTE), 
     CONSTRAINT "ANALYST_PK" PRIMARY KEY ("ANALYST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

  CREATE TABLE "MILE"."PROJECT" 
   (    "PROJECT_ID" NUMBER(9,0) NOT NULL ENABLE, 
    "NAME" VARCHAR2(20 BYTE), 
     CONSTRAINT "PROJECT_PK" PRIMARY KEY ("PROJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

  CREATE TABLE "MILE"."PROJECT_ANALYST" 
   (    "PROJECT_ID" NUMBER(9,0) NOT NULL ENABLE, 
    "ANALYST_ID" NUMBER(9,0) NOT NULL ENABLE, 
     CONSTRAINT "PROJECT_ANALYST_ANALYST_FK1" FOREIGN KEY ("ANALYST_ID")
      REFERENCES "MILE"."ANALYST" ("ANALYST_ID") ENABLE, 
     CONSTRAINT "PROJECT_ANALYST_PROJECT_FK1" FOREIGN KEY ("PROJECT_ID")
      REFERENCES "MILE"."PROJECT" ("PROJECT_ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

INSERT INTO "MILE"."ANALYST" (ANALYST_ID, NAME) VALUES ('1', 'a1');
INSERT INTO "MILE"."ANALYST" (ANALYST_ID, NAME) VALUES ('2', 'a2');
INSERT INTO "MILE"."ANALYST" (ANALYST_ID, NAME) VALUES ('3', 'a3');

INSERT INTO "MILE"."PROJECT" (PROJECT_ID, NAME) VALUES ('1', 'p1');
INSERT INTO "MILE"."PROJECT" (PROJECT_ID, NAME) VALUES ('2', 'p2');
INSERT INTO "MILE"."PROJECT" (PROJECT_ID, NAME) VALUES ('3', 'p3');

INSERT INTO "MILE"."PROJECT_ANALYST" (PROJECT_ID, ANALYST_ID) VALUES ('1', '1');
INSERT INTO "MILE"."PROJECT_ANALYST" (PROJECT_ID, ANALYST_ID) VALUES ('1', '2');
INSERT INTO "MILE"."PROJECT_ANALYST" (PROJECT_ID, ANALYST_ID) VALUES ('1', '3');
INSERT INTO "MILE"."PROJECT_ANALYST" (PROJECT_ID, ANALYST_ID) VALUES ('2', '2');

Code to test

            Transaction trans = new Transaction(IsolationLevel.ReadCommitted, "test");
            AnalystEntity analyst2 = new AnalystEntity();
            trans.Add(analyst2);
            analyst2.FetchUsingPK(2);

            ProjectAnalystCollection projectAnalyst = analyst2.ProjectAnalyst;
            trans.Add(projectAnalyst);

            projectAnalyst.DeleteMulti();

            ProjectEntity project1 = new ProjectEntity();
            trans.Add(project1);
            project1.FetchUsingPK(1);

            int count = project1.AnalystCollectionViaProjectAnalyst.Count;
            
            trans.Commit();

Break on the trans.commit and see that count = 3 instead of 2.

Indeed I reproduced the problem. Weird. I'm looking deeper to see what is happening.

(Edit) In the meantime, please use this:

projectAnalyst.DeleteMulti(ProjectAnalystFields.AnalystId == analyst2.AnalystId);

I found the issue. I don't know why I didn't see it before as it is pretty obvious.

It is not in LLBLGenPro code. The thing is that your PROJECT_ANALYST table has not PRIMARY KEY. This is a problem with individual Deletes/Fetchs/Refetchs as the framework can't guarantee a unique delete/fetch. So the template engine generates additional method overrides to prevent this. The DeleteMulti(no-parameters) executes individual deletes internally, but no deletes are executed due to above analysis.

As I see you have some options:

A. Set a primary key in your PROJECT_ANALYST.

OR

B. Override the Delete method of the ProjectAnalyst and implement your own delete mechanism for this entity.

OR

C. Perform a DeleteMulti(predicate) as I advised in above post, passing the corresponding predicates.

Additional advices: - You don't have to add a entity/collection to a transaction if you are just fetching. - First fetch everything you need, open transaction, make changes, then commit/rollback. The closest you do this, the best behavior you can expect from your code.

Ouch !!!

mmmmmmmmmmmm !?!?

First, thanks a lot for the answer. it is now very clear!!!

I have some comments:

• The easiest one, the fetching need to be in the transaction. It is to ensure coherence (hope its the right english word). It is to work on valid entity, otherwise the delete could crach on records already deleted by another transaction(query) or make assumption that you have, for example, 5 childs when in reality one of them has been added or deleted in the meantime.

• Another one. I fall in this bug easily. The support team fall in this bug also. I'm sure that few, to not say many, other peoples have surely fall into this bug. Nothing, nowhere let us know that no delete has never happen, no exception due to a missing PK and no message in the debug output-pane, even if I set up my app.config to diagnostics level of "4". I think it would be necessary to let know the programmer and perhaps throw an exception (i'm not sure about this one, I think it would have been nice but would break backward code compatibility). Do I have to add a suggestion for this ?

• The hardest one. I consult my collegue when I saw your answer because I didn't know what to think about it. My first reflex was that you detect m:n relation, then why don't just create the appropriate code to do the delete as needed. Then I came with the fact that LLBLGen don't seems to support more than one field PK. It would then make an exception to the standard behavior. I don't think that it is a nice idea to have more than one PK, except in this case, where I'm still not totally sure about it (perhaps I should have one to have same config every where ?). It is not totally clear in my head what it is better way to do it between adding or not a PK on a table that just do the join between 2 tables in an m:n relations. Usually I see an ORM has the helper to solve access to the DB. Does LLBLGen could do the job ? ... find that the table that is only there to join 2 m:n tables (throught a condition to have a PK on both fields) and do the job of delete the join ? Having this feature would certainly avoid this thread... at least one good think!!! :-)

• Before the last one, a question. Usually I only add method to new partial class. As part of the suggestion to override the methode "Delete", I saw that this method is already overriden in the generated class. If I change the code, what will prevent the LLBLGen generator from erasing what I change in the next pass of generation?

• The last one: What's the best way to solve this behavior ? Your opinion ?

Thanks!

Another one. I fall in this bug easily. The support team fall in this bug also. I'm sure that few, to not say many, other peoples have surely fall into this bug. Nothing, nowhere let us know that no delete has never happen, no exception due to a missing PK and no message in the debug output-pane, even if I set up my app.config to diagnostics level of "4". I think it would be necessary to let know the programmer and perhaps throw an exception (i'm not sure about this one, I think it would have been nice but would break backward code compatibility). Do I have to add a suggestion for this ?

We will look into this suggestion.

• The hardest one. I consult my collegue when I saw your answer because I didn't know what to think about it. My first reflex was that you detect m:n relation, then why don't just create the appropriate code to do the delete as needed. Then I came with the fact that LLBLGen don't seems to support more than one field PK. It would then make an exception to the standard behavior. I don't think that it is a nice idea to have more than one PK, except in this case, where I'm still not totally sure about it (perhaps I should have one to have same config every where ?). It is not totally clear in my head what it is better way to do it between adding or not a PK on a table that just do the join between 2 tables in an m:n relations. Usually I see an ORM has the helper to solve access to the DB. Does LLBLGen could do the job ? ... find that the table that is only there to join 2 m:n tables (throught a condition to have a PK on both fields) and do the job of delete the join ? Having this feature would certainly avoid this thread... at least one good think!!! :-)

LLBLGen Pro supports combined PKs (more than one field).

• Before the last one, a question. Usually I only add method to new partial class. As part of the suggestion to override the methode "Delete", I saw that this method is already overriden in the generated class. If I change the code, what will prevent the LLBLGen generator from erasing what I change in the next pass of generation?

In this case add an extension method, for example DeleteEx() to the partial class. The partial class will stay intact across re-generations.

• The last one: What's the best way to solve this behavior ? Your opinion ?

Any of the following would solve your problem. 1- Add a surrogate PK to your m:n table 2- Set the 2 FK fields as the a combined PK. 3- Leave the database as is and set the 2 FK fields as part of the Primary Key in the LLBLGen Pro Designer -> Edit Entity.