EF/asp.net core issue..
I've got a many to many relationship between idea and hashtag.
Currently i can POST an new idea with new hashtags just fine, but if i then POST a new idea, with the same hashtags, it tries to insert the hashtag again into hashtags on sql server, and fails since the hashtags should be unique...
What i expect of it is to just update junction table using the already created hashtag from first POST and create the new idea 😅

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.
https://github.com/artiomchi/...

Why 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

You'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

@Voxera exactly what i am trying to do, but EF makes a insert command instead of update to the DB 😅

@GiddyNaya haha 😂
Well the issue is that i .add(idea) (meaning in sql INSERT idea) and expect EF to know wether or no its related entity's in the DB already... Dont EF do that for you 🤔🤔

@BitByteBoolean an EF cannot magically know whether an entity exists or not.

Entity lifecycle management and caching is a wonderful world of pain.

@IntrusionCM
SQL server's merge when matched/when not matched are pretty reliable.

Though as previously noted, this should just be a moment where you use SQL raw, or dapper.

@SortOfTested I don't understand the matched / non matched part.

UPSERT and REPLACE can have nasty side effects - unlikely in this case, but still.

It's not a magical silver bullet

@IntrusionCM
Because it's a merge, you define the merge condition and you get a left and right execution path optionally. There's also the by source expression, which is rarely used.

https://sqlservertutorial.net/sql-s...

@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.

@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.

@IntrusionCM
Be interesting to see what it does when hooked up to this:

https://aws.amazon.com/blogs/...

@SortOfTested Oh Lord....

Now that will end in interdimensional clusterfuck...

I really think this is the epitome of bad ideas. Poison jar of bad idea TM.

*eyes wide open in terror*

@IntrusionCM
Whatever gets people on tsql

@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...

@IntrusionCM
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.

@SortOfTested Yeah...

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
One would hope a market ready translation layer from AWS would already have that sorted. I worked with some immensely talented people there, so I have a reasonably high level of confidence. Not enough to switch day 1, but enough to fund a pilot.

@SortOfTested if they really achieved that... Wow....

Though I would be very interested how.

Currently it's for me unbelievable that something like that could exist...

@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 👊