I'm using adapter.

Runtime version states: v2.0.50727 (Version indicates 2.6.0.0)

I have 4 LLBLGen Pro dll in project

1 is Access for brevity (ZipCode) 1 is AccessDBSpecific (ZipCodeDBSpecific) 1 is Access again for brevity (PostalCode) 1 is AccessDBSpecific (PostalCodeDBSpecific)

In a worker thread I'm attempting to copy data from ZipCode to PostalCode, since the data source is from a client, I don't trust it as far as duplication... So, I get a collection of 79,000 rows from ZipCode and in a foreach loop I create a PostalCodeEntity, populate it from ZipCodeEntity. So I have an adapter for ZipCode and I have an adapter for PostalCode and I create a third adapter from the PostalCode dll to do a fetch on the PostalCode table utilitizing the HashCode created as an ID for the PostalCodeEntity previously populated to see if one already exist.

I would expect, and this is where I might be wrong, if the Fetch doesn't find a row the State Abbreviation would not be populated I would assume. So, I check the length of the StateAbbr column content at which point I receive an ORMOutOfSyncException - why?

Am I not understanding what the state of the Entity I Fetch is (empty)? It does return "true" on the adapter.FetchEntity(check_for_dup_entity); call.

My goal is if the Fetch results in a populated Entity I would "continue;" and if not, then I would save the PostalCodeEntity with its adapter.

I looked at the online documentation as to why an ORMOutOfSyncException is thrown and it states it's because I didn't Refetch after a save, but I have not attempted to save anything yet. Once again, the documentation is not helpful to me on this point.

Thanks,

Norman

I would expect, and this is where I might be wrong, if the Fetch doesn't find a row the State Abbreviation would not be populated I would assume. So, I check the length of the StateAbbr column content at which point I receive an ORMOutOfSyncException - why?

If you want to check if an entity is fetched or not, please check on myEntity.Fields.State The possible values are: **EntityState.New
EntityState.Fetched
EntityState.OutOfSync
EntityState.Deleted **

Walaa wrote:

I would expect, and this is where I might be wrong, if the Fetch doesn't find a row the State Abbreviation would not be populated I would assume. So, I check the length of the StateAbbr column content at which point I receive an ORMOutOfSyncException - why?

If you want to check if an entity is fetched or not, please check on myEntity.Fields.State The possible values are: **EntityState.New
EntityState.Fetched
EntityState.OutOfSync
EntityState.Deleted **

Thank you!

Norman

Walaa:

What causes an OutOfSync error beside what I found as stated in previous post. It temporarily solved the issue, but after adding some additional code (Writing information to a XML via FileStream and XMLTextWriter) - it's back!

I'm utilizing the EntityState.Fetched to check for the duplication, but about 22 thousand rows into it, I'm back to an OutOfSync error

Norman

The following is copied from the manual, and give you a good idea about this exception and how to avoid it.

Setting the EntityState to Fetched automatically after a save By design an entity which was successfully saved to the database gets as EntityState OutOfSync. If you've specified to refetch the entity again after the save, the entity is then refetched with an additional fetch statement. This is done to make sure that default constraints, calculated fields and elements which could have been changed after the save action inside the database (for example because a database trigger ran after the save action) are reflected in the entity after the save action. If you know that this won't happen in your application, you can get a performance gain by specifying that LLBLGen Pro should mark a successfully saved entity as Fetched instead of OutOfSync. In this situation, LLBLGen Pro won't perform a fetch action to obtain the new entity values from the database.

To use this feature, you've to set the static/Shared property EntityBase2.MarkSavedEntitiesAsFetched to true (default is false). This will be used for all entities in your application, so if you have some entities which have to be fetched after the update (for example because they have a timestamp field), you should keep the default, false. You can also set this value using the config file of your application by adding the following line to the appSettings section of your application's config file:

<add key="markSavedEntitiesAsFetched" value="true"/>

You don't need to refetch an entity if it has a sequenced primary key (Identity or sequence), as these values are read back directly with the insert statement.

Walla:

Here is the snippets of the code and as you can see I'm not doing anything with the entity and get the error. That is the portion of the manual I initially read - which doesn't give me much assistance...

// For brevity I did not use namespaces in this code fragment - in actual code the namespaces are there 
...

