时间：2021-07-01 10:21:17 帮助过：16人阅读
http://stackoverflow.com/a/756569/864968
I’d suggest creating convention test that checks every oracle table configuration defines table name no greater then 30 characters.
This is extremely helpful when a) you don’t have Oracle license (which contains profiler and developer edition not) and b) you want to peek what’s going on under the hood after your C# is magically transformed to some kind of SQL.
EF allows to log executed queries.
private void EnableDebugLogs(DbContext context)
{
context.Database.Log = s => Debug.WriteLine(s); // SQL writer
}
Logged queries can be found in Output window in Visual Studio. You can provide any action particularly.
You’ve created EF migration, run Update-Database and you get this error.
Remember what I was talking about schemas and users? They’re the same thing!
In DbContext derived class specify schema name. It should be the same as the user you’re connecting with database.
public class FooDbContext : DbMigrationsConfiguration<FooDbContext>
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema(" < Your connection string user here > ".ToUpper()); // Make sure it‘s upper case !
}
}
This might happen while you’re running Update-Database and your ConnectionString does not contain Persist Security Info.
http://stackoverflow.com/questions/14810868/ora-01005-null-password-given-logon-denied
If you, just like us, have to support both MS SQL and Oracle, you should consider seperate migrations set for each RDBMS. To do so, configure your migration directory in DbMigrationConfiguration derived class.
public class FooMigrationConfiguration : DbMigrationsConfiguration<FooContext>
{
public FooMigrationConfiguration()
{
DatabaseHelper.SetMigrationDirectory(this, " i.e. Contexts\Foo\Migrations ");
}
}
public static void SetMigrationDirectory<TContext>(DbMigrationsConfiguration<TContext> migration, string migrationsPath) where TContext : DbContext
{
if (string.IsNullOrEmpty(migrationsPath))
{
throw new ArgumentException($"{nameof(migrationsPath)} cannot be null or empty.");
}
if (migrationsPath.Contains("/"))
throw new ArgumentException($"Invalid {nameof(migrationsPath)}. Path should be valid Windows path. Use backslashes instead of slashes.");
migration.MigrationsDirectory = migrationsPath;
}
Note: Speaking aside, support for both RDBMS is like maintaining two similar but different applications. They just differ. It is very unlikely that you will end up with application working on both MS SQL and Oracle without any changes.
EF is great tool, but don’t expect it to do miracles.
If you used to log exception errors directly to Database with help of NLog then you should also modify your database target in your nlog.config.
<!--NLog configuration-->
<!--For more info visit: https://github.com/NLog/NLog website -->
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"
autoReload="true"
throwExceptions="true"
internalLogLevel="Off"
internalLogFile="App_Data\nlog-internal.log" >
<targets>
<!--Puts logs into database-->
<target name="db" xsi:type="Database" commandType="Text" connectionStringName="FoobarDbContext">
<commandText>
insert into FOOBARSCHEMA."FOOBARTABLE"("Id", "Level", "Logger", "Message", "StackTrace", "Date", "TenantId", "UserId")
values(sys_guid(), :LogLevel, :Logger, :Message, :StackTrace, systimestamp, :TenantId, :UserId)
</commandText>
<!--Reserved words by Oracle (don‘t use them as variable names: Level, Date-->
<parameter name="LogLevel" layout="${level}"/>
<parameter name="Logger" layout="${logger}"/>
<parameter name="Message" layout="${message}"/>
<parameter name="StackTrace" layout="${exception:format=Message,Type,Method,StackTrace,Data:separator=\r\n\r\n:maxInnerExceptionLevel=10:innerFormat=Message,Type,Method,StackTrace,Data:innerExceptionSeparator=\r\n\r\n}"/>
<parameter name="TenantId" layout="${event-properties:item=TenantId}"/>
<parameter name="UserId" layout="${event-properties:item=UserId}"/>
</target>
</targets>
<rules>
<logger name="*" minlevel="Warn" writeTo="trace,db" />
</rules>
</nlog>
It’s quite clear that we should maintain at least two versions of configuration file.
We decided to name them accordingly Web.Oracle.config and Web.MSSQL.config. It’s clear and simple. During deploy the correct file is being selected and included into artifact.
You can also write watcher that detects Web.config change and accordingly replaces rest of web.configs in whole solution. You can do that like we did, with help of Cake Watch.
app.settings sectionIt is a good practice to have separate config file with common settings for both MS SQL and Oracle version. We can exclude them by adding file attribute.
<appSettings file="AppSettings.config" />
Connections string differ so much that should be kept separately.
<connectionStrings>
<add name="ApplicationDbContext"
providerName="Oracle.ManagedDataAccess.Client"
connectionString="User Id=ApplicationDbContext;Password=P4S5W0RD;Data Source=OracleDataSource;Persist Security Info=true" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory, Oracle.ManagedDataAccess.EntityFramework" />
<providers>
<provider invariantName="Oracle.ManagedDataAccess.Client"
type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</providers>
</entityFramework>
<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>
<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="OracleDataSource"
descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORC)))" />
</dataSources>
</version>
</oracle.manageddataaccess.client>
Sometime you might get following error
Could not load type ‘OracleInternal.Common.ConfigBaseClass‘ from assembly ‘Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342‘.
This is not clear to me. Sometime I have to uninstall it, sometime I have to install it (due the error suggests something opposite).
I presume you have gacutil in your PATH. To check out, simply type gacutil /? to see available parameters. If you don’t, google for gacutil location and add it to your system variable PATH, or use gacutil with absolute path. Remember to open command window in Administrator mode.
gacutil /u Oracle.ManagedDataAccess
C:\...\packages\Oracle.ManagedDataAccess.12.1.2400\bin\x64
λ gacutil /u Oracle.ManagedDataAccess
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.33440
Copyright (c) Microsoft Corporation. All rights reserved.
Assembly: Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitec
ture=MSIL
Uninstalled: Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchi
tecture=MSIL
Number of assemblies uninstalled = 1
Number of failures = 0
After that, reset IIS with iisreset to reload loaded assemblies and reload the page.
ref: Oracle .Net ManagedDataAccess Error: Could not load type ‘OracleInternal.Common.ConfigBaseClass’ from assembly
Type is not resolved for member ‘Oracle.ManagedDataAccess.Client.OracleException,Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342‘.
Generally speaking all you got to do is to install Oracle.ManagedDataAccess.dll to your GAC (Global Assembly Cache).
gacutil /i <dll_path>
Oracle.ManagedDataAccess.dll can be found in your nuget package lib directory (mine was .\packages\Oracle.ManagedDataAccess.12.1.2400\lib\net40).
C:\...\packages\Oracle.ManagedDataAccess.12.1.2400\lib\net40> gacutil /i .\Oracle.ManagedDataAccess.dll
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.33440
Copyright (c) Microsoft Corporation. All rights reserved.
Assembly successfully added to the cache
ref: Entity Framework Seed method exception
Install and uninstall enable further work, but what really would do the trick is a proper configuration that resolves constant installing and uninstalling assemblies from cache. So if come up with any better solution, feel free to share.
In both 11g and 12c, GUID Identity columns in migration files must be replaced from
Id = c.Guid(nullable: false, identity: true),
to
Id = c.Guid(nullable: false, identity: false, defaultValueSql: "SYS_GUID()"),
This is for both 11g and 12c
11g does not offer autoincrement + uniquness feature (commonly known as Identity in MS SQL). EF handles generating next Identity values by incrementing sequence by a trigger. Sequences are good for numeric column types, but they don’t work well with GUIDs.
Change generated triggers to insert SYS_GUID() (which is NEWID() equivalent in MS SQL) or change C# migration.
"Oracle 11.2.0.2.0 does not support APPLY" exception
Error says everything. More robust EF queries are not supported on 11g. You can rewrite your query, but it’s just workaround, not a solution for this particular problem. Apply are supported from version 12c. This is the main reason we moved from 11g to 12c. Fortunately, client was moving to 12c as well.
ref: ODAC 11.2 Release 4 (11.2.0.3.0) throwing “Oracle 11.2.0.2.0 does not support APPLY” exception
Bulk inserts are possible with ODP.NET Unmanaged Driver.
This one is quite important. In EF we can create transaction with specified Isolation level. Those differ in MS SQL and Oracle.
MS SQL 2014 isolation levels
READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSNAPSHOTSERIALIZABLEOracle 11g/12c isolation levels
READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLEI’m no Oracle expert, so if you want gain more knowledge about those, check out this awesome blog post. It helped me a lot in understanding differences in isolation levels between Oracle and MS SQL.
High RAM Memory Consumed by Oracle 11g for Windows Server 2008
clear screen;
SET FEEDBACK OFF;
/ and ; When do I need to use a semicolon vs a slash in Oracle SQL?
commit; commits changes on database. You have to put this after every inserting section in your script.
How SID is different from Service name in Oracle tnsnames.ora
Some configuration need to be done before your scheduler will run on Oracle.
properties["quartz.jobStore.driverDelegateType"] = "Quartz.Impl.AdoJobStore.OracleDelegate, Quartz";
http://stackoverflow.com/a/20343752/864968
["quartz.dataSource.default.provider"] = "OracleODPManaged-1211-40",
http://www.quartz-scheduler.net/documentation/quartz-2.x/tutorial/job-stores.html
It is highly possible that you’re gonna mess around with migrations, DbContexts and entities. You might end up with changes in your model that will need new migration.
We had one case when after adding a migration (for MSSQL) we got a lot of table creates like this migration was the very first, but it wasn’t.
To avoid such issues with your model changes (when you’re absolutly sure there were none, like any table definition changes), add ContextKey and MigrationsNamespace to your MigrationConfiguration.
ContextKey is stored in __MigrationHistory table, created by EF migrator during first successful migration.
MigrationsNamespace is a bit different. It will appear in C# migrations. During migration on database those migrations transformed to xml (edmx), gzipped, compressed with “base64” and finally stored in __MigrationHistory table. This will cause differences in model.
Also, correct namespace is crucial while looking for proper migrations during EF migrations scan.
Compressed model can be easly decompressed. Check this link for more.
Can I get decode an EntityFramework Model from a specified migration?,
Entity Framework Migrations Rebuild Target Hash
More here:
Changing the Namespace With Entity Framework 6.0 Code First Databases,
Namespace changes with Entity Framework 6 migrations
The easiest way is to handle it with try/catch section.
begin
execute immediate
‘
create table "SCHEMA"."__MigrationHistory"
(
"MigrationId" nvarchar2(150) not null,
"ContextKey" nvarchar2(300) not null,
"Model" blob not null,
"ProductVersion" nvarchar2(32) not null,
constraint "PK___MigrationHistory" primary key("MigrationId", "ContextKey")
)
‘;
exception
when others then
if sqlcode <> -955 then
raise;
end if;
end;
/
source: http://stackoverflow.com/questions/15630771/check-table-exist-or-not-before-create-it-in-oracle
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
(‘TABLE‘,
‘VIEW‘,
‘PACKAGE‘,
‘PROCEDURE‘,
‘FUNCTION‘,
‘SEQUENCE‘
))
LOOP
BEGIN
IF cur_rec.object_type = ‘TABLE‘
THEN
EXECUTE IMMEDIATE ‘DROP ‘
|| cur_rec.object_type
|| ‘ "‘
|| cur_rec.object_name
|| ‘" CASCADE CONSTRAINTS‘;
ELSE
EXECUTE IMMEDIATE ‘DROP ‘
|| cur_rec.object_type
|| ‘ "‘
|| cur_rec.object_name
|| ‘"‘;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ‘FAILED: DROP ‘
|| cur_rec.object_type
|| ‘ "‘
|| cur_rec.object_name
|| ‘"‘
);
END;
END LOOP;
END;
/
source: http://stackoverflow.com/a/1690419/864968
Sometime you have to insert row with specific ID. If your ID column is was defined with generated always as identity not null you will get the following exception: ORA-32795: cannot insert into a generated always identity column. This is the default way of generting scripts by EF btw.
You can replace this definition with genereted by default as identity on null. After doing this, you will be able to insert values to ID columns and nulls will be replaced with generated values.
Sometime we need to configure column types explicitly.
We can use extensions.
using System;
using System.Data.Entity.ModelConfiguration.Configuration;
/// <summary>
/// String property configuration helpers.
/// More info about Oracle Data Provider mapping can be found here https://docs.oracle.com/cd/E63277_01/win.121/e63268/entityCodeFirst.htm
/// </summary>
public static class OracleStringPropertyConfigurationExtensions
{
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size cannot be greater than 2000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsNVarChar2(this StringPropertyConfiguration property, int maxLength = NVARCHAR2_MAX)
{
if (property == null) throw new ArgumentNullException(nameof(property));
if (maxLength > NVARCHAR2_MAX) throw new ArgumentException($"Oracle nvarchar2 column accepts strings that are not greater than {NVARCHAR2_MAX}.");
property
.HasMaxLength(maxLength)
.IsUnicode(true);
return property;
}
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size cannot be greater than 4000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsVarChar2(this StringPropertyConfiguration property, int maxLength = VARCHAR2_MAX)
{
if (property == null) throw new ArgumentNullException(nameof(property));
if (maxLength > VARCHAR2_MAX) throw new ArgumentException($"Oracle varchar2 column accepts strings that are not greater than {VARCHAR2_MAX}.");
property
.HasMaxLength(maxLength)
.IsUnicode(false);
return property;
}
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size must be greater than 4000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsClob(this StringPropertyConfiguration property, int? maxLength = null)
{
if (property == null) throw new ArgumentNullException(nameof(property));
if (maxLength < CLOB_MIN) throw new ArgumentException($"To configure column as CLOB type set its max length to be greater or equal than {CLOB_MIN}.");
property.IsUnicode(false);
// String Length
if (maxLength.HasValue) property.HasMaxLength(maxLength);
else property.IsMaxLength();
return property;
}
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size must be greater than 4000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsNClob(this StringPropertyConfiguration property, int? maxLength = null)
{
if (property == null)