Wrap Sql query inside another Sql query using Peta Poco

Question

How can you wrap a Sql query with arguments inside another query?

For instance:

var sqlCount = new Sql("COUNT(*) as Result").From("ArticleGroups");
sqlCount.LeftJoin("Articles").On("Articles.ArticleGroupId = ArticleGroups.Id");
sqlCount.Where("Articles.SupplierId = @supplier", new { supplier = supplier });
sqlCount.GroupBy("ArticleGroups.Id, ArticleGroups.Description");

var sqlCountWrap = new Sql();
sqlCountWrap.Select("COUNT(*)").From(string.Format("({0}) pt", sqlCount.ToString()));

So my second Sql builder needs to wrap the first query into another one, but I get exception about the argument that is missing.

How can I fix this?

UPDATE

var sqlCountWrap = new Sql(sqlCount.ToString(), sqlCount.Arguments);
sqlCountWrap.Select("COUNT(*)").From(string.Format("({0}) pt", sqlCount.ToString()));

Passing the sql and arguments into the new Sql instance doesn't help:

Exception:

base = {"Specified argument was out of the range of valid values.\r\nParameter name: Parameter '@0' specified but only 0 parameters supplied (in `FROM (SELECT COUNT(*) as Result\nFROM ArticleGroups\nLEFT JOIN Articles\nON Articles.ArticleGroupId = ArticleGroups.Id...

score 0 · Answer 1 · answered Nov 16 '17 at 14:18

For everyone with the same issue as me, here is the solution:

var sqlCountWrap = new Sql();
sqlCountWrap.Select("COUNT(*)");
sqlCountWrap.Append("FROM (");
sqlCountWrap.Append(sqlCount.SQL, sqlCount.Arguments);
sqlCountWrap.Append(") pt");

Using the append you can wrap the sql inside another sql and pass the arguments.

Wrap Sql query inside another Sql query using Peta Poco

1 Answers1