crazyjson / citms.entityframeworkcore.oracle Goto Github PK

System.TypeLoadException: Method 'get_Info' in type 'Microsoft.EntityFrameworkCore.Oracle.Infrastructure.Internal.OracleOptionsExtension' from assembly 'Citms.EntityFrameworkCore.Oracle, Version=1.1.0.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
at Microsoft.EntityFrameworkCore.OracleDbContextOptionsExtensions.UseOracle

创建Tag，注明每个版本更新的内容，可否，谢了！

is there any solution to excute stored procedure by Citms.EntityFramework?

Hi,

The code doesn't currently work on .NET Core 2.1 because of the use of some EF internal methods that were removed.

I've noticed that there's a pull request from @huybn577 that does some changes but I don't know if those are the only ones required.

Do you have any plans for upgrading the library to 2.1, need any help?

每次执行Update-Database都是创建__EFMIGRATIONSHISTORY
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00955: 名称已由现有对象使用
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ORA-00955: 名称已由现有对象使用

I'm getting several errors trying to use migrations. To make this works I have to remove the migration table and existing migrations (actually only one at time) in the code modify the model and add a migration as the first one.

Any idea?

Category: Microsoft.EntityFrameworkCore.Database.Command
EventId: 20100

Executing DbCommand [Parameters=[:stime_0='?' (DbType = DateTime), :etime_1='?' (DbType = DateTime), :p_2='?' (DbType = Int32), :p_3='?' (DbType = Int32)], CommandType='Text', CommandTimeout='0']
Select
K0 "ID", K1 "ACTION_NAME", K2 "ALARM_TRANS_ID", K3 "COMPARE_TYPE", K4 "CREATED_TIME", K5 "CREATOR", K6 "EQP_NO", K7 "LOT_ID", K8 "REALLYVALUE", K9 "REMARKS", K10 "SET_TIME", K11 "UPDATED_TIME", K12 "UPDATOR" from(
select "m2".*, rownum r2 from
(
SELECT "s"."ID" K0, "s"."ACTION_NAME" K1, "s"."ALARM_TRANS_ID" K2, "s"."COMPARE_TYPE" K3, "s"."CREATED_TIME" K4, "s"."CREATOR" K5, "s"."EQP_NO" K6, "s"."LOT_ID" K7, "s"."REALLYVALUE" K8, "s"."REMARKS" K9, "s"."SET_TIME" K10, "s"."UPDATED_TIME" K11, "s"."UPDATOR" K12
FROM "LION_ALM_ACTION_HISTORY" "s"
WHERE ((("s"."CREATED_TIME" >= :stime_0)) AND (("s"."CREATED_TIME" < :etime_1)))
) "m2"
) "m1"
where r2 > :p_2
and r2 <= (:p_2 + :p_3)

我用这个DEMO 读取oracle 数据库中longraw字段，但是读出来的都是0字节，是oracle.entityframeworkcore.dll不支持吗？
例如：表A(ID,Name)，B（ID，AID,photo）photo是longraw类型,查询语句
select a.ID,b.photo from A a join B b on a.ID=B.AID

结果显示b.photo是0bytes，数据库中b.photo是有数据的

I used this DEMO to read the longraw field in the Oracle database, but it was all 0 Bytes, oracle. Entity frameworkcore.dll Is not supported? For example,
table A( ID, Name), B (ID, AID, photo) photo is LongRaw ,

select a.ID,b.photo from A a join B b on a.ID=B.AID

results show that B. Photo is 0bytes

是否支持CodeFirst,以下是生成的sql语句，无法在ORACLE中执行

c#代码

执行错误日志

正确的sql应该为

