Linq2db (MySQL). Set Row Index

Question

I need to specify the row index for each row (Mysql 5.7 with Linq2db).

How can I build a query like

SELECT `t`.*,  @n:=@n+1 as rank FROM ('some query') `t`, (SELECT @n := 0) `rowcounter`

Or how can I do it in another way

Answer 1

Here we go

[Sql.Expression("@n:=@n+1", ServerSideOnly = true)]
static int IncrementIndex()
{
    throw new NotImplementedException();
}

[Test, MySqlDataContext]
public void RowIndexTest(string context)
{
    using (var db = GetDataContext(context))
    {
        db.NextQueryHints.Add(", (SELECT @n := 0) `rowcounter`");

        var q =
            from p in db.Person
            select new
            {
                rank = IncrementIndex(),
                id   = p.ID
            };

        var list = q.ToList();
    }
}

Also do not forget to add Allow User Variables=True; to your connection string.

Answer 2

There isn't a particular LINQ way to get a row index from a SQL query but after you pull the SQL data over into your client, you can get an index over the query.

var ans = someQuery.AsEnumerable().Select((t, rank) => new { t, rank });