title: Entity framework model generating weird error message
body: <p>I have a PostgreSql 9.04 database that contains a postgres implementation of the ASPNet Membership Services database.  This implementation lives in the same database as my own tables, but in a different schema called "security".</p>
  
  <p>For business reasons, I have included the aspnet_Users, aspnet_Membership, and aspnet_Profiles table in my entity model.  Below is the DDL for these tables.</p>
  
  <pre><code>CREATE TABLE security.aspnet_users (
      userid UUID NOT NULL,
      applicationname VARCHAR(255) NOT NULL,
      username VARCHAR(255),
      lastactivitydate TIMESTAMP,
      isanonymous INT,
      CONSTRAINT aspnet_users_userid_pk PRIMARY KEY (userid),
      CONSTRAINT aspnet_users_username_pk UNIQUE (username, applicationname)
  );
  
  CREATE TABLE security.aspnet_membership (
      userid UUID NOT NULL,
      password VARCHAR(255),
      passwordsalt VARCHAR(255),
      passwordformat INT,
      email VARCHAR(255),
      passwordquestion VARCHAR(255),
      passwordanswer VARCHAR(255),
      comments VARCHAR(255),
      isapproved INT,
      islockedout INT,
      creationdate TIMESTAMP,
      lastlogindate TIMESTAMP,
      lastpasswordchangeddate TIMESTAMP,
      lastlockoutdate TIMESTAMP,
      failedpasswordattemptcount INT,
      failedpasswordattemptstart TIMESTAMP,
      failedpasswordanswercount INT,
      failedpasswordanswerstart TIMESTAMP,
  
      CONSTRAINT aspnet_membership_userid_pk PRIMARY KEY (userid),
      CONSTRAINT aspnet_membership_userid_ref FOREIGN KEY (userid) REFERENCES security.aspnet_users (userid)
  );
  
  CREATE TABLE security.aspnet_profiles (
      userid                      UUID,
      propertynames               BYTEA NOT NULL,
      propertyvaluesstring        BYTEA NOT NULL,
      propertyvaluesbinary        BYTEA NULL,
      lastupdateddate             TIMESTAMP NOT NULL,
  
      CONSTRAINT aspnet_profiles_userid_ref FOREIGN KEY (userid) REFERENCES security.aspnet_users (userid),
      CONSTRAINT aspnet_profiles_userid_key UNIQUE (userid)
  );
  </code></pre>
  
  <p>Again, these are the only tables from this schema that are in my model.</p>
  
  <p>Now, when I insert a row into these tables, and I call SaveChanges, I get the following error message:</p>
  
  <p>Unable to determine the principal end of the 'Membership_Users' relationship. Multiple added entities may have the same primary key.</p>
  
  <p>Below is a sample of code that I'm using to insert a new or update an existing row.  This is representative of everything I'm doing regarding updating the database.</p>
  
  <pre><code>public static void SaveUserProfile( CarSystemEntities context, UserProfileData data ) {
      if ( context == null )
          throw new ArgumentNullException( "context", "You must pass a non-null CarSystemEntities instance." );
  
      if ( data == null )
          throw new ArgumentNullException( "data", "You must pass a non-null UserProfileData instance." );
  
      try {
          Profile profile = null;
  
          if ( !context.Profiles.Any( p =&gt; p.Userid == data.ID ) ) {
              profile = new CarSystem.Profile{
                  LastUpdatedDate      = data.LastUpdatedDate.LocalDateTime,
                  PropertyNames        = data.PropertyNames, 
                  PropertyValuesBinary = data.PropertyValuesBinary, 
                  PropertyValuesString = data.PropertyValuesString,
                  Userid               = data.ID
              };
  
              context.Profiles.AddObject( profile );
          } else {
              profile = QueryUerProfiles( context ).Where( p =&gt; p.Userid == data.ID ).Single();
  
              if ( profile.LastUpdatedDate      != data.LastUpdatedDate )      profile.LastUpdatedDate      = data.LastUpdatedDate.LocalDateTime;
              if ( profile.PropertyNames        != data.PropertyNames )        profile.PropertyNames        = data.PropertyNames;
              if ( profile.PropertyValuesBinary != data.PropertyValuesBinary ) profile.PropertyValuesBinary = data.PropertyValuesBinary;
              if ( profile.PropertyValuesString != data.PropertyValuesString ) profile.PropertyValuesString = data.PropertyValuesString;
          }
  
      } catch ( Exception ex ) {
          throw new DataAccessException( DataAccessOperation.SaveProfile, FailureReason.DatabaseError, ex );
      }
  }
  </code></pre>
  
  <p>What is the cause of this error?  How do I fix this?</p>
  
  <p>Thanks</p>
  
  <p>Tony</p>
  
  <p>P.S.</p>
  
  <p>Upon further investigation, the problem isn't when inserting a row, it's when trying to update a row in the same transaction as the one that inserted the row. </p>
  
  <p>Essentially, there's a queue of objects to be written to the database.  Each object is removed from the queue in turn and a method like the one above is called to save it to the proper table.</p>
  
  <p>The first time a specific ID is seen, the method inserts it.  That is, the Any check returns false.  The code creates the new entity object and calls the AddObject method for the entity set that corresponds to the table.  So far so good.  </p>
  
  <p>After that, it is assumed that the Any check will return true, indicating that there is a row with the given ID in it already.  It is then supposed to update the row.  But it appears that the Any check is returning false the second time, too, even though I called AddObject on the Entity set that corresponds to the table on the first call.</p>
  
  <p>Any idea what I am doing wrong?</p>
  
  <p>PPS</p>
  
  <p>I've done some more testing with a Sql monitor open.  We are using the Devart dotConnect for PostgreSql library and they have a tool you can download called DbMonitor.  This allows you to track the SQL that is emitted &amp; executed by the Entity Framework.</p>
  
  <p>It turns out (predictably, as it happens) that the calls to .Any are executed as queries against the database immediately, while all of the inserts are queued up and applied once you call SaveChanges.  The Any calls do not seem to take any rows that are pending inserting into account, just what the database calls return.  As the rows to be inserted haven't been inserted yet, this query will always return false until SaveChanges is called.</p>
  
  <p>As a result, my logic as it stands won't work.  When a row that is pending insertion is updated (that is, it appears a second time in the queue), The insert logic would run a second time.  Sometimes I'd get an exception indicating a unique or primary key constraint was being violated, other times I'd get the message I originally posted about.</p>
  
  <p>I need all of the inserts and updates to be done in a single transaction, and I need the inserts to happen in the database at the time of the call to AddObject.  Yet I still need all of the inserts &amp; updates to rollback if something goes wrong inserting or updating a single row, or if some other error occurs in the C# code.</p>
  
  <p>Anybody have any ideas how I can do this with the Entity Framework?</p>