SELECT * FROM(
SELECT "A"."ID", "A"."BPMNBYTES", "A"."CATALOGID", "A"."CHECKOUTTIME", "A"."CHECKOUTUSER", "A"."COMMENTS", "A"."CREATEDBY", "A"."CREATEDTIME", "A"."ENGINEVERSION", "A"."ISRECYCLABLED", "A"."PROCESSDEFID", "A"."PROCESSIMAGE", "A"."PROCESSNAME", "A"."PROCESSVERSION", "A"."STATUS", "A"."TENANTID", "A"."UPDATEDBY", "A"."UPDATEDTIME", "T"."PRONAME", "T"."PROSTATUS", "T"."PROVERSION",rownum RN
FROM "DEVPROCESSMODEL" "A"
INNER JOIN (
SELECT "D"."PROCESSNAME" "PRONAME", MAX("D"."STATUS") "PROSTATUS", MAX("D"."PROCESSVERSION") "PROVERSION"
FROM "DEVPROCESSMODEL" "D" GROUP BY "D"."PROCESSNAME"
) "T" ON (("A"."PROCESSNAME" = "T"."PRONAME") AND ("A"."PROCESSVERSION" = "T"."PROVERSION")) AND ("A"."STATUS" = "T"."PROSTATUS")
ORDER BY "A"."ID" DESC
) WHERE rownum <=1 and RN > 10

非主键的排序分页查询 先生成rownum RN 后排序，导致筛选的结果不正确 @CrazyJson

应该需要先排序 再生成rn eg: https://blog.csdn.net/zhouxzcsdn/article/details/58744932

错误日志：

原因是Oracle的名称不得超出30个字符，上图中别名太长~

当自动生成的外键超过30会报错， 比如.Net core 内置的身份认证 。这个有没有方法思路处理一下。

It seems as though CLOB data types in oracle cannot be mapped into C#.
Could you please confirm?

更新时报错：不支持app.update(u=>u.id=='xxx',u =>new User(...))...这种

The provider is not supported yet: 'Citms.EntityFrameworkCore.Oracle. If you believe it's a mistake, please report this message to our support team: [email protected]

如题。

Hello,
thanks for sharing.
I want to ask a question.
How can i resolve on the picture?
Can you help me ?

下面的例子
//左联查询：
var sysparamodel = (from p in oms.T_SYS_PARAETER
join q in oms.T_MENU
on p.TM_ID equals q.TM_ID
into left
from q in left.DefaultIfEmpty()//左联
select new { p.TSP_PK, p.TM_ID, q.TM_NAME, p.TSP_NAME, p.TSP_KEY, p.TSP_VALUE, p.TSP_REMARK, p.TSP_STYPE,q.TM_SORT });
sysparamodel = sysparamodel.OrderBy(b => b.TM_SORT);
sysparamodel = sysparamodel.Skip(0).Take(10);

你的ef生成出来的sql：
SELECT *
FROM (SELECT "P"."TSP_PK",
"P"."TM_ID",
"Q"."TM_NAME",
"P"."TSP_NAME",
"P"."TSP_KEY",
"P"."TSP_VALUE",
"P"."TSP_REMARK",
"P"."TSP_STYPE",
"Q"."TM_SORT"
，ROWNUM AS ROWNO
FROM "T_SYS_PARAETER" "P"
LEFT JOIN "T_MENU" "Q"
ON "P"."TM_ID" = "Q"."TM_ID"
ORDER BY "Q"."TM_SORT" NULLS FIRST) A
WHERE ROWNUM <= 10 and ROWNUM> 0

但实际正确的sql应该是这样：
SELECT *
FROM (SELECT A.*, ROWNUM AS ROWNO FROM (SELECT "P"."TSP_PK",
"P"."TM_ID",
"Q"."TM_NAME",
"P"."TSP_NAME",
"P"."TSP_KEY",
"P"."TSP_VALUE",
"P"."TSP_REMARK",
"P"."TSP_STYPE",
"Q"."TM_SORT"
FROM "T_SYS_PARAETER" "P"
LEFT JOIN "T_MENU" "Q"
ON "P"."TM_ID" = "Q"."TM_ID"
ORDER BY "Q"."TM_SORT" NULLS FIRST) A
WHERE ROWNUM <= 10) WHERE ROWNO >0

First thank you for good work. I was waiting hopelessly People from Oracle for a long time to make driver and EF for .NET core.

I am still new c# and when I look into this project, I think maybe I am hopeless too.

However, maybe I can give a little contribution, see below.

If method DbContext.Add([NotNullAttribute] object entity) was called for a table with autoincrement primary key, with null value for the primary key, then next sql code was generated:

DECLARE
  TYPE efRowCUSTOMERS_0 IS RECORD   (ID NUMBER(10));
  TYPE efCUSTOMERS_0 IS TABLE OF efRowCUSTOMERS_0;
  listCUSTOMERS_0 efCUSTOMERS_0;
  v_RowCount INTEGER;
