Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / ASP.NET
 
Print

A propertymapping Extension for DataReaders

4.91/5 (47 votes)
26 Jan 2016CPOL7 min read 110.1K   3.7K  
A propertymapping extension for DataReaders

Introduction

Just like everyone else, I'm lazy by nature and want to do as little work as possible, and as I'm doing a lot of specialized reporting from databases, I wanted to save some work doing all those tedious property mappings by using an automapper. There are plenty of mappers around, but I wanted a simple one with a small footprint and a really high performance.
Another design goal was that I wanted it to work with any IDataReader.
So this mapper doesn't just work with DBReaders such as SQLDataReader or OracleDataReader but can just as well be used with a DataTableReader, StorageStream DataReader or why not Sebastien Lorions CSVReader[^].
This means you can map your data to POCOs using any datareader that implements IDataReader.

Using the Code

The public methods are just simple extension methods to IDataReader so using the mapper is really easy.

Just use it like: Reader.AsEnumerable<MyClass>
Or if you want a generic list or LinkedList in one go: Reader.ToList<MyClass>()
Same with Dictionary: Reader.ToDictionary<MyClass => MyClass.Key, MyClass>()

If your data needs to be parsed from string to other primitive types, you might need to specify the CultureInfo of your data.
Like this: Reader.AsEnumerable<MyClassInstance>(MyCultureInfo)

Note!
It's recommended to create the Reader with the CommandBehavoirs CloseConnection and KeyInfo.
Like this: Command.ExecuteReader((CommandBehavior)CommandBehavior.CloseConnection | CommandBehavior.KeyInfo)
KeyInfo is needed by the mapper to know if a field is nullable or not. CloseConnection is just a good habit.

The Mapper

The core of the mapper is a function that creates a delegate that uses the supplied IDataRecord to create an instance of the target class. This delegate is created from a lambda expression that is built using expression trees[^]. After initial creation this delegate is cached with a mapping on both TargetType and the SourceFields of the datareader

If the TargetType is an elementary Type, such as String or Int32, the mapper will use the first field of the DataReader since there isn't any name to map on and it simply doesn't make sense to have more than one field in the Reader.
The Delegate creates an instance of the target and assigns the (converted) value from the DataReader to this instance and returns it to the caller.

If it's a composite Type, there is a double loop where all the fields in the DataRecord are matched with the names of the public properties, fields or an attribute in the class that's going to be populated.
So this is a requirement when using the mapper with composite Types. The fieldnames of the DataReader must match the property/fieldnames or an attribute in the target class.
This matching is not case sensitive, but that's really easy to change if one would want that.

And then, it creates a binding that is used by the memberinit expression that creates the instance.

But having realized I'm even lazier than previously thought, I have added support for Tuples.
Since PropertyNames like Item1, Item2 and so on makes very little sense to map on, it's simply mapping on position instead.
It doesn't map nested tuples, so seven properties is the maximum.

