2012年1月2日随笔档案 - 小彬

其实前面说的这些，支持9i啊，支持数据类型啊，支持自增长啊，或者是上网搜索就能解决，或者并不需要很高的技术含量。

但是支持分页排序的问题，可是花了我不少时间（净时间2天以上）。

我们知道，在oracle中是使用rownum来实现分页的，需要使用三层嵌套sql，如下

select t3.* from (

  select t2.*, rownum as row_num from (
     select * from t order by t.id asc
  ) t2 where rownum<=20
) t3
where t2.row_num>11
order by t3.id asc

如果你对SQL有疑问可以点击这里

而在EFOracleProvider中，生成的sql语句是这个样子的（在调试的时候，sql语句会在输出窗口打印出来）

select t2.* from (
select t.*, rownum as row_num from t where rownum<=20 order by ID asc
) t2 where t2.row_num>10

order by ID asc

所以我们需要在EFOracleProvider中，加入一层嵌套。

我们在EFOracleProvider中搜索"ROWNUM"，很容易定位到 SqlGenerator.cs中，Visit(DbSkipExpression e) 实现了分页sql语句。

我们的目标是两层嵌套的sql语句里，再加1层嵌套，我们在

select t.*, rownum as row_num from t order by ID asc

外面加一个outer，主要代码如下

1 SqlSelectStatement outer = new SqlSelectStatement();

2 outer.Select.Append("ROWNUM ");
3             outer.Select.Append("AS ");
4             outer.Select.Append(row_numberSymbol);
5             outer.From.Append("( ");
6             outer.From.Append(input);
7             outer.From.AppendLine();
8             outer.From.Append(") ");
9             selectStatementStack.Push(input);
10             symbolTable.EnterScope();
11             var outerSymbol = BuildSymbol(input, e, inputColumns);
12             AddFromSymbol(outer, e.Input.VariableName, outerSymbol);
13             AddDefaultColumns(outer);
14             symbolTable.ExitScope();
15             selectStatementStack.Pop();

其中第6行，就是将原来查询，作为outer.From子句部分。

修改过的EFOracleProvider下载

/Files/binblog/EFOracleProvider_dll.zip

/Files/binblog/EFOracleProvider_src.zip

看下面这个sql语句

select t2.* from (
select t.*, rownum as row_num from t where rownum<=20 order by ID asc

) t2 where t2.row_num>10

order by ID asc

因为在查询的时候，order by 的执行是在 select 之后的，所以在第一层查询中，得到的结果可能是如下

ID row_num

1 3

8 20

20 4

21 1

...

100 8

===20条记录，其中row_num字段的值在1-20

这样的子结果集，在经过第二层过滤的时候，是得不到我们想要的结果的

ID

11

12

13

...

20

所以需要用如下的sql语句实现分页排序

select t3.* from (
  select t2.*, rownum as row_num from (
     select * from t order by t.id asc
  ) t2 where rownum<=20
) t3
where t2.row_num>11

order by t3.id asc

我们知道Oracle不像SqlServer那样，支持原生的自动增长型，而是通过sequence来实现类似于自增长类型的效果。

对于以项目为主的公司而言，往往需要做到数据库之间的切换。而当初引入Entity Framework，一个重要的目标就是不同数据库之间的快速切换。

现在面临的一个问题就是：自增长类型，如果做到不用修改代码，就能支持无缝切换

说句题外话，关于主键类型的选择

1. 自增长类型

优：简单、查找方便、空间占用少

缺：不同类型数据库的支持不同、数据同步

2. Guid 的string

优：数据同步较方便

缺：占用空间大——一般用32个字符，查找不便——没有先后次序规律

3. 自定义的生成规则

嗯，不管你们用什么类型的主键，反正我是需要改造一下EFOracleProvider来支持如下的功能

var entity = new XX();
//去掉设置主键值
//entity.ID = 20;
entity.Column1 = something;

//dbContext.Add(entity);

在动手前，我们需要先约定以下规则：

1. 所有表的主键名称统一叫ID

2. 为每个表建立一个序列(sequence)，规则是 SEQ_表名（序列的名字长度不能超过30个字符，所以表名不要超过26个字符）

完成该功能，需要修改以下3个文件（共4处地方）

1. Resources\EFOracleStoreSchemaDefinition.ssdl

2. SqlGen\DmlSqlGenerator.cs

3. SqlGen\SqlGenerator.cs

1. 修改 EFOracleStoreSchemaDefinition.ssdl

定位到第36行，将

, 0 "IsIdentity"

