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 Companies()
{
var service = new ODataService();
var result = service.Execute(Company.GetCompanies(), HttpUtility.ParseQueryString(this.Request.Url.Query));
return(this.Content(result.Results.ToString(), "application/json"));
}
protected override void OnStateChanged(EventArgs e)
{
if (State == HttpServerState.Started)
{
_service = new ODataService(
_sessionFactroy,
String.Format("http://{0}/OData/", EndPoint),
"SouthWind",
"NHibernate.OData.Demo"
);
}
}
protected override void OnStateChanged(EventArgs e)
{
if (State == HttpServerState.Started)
{
_service = new ODataService(
_sessionFactroy,
String.Format("http://{0}/OData/", EndPoint),
"SouthWind",
"NHibernate.OData.Demo"
);
}
}
public HttpResponseMessage Companies(HttpRequestMessage request)
{
var service = new ODataService();
var result = service.Execute(Company.GetCompanies(), request.GetQueryNameValuePairs());
var response = request.CreateResponse();
response.Content = new StringContent(result.Results.ToString(), Encoding.UTF8, "application/json");
return response;
// alternatively, we could change the return type to object and do:
// return JObject.Parse(result.Results.ToString());
// this is necessary (for now) since WebApi wants to be the one to do the serialization
}
public HttpResponseMessage Companies(HttpRequestMessage request)
{
var service = new ODataService();
var result = service.Execute(Company.GetCompanies(), request.GetQueryNameValuePairs());
var response = request.CreateResponse();
response.Content = new StringContent(result.Results.ToString(), Encoding.UTF8, "application/json");
return(response);
// alternatively, we could change the return type to object and do:
// return JObject.Parse(result.Results.ToString());
// this is necessary (for now) since WebApi wants to be the one to do the serialization
}
protected override TestServer CreateTestServer()
{
var service = new ODataService();
return(new TestServer(url =>
{
using (var db = new CustomersContext())
{
var query = db.Customers;
var result = service.Execute(query, HttpUtility.ParseQueryString(url.Query));
return result.Results.ToString();
}
}));
}
protected override TestServer CreateTestServer()
{
var service = new ODataService();
var dynamicQuery = CustomersContext.GetCustomers()
.Select(ToODataEntity)
.Cast <ODataEntity>()
.ToArray()
.AsQueryable();
return(new TestServer(url =>
{
var result = service.Execute(dynamicQuery, HttpUtility.ParseQueryString(url.Query));
return result.Results.ToString();
}));
}
public HttpResponseMessage Count(string name, string collection, HttpRequestMessage request)
{
int count = ODataService.Count(name, collection, request.GetQueryNameValuePairs());
string mediaType = request.GetResponseMediaType();
switch (mediaType)
{
case "application/json":
string json = string.Format("{{\"Count\": {0}}}", count);
return(CreateHttpResponseMessage(json, request));
case "application/xml":
XElement element = new XElement("Count", count);
return(CreateHttpResponseMessage(element, request));
default:
throw new NotSupportedException(mediaType.ToString());
}
}
public HttpResponseMessage GetUtcNow(string name, HttpRequestMessage request)
{
const string FORMAT = "yyyy-MM-ddTHH:mm:ss.FFFFFFFZ";
DateTime utcNow = ODataService.GetUtcNow(name);
string mediaType = request.GetResponseMediaType();
switch (mediaType)
{
case "application/json":
string json = string.Format("{{\"utcnow\": {0}}}", utcNow.ToString(FORMAT));
return(CreateHttpResponseMessage(json, request));
case "application/xml":
XElement element = new XElement("utcnow", utcNow.ToString(FORMAT));
return(CreateHttpResponseMessage(element, request));
default:
throw new NotSupportedException(mediaType.ToString());
}
}
public HttpResponseMessage GetCollection(string name, string collection, HttpRequestMessage request)
{
int?count = null;
if (IsPagingQuery(request))
{
count = ODataService.Count(name, collection, request.GetQueryNameValuePairs());
}
string mediaType = request.GetResponseMediaType();
switch (mediaType)
{
case "application/json":
{
ODataService <string> service = new ODataService <string>(name, request.GetQueryNameValuePairs());
IEnumerable <string> jsonCollection = service.GetCollection(collection);
string json = string.Format("[{0}]", string.Join(",", jsonCollection));
if (count != null)
{
json = string.Format("{{\"@count\":{0},\"value\":{1}}}", count, json);
}
return(CreateHttpResponseMessage(json, request));
}
case "application/xml":
{
ODataService <XElement> service = new ODataService <XElement>(name, request.GetQueryNameValuePairs());
IEnumerable <XElement> xCollection = service.GetCollection(collection, out XElement xsd);
XElement element = new XElement(collection, xCollection);
element.SetAttributeValue(XNamespace.Xmlns + "i", XSI);
return(CreateHttpResponseMessage(Pack(element, count, xsd), request));
}
default:
throw new NotSupportedException(mediaType.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"));
}
}
public HttpResponseMessage Find(string name, string collectionKey, HttpRequestMessage request)
{
string collection = SplitCollectionKey(collectionKey, out string[] key);
string mediaType = request.GetResponseMediaType();
switch (mediaType)
{
case "application/json":
{
ODataService <string> service = new ODataService <string>(name, request.GetQueryNameValuePairs());
string json = service.Find(collection, key);
return(CreateHttpResponseMessage(json, request));
}
case "application/xml":
{
ODataService <XElement> service = new ODataService <XElement>(name, request.GetQueryNameValuePairs());
XElement element = service.Find(collection, key, out XElement xsd);
if (element == null)
{
string entity = service.Schema.Elements("entity").First(x => x.Attribute("collection").Value == collection).Attribute("name").Value;
element = new XElement(entity);
element.SetAttributeValue(XNamespace.Xmlns + "i", XSI);
element.SetAttributeValue(XSINamespace + "nil", "true");
return(CreateHttpResponseMessage(element, request));
}
element.SetAttributeValue(XNamespace.Xmlns + "i", XSI);
return(CreateHttpResponseMessage(Pack(element, null, xsd), request));
}
default:
throw new NotSupportedException(mediaType.ToString());
}
}
public HttpResponseMessage GetDefault(string name, string entity, HttpRequestMessage request)
{
string mediaType = request.GetResponseMediaType();
switch (mediaType)
{
case "application/json":
{
string json = new ODataService <string>(name, request.GetQueryNameValuePairs()).GetDefault(entity);
return(CreateHttpResponseMessage(json, request));
}
case "application/xml":
{
XElement element = new ODataService <XElement>(name, request.GetQueryNameValuePairs()).GetDefault(entity, out XElement xsd);
element.SetAttributeValue(XNamespace.Xmlns + "i", XSI);
return(CreateHttpResponseMessage(Pack(element, null, xsd), request));
}
default:
throw new NotSupportedException(mediaType.ToString());
}
}
public Person(int Id)
{
var oDataService = new ODataService(new Uri("YourURL"));
Name = oDataService.Persons.Where(x => x.Id == Id).Select(x => x.Name);
}
public ActionResult Companies()
{
var service = new ODataService();
var result = service.Execute(Company.GetCompanies(), HttpUtility.ParseQueryString(this.Request.Url.Query));
return this.Content(result.Results.ToString(), "application/json");
}