src_collection = new EntityCollection<ZipcodeEntity>(new ZipcodeEntityFactory());
src_adapter.FetchEntityCollection(src_collection, null);

dest_adapter = new DataAccessAdapter(dest_connection_string.ToString(), true);
check_for_dup_adapter = new DataAccessAdapter(dest_connection_string.ToString(), true);
...

foreach (ZipcodeEntity src_entity in src_collection)
{
   ...

   PostalCodeEntity dest_entity = new PostalCodeEntity();

   Int32 id = 0;

   dest_entity.LocationId = -2024982427
   dest_entity.PostalCode = src_entity.ZipCode.Trim();

   ...

   dest_entity.PostalCodeId = id = (dest_entity.PostalCode + dest_entity.CityAliasName + dest_entity.City + dest_entity.County + dest_entity.StateAbbr).GetHashCode();

   PostalCodeEntity check_for_dup_entity = new PostalCodeEntity(id);
   check_for_dup_adapter.FetchEntity(check_for_dup_entity);

   if (EntityState.Fetched == check_for_dup_entity.Fields.State)
   {
      ...

      continue;
   }

   if (true == dest_adapter.SaveEntity(dest_entity))
   {
      ...
   }

}

So, at the if (true == dest_adapter.SaveEntity(dest_entity)) is where the exception (OutOfSync) occurs after 25,158 rows have been addressed??? I've done that Save with and without the refetch (doesn't change anything)

I am reading from the src_entity and/or dest_entity (PRIOR to saving) if check_for_dup_entity IS fetched or if the end-user left data for the StateAbbr in the src_entity.

These are Access databases, so no timestamp column. There is not AutoIncrement for an ID (my ID is the HashCode created).

Hope this assist.

Norman

In the previous post there is a semi-colon after the

dest_entity.LocationId = -2024982427;

in the actual code

Norman

Please can you also post the stack trace and full text of the exception...?

Thanks

Matt

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code
  Message="An exception was caught during the execution of an action query: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  RuntimeBuild="01162009"
  RuntimeVersion="2.6.0.0"
  QueryExecuted="\r\n\tQuery: INSERT INTO [PostalCode] ([location_id], [postal_code_id], [postal_code], [city], [state_abbr], [state], [area_code], [city_alias_name], [city_alias_abbr], [county], [add_user], [add_datetime]) VALUES (@LocationId, @PostalCodeId, @PostalCode, @City, @StateAbbr, @State, @AreaCode, @CityAliasName, @CityAliasAbbr, @County, @AddUser, @AddDatetime)\r\n\tParameter: @LocationId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: -2024982427.\r\n\tParameter: @PostalCodeId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 93469275.\r\n\tParameter: @PostalCode : String. Length: 25. Precision: 0. Scale: 0. Direction: Input. Value: \"29152\".\r\n\tParameter: @City : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"Shaw AFB\".\r\n\tParameter: @StateAbbr : String. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: \"SC\".\r\n\tParameter: @State : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"South Carolina\".\r\n\tParameter: @AreaCode : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"803\".\r\n\tParameter: @CityAliasName : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"Shaw AFB\".\r\n\tParameter: @CityAliasAbbr : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"\".\r\n\tParameter: @County : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: \"Sumter\".\r\n\tParameter: @AddUser : String. Length: 120. Precision: 0. Scale: 0. Direction: Input. Value: \"System\".\r\n\tParameter: @AddDatetime : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 4/17/2009 12:00:00 AM.\r\n"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
       at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute()
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions, Int32& totalAmountSaved)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave)
       at dM.Forms.MainWindow.BackgroundWorkerThread_DoWork(Object sender, DoWorkEventArgs e) in E:\doubleM Software\dMPostalCode\dMPostalCodeUtility\dMPostalCodeUtility\Forms\MainWindow.cs:line 684
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
  InnerException: System.Data.OleDb.OleDbException
       Message="The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
       Source="Microsoft Office Access Database Engine"
       ErrorCode=-2147467259
       StackTrace:
            at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
            at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
            at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
            at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
            at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
            at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
            at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
       InnerException: 

Norman