Shrink ▲   
/// <summary>
/// Creates a delegate that creates an instance of Target from the supplied DataRecord
/// </summary>
/// <param name="RecordInstance">An instance of a DataRecord</param>
/// <returns>A Delegate that creates a new instance of Target with the values set from the supplied DataRecord</returns>
/// <remarks></remarks>
private static Func<IDataRecord, Target> GetInstanceCreator<Target>(IDataRecord RecordInstance, CultureInfo Culture,Boolean MustMapAllProperties)
{
    Type RecordType = typeof(IDataRecord);
    ParameterExpression RecordInstanceExpression = Expression.Parameter(RecordType, "SourceInstance");
    Type TargetType = typeof(Target);
    DataTable SchemaTable = ((IDataReader)RecordInstance).GetSchemaTable();
    Expression Body = default(Expression);

    //The actual names for Tuples are System.Tuple`1,System.Tuple`2 etc where the number stands for the number of Parameters 
    //This crashes whenever Microsoft creates a class in the System Namespace called Tuple`duple 
    if (TargetType.FullName.StartsWith("System.Tuple`"))
    {
        ConstructorInfo[] Constructors = TargetType.GetConstructors();
        if (Constructors.Count() != 1)
            throw new ArgumentException("Tuple must have one Constructor");
        var Constructor = Constructors[0];

        var Parameters = Constructor.GetParameters();
        if (Parameters.Length > 7)
            throw new NotSupportedException("Nested Tuples are not supported");

        Expression[] TargetValueExpressions = new Expression[Parameters.Length];
        for (int Ordinal = 0; Ordinal < Parameters.Length; Ordinal++)
        {
            var ParameterType = Parameters[Ordinal].ParameterType;
            if (Ordinal >= RecordInstance.FieldCount)
            {
                if (MustMapAllProperties) { throw new ArgumentException("Tuple has more fields than the DataReader"); }
                TargetValueExpressions[Ordinal] = Expression.Default(ParameterType);
            }
            else
            {
                TargetValueExpressions[Ordinal] = GetTargetValueExpression(
                                                RecordInstance, 
                                                Culture, 
                                                RecordType, 
                                                RecordInstanceExpression, 
                                                SchemaTable, 
                                                Ordinal, 
                                                ParameterType);
            }
        }
        Body = Expression.New(Constructor, TargetValueExpressions);
    }
    //Find out if SourceType is an elementary Type.
    else if (TargetType.IsElementaryType())
    {
        //If you try to map an elementary type, e.g. ToList<Int32>, there is no name to map on. So to avoid error we map to the first field in the datareader
        //If this is wrong, it is the query that's wrong.
        const int Ordinal = 0;
        Expression TargetValueExpression = GetTargetValueExpression(
                                                RecordInstance, 
                                                Culture, 
                                                RecordType, 
                                                RecordInstanceExpression, 
                                                SchemaTable, 
                                                Ordinal, 
                                                TargetType);

        ParameterExpression TargetExpression = Expression.Variable(TargetType, "Target");
        Expression AssignExpression = Expression.Assign(TargetExpression, TargetValueExpression);
        Body = Expression.Block(new ParameterExpression[] { TargetExpression }, AssignExpression);
    }
    else
    {
        //Loop through the Properties in the Target and the Fields in the Record to check which ones are matching
        SortedDictionary<int, MemberBinding> Bindings = new SortedDictionary<int, MemberBinding>();
        foreach (FieldInfo TargetMember in TargetType.GetFields(BindingFlags.Public | BindingFlags.Instance))
        {
            Action work = delegate
            {
                for (int Ordinal = 0; Ordinal < RecordInstance.FieldCount; Ordinal++)
                {
                    //Check if the RecordFieldName matches the TargetMember
                    if (MemberMatchesName(TargetMember, RecordInstance.GetName(Ordinal)))
                    {
                        Expression TargetValueExpression = GetTargetValueExpression(
                                                                RecordInstance, 
                                                                Culture, 
                                                                RecordType, 
                                                                RecordInstanceExpression, 
                                                                SchemaTable, 
                                                                Ordinal, 
                                                                TargetMember.FieldType);

                        //Create a binding to the target member
                        MemberAssignment BindExpression = Expression.Bind(TargetMember, TargetValueExpression);
                        Bindings.Add(Ordinal, BindExpression);
                        return;
                    }
                }
                //If we reach this code the targetmember did not get mapped
                if (MustMapAllProperties)
                {
                    throw new ArgumentException(String.Format("TargetField {0} is not matched by any field in the DataReader", TargetMember.Name));
                }
            };
            work();
        }

        foreach (PropertyInfo TargetMember in TargetType.GetProperties(BindingFlags.Public | BindingFlags.Instance))
        {
            if (TargetMember.CanWrite)
            {
                Action work = delegate
                    {
                        for (int Ordinal = 0; Ordinal < RecordInstance.FieldCount; Ordinal++)
                        {
                            //Check if the RecordFieldName matches the TargetMember
                            if (MemberMatchesName(TargetMember, RecordInstance.GetName(Ordinal)))
                            {
                                Expression TargetValueExpression = GetTargetValueExpression(
                                                                        RecordInstance, 
                                                                        Culture, 
                                                                        RecordType, 
                                                                        RecordInstanceExpression, 
                                                                        SchemaTable, 
                                                                        Ordinal, 
                                                                        TargetMember.PropertyType);

                                //Create a binding to the target member
                                MemberAssignment BindExpression = Expression.Bind(TargetMember, TargetValueExpression);
                                Bindings.Add(Ordinal, BindExpression);
                                return;
                            }
                        }
                        //If we reach this code the targetmember did not get mapped
                        if (MustMapAllProperties)
                        {
                            throw new ArgumentException(String.Format("TargetProperty {0} is not matched by any Field in the DataReader", TargetMember.Name));
                        }
                    };
                work();
            }
        }

        //Create a memberInitExpression that Creates a new instance of Target using bindings to the DataRecord
        Body = Expression.MemberInit(Expression.New(TargetType), Bindings.Values);
    }
    //Compile the Expression to a Delegate
    return Expression.Lambda<Func<IDataRecord, Target>>(Body, RecordInstanceExpression).Compile();
}

