| Detailed description |
Using Entity Framework 4 Code Only, i map my entities like:
public CountryMap()
{
HasKey(x => x.ID);
Property(x => x.ID).IsIdentity().StoreGeneratedPattern = StoreGeneratedPattern.None;
Property(x => x.creationDate);
Property(x => x.lastChangedDate) .IsConcurrencyToken().IsComputed().HasStoreType("timestamp");
....
MapSingleType(t => new
{
id = t.ID,
creationdate = t.creationDate,
creationuserid = t.creationUserID,
lastchangeddate = t.lastChangedDate,
lastchangeduserid = t.lastChangedUserID,
name = t.name,
code = t.code
}).ToTable("public.country");
}
SO, focus on the lastchangeddate property. This is meant for concurrency. On the pg server it is declared as:
ALTER TABLE country ADD COLUMN lastchangeddate timestamp without time zone;
ALTER TABLE country ALTER COLUMN lastchangeddate SET NOT NULL;
ALTER TABLE country ALTER COLUMN lastchangeddate SET DEFAULT now();
I want to mimic the mySQL/MS SQL DateTime. On Every update, i want this property to be part of the where clause in the Update statement. Like a Version kind of thing, very comon.
So, i use these:
Database["PostgreSQL"].ExecuteNonQuery(
@"CREATE OR REPLACE FUNCTION update_lastChangedDate_column()
RETURNS TRIGGER AS $BODY$
BEGIN
IF NEW.lastChangedDate = OLD.lastChangedDate THEN NEW.lastChangedDate := now(); END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';
CREATE TRIGGER trg_Country_update_lastChangedDate_column BEFORE UPDATE ON country FOR EACH ROW EXECUTE PROCEDURE update_lastChangedDate_column();
This works, and is ok. Only supplied so the reader has a beter idea of the whole.
When I insert a row, a lastchangeddate is filled with now(). On every update, its value is updated with now().
When i do insert in .Net, EF (see EF mapping above) and Npgsql generate this:
INSERT INTO "public"."country"("code","creationdate","creationuserid","id","lastchangeduserid","name") VALUES ('SR',TIMESTAMP '2010-10-28T23:29:27.9988733+02:00',NULL,'6d01a577-1baf-4454-aa16-9e1d01831f22',NULL,'Suriname');SELECT currval(pg_get_serial_sequence('"public"."country"', 'lastchangeddate')) AS "lastchangeddate"
If i look in the database, everything seems ok. lastchangeddate has a correct value!. But the entity which is saved, is not updated with the lastchangeddate.
It stays NULL in c#.
When i copy past the above SQL in pgAdmin, and execute it (with another GUID offcource) The result is empty. The data in de db is ok, with a correct lastchangeddate, but the select statement return empty.
Why?
Because it doesn't make sence:
SELECT currval(pg_get_serial_sequence('"public"."country"', 'lastchangeddate')) AS "lastchangeddate"
I have NO sequence. I have a timestamp column with a default value of now().
That's it. SO pg_get_serial_sequence('"public"."country"', 'lastchangeddate') returns nothing, and so does currval.
I think my point is made and understandable.
Thanks for your efforts and great work.
|
|