According to the exception information, when you save dest_entity, you violate the PK or some unique constriaint/index. Please debug at that point and check whether or not you are inserting a duplicate row.

daelmo:

I initially thought that myself, hence the check for duplicate block just before the save entity section.

Also, if I run this application again without changing the data whatsoever it will add anywhere from 4 to 1079 rows before the same error appear, run it again, and it will add additional rows...

I check the data in both the dest table and the source table and I do not find a duplicate. I wrote a routine in an exception handler that writes out to a file the source entity as well as the destination data and they are not the same. I should correct myself and say just prior to the save I write to the file the destination data and then when an exception occurs I write out the source entity data (no match). There is some data that is the same such as the zip code/postal code and the state abbreviation but there is a difference in either the city alias or the city... The HashCode is different at the time of the exception.

Norman

daelmo:

I wrote some more code to capture the DETAIL of BOTH src and dest...

<ExceptionInformation>
   <InitialException>
      <Source>SD.LLBLGen.Pro.ORMSupportClasses.NET20</Source>
      <Message>An exception was caught during the execution of an action query: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.</Message>
      <IdErrorOn>79801379</IdErrorOn>
      <EntityState>OutOfSync</EntityState>
      <SourcePostalCode>31098</SourcePostalCode>
      <DestPostalCode>31098</DestPostalCode>
      <SourceAreaCode>478</SourceAreaCode>
      <DestAreaCode>478</DestAreaCode>
      <SourceCity>Warner Robins</SourceCity>
      <DestCity>Warner Robins</DestCity>
      <SourceCityAliasName>Robins AFB</SourceCityAliasName>
      <DestCityAliasName>Robins AFB</DestCityAliasName>
      <SourceCityAliasAbbr />
      <SourceCounty>Houston</SourceCounty>
      <DestCounty>Houston</DestCounty>
      <SourceState>GA</SourceState>
      <DestState>Georgia</DestState>
      <DestStateAbbr>GA</DestStateAbbr>
      <DestPostalCodeId>79801379</DestPostalCodeId>
      <InnerException>
         <ExceptionDepth>1</ExceptionDepth>
         <Source>Microsoft Office Access Database Engine</Source>
         <Message>
            <The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. />
         </Message>
      </InnerException>
   </InitialException>
</ExceptionInformation>

You are correct, all the data is the same; however, why didn't the section where I check for duplication prevent this from reaching the save code as it does any other time... I believe I'm back to what causes OutOfSync? The only column in the Access table is the primary key, which is the HashCode, which is also what I test before attempting to add the row. There are no unique columns in the table (only the primary key)

Additionally, when I don't change the tables/data of either and run the application it will continue on as stated above for X rows.

I believe this comes back to OutOfSync and how am I raising that error.

Thanks,

Norman

daelmo:

I'm mistating something above... It's not an OutOfSync "error," but what would prevent this Exception would be the check for a dupliation, and rather than getting the expected "Fetched" state I'm getting an "OutOfSync" -- as you can see in the <EntityState> element toward the top.

That's why I'm stating I'm back to the OutOfSync and why I'm asking why/how am I getting that EntityState

Thanks and I apologize for not fully thinking this out and writing the additional code to capture additional information, etc. before responding initially this evening.

Norman

What threw me off I guess is Matt asking for information about the exception. I know the exception is correct, the this thread is not about the exception but why I'm getting to the exception when I've done my "check for duplication" to prevent the exception.

As stated and the title of the Thread is OutOfSync, I know I'm getting the exception due to a duplicate row being inserted... However, I've written the code to "continue" the loop without inserting if a duplicate is found. The problem is as already stated... I'm getting an OutOfSync EntityState and that is what causing the code to hit the Insert code and raise the Exception.

So, information about what I already know (duplatication) is not really the topic, it's about what is causing the OutOfSync EntityState and how/why that EntityState is being triggered.

Sorry, for the rambling, but I just want to be clear and not get side tracked again - I know the exception is due to a duplicate row being inserted.

Norman

Finally, really finally <smile>

I got the check for duplicate part at the beginning of this thread with the generous addiitional information provided by Walaa...

However, after implementing the "check EntityState" rather than wanting to "look" at column information to see if a row was "Fetched" I now have really the same issue to deal with, but I like and believe the EntityState fits this scenerio far better than what I was originally attempting to do...

