SkipTake<String>(int, int, PetaPoco.Sql) returning the wrong column

[jodydonetti]

Hi, i'm using PetaPoco v4.0.3 and doing this:

db.SkipTake<String>(10, 20, PetaPoco.Sql)

where the PetaPoco.Sql variable contains the following SQL code, generated using the helper methods:

SELECT ColumnName1 FROM TableName ORDER BY ColumnName2

I've tracked down the problem to this: once the sql query has been executed and it is creating the instances for each rows, it gets the value from the first column (i assume it does this because of the fact that <T> is a primitive type) but, since i'm using SkipTake<T> , it builds the final sql code adding the ROW_NUMBER() stuff and it adds, as a first column, the peta_rn column.

The code i'm talking about is this:

sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
          sqlOrderBy==null ? "ORDER BY (SELECT NULL)" : sqlOrderBy, sqlSelectRemoved, args.Length, args.Length + 1);

Now, i've tryied to simply put the peta_rn field at the end of the column list, like this:

sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) {1} , peta_rn) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
          sqlOrderBy==null ? "ORDER BY (SELECT NULL)" : sqlOrderBy, sqlSelectRemoved, args.Length, args.Length + 1);

but then the problem moved forward, i suppose in one of these 2 points:

1. when you are IL-emitting the low-level stuff
2. inside the FindSplitPoint(...) method

At feeling, i think it may depends from the fact that normally (deserializing complex types) it removes the first column but, when doing the same for simple types, it doesn't do that.

Am i right? Is it possible to fix that?

Thanks, and btw excellent work so far!

[jodydonetti]

UPDATE: my bad, after more than 14 hours of coding in a row, i didn't noticed that peta_rn in that particular position was defining the alias for the ROW_NUMBER() column expression.

Now i'm trying to play around with this piece:

sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
          sqlOrderBy==null ? "ORDER BY (SELECT NULL)" : sqlOrderBy, sqlSelectRemoved, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, skip+take }).ToArray();

and i'm trying to replace the initial SELECT * with something like SELECT col1,col2,col3 but the problem is the thing most near to the column list is the sqlSelectRemoved variable, that btw contains the FROM TableName part too.

Obviously i may remove that part easily (and, in fact, i'm doing some tests in that direction) but i don't know which other kind of SQL code may contains that variable, so i would like to know if that is not an hazard.

Thanks