I would like to know if there is any way to have a Stored Procedure used to perform a save for a particular entity when that entity is encountered in a DataAccessAdapter.SaveEntity call?

I have a few tables in my db that will hold information that is shared or linked to other tables. One of these tables is an Address table.

Any other Entity in the system needing an Address simply gets a link or reference table. So a Customer would have a CustomerAddress that links the Customer and Address.

-CustomerEntity --CustomerAddressEntity ---AddressEntity

Under this model the Address table is only ever the target of insert statements. So if a Customer has an Address that needs to be resolved at the point of a save I need to pass in the relevant information (Street, City, Zip, etc..) to a Stored Procedure and return the AddressId of the existing record, update it on the CustomerAddress Entity, and continue with my save.

I have found that if the AddressEntity being saved does not exist and I left the adapter do its normal business everything works fine. The issue is when the AddressEntity ebing saved already exists.

I have experimented with overriding the CreateInsertDQ method of my DataAccessAdapter, but when after the query is executed the related fields in the CustomerAddress are not set to the newly retrieved AddressId.

Can you perhaps suggest a way in which I may be able to accomplish this?

I am using 2.6 latest release.

Thanks.

Harry wrote:

Under this model the Address table is only ever the target of insert statements. So if a Customer has an Address that needs to be resolved at the point of a save I need to pass in the relevant information (Street, City, Zip, etc..) to a Stored Procedure and return the AddressId of the existing record, update it on the CustomerAddress Entity, and continue with my save.

That seems it would be achieved writing your own method that return an AddresEntity to stick with the CustomerEntity.

What you can do is: 1. Fetch the AddressEntity, if that doesn't exist, the entity would comes IsNew=true. You can project StoredProcedure to the Address at this point, if you need it. 2. If the address is new, complete the information in it. 3. Relate the Address with the CustomerToSave. Doesn't matter whether it's new or not. 4. Save recursively

Thanks for the reply. I am not sure exactly where you were going with your suggestion.

The issue I am dealing with at the moment is the following: Company A has: Customer 1 has CustomerAddress 1 has Address 1 has 123 Main St. Hometown MD 00000 Customer 2 has CustomerAddress 1 has Address 1 has 123 Main St. Hometown MD 00000

Both addresses are new as they do not exist in the database. As each one is a seperate instance they have different ObjectId's.

So when the SaveEntity is called on the Company the graph produced is switched around: CompanyEntity CustomerEntity AddressEntity CustomerAddressEntity CustomerEntity AddressEntity CustomerAddressEntity

This is all good, so far.... The issue is that when the second AddressEntity is processed by the adapter to be saved it is New so an insert is generated and that insert violates the unique index on the table because that same entry was just inserted for the first Address.

1. Fetch the AddressEntity, if that doesn't exist, the entity would comes IsNew=true. You can project StoredProcedure to the Address at this point, if you need it.

How exactly would I accomplish this? Is there a way to indicate for a particular entity, such as the AddressEntity, to use a SP to do inserts/updates??

Thanks again for the help.

Can you perhaps suggest a way in which I may be able to accomplish this?

Would you please post a code snippet, of how did you construct the below graph?

Customer 1 has CustomerAddress 1 has Address 1 has 123 Main St. Hometown MD 00000 Customer 2 has CustomerAddress 1 has Address 1 has 123 Main St. Hometown MD 00000

(EDIT) Also I think you can use ValidateEntityBeforeSave for the AddressEntity. That's where you should attempt to fetch the entity based on a filter compused of all the fields in hand.

I am not quite sure what you are asking for. I do not construct the graph.....

I have a Company. The Company has an EntityCollection of Customer. The Customer has and EntityCollection of CustomerAddress. The CustomerAddress has an Address.

Is this what you are looking for??

I mean how the graph was created in code, was the same Address entity assigned to both customers, or did you use 2 different instances of the AddressEntity?

Also I think you can use ValidateEntityBeforeSave for the AddressEntity. That's where you should attempt to fetch the entity based on a filter compused of all the fields in hand.

Walaa wrote:

I mean how the graph was created in code, was the same Address entity assigned to both customers, or did you use 2 different instances of the AddressEntity?

Ahh... I see what you are asking. I have no idea nor any control over that. I am working on Services that accept the objects and processes them.

We are to have several different "clients" consuming or using our objects and have no way to ensure they use a certain approach when collecting the information from their "clients".

Does this make sense?

Makes sense.

Would you please try the ValidateEntityBeforeSave approach.

Kewl.

I am currently using the ValidateEntityBeforeSave approach, however it does not address the issue I outlined in my scenario.

The 2 addresses being sent to the adapter are both new and do not exist in the db so the validate does nothing with them.

When the PersistQueue is called with these 2 entities it tries to insert both of them and the second always fails....

If you can come up with any other ideas please let me know.

Thanks.

The 2 addresses being sent to the adapter are both new and do not exist in the db so the validate does nothing with them.

When the PersistQueue is called with these 2 entities it tries to insert both of them and the second always fails....

Check the following example:

public override void ValidateEntityBeforeSave( IEntityCore involvedEntity )
{
    AddressEntity toValidate = (AddressEntity)involvedEntity;

    RelationPredicateBucket filter = new RelationPredicateBucket();
    filter.PredicateExpression.Add(AddressFields.City == involvedEntity.City);
    // repeat the above line for all fields of the Address except the PK.

    EntityCollection<AddressEntity> addresses = new EntityCollection<AddressEntity>();
    DataAccessAdapter adapter = new DataAccessAdapter();
    adapter.FetchEntityCollection(addresses, filter);
    
    if(addresses.Count > 0)
    {
        involvedEntity = addresses[0];
    }

    base.ValidateEntityBeforeSave( involvedEntity );
}

That is similar to what I was trying to do originally.

The problem with that is that by the time the adapter calls the validate before save it already has the Address in its insert queue, so when you replace it with a newly fetched instance the adapter attempts to generate an insert from an Entity with 0 fields changed and throws an exception.

I see.

I then can see no other way but to use an After Insert Trigger in the database.

Which should check if there is another matching old row in the table, and if exists it should be deleted and references to it in other tables should be changed to the newly inserted row.