Print Page | Close Window

How to ignore properties in Insert

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2537
Printed Date: 29-Jul-2026 at 4:58pm


Topic: How to ignore properties in Insert
Posted By: tj62
Subject: How to ignore properties in Insert
Date Posted: 02-Mar-2011 at 6:55am
Hi,
I have problem when inserting entities into my entity model, as the entity model seems to try to insert null into table columns that should be ignored, but handled by the database behind in an insert trigger, instead. Let me make a simple example.

Database definition:

create table A (
    id1 SERIAL not null,    --primary key
    id2 INT,      -- nullabel id that is only managed by insert trigger and an old legacy system.
    name VARCHAR(50)
)
alter table A constraint primary key
    (id1)
    constraint pk_A;

I have an insert trigger for the table A that updates id2 correctly..for example.
UPDATE A SETid2 = 107 WHERE id1=new.id1;

My Silverlight application is noT supposed to use id2, so in my entity model I delete the property id2 although it still exist in the table-definition of my entity model (it can't be deleted from the table definition). This all works fine untill I have to insert into A. Then the database returns an error that the trigger is trying to change a value that is beeing inserted. After some investigation I found out that the Entity Model is applying insert command like this:

INSERT INTO A (id2, name) VALUES (NULL, "My Name");

So it tries to put a NULL into id2 that causes the conflict in the trigger. Instead I want it to create the insert command such:

INSERT INTO A (name) VALUES( "My Name");

However I cant find anywhere in the EM setup how to force it to leave unbound properties in insert and update commands. The only solution I find is to go into the .edmx file with text editor and comment out the property "id2" in the table definition. However whenever I have to update the model from database (add more tables etc.) this manual editing is destroyed. A second solution is to define an Insert stored procedure for the Entity.
Both solutions are very bad as I have 43 tables/Entities with this nature.

Is it somehow possible to tell the Entity Model to leave unbound table properties totally alone and ignore them totally in insert and update?

Version of DevForce is 6.0.7.0. Database is Informix Dynamic Server 11.7 with latest IBM .Net provider beta. Using Visual Studio 2010.


Replies:
Posted By: sbelini
Date Posted: 04-Mar-2011 at 12:31pm
Hi tj62,
 
I wasn't able to reproduce the issue against MS SQL.
I will test on Informix and let you know.
 
Silvio.

Posted By: tj62
Date Posted: 04-Mar-2011 at 1:42pm

You are somehow misunderstanding the question. This is not about if the trigger works or not, but if it is possible to somehow avoid that the Entity Model tries to insert NULLs into database table columns that are not in the Entity (not mapped to Entity variables:  That is to say to force EM to leave those columns totally untouched in Inserts and Updates.
 
Don't waste time on testing this with INformix or other database types, I know all the details happening there. That is not the question.
 
Regards
  TJ

Posted By: sbelini
Date Posted: 04-Mar-2011 at 3:30pm
Throstur,
 
To answer your question:
 
Is it somehow possible to tell the Entity Model to leave unbound table properties totally alone and ignore them totally in insert and update?
No, it is not possible.
Unfortunatelly, that's something that can't be done in the Entity Framework.
DevForce runs on top of the Entity Framework and has minimal control over either EF or the EF provider’s  SQL generation.
 
You already found the 2 possible ways of solving this problem: manually editing the edmx or creating stored procs for the inserts.
If you adopt the first approach, I suggest writing a script that does the changes in the edmx xml, so you can run it after you regenerate your model.
As for the latter, you will have more work upfront creating the stored procs, but it will require less maintenance.
 
Kind regards,
   Silvio.

Posted By: tj62
Date Posted: 05-Mar-2011 at 4:18am
Silvio,
Thank you for your reply. That was my concern. Well I will go for solution one. Actually I intended to write a C++ program that opens the edmx file after every update from the database and removes the unwanted items. However you say script, I'm quite novice with that stuff. Is it possible to create a script that I can run from within Visual Studio. If so could you give me a "How To"  link.
Regards
  TJ

Posted By: sbelini
Date Posted: 05-Mar-2011 at 9:40am
 
I couldn't find any info on running a script within VS when you rebuild the model.
However, in this thread at http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/b688c277-fa42-4d92-a4fc-31d817456760/ - http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/b688c277-fa42-4d92-a4fc-31d817456760/ , someone uses XMLStarlet in a batch to edit the edmx. (he runs it manually though)
What I could think of is calling the batch in the post-build events of your domain model project. It might not be the most appropriate place for calling the batch, but you will need to rebuild everytime you regenerate your model.
 
Silvio.


Print Page | Close Window