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.
https://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.
Comments
Anonymous
June 23, 2008
Jarek has released his sample provider for Oracle here . In his post he talks about a few of the moreAnonymous
June 23, 2008
This new sample builds on top of System.Data.OracleClient and showcases some techniques a provider writerAnonymous
June 23, 2008
Just a quick note to make sure people are aware of a couple interesting events from the last day or two.Anonymous
June 24, 2008
As noted on Jaroslaw Kowalski's blog , we have released a new flavor of the ADO.NET Entity FrameworkAnonymous
June 24, 2008
The comment has been removedAnonymous
June 24, 2008
Данный пример демонстрирует работу EntityFramework с Oracle. Он являеться оболочкой для System.Data.OracleClient.Anonymous
June 27, 2008
Hi Jarek I'm having trouble unzipping the EFSampleProvider (sql, not oracle). I noticed that someone left a comment in the discussions for that project with the same problem. I wanted to look at the provider only to see how the dbconnections are handled. Any chance of an updated zip file? thanks julieAnonymous
October 16, 2008
If you create an Entity Data Model for Oracle, will this be separate and distinct from an Entity Data Model for SQL Server? In addition, does that mean I have to create separate Linq queries based on the target platform/Entity Data Model I am querying?