created a odata v4 project using visual studio 2015 with "EF Designer From Database"
I got 2 entity and they are sql-view i created from microsoft sql server 2015,
i can get the data with simply odata uri localhost:post/odata/BookingInfoes('BookingId') or localhsot:post/odata/Timemarkers('BookingId')
My question is one BookingInfo relate to many timemarker items and i couldnt use the command ?$expand, i tried many approach in google but still no luck,
i want something like
{BookingID:123,BookingDate:"sss",[TimeMarker:[{id:1,info:"sss"},{id:2,info:"balh bla"},{id:3,info:"foo foof oo"}]]}
please help, this stuck me a week and i have no idea how to deal with.
entity model BookingInfo.cs
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace wcod3.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
public partial class BookingInfo
{
[Required]
public string BookingID { get; set; }
public string BookingDate { get; set; }
public string MeetName { get; set; }
public string ChiMeetName { get; set; }
public string VideoSource_1500 { get; set; }
public string languages { get; set; }
public string AvailableLangs { get; set; }
public Nullable<int> StatusMarkers { get; set; }
public string MeetRoomID { get; set; }
public Nullable<System.DateTime> StartTime { get; set; }
public Nullable<int> nexturl { get; set; }
public string StrBookingStartTime { get; set; }
public string MeetomgStatus { get; set; }
[Required]
public List<TimeMarker> TimeMarkers { get; set; }
}
}
Model TimeMarker.cs also generated from sql-view
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace wcod3.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
public partial class TimeMarker
{
public Nullable<long> nrow { get; set; }
public Nullable<System.DateTime> StartTime { get; set; }
public Nullable<int> OrderNo { get; set; }
public string MeetingID { get; set; }
public string AgendaName { get; set; }
public string ChiAgendaName { get; set; }
public int AgendaCode { get; set; }
public string AgendaTime { get; set; }
public string SpeakerCode { get; set; }
public Nullable<int> MarkerID { get; set; }
public string AgendaRunningTime { get; set; }
public Nullable<bool> AllLangFail { get; set; }
public Nullable<bool> isLive { get; set; }
public string PopUpMsg { get; set; }
public Nullable<bool> HasVideo { get; set; }
// public virtual BookingInfo BookingInfoes { get; set; }
}
}
WebApiConfig
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using System.Web.Http;
using System.Web.Http.OData.Builder;
using System.Web.Http.OData.Extensions;
using System.Web.OData.Extensions;
using wcod3.Models;
namespace wcod3
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
ODataModelBuilder builder = new ODataConventionModelBuilder();
config.Count().Filter().OrderBy().Expand().Select().MaxTop(null);
var json = config.Formatters.JsonFormatter;
json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects;
config.Formatters.Remove(config.Formatters.XmlFormatter);
config.Formatters.Remove(config.Formatters.XmlFormatter);
config.Formatters.JsonFormatter.SerializerSettings.Formatting = Formatting.Indented;
config.Formatters.JsonFormatter.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
// var json = config.Formatters.JsonFormatter;
builder.EntitySet<TimeMarker>("TimeMarkers");
builder.EntitySet<MeetingInfo>("BookingInfoes");
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
config.Routes.MapODataServiceRoute("odata", "odata/v4", builder.GetEdmModel());
}
}
}
Here's my BookingsController
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.ModelBinding;
using System.Web.Http.OData;
using System.Web.Http.OData.Routing;
using wcod3.Models;
namespace wcod3.Controllers
{
/*
The WebApiConfig class may require additional changes to add a route for this controller. Merge these statements into the Register method of the WebApiConfig class as applicable. Note that OData URLs are case sensitive.
using System.Web.Http.OData.Builder;
using System.Web.Http.OData.Extensions;
using wcod3.Models;
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<BookingInfo>("BookingInfoes");
config.Routes.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());
*/
public class BookingInfoesController : ODataController
{
private Hotel_devEntities db = new Hotel_devEntities();
// GET: odata/BookingInfoes
[EnableQuery]
public IQueryable<BookingInfo> GetBookingInfoes()
{
return db.BookingInfoes;
}
public IHttpActionResult Get([FromODataUri] string key)
{
return Ok(db.BookingInfoes.AsQueryable().Where(BookingInfo => BookingInfo.BookingID == key));
}
/*** code not show ***/
TimeMarkersController.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.ModelBinding;
using System.Web.Http.OData;
using System.Web.Http.OData.Query;
using System.Web.Http.OData.Routing;
using wcod3.Models;
namespace wcod3.Controllers
{
/*
The WebApiConfig class may require additional changes to add a route for this controller. Merge these statements into the Register method of the WebApiConfig class as applicable. Note that OData URLs are case sensitive.
using System.Web.Http.OData.Builder;
using System.Web.Http.OData.Extensions;
using wcod3.Models;
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<TimeMarker>("TimeMarkers");
config.Routes.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());
*/
public class TimeMarkersController : ODataController
{
private Hotel_devEntities db = new Hotel_devEntities();
public IHttpActionResult Get()
{
return Ok(db.TimeMarkers.AsQueryable());
}
ackages.config
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="EntityFramework" version="6.1.3" targetFramework="net452" />
<package id="Microsoft.AspNet.OData" version="6.0.0" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi.Client" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi.Core" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.AspNet.WebApi.WebHost" version="5.2.3" targetFramework="net452" />
<package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="1.0.0" targetFramework="net452" />
<package id="Microsoft.Data.Edm" version="5.6.0" targetFramework="net452" />
<package id="Microsoft.Data.OData" version="5.6.0" targetFramework="net452" />
<package id="Microsoft.Extensions.DependencyInjection" version="1.0.0" targetFramework="net452" />
<package id="Microsoft.Extensions.DependencyInjection.Abstractions" version="1.0.0" targetFramework="net452" />
<package id="Microsoft.Net.Compilers" version="1.0.0" targetFramework="net452" developmentDependency="true" />
<package id="Microsoft.OData.Core" version="7.0.0" targetFramework="net452" />
<package id="Microsoft.OData.Edm" version="7.0.0" targetFramework="net452" />
<package id="Microsoft.Spatial" version="7.0.0" targetFramework="net452" />
<package id="Newtonsoft.Json" version="10.0.3" targetFramework="net452" />
<package id="System.Spatial" version="5.6.0" targetFramework="net452" />
</packages>