Checking whether there is a match between a Property and a Field is done by comparing the Fieldname of the DataReader with the Name or a FieldNameAttribute of the Property

Shrink ▲   
/// <summary>
/// Returns The FieldNameAttribute if existing
/// </summary>
/// <param name="Member">MemberInfo</param>
/// <returns>String</returns>
private static string GetFieldNameAttribute(MemberInfo Member)
{
    if (Member.GetCustomAttributes(typeof(FieldNameAttribute), true).Count() > 0)
    {
        return ((FieldNameAttribute)Member.GetCustomAttributes(typeof(FieldNameAttribute), true)[0]).FieldName;
    }
    else
    {
        return string.Empty;
    }
}

/// <summary>
/// Checks if the Field name matches the Member name or Members FieldNameAttribute
/// </summary>
/// <param name="Member">The Member of the Instance to check</param>
/// <param name="Name">The Name to compare with</param>
/// <returns>True if Fields match</returns>
/// <remarks>FieldNameAttribute takes precedence over TargetMembers name.</remarks>
private static bool MemberMatchesName(MemberInfo Member, string Name)
{
    string FieldnameAttribute = GetFieldNameAttribute(Member);
    return FieldnameAttribute.ToLower() == Name.ToLower() || Member.Name.ToLower() == Name.ToLower();
}

The FieldNameAttribute takes priority.

The actual Attribute is shown below

[AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false)]
class FieldNameAttribute : Attribute
{
    private readonly string _FieldName;
    public string FieldName
    {
        get { return _FieldName; }
    }
    public FieldNameAttribute(string FieldName)
    {
        _FieldName = FieldName;
    }
}

You use it by simply adding the attribute to a property or field like this:

[FieldName("Shipping Country")]
public CountryEnum? ShipCountry { get; set; }

For each mapped property, we need to check whether the source is nullable or not, the reason that this is important is performance.
If we know that the source does not contain nulls, the assignment can be simplified.
And if the source is null, we assign the Target's default value.

IDataReaders do not handle nullables as such but all the information we need exists in the SchemaTable and the IsNull field.

Shrink ▲   
/// <summary>
/// Returns an Expression representing the value to set the TargetProperty to
/// </summary>
/// <remarks>Prepares the parameters to call the other overload</remarks>
private static Expression GetTargetValueExpression(
    IDataRecord RecordInstance,
    CultureInfo Culture,
    Type RecordType,
    ParameterExpression RecordInstanceExpression,
    DataTable SchemaTable,
    int Ordinal,
    Type TargetMemberType)
{
    Type RecordFieldType = RecordInstance.GetFieldType(Ordinal);
    bool AllowDBNull = Convert.ToBoolean(SchemaTable.Rows[Ordinal]["AllowDBNull"]);
    Expression RecordFieldExpression = GetRecordFieldExpression(RecordType, RecordInstanceExpression, Ordinal, RecordFieldType);
    Expression ConvertedRecordFieldExpression = GetConversionExpression(RecordFieldType, RecordFieldExpression, TargetMemberType, Culture);
    MethodCallExpression NullCheckExpression = GetNullCheckExpression(RecordType, RecordInstanceExpression, Ordinal);

    //Create an expression that assigns the converted value to the target
    Expression TargetValueExpression = default(Expression);
    if (AllowDBNull)
    {
        TargetValueExpression = Expression.Condition(
            NullCheckExpression,
            Expression.Default(TargetMemberType),
            ConvertedRecordFieldExpression,
            TargetMemberType
            );
    }
    else
    {
        TargetValueExpression = ConvertedRecordFieldExpression;
    }
    return TargetValueExpression;
}

