var res = await c.GetTable<Mytable>().
          Where(x=>x.Username == username).
          OrderByDescending(x=>x.State == state).
          ThenByDescending(x=>x.Date).
          Skip(startFromIndex ?? 0).Take(20).
          Select(r=>
               new MyRecord
               {
                   Rating = Math.Round(r.Rating, 2),
                   City = r.City,
                   State = r.State,
                   FirstName = r.FirstName,
                   LastName = r.LastName,
                   Description= r.Description,
                   Date = r.Date,
               }
                    ).
          ToListAsync();

DECLARE @username NVarChar(100) -- String
SET     @username = N'XXXXXXXXX'
DECLARE @state_2 NVarChar(2) -- String
SET     @state_2 = N'KY'
DECLARE @skip Int -- Int32
SET     @skip = 900
DECLARE @take Int -- Int32
SET     @take = 20

SELECT
    [x].[rating],
    [x].[city],
    [x].[state],
    [x].[firstName],
    [x].[lastName],
    [x].[description],
    [x].[date]
FROM
    [dbo].[mytable] [x]
WHERE
    [x].[username] = @username 
ORDER BY
    IIF([x].[state] = @state_2, 1, IIF((NOT ([x].[state] = @state_2)), 0, NULL)) DESC,
    [x].[date] DESC
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY

С>DECLARE @username NVarChar(100) -- String
С>SET     @username = N'XXXXXXXXX'
С>DECLARE @state_2 NVarChar(2) -- String
С>SET     @state_2 = N'KY'
С>DECLARE @skip Int -- Int32
С>SET     @skip = 900
С>DECLARE @take Int -- Int32
С>SET     @take = 20

С>SELECT
С>    [x].[rating],
С>    [x].[city],
С>    [x].[state],
С>    [x].[firstName],
С>    [x].[lastName],
С>    [x].[description],
С>    [x].[date]
С>FROM
С>    [dbo].[mytable] [x]
С>WHERE
С>    [x].[username] = @username 
С>ORDER BY
С>    IIF([x].[state] = @state_2, 1, IIF((NOT ([x].[state] = @state_2)), 0, NULL)) DESC,
С>    [x].[date] DESC
С>OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY