How to save the same (duplicate) new entity only once during a save process?

In order to save space in the database and reduce redundacy, I have several entities I call "auto-ref" entities (not sure what the correct term is).  For these entities, I want to save them if this is the first time they have been used, or supply the ID if they have been used before.

A prime example is a Device's DeviceType entity, containing the record ID and a Name field.  I will save many of the same types of device, but I only want to save a particular DeviceType once in the databse.

What I do now is check to see if the DeviceType exists and use the existing DeviceType entity's ID if it does, or create a new one if it does not, like this:

        private void AddRefDeviceType(ref XmlDevice device, DeviceType deviceType)
        {
            if (null == deviceType)
            {
                throw new ArgumentNullException("deviceType", "DeviceType is a required entity");
            }

            DeviceType existing = UnitOfWork
                                    .DeviceTypes
                                    .DefaultIfEmpty(null)
                                    .First(p => p.TypeName == deviceType.TypeName);
 
            if (null == existing)
            {
                device.DeviceType = new DeviceType { TypeName = deviceType.TypeName };
            }
            else
            {
                device.DeviceTypeId = existing.Id;
            }
        }

So far, so good, this seems to work just fine (no matter what I thought I saw last week).  But, I run into problems when
I try to add more than one of the same type of device in the same unit of work.  Say the primary record contains a collection
of Devices, and 2 of them are the same type.  When that happens, I get 2 identical TypeName records.  That makes sense when I
think about it, but it's NOT what I want to happen.

Is it possible to do this without duplicating records using your ORM model?  Can I call UnitOfWork.SaveChanges() multiple times
within the same session? If so, are there any caveats I need to be aware of?  Is there another way to do this so I don't get 
duplicate TypeName entities, or am I stuck with multiple duplicate records in this case?

Thanks for your time and consideration,
Dave

You can certainly call SaveChanges multiple times on the same unit of work.  Note that these will be separate transactions unless you explicitly wrap all the saves in one transaction.

Regarding the duplicate TypeName entities, the problem is that you are checking only whether you have a suitable saved DeviceType.  You also need to check for a suitable unsaved DeviceType.  Something like this should work:

public static DeviceType FindDeviceTypeByName(ModelUnitOfWork uow, string typeName) {
  // Check for existing saved DeviceType
  var deviceType = uow.DeviceTypes.FirstOrDefault(t => t.TypeName == typeName);
  if (deviceType != null)
    return deviceType;
  // Check for existing unsaved DeviceType
  return uow.OfType<DeviceType>()
                   .Where(t => t.EntityState == EntityState.New)
                   .FirstOrDefault(t => t.TypeName == typeName);
}

(If DeviceType.TypeName can change, you'll want to skip the EntityState check in case you have a DeviceType whose name has been changed and now matches, but the change has not yet been flushed to the database.)

Awesome!  That's exactly what I was looking for, thanks...

Hi -

After cleaning up a half dozen other errors exposed by getting this approach implemented, I finally got to the point where it really should have worked.  Now I'm getting the following exception:

{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_XmlDevices_DeviceNames\". The conflict occurred in database \"HawkDev\", table \"dbo.DeviceNames\", column 'DeviceNameID'.\r\nThe statement has been terminated."}    System.Exception {System.Data.SqlClient.SqlException}

I have a primary record called TestRecord that has a DeviceName record associated with it (the Host Name).  This has a collection of XmlDevices, each of whom also has a DeviceName record.  2 records in, the XmlDevice has a DeviceName record identical to the primary record.

In order to even find the unsaved record, I had to call UoW.Add(TestRecord) before trying to add the XmlDevices to the collection. Prior to doing that, it (obviously) couldn't find the previous DeviceName record...

Now, I am using the IdentityColumn approach (Sql Server 2008 R2) for ID columns, so all the IDs assigned by the Lightspeed engine are negative numbers.  Because of this, the ID that gets assigned to the XmlDevice record is a negative number -- could this possibly be causing the problems I am now seeing?

I hope that's clear...  To put it another way, the TestRecord has a New DeviceName attached to it with a placeholder ID of, say, -19.  Further on down the line, instead of creating a new DeviceName record, I find that existing New entity, assign -19 to the DeviceNameId field and leave DeviceName null.

Is this supposed to work?  Do I need to convert the whole mess to KeyTable identities to make it work?  What are your plans to promote world peace?

(Oops, got carried away there.)

Thanks for the help, Dave

Figured it out.  If I use the entities themselves instead of the Ids, everybody is happy. So, from my example above, I would assign the existing DeviceName record to the DeviceName field and leave the DeviceNameId field blank...

I'm not sure why this was so hard for me to figure out (invalid IDs within a transaction are a bad thing), but it was.  It had to be pointed out to me before I could see it...

Thanks for listening, Dave