How to query System generated columns on Developer Edition

Question

We are using EF 8.0 to manage data, one of the recent challenges is that the query on SQL Server Developer Edition and Enterpise Edition returns different results.

i.e., two columns StartTime/EndTime are generated like

entity.Property(e => e.StartTime).ValueGeneratedOnAddOrUpdate();	
			

However, a rawSql (Select *) won't return those two columns on Developer Edition. My question: how to write the right query disregarding the different editions? Thanks.

Answer 1

After the edit of your question, I am brave enough to make a guess of what is going on. But it is just a guess - I have to read between the lines quite a bit.

When you say:

However, a rawSql (Select *) won't return those two column

That sounds like if you run

SELECT * FROM tbl

You don't see these two columns at all.

That would be the case if the table in question is a temporal table and the period columns have the HIDDEN attribute. Compare these two:

CREATE TABLE temporal1 (
   keycol int NOT NULL,
   datacol nvarchar(22) NOT NULL,
   starttime datetime2(3) GENERATED ALWAYS AS ROW START,
   endtime datetime2(3) GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (starttime, endtime),
   CONSTRAINT pk_temporal1 PRIMARY KEY (keycol)
)
WITH (SYSTEM_VERSIONING = ON)
CREATE TABLE temporal2 (
   keycol int NOT NULL,
   datacol nvarchar(22) NOT NULL,
   starttime datetime2(3) GENERATED ALWAYS AS ROW START HIDDEN,
   endtime datetime2(3) GENERATED ALWAYS AS ROW END HIDDEN,
   PERIOD FOR SYSTEM_TIME (starttime, endtime),
   CONSTRAINT pk_temporal2 PRIMARY KEY (keycol)
)
WITH (SYSTEM_VERSIONING = ON)
SELECT * FROM temporal1
SELECT * FROM temporal2