BEGIN
  listCUSTOMERS_0 := efCUSTOMERS_0();
  listCUSTOMERS_0.extend(1);
  INSERT INTO "CUSTOMERS" ("ADDRESS", "FIRSTNAME", "LASTNAME")
  VALUES (:p0, :p1, :p2)
  RETURNING "ID" INTO listCUSTOMERS_0(1);
  OPEN :cur1 FOR SELECT listCUSTOMERS_0(1) FROM DUAL;
END;

But line before the last line should be like this:

RETURNING "ID" INTO listCUSTOMERS_0(1).ID;

My solution is a code change in OracleUpdateSqlGenerator.cs.

Old code:

252                commandStringBuilder
253                    .AppendLine()
254                    .Append("RETURNING ")
255                    .AppendJoin(
256                        operations,
257                        (sb, cm) => sb.Append(SqlGenerationHelper.DelimitIdentifier(cm.ColumnName)))
258                    .Append($" INTO list{name}({commandPosition + 1})");

is replaced with:

252                commandStringBuilder
253                    .AppendLine()
254                    .Append("RETURNING ")
255                    .AppendJoin(
256                        operations,
257                        (sb, cm) => sb.Append(SqlGenerationHelper.DelimitIdentifier(cm.ColumnName)
258                              +  $" INTO list{name}({commandPosition + 1}).{cm.ColumnName}"));

当使用Include贪婪加载时, 主表Include子表没问题。
var blog = context.Set<Blog>().Include(b=>b.Posts).FirstOrDefaultAsync();
但是当子表Include主表会提示 【"ORA-00918: 未明确定义列"】
var post = context.Set<Post>().Include(p=>p.Blog).FirstOrDefaultAsync();

We tried many samples. To see the problem use the fallowing code:

var data = context.Products.FromSql("select * from Product").FirstOrDefault();

The result is null reference exception:
at Microsoft.EntityFrameworkCore.Oracle.Query.Sql.Internal.OracleQuerySqlGenerator.GenerateProjection(Expression projection) at Microsoft.EntityFrameworkCore.Oracle.Query.Sql.Internal.OracleQuerySqlGenerator.ProcessExpressionList[T](IReadOnlyList`1 items, Action`1 itemAction, Action`1 joinAction) at Microsoft.EntityFrameworkCore.Oracle.Query.Sql.Internal.OracleQuerySqlGenerator.VisitSelect(SelectExpression selectExpression) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.GenerateSql(IReadOnlyDictionary`2 parameterValues) at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.GetRelationalCommand(IReadOnlyDictionary`2 parameters) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer) at Microsoft.EntityFrameworkCore.Oracle.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext() at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found) at lambda_method(Closure ) at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found) at System.Linq.Enumerable.First[TSource](IEnumerable`1 source) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)

We are using AspNetCore 2.2 and citms.entityframeworkcore.oracle 1.0.7

Hi
when try to use Scaffold-DbContext with Citms.EntityFrameworkCore.Oracle as provider

call
Scaffold-DbContext -Connection "<connection_string>" -Provider citms.entityframeworkcore.oracle -OutputDir Models

Visual Studio 2019 (preview 4) returns error:
Method 'Create' in type 'Microsoft.EntityFrameworkCore.Oracle.Scaffolding.Internal.OracleDatabaseModelFactory' from assembly 'Citms.EntityFrameworkCore.Oracle, Version=1.0.9.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.

System.TypeLoadException: Method 'Create' in type 'Microsoft.EntityFrameworkCore.Oracle.Scaffolding.Internal.OracleDatabaseModelFactory' from assembly 'Citms.EntityFrameworkCore.Oracle, Version=1.0.9.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
   at Microsoft.EntityFrameworkCore.Oracle.Design.Internal.OracleDesignTimeServices.ConfigureDesignTimeServices(IServiceCollection serviceCollection)
   at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureDesignTimeServices(Type designTimeServicesType, IServiceCollection services)
   at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.ConfigureProviderServices(String provider, IServiceCollection services, Boolean throwOnError)
   at Microsoft.EntityFrameworkCore.Design.Internal.DesignTimeServicesBuilder.Build(String provider)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)