As this thread points out, when I was checking the column values I received an OutOfSync Exception at that time...

As you also already know, once you put in the try {} catch {} finally {} blocks things change (smile)... for some reason because I'm testing for a specific EntityState (Fetched) the code goes on to insert the row which is a duplicate.

Walaa, thank you again, now I'm on the issue of OutOfSync still though.

Norman (going to bed - it's 1:42 a.m. here)

Appologies for the time you wasted, Normanlc I still hope you had a good night sleep

Anyway, the outofsync is coming from the fact that you're reading entity data which isn't persisted yet or should be fetched first.

If you want to check values without running into that exception, check the field values directly: entity.Fields[index/name].CurrentValue

Be aware though that if a field has no value, it IS null. So an int field has its CurrentValue set to null if it has no value.

I haven't read into the deeper background of your problem/setup though it seems you want to check whether there is already an entity instance (== data) for the entity you're about to import from a source. You should check on PK values, so check whether there's an entity with the same PK. If so, set that one's values and persist it. If not, (entity is new), insert the original. As said, what I describe here might be way too simplistic, as I haven't read up into your setup that well (only your first post)

Be aware that hashcodes aren't reliable for comparing entities: they're an int and you WILL run into duplicates which really aren't duplicates (as the space for different hashvalues is too small to contain all different states for all fields in an entity). However, a PK is a PK -> it's unique. So when you fetch the entity from postal with the same PK value, you simply set its field values with the entity values from Zipcode. If they're the same, nothing is changed, if they're not, the postal entity is dirty and saveable. An entity which got no changed fields isn't saved so you won't lose performance.

Otis et al:

Interesting fact discovered by me adding the code, so therefore I don't believe it is a waste of time.

The ONLY time the EntityState is set to OutOfSync is when any of the following columns contains "AFB"

I may be causing some of the duplication myself in that I take the user's entry which has been for example:

MINOT AFB - that's one user row MINOT A F B - that's another user row

Before assigning the Destination Entity I do a call to PostalCodeFixUp, which initially changes the string to lower case and then I capitalize the first letter of each word, and finally I check for specific circumstances such as Afb with a space before and/or after at it and then I do a stringbuilder replace and change that to AFB... Hence I'm creating a duplication row right there...

I do that for state abbreviation, College Names/Acronyms, etc. I have built a small dictionary if you will I test against before returning the string to be assigned to the Entity.Column value.

I just find it interesting that LLBLGen "coughs" when it involves "AFB" at which time it sets the EntityState to OutOfSync apparently...

I have checked the HashCode, since you brought that up, and they are the same so it should be "Fetched" when I check for a duplicate before either "continue" in the loop or move on to insert the "unique" row into the table.

Just so you realize and as depicted in the code above, the HashCode is generated from the values within the Columns of the Entity - not anytime before that.

Just a word about HashCode as a Primary Key... I've read everything Joe Celko has written (the good and the bad) but I do agree with him that AutoIncrement/IDENTITY columns as primary keys is a bad thing. Especially if you transfer between databases or "checkout" data for manipulation let's say on a laptop and then check it back in. The delimma I run into is using what I believe to be true "a numeric" is best for a primary id because computers/databases handle numbers faster than character data (that maybe my age coming through in my mind) and to make a multiple columns concatenated for a primary key becomes a nightmare as a foreign key... Obviously things that are unique within an industry like UPC, SSN (which isn't that good of an idea these days with identity theft), but there are other professional code to uniquely identify and are great, but a Postal Code is not unique as we know from reviewing any postal code table (city alias', postal office references, etc. don't allow them to be unique - hence you have a postal code represent 4 different names/locations)...

But since you brought it up Otis, I would love to hear or review any material you have as to what makes a good candidate for a Primary Key/Foreign Key... I know this is off topic but at the same time you mentioned it in your post <smile>...

Again, I'm really going to bed, but as you know when something is "stuck" on your mind as a coder you often cannot walk away from it easily (or at least I haven't been able to do so for some time now)...

Thanks again and I hope this additional information can shed some light, I would be more than happy to submit the entire little utility I'm trying to create and the databases privately - privately only because it is client source for the postal codes/zipcodes.

Really, no really, no I mean it, I'm going to bed.

Norman

Otis et al:

I did change the line of code where I generate the HashCode from reading the dest_entity (which has not been saved yet) as Otis indicated I should do.

dest_entity.PostalCodeId = id = (dest_entity.PostalCode + dest_Entity.CityAliasName + dest_entity.City + dest_entity.County + dest_entity.StateAbbr).GetHashCode();

// replaced per Otis at Software Solutions

dest_entity.PostalCodeId = id = (dest_postal_code + dest_city_alias_name + dest_city + dest_county + dest_state_abbr).GetHashCode();

It still crashes due to duplication and the EntityState is OutOfSync when either the dest_city_alias_name or dest_city contains "AFB."

The HashCode is the same and thus the call to Fetch with the id (HashCode) should have returned "Fetched" rather than "OutOfSync."

Norman

I've read this thread back and forth many times now, for the last 20 minutes, and I have to admit I'm totally lost and confused, especially with eth AFB thing!!

I think the only way to get me straight here is to see a repro solution. Would you please attach a small simple repro solution with test data.

Also please post the correct runtime library version used, not the one you've posted, please check the following thread for more info: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

An un-relevant hint: why using 2 adapters for the same connection

dest_adapter = new DataAccessAdapter(dest_connection_string.ToString(), true); check_for_dup_adapter = new DataAccessAdapter(dest_connection_string.ToString(), true);

I also don't quite follow the AFB value related issue. It seems that when that value turns up, no fields were changed.

Though I think you're doing this a bit too complicated: - fetch entity Z from Zipcode - fetch using PK of Z an entity P from Postal - set all fields of P with the values from Z - save P. If nothing changed in P, P isn't saved. - repeat.

Otis/Walaa:

The "deal" with "AFB" is that when the app changes states to "OutOfSync" and then tries to add a duplicate row (which is the Exception) - the data contains information about an "AFB" (Air Force Base)...

I've attached the exception logs and as you can see, each exception is only on rows containing "AFB" in either the City or CityAliasName. I just found that it is a common denominator - nothing more. Also as I stated, If I restart the process (Worker Thread copying data) it doesn't fail (receive an exception on those same rows, it by passes them and goes until it hits another row which contains "AFB" information in the entity.

I've been working on a generic (I use Infragistics for window controls - I've re-written the application using VS 2008 with Microsoft controls) to submit as requested by Walaa.

It's nearly completed - I've had to tend to other duties most of the day. Anyway, I'll submit this now with the Exception Logs.

Norman

Otis/Walaa:

Okay, it didn't help much as far as getting to the bottom of why the behavior I was receiving was in fact received.

First I updated the runtime libraries to the "latest and greatest." I replaced libraries from I believe January of this year or it may have been December of last year. I was working between a workstation and a laptop and don't recall off the top of my head.

Anyway, I did the following process:

I got the collection from the Source Database.

Because the source database contained a primary key I did not want to use (AutoIncrement) I saved the information needed to create the HashCode to memory variables. I then created the HashCode.

I did as Otis suggested and Fetched the entity from the PostalCode database utilizing the ID created.

I did as Walaa stated and dumped the check_for_dup_adapter as well as check_for_dup_entity.

I wanted to get a count of duplicated rows, so I also used Walaa's suggestion and checked the EntityState and if it was EntityState.Fetched I continue; as well as write the data to an XML file for review. I could then confirm those "duplicates" were in fact in the database, which was confirmed after the utility concluded.

If the EntityState was not "Fetched" I transferred the information from the ZipCodeEntity the PostalCodeEntity as Otis suggested and saved it.

I did NOT receive any EntityState.OutOfSync as previously experienced. The "Why" is the part I did not discover.

The utility works as it should.

The REASON for the AFB was also disclosed after reviewing the source database. They had put the Air Force Base (AFB) into the database several different ways in error. After my utility removed spaces, capitalized properly, etc. - those were the ONLY rows that were in fact DUPLICATES.

I want to THANK EVERYONE that addressed me in this thread. I REALLY DO APPRECIATE your valuable time and have attempted not to bother any of you too often over the past several years I've been working with LLBLGen Pro, which I still believe is a GREAT product.

Thank you again,

Norman