How to use ASP. NET Web API OData to develop OData V4 Service and odatacode-First using Entity Framework 6.x Code-first in Oracle

How to use ASP. NET Web API OData to develop OData V4 Service and odatacode-First using Entity Framework 6.x Code-first in Oracle

 

Environment: Visual Studio 2013 +. Net Framework 4.5.2

1. Create a project

2. Install OData, ODP. NET

Installation package:

 

The following is some code:

Using System; using System. collections. generic; using System. linq; using System. web; namespace WebAppOdataEF. models {public class AUDIT_TEMPLATE {// <summary> // Gets or sets the identifier. /// auto-increment ID /// </summary> /// <value> The identifier. </value> public decimal ID {get; set ;}/// <summary> // Gets or sets the audit template no. /// approve template code /// </summary> /// <value> The audit template no. </value> public string AUDITTEMPLATENO {get; set ;}/// <summary> // Gets or sets the name of the audit template. /// Approval template name /// </summary> /// <value> The name of the audit template. </value> public string AUDITTEMPLATENAME {get; set ;}/// <summary> // Gets or sets the bill type no. /// document type /// </summary> /// <value> The bill type no. </value> public string BILLTYPENO {get; set ;}/// <summary> // Gets or sets the remark. /// remarks /// </summary> /// <value> The remark. </value> public string REMARK {get; set ;}/// <summary> // Gets or sets the template status. /// status 1 valid 0 invalid // </summary> /// <value> The template status. </value> public decimal TEMPLATESTATUS {get; set ;}/// <summary> // Gets or sets the company no. /// company code /// </summary> /// <value> The company no. </value> public string COMPANYNO {get; set ;}/// <summary> // Gets or sets the create time. /// creation time /// </summary> /// <value> The create time. </value> public DateTime CREATETIME {get; set ;}/// <summary> // Gets or sets the create user no. /// creator /// </summary> /// <value> The create user no. </value> public string CREATEUSERNO {get; set ;}/// <summary> // Gets or sets the update time. /// update time /// </summary> /// <value> The update time. </value> public DateTime UPDATETIME {get; set ;}/// <summary> // Gets or sets the update user no. /// updated by /// </summary> /// <value> The update user no. </value> public string UPDATEUSERNO {get; set ;}}}

This is the entity code. The class name and attribute name must be the same as the table structure table name and field name in the database, and must be in the same case.

EF Context code:

using System;using System.Collections.Generic;using System.Data.Entity;using System.Data.Entity.ModelConfiguration.Conventions;using System.Linq;using System.Web;namespace WebAppOdataEF.Models{    public class TemplatesContext : DbContext    {        static TemplatesContext()        {            Database.SetInitializer<TestsContext>(null);            //Database.SetInitializer(new CreateDatabaseIfNotExists<TestsContext>());            //Database.SetInitializer(new DropCreateDatabaseAlways<TestsContext>());            //Database.SetInitializer(new DropCreateDatabaseIfModelChanges<TestsContext>());        }        /// <summary>        /// Initializes a new instance of the <see cref="TemplatesContext"/> class.        /// </summary>        public TemplatesContext()            : base("name=SysBasicOracleDbContext")        {            this.Configuration.LazyLoadingEnabled = false;        }        /// <summary>        /// Gets or sets the templates.        /// </summary>        /// <value>The templates.</value>        public DbSet<AUDIT_TEMPLATE> Templates { get; set; }        protected override void OnModelCreating(DbModelBuilder modelBuilder)        {            modelBuilder.HasDefaultSchema("TEST");            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();            modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();        }    }}

Here is your user,

"TEST" must be capitalized

ModelBuilder. hasdefaschema Schema ("TEST ");

Controller code:

using System;using System.Collections.Generic;using System.Linq;using System.Net;using System.Net.Http;using System.Web.Http;using System.Web.OData;using WebAppOdataEF.Models;namespace WebAppOdataEF.Controllers{    public class TemplatesController : ODataController    {        TemplatesContext db = new TemplatesContext();        private bool TestExists(int key)        {            return db.Templates.Any(p => p.ID == key);        }        protected override void Dispose(bool disposing)        {            db.Dispose();            base.Dispose(disposing);        }        [EnableQuery]        public IHttpActionResult Get()        {            return Ok(db.Templates);        }        [EnableQuery]        public SingleResult<AUDIT_TEMPLATE> Get([FromODataUri] int key)        {            IQueryable<AUDIT_TEMPLATE> result = db.Templates.Where(p => p.ID == key);            return SingleResult.Create(result);        }    }}

　　

using System;using System.Collections.Generic;using System.Linq;using System.Net;using System.Net.Http;using System.Web.Http;using WebAppOdataEF.Models;using System.Data.Entity;using System.Data.Entity.Infrastructure;using System.Threading.Tasks;using System.Web.OData;namespace WebAppOdataEF.Controllers{    public class TestsController : ODataController    {        TestsContext db = new TestsContext();        private bool TestExists(int key)        {            return db.Tests.Any(p => p.ID == key);        }        protected override void Dispose(bool disposing)        {            db.Dispose();            base.Dispose(disposing);        }        [EnableQuery]        public IHttpActionResult Get()        {            return Ok(db.Tests);        }        [EnableQuery]        public SingleResult<TESTS> Get([FromODataUri] int key)        {            IQueryable<TESTS> result = db.Tests.Where(p => p.ID == key);            return SingleResult.Create(result);        }        public async Task<IHttpActionResult> Post(TESTS product)        {            if (!ModelState.IsValid)            {                return BadRequest(ModelState);            }            db.Tests.Add(product);            await db.SaveChangesAsync();            return Created(product);        }        public async Task<IHttpActionResult> Patch([FromODataUri] int key, Delta<TESTS> product)        {            if (!ModelState.IsValid)            {                return BadRequest(ModelState);            }            var entity = await db.Tests.FindAsync(key);            if (entity == null)            {                return NotFound();            }            product.Patch(entity);            try            {                await db.SaveChangesAsync();            }            catch (DbUpdateConcurrencyException)            {                if (!TestExists(key))                {                    return NotFound();                }                else                {                    throw;                }            }            return Updated(entity);        }        public async Task<IHttpActionResult> Put([FromODataUri] int key, TESTS update)        {            if (!ModelState.IsValid)            {                return BadRequest(ModelState);            }            if (key != update.ID)            {                return BadRequest();            }            db.Entry(update).State = EntityState.Modified;            try            {                await db.SaveChangesAsync();            }            catch (DbUpdateConcurrencyException)            {                if (!TestExists(key))                {                    return NotFound();                }                else                {                    throw;                }            }            return Updated(update);        }        public async Task<IHttpActionResult> Delete([FromODataUri] int key)        {            var product = await db.Tests.FindAsync(key);            if (product == null)            {                return NotFound();            }            db.Tests.Remove(product);            await db.SaveChangesAsync();            return StatusCode(HttpStatusCode.NoContent);        }    }}

WebApiConfig code:

Using System; using System. collections. generic; using System. linq; using System. web. http; using WebAppOdataEF. models; using System. web. OData. builder; using System. web. OData. extensions; namespace WebAppOdataEF {public static class WebApiConfig {public static void Register (HttpConfiguration config) {// Web API configuration and service ODataModelBuilder builder = new ODataConventionModelBuilder (); builder. entitySet <TESTS> ("Tests"); builder. entitySet <TB_MENU> ("TbMenus"); builder. entitySet <AUDIT_TEMPLATE> ("Templates"); config. mapODataServiceRoute (routeName: "ODataRoute", routePrefix: null, model: builder. getEdmModel (); // Web API route // config. mapHttpAttributeRoutes (); // config. routes. mapHttpRoute (// name: "DefaultApi", // routeTemplate: "api/{controller}/{id}", // defaults: new {id = RouteParameter. optional }//);}}}

WebApiApplication code:

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Http;using System.Web.Routing;namespace WebAppOdataEF{    public class WebApiApplication : System.Web.HttpApplication    {        protected void Application_Start()        {            GlobalConfiguration.Configure(WebApiConfig.Register);        }    }}

WebConfig file:

<? Xml version = "1.0" encoding = "UTF-8"?> <! -- For more information about how to configure ASP. NET applications, visit the http://go.microsoft.com/fwlink? LinkId = 301879 --> <configuration> <configSections> <! -- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink? LinkID = 237468 --> <section name = "entityFramework" type = "System. data. entity. internal. configFile. entityFrameworkSection, EntityFramework, Version = 6.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089 "requirePermission =" false "/> <! -- <Section name = "oracle. manageddataaccess. client "type =" OracleInternal. common. ODPMSectionHandler, Oracle. managedDataAccess, Version = 4.121.2.0, Culture = neutral, PublicKeyToken = 89b483f429c47342 "/> --> </configSections> <etettings> </appSettings>

<! -- <ConnectionStrings>
<Add name = "TestsContext" providerName = "Oracle. ManagedDataAccess. Client"
ConnectionString = "User Id = system; Password = 111111; Data Source = XE"/>
<Add name = "SysBasicOracleDbContext" providerName = "Oracle. ManagedDataAccess. Client" connectionString = "User Id = test; Password = test; Data Source = test"/>
</ConnectionStrings> -->

 <connectionStrings>
    <add name="SysBasicOracleDbContext" connectionString="Data Source= (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

 (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) );User ID=test;Password=test;Persist Security Info=True" providerName="Oracle.ManagedDataAccess.Client" /> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.5" /> 

　　

Running result:

Http: // localhost: 54577/Templates

Http: // localhost: 54577/Templates (7)

Http: // localhost: 54577/Templates? $ Count = true

Http: // localhost: 54577/Templates? $ Count = true & $ select = ID, REMARK

Http: // localhost: 54577/Templates? $ Count = true & $ select = ID, REMARK & $ top = 2 & $ skip = 1

Http: // localhost: 54577/Templates? $ Count = true & $ select = ID, REMARK & $ top = 2 & $ skip = 1 & $ orderby = ID % 20 desc

Http: // localhost: 54577/Templates? $ Count = true & $ filter = AUDITTEMPLATENO % 20eq % 20% 27t5% 27

Please refer to Odata.org Official Website:

Basic operations:

Http://www.odata.org/getting-started/basic-tutorial/#filter

Advanced Operations:

Http://www.odata.org/getting-started/advanced-tutorial/

Github address:

Http://odata.github.io/

Https://github.com/OData/RESTier

 

 

References:

Tutorial 2 (English version ):

1. Using NuGet to Install and Configure Oracle Data Provider for. NET

2. Entity Framework Code First and Code First Migrations for Oracle Database

Http://www.cnblogs.com/yjmyzz/p/how-to-use-code-first-in-oracle-with-entity-framework-6.html

Http://www.cnblogs.com/shanyou/archive/2010/02/19/1669360.html

Http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/create-an-odata-v4-endpoint

Http://www.odata.org/blog/how-to-use-web-api-odata-to-build-an-odata-v4-service-without-entity-framework/