Is there a way to query a temporal table to see if any history exists?-CodePudding

My EF Core 6 application is using SQL Temporal tables.

I have an existing query that returns a number of rows based on a filter:

       var results = Context.MyTable
            .Where(cn => cn.Name == "Fred")
            .Skip(5)
            .Take(10)
            .Select(x=>new { FirstName = x=>x.Name, Dob = x=>x.Dob });

What I want to do, is add a property in the response that shows if there is any history for each row in the query, so I can return it to the UI, which will show paging controls for stepping through the history.

       var results = Context.MyTable
            .Where(cn => cn.Name == "Fred")
            .Skip(5)
            .Take(10)
            .Select(x=>new { FirstName = x=>x.Name, Dob = x=>x.Dob, HasHistory = x.???? });

CodePudding user response：

Try the following:

var results = Context.MyTable
    .Where(cn => cn.Name == "Fred")
    .Skip(5)
    .Take(10)
    .Select(x => new 
    { 
        FirstName = x.Name, 
        Dob = x.Dob, 
        HasHistory = Context.MyTable.TemporalAll().Count(h => h.Id == x.Id) > 1
    });