如何使用ASP.NET Web API OData在Oracle中使用Entity Framework 6.x Code-First方式开发 OData V4 Service-猿圈-程序猿的知识社区

环境: Visual Studio 2013 + .Net Framework 4.5.2

1.新建项目

2.安装OData，ODP.NET

安装的包：

下面是部分代码：

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.
        /// 自增ID
        /// </summary>
        /// <value>The identifier.</value>
        public decimal ID { get; set; }

        /// <summary>
        /// Gets or sets the audit template no.
        /// 审批模板编码
        /// </summary>
        /// <value>The audit template no.</value>
        public string AUDITTEMPLATENO { get; set; }

        /// <summary>
        /// Gets or sets the name of the audit template.
        /// 审批模板名称
        /// </summary>
        /// <value>The name of the audit template.</value>
        public string AUDITTEMPLATENAME { get; set; }

        /// <summary>
        /// Gets or sets the bill type no.
        /// 单据类型
        /// </summary>
        /// <value>The bill type no.</value>
        public string BILLTYPENO { get; set; }

        /// <summary>
        /// Gets or sets the remark.
        /// 备注
        /// </summary>
        /// <value>The remark.</value>
        public string REMARK { get; set; }

        /// <summary>
        /// Gets or sets the template status.
        /// 状态1有效 0无效
        /// </summary>
        /// <value>The template status.</value>
        public decimal TEMPLATESTATUS { get; set; }

        /// <summary>
        /// Gets or sets the company no.
        /// 公司编码
        /// </summary>
        /// <value>The company no.</value>
        public string COMPANYNO { get; set; }

        /// <summary>
        /// Gets or sets the create time.
        /// 创建时间
        /// </summary>
        /// <value>The create time.</value>
        public DateTime CREATETIME { get; set; }

        /// <summary>
        /// Gets or sets the create user no.
        /// 创建人
        /// </summary>
        /// <value>The create user no.</value>
        public string CREATEUSERNO { get; set; }

        /// <summary>
        /// Gets or sets the update time.
        /// 更新时间
        /// </summary>
        /// <value>The update time.</value>
        public DateTime UPDATETIME { get; set; }

        /// <summary>
        /// Gets or sets the update user no.
        /// 更新人
        /// </summary>
        /// <value>The update user no.</value>
        public string UPDATEUSERNO { get; set; }
    }
}

这个是实体代码，类名称和属性名称要和数据库中的表结构表名和字段名称一样，大小写都一样。

EF Context代码：

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>();
        }
    }
}

　　这里是你的用户，

"TEST" 一定要大写

modelBuilder.HasDefaultSchema("TEST");

Controller部分代码：

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代码：

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 配置和服务
            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 路由
            //config.MapHttpAttributeRoutes();

            //config.Routes.MapHttpRoute(
            //    name: "DefaultApi",
            //    routeTemplate: "api/{controller}/{id}",
            //    defaults: new { id = RouteParameter.Optional }
            //);
        }
    }
}

　　WebApiApplication代码：

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文件：

<?xml version="1.0" encoding="utf-8"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  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>
  <appSettings></appSettings>


 <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" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
  <system.data>
    <DbProviderFactories>
      <remove invariant="Oracle.ManagedDataAccess.Client" />
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"
          type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </DbProviderFactories>
</system.data>
  <system.webServer>
    <handlers>
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <remove name="OPTIONSVerbHandler" />
      <remove name="TRACEVerbHandler" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security.OAuth" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security.Cookies" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.2.2.0" newVersion="5.2.2.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <!--<oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="XE" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE))) "/>
        <dataSource alias="test" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test))) " />
      </dataSources>
    </version>
  </oracle.manageddataaccess.client>-->
  <entityFramework>
    <!--<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>-->
    <defaultConnectionFactory type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory,
      Oracle.ManagedDataAccess.EntityFramework,
      Version=6.121.2.0,
      Culture=neutral,
      PublicKeyToken=89b483f429c47342" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
      <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices,Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </providers>
  </entityFramework>
</configuration>

运行结果：

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%20desc

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

请参考Odata.org官网：

基础操作：

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

高级操作：

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

Github地址:

http://odata.github.io/

https://github.com/OData/RESTier

参考文章：

二篇教程(英文版):

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/

菜单 学习猿地 - LMONKEY

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练！告别无效练习

接私单赚外块

VIP优先接，累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

领取更多软件工程师实用特权

Java开发工程师

何以解忧，唯有 Java！

Python开发工程师

人生苦短我要学Python！

PHP开发工程师

PHP是世界上最好的编程语言!

GO开发工程师

想优雅的写程序，赶紧GO!

大数据开发工程师

弄大数据，就是在搞革命!

前端开发工程师

不仅最好，而且最全!

UI开发工程师

这个世界从来不缺少美，缺少你来创造美!

Linux运维工程师

不只是说说而已！

WEB前端1+X

不仅为考证，轻松做开发

计算机二级（C语言）

一切只为考证！

猿工手册

各种工具精挑细选

猿材料

各种工具精挑细选

猿代码

各种工具精挑细选

猿著课件

各种工具精挑细选

知识题库

知识闯关节节高，刷题涨知识！

阶段练习

单元测试知识学习状况秒掌握！

期末考试

期末测试，学习情况即刻知道！

面试题库

最新的企业技术人员招聘真题练习！

Java专区

2948篇 ｜ 24.5万人浏览

Python专区

2593篇 ｜ 19.5万人浏览

大数据专区

2948篇 ｜ 18.5万人浏览

PHP专区

3953篇 ｜ 27.5万人浏览

Go专区

1382篇 ｜ 12.5万人浏览

Web前端专区

1453篇 ｜ 31.5万人浏览

Linux云计算

1230篇 ｜ 4.5万人浏览

其他专区

53232篇文章 ｜ 11.5万人浏览

搜索

历史记录 清除记录

近期热搜

项目开发全程实录（电商EW_Shop）

8674 人 1年前

轻松学会Laravel-项目篇（商城API）

2022 人 1年前

Python数据分析2.0-金融

325 人 1年前

Python办公自动化2.0

241 人 1年前

使用账号登录

启用更安全省心的  微信登录

Java开发工程师

何以解忧，唯有 Java！

Python开发工程师

菜单学习猿地 - LMONKEY

尊享10项VIP特权持续新增

2948篇｜ 24.5万人浏览

2593篇｜ 19.5万人浏览

2948篇｜ 18.5万人浏览

3953篇｜ 27.5万人浏览

1382篇｜ 12.5万人浏览

1453篇｜ 31.5万人浏览

1230篇｜ 4.5万人浏览

53232篇文章｜ 11.5万人浏览

历史记录清除记录

2948篇｜ 24.5万人浏览

2593篇｜ 19.5万人浏览

2948篇｜ 18.5万人浏览

3953篇｜ 27.5万人浏览

1382篇｜ 12.5万人浏览

1453篇｜ 31.5万人浏览

1230篇｜ 4.5万人浏览

53232篇文章｜ 11.5万人浏览