Here we check if the RecordValue is null. It's done by checking the value of the IsDBNull property of the Reader.

/// <summary>
/// Gets an Expression that checks if the current RecordField is null
/// </summary>
/// <param name="RecordType">The Type of the Record</param>
/// <param name="RecordInstance">The Record instance</param>
/// <param name="Ordinal">The index of the parameter</param>
/// <returns>MethodCallExpression</returns>
private static MethodCallExpression GetNullCheckExpression(Type RecordType, ParameterExpression RecordInstance, int Ordinal)
{
    MethodInfo GetNullValueMethod = RecordType.GetMethod("IsDBNull", new Type[] { typeof(int) });
    MethodCallExpression NullCheckExpression = Expression.Call(RecordInstance, GetNullValueMethod, Expression.Constant(Ordinal, typeof(int)));
    return NullCheckExpression;
}

We also need to create a SourceExpression from the RecordField.
If we use the proper getter method from the Reader, we can avoid some boxing and casting operations.

Shrink ▲   
/// <summary>
/// Gets an Expression that represents the getter method for the RecordField
/// </summary>
/// <param name="RecordType">The Type of the Record</param>
/// <param name="RecordInstanceExpression">The Record instance</param>
/// <param name="Ordinal">The index of the parameter</param>
/// <param name="RecordFieldType">The Type of the RecordField</param>
/// <returns></returns>
private static Expression GetRecordFieldExpression(Type RecordType, ParameterExpression RecordInstanceExpression, int Ordinal, Type RecordFieldType)
{
    MethodInfo GetValueMethod = default(MethodInfo);

    switch (RecordFieldType.FullName)
    {
        case "System.Boolean" :
            GetValueMethod = RecordType.GetMethod("GetBoolean", new Type[] { typeof(int) });
            break;
        case "System.Byte":
            GetValueMethod = RecordType.GetMethod("GetByte", new Type[] { typeof(int) });
            break;
        case "System.Byte[]":
            GetValueMethod = typeof(HelperFunctions).GetMethod("RecordFieldToBytes", new Type[] { typeof(IDataRecord), typeof(int) });
            break;
        case "System.Char":
            GetValueMethod = RecordType.GetMethod("GetChar", new Type[] { typeof(int) });
            break;
        case "System.DateTime":
            GetValueMethod = RecordType.GetMethod("GetDateTime", new Type[] { typeof(int) });
            break;
        case "System.Decimal":
            GetValueMethod = RecordType.GetMethod("GetDecimal", new Type[] { typeof(int) });
            break;
        case "System.Double":
            GetValueMethod = RecordType.GetMethod("GetDouble", new Type[] { typeof(int) });
            break;
        case "System.Single":
            GetValueMethod = RecordType.GetMethod("GetFloat", new Type[] { typeof(int) });
            break;
        case "System.Guid":
            GetValueMethod = RecordType.GetMethod("GetGuid", new Type[] { typeof(int) });
            break;
        case "System.Int16":
            GetValueMethod = RecordType.GetMethod("GetInt16", new Type[] { typeof(int) });
            break;
        case "System.Int32":
            GetValueMethod = RecordType.GetMethod("GetInt32", new Type[] { typeof(int) });
            break;
        case "System.Int64":
            GetValueMethod = RecordType.GetMethod("GetInt64", new Type[] { typeof(int) });
            break;
        case "System.String":
            GetValueMethod = RecordType.GetMethod("GetString", new Type[] { typeof(int) });
            break;
        default:
            GetValueMethod = RecordType.GetMethod("GetValue", new Type[] { typeof(int) });
            break;
    }

    Expression RecordFieldExpression;
    if (object.ReferenceEquals(RecordFieldType, typeof(byte[])))
    {
        RecordFieldExpression = Expression.Call(GetValueMethod, new Expression[] { RecordInstanceExpression, Expression.Constant(Ordinal, typeof(int)) });
    }
    else
    {
        RecordFieldExpression = Expression.Call(RecordInstanceExpression, GetValueMethod, Expression.Constant(Ordinal, typeof(int)));
    }
    return RecordFieldExpression;
}

