I have the following T-SQL that I want to write in c# code using LINQ and entity framework core...
UPDATE [MyTable1] SET [LastContacted] = CURRENT_TIMESTAMP FROM [MyTable1] t1 JOIN [MyTable2] t2 ON t2.[ID] = t1.[Table1ID] WHERE t2.[MyColumn1] = 'MySearchName'
The bit I'm having a problem with is setting the CURRENT_TIMESTAMP. It MUST use the time from the database and not the time from the service running the code so I can't use DateTime.Now as that would be the time from the service not the database time as there is no gaurantee the clocks are in sync.
At the moment I'm using ExecuteSqlInterpolated to execute this command as hard coded SQL but really I want to use EF to update via the entity models and SaveChanges() etc, but I don't know if there is anyway to get EF/LINQ to set the value to something that can only be got when the query runs on the database.
Does anyone know if this is possible and how to do it?
This would be one of the times where I would consider a database trigger and not do anything in C#