Mapping Query Result with Entity Properties

Following code helps me to automatically map the query result with Entity properties. For example, result of the following query “ReportTypeCode“ is mapped with Report object’s ReportTypeCode property.

SELECT R.report_type_code AS ReportTypeCode FROM Report_Type R

QUESTIONS

• Is there any datatype or scenario that it will not be able to handle?

• Is there any improvement suggestions?

CODE

```
public static class EntityDataMappingHelper
{
///
/// Method for filling entity from data
///
public static void FillEntityFromRecord(Object entity, Dictionary record)
{
if (entity != null && record != null)
{
PropertyInfo[] propertyInfoArray = entity.GetType().GetProperties();
foreach (PropertyInfo prop in propertyInfoArray)
{
if (record.ContainsKey(prop.Name))
{
if (String.Equals(prop.PropertyType.FullName, "System.String"))
{
prop.SetValue(entity, DBNull.Value.Equals(record[prop.Name]) ? null : Convert.ToString(record[prop.Name], CultureInfo.InvariantCulture), null);
}
else if (String.Equals(prop.PropertyType.FullName, "System.Decimal"))
{
prop.SetValue(entity, DBNull.Value.Equals(record[prop.Name]) ? 0 :
Convert.ToDecimal(record[prop.Name], CultureInfo.InvariantCulture), null);
}
else
{
prop.SetValue(entity, DBNull.Value.Equals(record[prop.Name]) ? null : record[prop.Name], null);
}
}
}
}
}

///
/// Method for selecting records from Data Reader
///
public static ArrayList SelectRecords(Collection entityList, IDataReader reader)
{
ArrayList resultList = new ArrayList();
if (entityList != null && reader !

Observations

-
You are reinventing the wheel. Yes, an Object/Relational Mapper such as Entity Framework adds an overhead, but if your queries are select abc from xyz I don't see it being an issue.

-
Using reflection has an overhead and performance hit, too.

-
Projecting R.report_type_code into a full-fledged Report instance makes no sense. If you want to select report type codes, return report type codes, not a bunch of reports without a description.

-
If a command needed a parameter, would you concatenate it into a WHERE statement within the command text, or use an SqlParameter? Would it be the client code's responsibility? Your code isn't crystal-clear about this.

-
A static class called EntityDataMappingHelper can be considered a code smell. From this Programmers.SE answer (emphasis mine):


if a helper method has any external dependency (e.g. a DB) which makes it - thus its callers - hard to unit test, it is better to declare it non-static. This allows dependency injection, thus making the method's callers easier to unit test.

Recommendations

-
The SelectRecords method doesn't need an instance, it needs a type - make your method generic, substitute that Collection for a ` type parameter with some where TEntity : class, new() type constraint.

-
You shouldn't be calling your strongly-typed return value records - call it reports instead, it's less confusing. Or call it entities if you prefer a more generic name; a "record" is a low-level thing that a "report" doesn't even know/care about.

-
If the database schema is all yours, I think your Report entity wants an Id property. Just in case there's eventually another "entity" that wants to refer to a specific report. Much better than indexing the ReportName column.

-
Use List over the obsolete ArrayList`:

• from StackOverflow: ArrayList belongs to the days that C# didn't have generics. It's deprecated in favor of List. You shouldn't use ArrayList in new code that targets .NET >= 2.0 unless you have to interface with an old API that uses it.

Alternatives

-
Give Entity Framework (Code-First) a try. I'm sure you won't even notice the "performance hit" (given your current code it might actually be a performance increase), and your client code will be much, much simpler. How about this:

IEnumerable reports;
IEnumerable reportTypeCodes;
using (var context = new MyEntityFrameworkContext())
{
    reports = context.Reports.ToList();
    reportTypeCodes = context.Reports
                             .Where(report => report.ReportTypeCode != null)
                             .GroupBy(report => report.ReportTypeCode)
                             .Select(grouping => grouping.Key)
                             .ToList();
}

-
If you really don't want to use EF, you might actually fall in love with Dapper.NET, a micro-ORM - from the SO tag wiki:


it focuses on making the materialization as fast as possible, with no overheads from things like identity managers - just "run this query and give me the (typed) data". [...] Quite possibly the fastest materializer available for .NET.

StackExchange Code Review Q#20075, answer score: 6