Converting the Fields

We also need to check if the Source and Target properties are of different types, and if they are we need to convert them.
If they are the same type, we only simply return the Source property.
But if they are different, we also need to cast them from the SourceType to the TargetType.

The built in Expression.Convert can handle all implicit and explicit casts, but there are two special cases that need to be handled here.
There are no operators for converting primitive types to String. So if we were to try this, the function would throw an exception.
So this is handled by calling the ToString method of the source. ToString is not the same as a type conversion but for any primitive type, it will do fine.
The other case is the conversion from String to other primitive types and enum, this is handled by parsing the String in a different method.

Shrink ▲   
/// <summary>
/// Gets an expression representing the Source converted to the TargetType
/// </summary>
/// <param name="SourceType">The Type of the Source</param>
/// <param name="SourceExpression">An Expression representing the Source value</param>
/// <param name="TargetType">The Type of the Target</param>
/// <returns>Expression</returns>
private static Expression GetConversionExpression(Type SourceType, Expression SourceExpression, Type TargetType, CultureInfo Culture)
{
    Expression TargetExpression;
    if (object.ReferenceEquals(TargetType, SourceType))
    {
        //Just assign the RecordField
        TargetExpression = SourceExpression;
    }
    else if (object.ReferenceEquals(SourceType, typeof(string)))
    {
        TargetExpression = GetParseExpression(SourceExpression, TargetType, Culture);
    }
    else if (object.ReferenceEquals(TargetType, typeof(string)))
    {
        //There are no casts from primitive types to String.
        //And Expression.Convert Method (Expression, Type, MethodInfo) only works with static methods.
        TargetExpression = Expression.Call(SourceExpression, SourceType.GetMethod("ToString", Type.EmptyTypes));
    }
    else if (object.ReferenceEquals(TargetType, typeof(bool)))
    {
        MethodInfo ToBooleanMethod = typeof(Convert).GetMethod("ToBoolean", new[] { SourceType });
        TargetExpression = Expression.Call(ToBooleanMethod, SourceExpression);
    }
    else if (object.ReferenceEquals(SourceType, typeof(Byte[])))
    {
        TargetExpression = GetArrayHandlerExpression(SourceExpression, TargetType);
    }
    else
    {
        //Using Expression.Convert works wherever you can make an explicit or implicit cast.
        //But it casts OR unboxes an object, therefore the double cast. First unbox to the SourceType and then cast to the TargetType
        //It also doesn't convert a numerical type to a String or date, this will throw an exception.
        TargetExpression = Expression.Convert(SourceExpression, TargetType);
    }
    return TargetExpression;
}

Different types use different Parse methods so we have to use a Switch to choose the right method.
All Numbers actually use the same method, but since Number is an internal Class in the .NET Framework, the Switch becomes a bit verbose.

Shrink ▲   
/// <summary>
/// Creates an Expression that parses a string
/// </summary>
/// <param name="SourceExpression"></param>
/// <param name="TargetType "></param>
/// <param name="Provider"></param>
/// <returns></returns>
private static Expression GetParseExpression(Expression SourceExpression, Type TargetType , CultureInfo Culture)
{
    Type UnderlyingType = GetUnderlyingType(TargetType );
    if (UnderlyingType.IsEnum)
    {
        MethodCallExpression ParsedEnumExpression = GetEnumParseExpression(SourceExpression, UnderlyingType);
        //Enum.Parse returns an object that needs to be unboxed
        return Expression.Unbox(ParsedEnumExpression, TargetType );
    }
    else
    {
        Expression ParseExpression = default(Expression);
        switch (UnderlyingType.FullName)
        {
            case "System.Byte":
            case "System.UInt16":
            case "System.UInt32":
            case "System.UInt64":
            case "System.SByte":
            case "System.Int16":
            case "System.Int32":
            case "System.Int64":
            case "System.Double":
            case "System.Decimal":
                ParseExpression = GetNumberParseExpression(SourceExpression, UnderlyingType, Culture);
                break;
            case "System.DateTime":
                ParseExpression = GetDateTimeParseExpression(SourceExpression, UnderlyingType, Culture);
                break;
            case "System.Boolean":
            case "System.Char":
                ParseExpression = GetGenericParseExpression(SourceExpression, UnderlyingType);
                break;
            default:
                throw new ArgumentException(string.Format("Conversion from {0} to {1} is not supported", "String", TargetType ));
        }
        if (Nullable.GetUnderlyingType(TargetType ) == null)
        {
            return ParseExpression;
        }
        else
        {
            //Convert to nullable if necessary
            return Expression.Convert(ParseExpression, TargetType );
        }
    }
}

