public ActionResult PivotedData(string productNames)
{
var productNameList = productNames.Split(',');
Product[] products;
using (var context = new OrdersContext())
{
products = context.Products.Where(p => productNames.Contains(p.Name)).ToArray();
var pivotQuery = string.Format(@"(
SELECT c.name AS customer
, {0}
FROM orders o
JOIN customers c ON c.id = o.customer_Id
JOIN products p ON p.id = o.product_Id
WHERE p.id IN ({1})
GROUP BY c.id, c.name
)",
string.Join(", ", products.Select(p => string.Format("SUM(CASE WHEN p.id = {0} THEN o.units ELSE 0 END) AS [{1}]", p.Id, p.Name))),
string.Join(", ", products.Select(p => p.Id))
);
var sqlContext = new ODataSqlContext(
new SqlServerSyntax(SqlServerSyntax.Version.Sql2012),
new DefaultSqlExecutor(() => new SqlConnection(context.Database.Connection.ConnectionString))
);
var query = sqlContext.Query <ODataEntity>(pivotQuery);
var service = new ODataService();
var result = service.Execute(query, HttpUtility.ParseQueryString(this.Request.Url.Query));
return(this.Content(result.Results.ToString(), "application/json"));
}
}
protected override TestServer CreateTestServer()
{
string connectionString;
using (var context = new CustomersContext())
{
context.Database.Initialize(force: false);
connectionString = context.Database.Connection.ConnectionString;
}
SqlSyntax syntax;
using (var connection = new SqlConnection(connectionString))
{
syntax = new SqlServerSyntax(SqlServerSyntax.GetVersion(connection));
}
var sqlContext = new ODataSqlContext(syntax, new DefaultSqlExecutor(() => new SqlConnection(connectionString)));
var service = new ODataService();
return(new TestServer(url =>
{
var query = sqlContext.Query <ODataEntity>("(SELECT * FROM customers)");
var result = service.Execute(query, HttpUtility.ParseQueryString(url.Query));
return result.Results.ToString();
}));
}
public ActionResult Data(int id)
{
using (var context = new CustomersContext())
{
context.Database.Initialize(force: false);
var schema = GenerateRandomSchema(id);
var sql = string.Join(
Environment.NewLine + "UNION ALL ",
Enumerable.Range(0, count: schema.Values.First().Count)
.Select(i => "SELECT " + string.Join(", ", schema.Select(kvp => string.Format("'{0}' AS {1}", kvp.Value[i], kvp.Key))))
);
var sqlContext = new ODataSqlContext(
new SqlServerSyntax(SqlServerSyntax.Version.Sql2012),
new DefaultSqlExecutor(() => new SqlConnection(context.Database.Connection.ConnectionString))
);
var query = sqlContext.Query <ODataEntity>("(" + sql + ")");
var service = new ODataService();
// hack on OData v4 handling
var queryParameters = HttpUtility.ParseQueryString(this.Request.Url.Query);
//if (bool.Parse(queryParameters["$count"] ?? bool.FalseString))
//{
// queryParameters["$inlinecount"] = "allpages";
//}
var result = service.Execute(query, queryParameters);
var resultJObject = JObject.Parse(result.Results.ToString());
//JToken count;
//if (resultJObject.TryGetValue("odata.count", out count))
//{
// resultJObject["@odata.count"] = count;
//}
return(this.Content(resultJObject.ToString(), "application/json"));
}
}