I have an entity that has a primary key. The field is not an identity and I need to set it to a particular value for a relation with another table.

Essentially I want to do this:

PhysicalInspection phys = new PhysicalInspection(); phys.ID = 15733; adapter.SaveEntity(phys, true);

After the save the ID is changed to something else. How can I force it to remain unchanged?

Thanks, Sean Butler

Do you have a trigger defined on the table?

No, there isn't a trigger on the table.

gt5149c wrote:

No, there isn't a trigger on the table.

Could you enable tracing and check which queries are generated and executed? (see using the generated code -> troubleshooting and debugging). The typical cause for what you're experiencing is a trigger on the table which inserts the id again.

I have a theory on the problem. I have inheritance turned on and physical inspection is has a foreign key to Event and its primary key is ID. ID is an identity on the Event table. Could it be confused when I say ID and be referencing the Event table?

I traces the database and it creates a new Event and then updated the ID for physical inspection. I does this even when I retrieve the correct event before I hit save.

Any ideas?

Is PhysicalInspection a sub-type of Event? If it is then an Event record must exist for PhyisicalInspection to exist. If that is the case and it is an insert then I would image that the Event is inserted first and it's PK value can't be set since it's an identity.

I don't think that you will be able insert a PhysicalInspection entity with your own PK if this is your scenario.

gt5149c wrote:

I have a theory on the problem. I have inheritance turned on and physical inspection is has a foreign key to Event and its primary key is ID. ID is an identity on the Event table. Could it be confused when I say ID and be referencing the Event table?

I traces the database and it creates a new Event and then updated the ID for physical inspection. I does this even when I retrieve the correct event before I hit save. Any ideas?

Ah, but then it's intented behavior. If you have PhysicalInspection which is a subtype of Event, then the PK of Event is also the PK of PhysicalInspection ENTITY, as Event is part of PhysicalInspection. The PK of the PhysicalInspection TABLE is also an FK, so always dependent on the value inserted in Event.

When you set the field, it's ignored, as it's an identity field. this means that the event's new PK value is synced with the PK of the PhysicalInspection TABLE, so the PK in that table points to the right Event row (as it's an FK)

Yes, PhysicalInspection is a sub-type of Event. However, I do create an Event before I try to create the PhysicalInspection.

The following code doesn't work for me:

Event evt = New Event(); adapter.Save(evt); PhysicalInspection inspect = New PhysicalInspection();

inspect.ID = evt.ID; adapter.Save(inspect);

After the save inspect has an ID that is different from evt's ID field. Is there a setting to change the behavior and have it not create a new Event record?

You don't have to save an Event first, saving a PhysicalInspection entity automatically saves the event for you, as it's a subtype of that entity.

I want to save the event since I have many objects that are sub-types of event. I want to create 1 event and link any number of objects to it.

Is this not possible with inheritance turned on for my particular naming scheme?

Hello,

you should delete inheritance for event in your case because with inheritance, 1 event is only link to one child(sub type). You could delete inheritence for one entity using the designer.

gt5149c wrote:

I want to save the event since I have many objects that are sub-types of event. I want to create 1 event and link any number of objects to it.

Is this not possible with inheritance turned on for my particular naming scheme?

That's not possible, you can't share a single event's DATA (which makes up the entity) with a number of subtypes. This is because the PhysicalInspection data is stored in 2 tables, event and PhysicalInspection. So sharing the event part with more entities, you will run the risk of changing multiple entities at once.

if you want to share an event, simply don't make PhysicalInspection a subtype, but keep the association. Though I then wouldn't make the PK of PhysicalInspection an FK to event, as that would prohibit storing 2 PhysicalInspections in the table sharing the same Event.

Sorry, I didn't mean that I needed 2 physical inspections on 1 event. I'm going to have 1 event and under it 1 physical inspection, 1 casing and 1document.

That shouldn't break any database rules. All of the child tables have ID as the primary key and a foreign key to Event.

Is there a way to point them at an Event that already exists?

I'm going to have 1 event and under it 1 physical inspection, 1 casing and 1document.

This is not possible too.

I Parent record should be unique to only one of the subTypes.

Thus 1 PhysInspection should have 1 row in th Event table that no one elses uses. 1 Casing should have another row in th Event table that no one elses uses.

You can't use One Event to be the Parent of more than one Sub-Entity, even in different tables.

So you shouldn't use inheritance in this case, and have the Event be referenced from PhysicalInspection (and the others) by a normal FK, and if necessary, also with a unique constraint.

You then can simply have what you're requesting: myPhysicalInspection.EventId = myEvent.ID; etc.

As PhysicalInspection is a separate entity, I wouldn't recommend using the PK as the FK.

OK, thanks. I'll remove all the sub-types of event.