1

I am working on a project that uses Entity Framework and change tracking in SQL Server. I want to create a user defined function that returns the current version of the tracked changes in the database, so that I can use it in my queries with Entity Framework.

However, when I try to create the function, I get the following error:

Invalid use of a side-effecting operator 'change_tracking_current_version' within a function.

Is there a way to create a user defined function with it without causing an error?

Here is the code of my function:

CREATE FUNCTION dbo.GetChangeVersion()
RETURNS bigint
AS
BEGIN
DECLARE @version bigint;
SET @version = CHANGE_TRACKING_CURRENT_VERSION();
RETURN @version;
END
IvanH
  • 5,039
  • 14
  • 60
  • 81
  • 2
    Possible cross-site duplicate: [Invalid use of a side-effecting operator 'change\_tracking\_min\_valid\_version' within a function](https://dba.stackexchange.com/questions/117735/invalid-use-of-a-side-effecting-operator-change-tracking-min-valid-version-wit) – Thom A Jul 21 '23 at 11:15
  • 1
    why do you need to create a function if you can just call it – siggemannen Jul 21 '23 at 11:20
  • 1
    @ThomA - Similar but `CHANGE_TRACKING_CURRENT_VERSION` and `CHANGE_TRACKING_MIN_VALID_VERSION(table_object_id)` are not the same – Martin Smith Jul 21 '23 at 11:34
  • Not the same function, sure, but the *reason* almost certainly is, @MartinSmith . They are both `CHANGE_TRACKING__VERSION` functions giving side-effecting errors, when they likely aren't (really). – Thom A Jul 21 '23 at 11:43
  • 1
    But the difference is important. As `CHANGE_TRACKING_CURRENT_VERSION` is niladic that makes the view workaround more viable for this case – Martin Smith Jul 21 '23 at 11:47
  • @siggemannen because I need it for Entity Framework and I do not know any other method how to map system function (I can map only user defined ones). – IvanH Jul 21 '23 at 12:15

1 Answers1

2

You can use the same workaround as also works for RAND().

Create a helper view

CREATE VIEW dbo.CHANGE_TRACKING_CURRENT_VERSION
AS
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CurrentVersion

Then use

CREATE FUNCTION dbo.GetChangeVersion()
RETURNS bigint
AS
BEGIN
DECLARE @version bigint;
SET @version = (SELECT CurrentVersion FROM dbo.CHANGE_TRACKING_CURRENT_VERSION)
RETURN @version;
END
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This is not answer to 'why' but to 'how' but I have change the question to fit to the answer because it it in reality what I need to know. – IvanH Jul 21 '23 at 12:11
  • Yeah I just answered the "Is there a way to create a user defined function with it without causing an error?" part. As I don't have access to the source code of builtin functions I can't answer "why" – Martin Smith Jul 21 '23 at 12:22