Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
From the creators of devRant, Pipeless lets you power real-time personalized recommendations and activity feeds using a simple APILearn More
Ignoring the fact that duplicates should probably just ignore the new tag, I'd just use raw sql. Either write upsert logic based on the key, or use a merge join to skip the operation.
I don't recommend entity framework for exactly these sort of reasons. As it stands, you'll either need take on a third party dependency or build the expression tree manually to get the update flag set properly without a double round trip to validate existing keysets. Meanwhile SQL already numerous facilities to handle this operation OOTB.
Voxera886850dWhy create a new hashtag, reuse the id of the existing when creating the new idea.
That should not be a problem and is common practice
GiddyNaya703850dYou're making an INSERT and expecting an UPDATE... if that works dont you think it would break the law of GIGO? 😂
I wouldn't use UPSERT. Update or insert is a tricky bastard, like REPLACE (MySQL).
Preventing a double round trip is easy by utilising INSERT INTO with a subquery and not exists or a temporary table.
Temporary table can be viable for bulk insert depending on number of inserts.
UPSERT and REPLACE will always lead to multiple operations and can have certain side effects regarding foreign keys / triggers / statements.
They should be avoided for simple things like that imho
@SortOfTested brain pooped pretty much.
I'm not very much familiar with SQL server.
Brain didn't make the connection between EF , MS and that little shitbird called SQL server.
PostGres has UPSERT, MySQL has ON DUPLICATE KEY UPDATE and REPLACE.
*tada* complete confusion achieved.
Yes. Merge exists in Oracle, SQL server and DB/2.
I would need to do some proper research, my brain was in PostGres / MySQL world.
Voxera886849d@BitByteBoolean are you trying to insert both in one go?
If you first search for the hashtag and then add the id to the new idea object it should understand that it is a reference to an existing hashtag but if you add both objects it will try to create both.
It does not know that one exists.
Sure you could build an orm that does that but it will most likely either have to be custom for one database or very slow as it would always have to do a lot of extra fetches to see if something exists or not.
I suggest finding a few good tutorials on EF and then try it.
@SortOfTested it can make sense...
Rarely used in a senseful way.
But even if PostGres understands TSQL.... The whole behaviour of PostGres is entirely different. There's a reason why migrations are extremely painful.
Behaviourism in databases isn't fun.
Did migrations multiple times and I'd rather had klingon pain sticks shoved up my butt...
It has enough functional overlap to encode the operations. It's also not a long term solution. The billed goal is to make moving to Postgres less of a risk.
Rewrite and retest all the queries is a lot to ask. Hook it to babelfish and rerun tests has a much lower opportunity cost, and is low cost to back out of.
But that's exactly the risk.
From null behaviour to collation / charset to sorting to SQL feature set and derivations from SQL standard...
Postgres might execute the query. But the result set could be entirely different.
Countless times devs expected things to be deterministic when they didn't wrote it to be deterministic. :(
It sounds great, yes. But I really don't want to know how much fun it is to find the outliers when everything seems to be working :(
@IntrusionCM @SortOfTested @Voxera so i found my issue was with state handling of the entity's.. the add method of EF will always set an added state to the entity and its related entitys(the navigation properties) and added state will always make an insert. So i iterated through the hashtags to find those already loaded in context, detached those and usef update insteaf og add. (Autogenerated primary keys will if null insert, if not null update) and thus problem got fixed. I can now insert the idea with hashtags, both new and already existing ones from the db 👍
Thanks for the pointers to the right direction all 👊