Sample Entity Framework Provider for Oracle

I wanted to let you know that we have just released Sample Entity Framework Provider for Oracle on MSDN Code Gallery.

http://code.msdn.com/EFOracleProvider

The provider is implemented using essentially the same technique as EFSampleProvider, but targets System.Data.OracleClient instead of System.Data.SqlClient. The provider is compatible with Visual Studio SP1 Beta and the code is released under Microsoft Public License (Ms-PL).

There are three notable features of this provider:

• Non-default type mappings. In EFOracleProviderManifest we are supporting non-default type mappings:
  • EFOracle.number(1,0) maps to Edm.Boolean
  • EFOracle.number(5,0) maps to Edm.Int16
  • EFOracle.number(11,0) maps to Edm.Int32
  • EFOracle.raw(16) maps to Edm.Guid

Here's the interesting piece of code taken from EFOracleProviderManifest.GetEdmType() that does it. It looks at facets attached to the given store type and translates certain combinations of them into mentioned EDM types.

case "number":
    {
        byte precision;
        byte scale;

        if (MetadataHelpers.TryGetPrecision(storeType, out precision) &&
            MetadataHelpers.TryGetScale(storeType, out scale))
        {
            if (precision == 1 && scale == 0)
                return TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Boolean));

            if (precision == 5 && scale == 0)
                return TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int16));

            if (precision == 11 && scale == 0)
                return TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));

            return TypeUsage.CreateDecimalTypeUsage(edmPrimitiveType, precision, scale);
        }
        else
        {
            return TypeUsage.CreateDecimalTypeUsage(edmPrimitiveType);
        }
    }

• Data type coercion in the data reader. Data provider analyzes Canonical Query Tree (CQT) and remembers expected CLR types for each result column ordinal. Whenever this data type is different from the data returned by wrapped OracleDataReader, the value is coerced using Convert.ChangeType().

The code below extracts ExpectedColumnTypes from the canonical query tree:

// Set expected column types for DbQueryCommandTree
DbQueryCommandTree queryTree = commandTree as DbQueryCommandTree;
if (queryTree != null)
{
    DbProjectExpression projectExpression = queryTree.Query as DbProjectExpression;
    if (projectExpression != null)
    {
        EdmType resultsType = projectExpression.Projection.ResultType.EdmType;

        StructuralType resultsAsStructuralType = resultsType as StructuralType;
        if (resultsAsStructuralType != null)
        {
            command.ExpectedColumnTypes = new PrimitiveType[resultsAsStructuralType.Members.Count];

            for (int ordinal = 0; ordinal < resultsAsStructuralType.Members.Count; ordinal++)
            {
                EdmMember member = resultsAsStructuralType.Members[ordinal];
                PrimitiveType primitiveType = member.TypeUsage.EdmType as PrimitiveType;
                command.ExpectedColumnTypes[ordinal] = primitiveType;
            }
        }
    }
}

// Set expected column types for DbFunctionCommandTree
DbFunctionCommandTree functionTree = commandTree as DbFunctionCommandTree;
if (functionTree != null)
{
    if (functionTree.ResultType != null)
    {
        Debug.Assert(MetadataHelpers.IsCollectionType(functionTree.ResultType.EdmType),
            "Result type of a function is expected to be a collection of RowType or PrimitiveType");

        EdmType elementType = MetadataHelpers.GetElementTypeUsage(functionTree.ResultType).EdmType;

        if (MetadataHelpers.IsRowType(elementType))
        {
            ReadOnlyMetadataCollection<EdmMember> members = ((RowType)elementType).Members;
            command.ExpectedColumnTypes = new PrimitiveType[members.Count];

            for (int ordinal = 0; ordinal < members.Count; ordinal++)
            {
                EdmMember member = members[ordinal];
                PrimitiveType primitiveType = (PrimitiveType)member.TypeUsage.EdmType;
                command.ExpectedColumnTypes[ordinal] = primitiveType;
            }

        }
        else if (MetadataHelpers.IsPrimitiveType(elementType))
        {
            command.ExpectedColumnTypes = new PrimitiveType[1];
            command.ExpectedColumnTypes[0] = (PrimitiveType)elementType;
        }
        else
        {
            Debug.Fail("Result type of a function is expected to be a collection of RowType or PrimitiveType");
        }
    }
}

The provider needs to store this information in the DbCommand and pass down to DbDataReader. In the reader implementation, actual data coercion is done in the following manner:

public partial class EFOracleDataReader : DbDataReader 
{
    private PrimitiveType[] _expectedColumnTypes;

    // ...

    public override DateTime GetDateTime(int ordinal)
    {
        return (DateTime)GetValue(ordinal);
    }

    public override decimal GetDecimal(int ordinal)
    {
        return (decimal)GetValue(ordinal);
    }

    public override object GetValue(int ordinal)
    {
        object rawValue = _oracleReader.GetValue(ordinal);

        if (_expectedColumnTypes != null)
        {
            if (!(rawValue is DBNull) && rawValue.GetType()
                != _expectedColumnTypes[ordinal].ClrEquivalentType)
            {
                rawValue = ChangeType(rawValue, _expectedColumnTypes[ordinal].ClrEquivalentType);
            }
        }
        return rawValue;
    }

    private object ChangeType(object sourceValue, Type targetType)
    {
        if (sourceValue is byte[] && targetType == typeof(Guid))
        {
            return new Guid((byte[])sourceValue);
        }
   
        if (sourceValue is DateTime && targetType == typeof(DateTimeOffset))
        {
            return new DateTimeOffset((DateTime)sourceValue);
        }

        return Convert.ChangeType(sourceValue, targetType, CultureInfo.InvariantCulture);
    }

    // ...
}

• Canonical Functions. Most Canonical Functions required by Entity Framework have been implemented using Oracle's built-in functions. In some cases it required some query rewriting, such as when handling Right() canonical function which has no direct Oracle translation and SUBSTR() must be used instead.

private static ISqlFragment HandleCanonicalFunctionRight(SqlGenerator sqlgen, DbFunctionExpression e)
{
    SqlBuilder result = new SqlBuilder();
    result.Append("(CASE WHEN LENGTH(");
    result.Append(e.Arguments[0].Accept(sqlgen));
    result.Append(") >= (");
    result.Append(e.Arguments[1].Accept(sqlgen));
    result.Append(") THEN ");
    result.Append("SUBSTR (");
    result.Append(e.Arguments[0].Accept(sqlgen));
    result.Append(",-(");
    result.Append(e.Arguments[1].Accept(sqlgen));
    result.Append("),");
    result.Append(e.Arguments[1].Accept(sqlgen));
    result.Append(")");
    result.Append(" ELSE ");
    result.Append(e.Arguments[0].Accept(sqlgen));
    result.Append(" END)");
    return result;
}

Let me know if you have any questions about the EFOracleProvider release and EF provider interface in general.