The actual parsing is done by calling the Parse Method of the Target property.

/// <summary>
/// Creates an Expression that parses a string to a number
/// </summary>
/// <param name="SourceExpression"></param>
/// <param name="TargetType "></param>
/// <param name="Provider"></param>
/// <returns></returns>
private static MethodCallExpression GetNumberParseExpression(Expression SourceExpression, Type TargetType ,  CultureInfo Culture)
{
    MethodInfo ParseMetod = TargetType .GetMethod("Parse", new[] { typeof(string), typeof(NumberFormatInfo) });
    ConstantExpression ProviderExpression = Expression.Constant(Culture.NumberFormat, typeof(NumberFormatInfo));
    MethodCallExpression CallExpression = Expression.Call(ParseMetod, new[] { SourceExpression, ProviderExpression });
    return CallExpression;
}

The other Parse methods follow the same pattern, but use different parameters.

Performance

Here are some examples of the debugview code for the TargetValueExpression.
First the assignment of a NOT NULL field of the type int to an int property.

.Call $SourceInstance.GetInt32(0)

Here we have one function call.

The unnecessary use of a nullable int looks like this:

(System.Nullable`1[System.Int32]).Call $SourceInstance.GetInt32(14)

Here we're having an extra cast.

But compare this with the parsing of a string that can be null to a nullable int.

.If (
    .Call $SourceInstance.IsDBNull(2)
) {
    null
} .Else {
    (System.Nullable`1[System.Int32]).Call System.Int32.Parse(
        .Call $SourceInstance.GetString(2),
        .Constant<System.Globalization.NumberFormatInfo>(System.Globalization.NumberFormatInfo))
}

Here, we have three function calls and a cast.

Trying to avoid conversions is obvious for most.
But for the sake of performance, I can't stress enough the importance of making the database fields NOT NULL when they don't contain any null values.

History

  • 26th October, 2013: v1.0 First release
  • 14th January, 2014: v2.0 Complete rewrite to use Expression.MemberInit to create a new instance instead of merely setting the properties of an existing instance in a loop
  • 26th January, 2014: v2.01 Now handles conversion from string to enum
  • 15th February, 2014: v2.02 Improved null handling and performance
  • 15th February, 2014: v2.03 Now handles conversion from string to nullable enum
  • 28th February, 2014: v2.04 Now handles FieldMatching using an Attribute
  • 23th May, 2014: v3.0 Upgraded to .NET 4.5, Now the caching mechanism checks name and type of the fields in the reader and therefore it can create instances of the same type from different IDataReaders
  • 28th May, 2014: v3.01 Now supports conversion (parsing) of strings to all primitive types
  • 25th June, 2014: v3.02 Now supports conversion to Boolean from all primitive types except Char and DateTime.
  • 18th September, 2014: v3.03 Fixed bug with empty datareaders.
  • 13th Oktober, 2014: v3.04 Added support for elementary type generics.
  • 4th November, 2014: v3.05 Bugfix, when using Single DataType in the VB version, and small performance enhancement.
  • 30th January, 2015: v3.06 Added support for Tuples
  • 4th May, 2015: v4.0 Encountered a nasty bug when all properties wasn't mapped because of a misspelled fieldname. So I have added a check that throws an exception if not all properties have been mapped. As there can be a need to not set all properties at instantiation of an item I have added an optional MustMapAllProperties parameter to all public Methods that defaults to true.
  • 26th January 2016: v4.01 Added support for CommandBehavior.SequentialAccess and MemoryStreams.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2013 by Jörgen Andersson
Everything else Copyright © CodeProject, 1999-2025

Web01 2.8:2024-12-08:1