ITKeyword,专注技术干货聚合推荐

注册 | 登录

如何使用ASP.NET Web API OData在Oracle中使用Entity Framework 6.x Code-First方式开发 OData V4 Service

分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:在ASP.NET Web API中使用OData的单例模式

  从OData v4开始增加了对单例模式的支持,我们不用每次根据主键等来获取某个EDM,就像在C#中使用单例模式一样。实现方式大致需要两步:   1、在需要实现单例模

 

环境: 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="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" /> <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

推荐:在ASP.NET Web API中使用OData的Containment

  通常情况下,一个OData的EDM(Entity Data Model)在配置的时候定义了,才可以被查询或执行各种操作。比如如下:   builder.EntitySet<SomeModel>("SomeModels")

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/

推荐:在ASP.NET Web API中使用OData的Action和Function

  本篇体验OData的Action和Function功能。上下文信息参考"ASP.NET Web API基于OData的增删改查,以及处理实体间关系"。在本文之前,我存在的疑惑包括:   ● 为

  环境: Visual Studio 2013 + .Net Framework 4.5.2 1.新建项目 2.安装OData,ODP.NET 安装的包:   下面是部分代码: using System;using System.Collections.Generic;using System.Linq;

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。