修改为：

, case c.COLUMN_NAME when 'ID' then 1 else 0 end "IsIdentity"

目的在于，告诉生成工具(edmgen2，网上搜) ，如果是ID字段，在edmx文件中，加入

变成

2. 修改insert语句的生成规则，在DmlSqlGenerator.cs文件中，找到GenerateInsertSql方法，然后用如下代码替换

GenerateInsertSql

        internal static string GenerateInsertSql(DbInsertCommandTree tree, EFOracleProviderManifest providerManifest, EFOracleVersion sqlVersion, out List<OracleParameter> parameters)
        {
            StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
            ExpressionTranslator translator = new ExpressionTranslator(commandText, tree,
                null != tree.Returning, sqlVersion);

            // insert [schemaName].[tableName]
            commandText.Append("insert into ");
            tree.Target.Expression.Accept(translator);
            if (0 < tree.SetClauses.Count)
            {
                // (c1, c2, c3, ...)
                commandText.Append("(");
                bool first = true;
                var dbNewInstanceExpression = tree.Returning as DbNewInstanceExpression;//自增长ID，zhb added
                bool hasAutoIdentity = dbNewInstanceExpression != null && dbNewInstanceExpression.Arguments.Count > 0;
                if (hasAutoIdentity)
                {
                    first = false;
                    dbNewInstanceExpression.Arguments[0].Accept(translator);
                }
                foreach (DbSetClause setClause in tree.SetClauses)
                {
                    if (first) { first = false; }
                    else { commandText.Append(", "); }
                    setClause.Property.Accept(translator);
                }
                commandText.AppendLine(")");

                // values c1, c2, ...
                first = true;
                commandText.Append("values (");
                if (hasAutoIdentity)
                {
                    translator.Sequence(tree.Target.Expression as DbScanExpression);
                    first = false;
                }
                foreach (DbSetClause setClause in tree.SetClauses)
                {
                    if (first) { first = false; }
                    else { commandText.Append(", "); }
                    setClause.Value.Accept(translator);

                    translator.RegisterMemberValue(setClause.Property, setClause.Value);
                }
                commandText.AppendLine(")");
            }
            else
            {
                // default values
                commandText.AppendLine().AppendLine("default values");
            }
            // generate returning sql
            GenerateReturningSql(commandText, tree, translator, tree.Returning, providerManifest, sqlVersion);

            parameters = translator.Parameters;
            return commandText.ToString();

这段代码的作用在于，将原本

insert into t(column1) values('value1')

变成

insert into t(id, column1) values(seq_t.nextval, 'value1')

3. 由于我们在第2步时，调用了

translator.Sequence(tree.Target.Expression as DbScanExpression);

所以我们需要修改一下ExpressionTranslator 类(在DmlSqlGenerator.cs文件中），为其添加一个Sequece的方法，如下

  internal void Sequence(DbScanExpression scanExpression)
            {
                _commandText.Append(SqlGenerator.GetSequenceSql(scanExpression.Target));

}

4. 由于第3步，sequence的生成，又转到了SqlGenerator类中，于是我们在SqlGenerator.cs中，添加GetSequenceSql方法

GetSequenceSql

internal static string GetSequenceSql(EntitySetBase entitySetBase)
        {
            // ##ORACLE
            // CONSIDER caching generated SQL here

            string definingQuery = MetadataHelpers.GetMetadataProperty<string>(entitySetBase,
                MetadataHelpers.DefiningQueryMetadata);

            if (true)
            {
                // construct escaped T-SQL referencing entity set
                StringBuilder builder = new StringBuilder(50);

                string table = MetadataHelpers.GetMetadataProperty<string>(entitySetBase,
                    MetadataHelpers.TableMetadata);

                if (!string.IsNullOrEmpty(table))
                {
                    builder.Append("seq_" + table);
                }
                else
                {
                    builder.Append("seq_" + entitySetBase.Name);
                }
                builder.Append(".nextval");
                return builder.ToString();
            }

至此，修改代码工作算是完成了，编译重新发布吧

SqlServer中有丰富的类型 bit, byte, short, int, long 等等

但是到了Oracle, 就是一个类型Number

本文需要修改的东西很少，主要是告诉大家，oracle数据类型与.net 类型如何对应转换的。

只需要修改一个地方，就可以支持sqlserver中常见的int, smallint, tinyint, bit类型

在EFOracleProviderManifest.cs 中，GetEdmType方法下，实现了数据库类型到.net数据类型的转换操作

我们要修改的地方在于

case "number":

下的代码，原本已经实现对应关系如下：

oracle----.net----sqlserver

number(1,0)<== >bool<==>bit

number(5,0)<==>short<==>smallint

number(11,0)<==>int<==>int

不知道当初作者写的时候，为什么把number(11,0)而不是number(10,0)转换为int（int.MaxValue=

2,147,483,647，为10位长度）

现在，把number(3,0)<==>byte<==>tinyint加上去吧

Case "number"

如果你还是首次使用EFOracleProvider，建议你将number(10,0) 对应 int。但对于我而言，因为之前不知道这里的对应关系，所以项目中的实体都是decimal类型，再改成int类型的话，影响的地方太多，所以还是保持number(11,0)对应int类型。

对了，如果你要支持char,timestamp类型的话，点击这里

这篇到此就结束了

EFOracleProvider的最后发布时间是2008年，但是居然不支持Oracle9i。

用Oracle官方的组件吧，两个问题：

1. 组件好大啊，好几百兆

2. 最重要在于，一直在beta

但是经理说要将俺们的技术升级到2010，用Entity Framework+MVC+JQuery

那就上吧。

第一个问题，就是支持9i的问题——声明一下，这个解决办法是在网上搜来的。写在这里只是为了汇总。

为了让EFOracleProvider支持9i,需要修改3个类：EFOracleVersion、EFOracleProviderManifest、EFOracleVersionUtils（在EFOracleVersion.cs中）

1. 在enum EFOracleVersion中添加一个enum值，如下

/// <summary>

        /// Oracle9i
        /// </summary>
        Oracle9i = 9,

2. 在EFOracleProviderManifest中添加一个const值

internal const string TokenOracle9i = "9i";

3. 修改EFOracleVersionUtils类，如下，

View Code

    /// <summary>
    /// This class is a simple utility class that determines the version from the
    /// connection
    /// </summary>
    internal static class EFOracleVersionUtils
    {
        /// <summary>
        /// Get the version from the connection.
        /// </summary>
        /// <param name="connection">current connection</param>
        /// <returns>version for the current connection</returns>
        internal static EFOracleVersion GetStorageVersion(EFOracleConnection connection)
        {
            string serverVersion = connection.ServerVersion;
            if (serverVersion.StartsWith("9."))
            {
                return EFOracleVersion.Oracle9i;
            }
            else if (serverVersion.StartsWith("10."))
            {
                return EFOracleVersion.Oracle10g;
            }
            else if (serverVersion.StartsWith("11."))
            {
                return EFOracleVersion.Oracle11g;
            }

            throw new ArgumentException("Could not determine storage version; " +
                    "a valid storage connection or a version hint is required.");
        }

        internal static string GetVersionHint(EFOracleVersion version)
        {
            switch (version)
            {
                case EFOracleVersion.Oracle9i:
                    return EFOracleProviderManifest.TokenOracle9i;

                case EFOracleVersion.Oracle10g:
                    return EFOracleProviderManifest.TokenOracle10g;

                case EFOracleVersion.Oracle11g:
                    return EFOracleProviderManifest.TokenOracle11g;

                default:
                    throw new ArgumentException("Could not determine storage version; " +
                            "a valid storage connection or a version hint is required.");
            }
        }

        internal static EFOracleVersion GetStorageVersion(string versionHint)
        {
            if (!string.IsNullOrEmpty(versionHint))
            {
                switch (versionHint)
                {
                    case EFOracleProviderManifest.TokenOracle9i:
                        return EFOracleVersion.Oracle9i;

                    case EFOracleProviderManifest.TokenOracle10g:
                        return EFOracleVersion.Oracle10g;

                    case EFOracleProviderManifest.TokenOracle11g:
                        return EFOracleVersion.Oracle11g;
                }
            }

            throw new ArgumentException("Could not determine storage version; " +
                    "a valid storage connection or a version hint is required.");
        }

        internal static bool IsVersionX(EFOracleVersion storageVersion)
        {
            return storageVersion == EFOracleVersion.Oracle9i || storageVersion == EFOracleVersion.Oracle10g ||
                storageVersion == EFOracleVersion.Oracle11g;
        }

这样，EFOracleProvider就能支持Oracle9i了。

修改EFOracleProvider系列预告

修改EFOracleProvider——让EFOracleProvider支持自增长类型

修改EFOracleProvider——让EFOracleProvider支持int, short, byte, bit

修改EFOracleProvider——解决分页排序问题

修改过的EFOracleProvider下载

dll下载

源码下载

小彬的Blog

公告