In a previous post, I demoed how to use Table Valued Parameters (TVP’s) with EF. This works great, but, if you’ll notice, it only suppports one-column TVP’s with a simple scalar list of values. I expanded this a bit.
Again, the context of this is that I’m using Entity Framework, but I’m replacing certain generated queries and code-based logic with stored procedures to get better performance. There are many cases where Entity Framework, and ORM’s in general, perform really poorly. A typical example is inserting or updating 1000+ records which have children, grand-children, and great-grand children. This sort of operation can result in 16,000+ SQL calls. If each one of those calls takes just 1/10 of a second, you’re still waiting for nearly three minutes. Is that user friendly? But, I digress .. At any rate, one way to mitigate the problem is to pass the data for this typical work-flow to a stored procedure as a TVP. The TVP, though, has to have all the data/columns necessary to do the work. A simple list of scalar values won’t cut it a lot of times.
From the previous post, you’ll recall that the end result was an IEnumerable<SqlDataRecord< that is passed along as a SqlParameter with a type of SqlDbType.Structured. This doesn’t change. What does change though is that now, instead of a simple list of primitives (ints, strings, etc), the CreateTableInput<T> method needs to support any class. To achieve this, the method is extended to handle both the simple primitive/scalar case as well as any T.
private static IEnumerable<SqlDataRecord> CreateTableInput<T>(string name, IEnumerable<T> items, List<string> excludedProps = null)
{
var type = typeof(T);
var records = new List<SqlDataRecord>();
if (AuditAndMappingExtensions.IsPrimitiveType(type))
{
var sqlType = TypeConvertor.ToSqlDbType(type);
foreach (T value in items)
{
var metaData = new SqlMetaData[] { new SqlMetaData(name, sqlType) };
SqlDataRecord record = new SqlDataRecord(metaData);
record.SetValue(0, value);
records.Add(record);
}
}
else
{
var allProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => (excludedProps == null || !excludedProps.Contains(x.Name)) && (x.PropertyType == typeof(string) || x.PropertyType == typeof(decimal) ||
(!x.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) &&
(AuditAndMappingExtensions.IsPrimitiveType(x.PropertyType) || AuditAndMappingExtensions.IsNullablePrimitive(x.PropertyType) || AuditAndMappingExtensions.IsNullableEnum(x.PropertyType)))))
.ToList();
var props = type.GetProperties()
.Select(prop =>
{
var displayAttribute = (DisplayAttribute)prop.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault();
var maxLengthAttribute = (MaxLengthAttribute)prop.GetCustomAttributes(typeof(MaxLengthAttribute), false).FirstOrDefault();
return new
{
Name = prop.Name,
DisplayName = displayAttribute == null ? prop.Name : displayAttribute.Name,
Order = displayAttribute == null || !displayAttribute.GetOrder().HasValue ? 999 : displayAttribute.Order,
ShortName = displayAttribute == null ? string.Empty : displayAttribute.ShortName,
PropertyInfo = prop,
PropertyType = prop.PropertyType,
HasDisplayName = displayAttribute != null,
SqlDbType = TypeConvertor.ToSqlDbType(prop.PropertyType),
MaxLength = maxLengthAttribute?.Length ?? 200,
IsStringType = prop.PropertyType == typeof(string)
};
})
.OrderBy(prop => prop.Order)
.Where(prop => !string.IsNullOrEmpty(prop.DisplayName))
.ToList();
var metaData = props.Select(x => { var meta = x.IsStringType ? new SqlMetaData(x.Name, x.SqlDbType, x.MaxLength) : new SqlMetaData(x.Name, x.SqlDbType); return meta; }).ToArray();
foreach (T item in items)
{
SqlDataRecord record = new SqlDataRecord(metaData);
var position = 0;
props.Select(p => { var propValue = p.PropertyInfo.GetValue(item, null); record.SetValue(position, propValue); position++; return propValue; }).ToList();
records.Add(record);
}
}
return records.AsEnumerable<SqlDataRecord>();
}
You can see it’s a bit of reflection to get the properties of T when T is not a primitive. The only thing special about the relection is that I do look for Display and MaxLength attributes. If there is a Display attribute with a blank name, the property is excluded. If the Property is a string, and MaxLength is specified, then that maxlength is used for defining the SqlColumn. From there, we’re create the SqlMetaData, converting the C# type for the property to the appropriate SqlDbType, creating a SqlDataRecord per item, and using all of the previous information to create each column.
With the above code in place, it’s possible to call any stored procedure that takes a TVP using any type T. In my repository pattern, I expose a method that, behind the scenes, using EF’s SqlQuery<T> to execute any arbitrary stored procedure. It uses the method listed above in conjunction with a bit of other magic to convert a dynamic to the appropriate SqlParamters. In my case, bringing it all together looks like this:
// Define our table valued parameter
var input = items.Select(x => new MyInputType()
{
Prop1 = x.Prop1,
Prop2 = x.Prop2,
Prop3 = x.Prop3,
Prop4 = x.Prop4,
}).ToList();
// Create our dynamic
dynamic procParams = new ExpandoObject().Init(new
{
myProperty = new ExpandoObject().Init(new
{
IsTable = true,
TableType = "MyInputType",
ColumnName = "None",
Values = input
})
});
var returnVals = (List<MyReturnType>)_myRepo.GetFromSproc<MyReturnType>(procParams, "myStoredProcedure");
Here are my “mapping” extensions that I use for determining if a property is primitive or nullable:
public static class AuditAndMappingExtensions
{
public static bool IsPrimitiveType(this Type t)
{
var isPrimitive = t.IsPrimitive || t.IsValueType || t == typeof(string) || t == typeof(decimal);
return isPrimitive;
}
public static bool IsNullablePrimitive(this Type t)
{
var isNullable = t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>);
if (isNullable)
{
var underlyingType = Nullable.GetUnderlyingType(t);
return IsPrimitiveType(underlyingType);
}
return isNullable;
}
public static bool IsNullableEnum(Type t)
{
Type u = Nullable.GetUnderlyingType(t);
return (u != null) && u.IsEnum;
}
}
Here’s the class I use for determining the SqlDbType from the CLR type.:
public class TypeConvertor
{
private struct DbTypeMapEntry
{
public Type Type;
public DbType DbType;
public SqlDbType SqlDbType;
public DbTypeMapEntry(Type type, DbType dbType, SqlDbType sqlDbType)
{
this.Type = type;
this.DbType = dbType;
this.SqlDbType = sqlDbType;
}
};
private static ArrayList _DbTypeList = new ArrayList();
#region Constructors
static TypeConvertor()
{
DbTypeMapEntry dbTypeMapEntry = new DbTypeMapEntry(typeof(bool), DbType.Boolean, SqlDbType.Bit);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(byte), DbType.Double, SqlDbType.TinyInt);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(byte[]), DbType.Binary, SqlDbType.Image);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(DateTime), DbType.DateTime, SqlDbType.DateTime);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(Decimal), DbType.Decimal, SqlDbType.Decimal);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(double), DbType.Double, SqlDbType.Float);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(Guid), DbType.Guid, SqlDbType.UniqueIdentifier);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(Int16), DbType.Int16, SqlDbType.SmallInt);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(Int32), DbType.Int32, SqlDbType.Int);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(Int64), DbType.Int64, SqlDbType.BigInt);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(object), DbType.Object, SqlDbType.Variant);
_DbTypeList.Add(dbTypeMapEntry);
dbTypeMapEntry = new DbTypeMapEntry(typeof(string), DbType.String, SqlDbType.VarChar);
_DbTypeList.Add(dbTypeMapEntry);
}
private TypeConvertor() { }
#endregion
#region Methods
/// <summary>
/// Convert db type to .Net data type
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static Type ToNetType(DbType dbType)
{
DbTypeMapEntry entry = Find(dbType);
return entry.Type;
}
/// <summary>
/// Convert TSQL type to .Net data type
/// </summary>
/// <param name="sqlDbType"></param>
/// <returns></returns>
public static Type ToNetType(SqlDbType sqlDbType)
{
DbTypeMapEntry entry = Find(sqlDbType);
return entry.Type;
}
/// <summary>
/// Convert .Net type to Db type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static DbType ToDbType(Type type)
{
DbTypeMapEntry entry = Find(type);
return entry.DbType;
}
/// <summary>
/// Convert TSQL data type to DbType
/// </summary>
/// <param name="sqlDbType"></param>
/// <returns></returns>
public static DbType ToDbType(SqlDbType sqlDbType)
{
DbTypeMapEntry entry = Find(sqlDbType);
return entry.DbType;
}
/// <summary>
/// Convert .Net type to TSQL data type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static SqlDbType ToSqlDbType(Type type)
{
DbTypeMapEntry entry = Find(type);
return entry.SqlDbType;
}
/// <summary>
/// Convert DbType type to TSQL data type
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
public static SqlDbType ToSqlDbType(DbType dbType)
{
DbTypeMapEntry entry = Find(dbType);
return entry.SqlDbType;
}
private static DbTypeMapEntry Find(Type type)
{
object retObj = null;
for (int i = 0; i < _DbTypeList.Count; i++)
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if (entry.Type == (Nullable.GetUnderlyingType(type) ?? type))
{
retObj = entry;
break;
}
}
if (retObj == null)
{
throw
new ApplicationException("Referenced an unsupported Type");
}
return (DbTypeMapEntry)retObj;
}
private static DbTypeMapEntry Find(DbType dbType)
{
object retObj = null;
for (int i = 0; i < _DbTypeList.Count; i++)
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if (entry.DbType == dbType)
{
retObj = entry;
break;
}
}
if (retObj == null)
{
throw
new ApplicationException("Referenced an unsupported DbType");
}
return (DbTypeMapEntry)retObj;
}
private static DbTypeMapEntry Find(SqlDbType sqlDbType)
{
object retObj = null;
for (int i = 0; i < _DbTypeList.Count; i++)
{
DbTypeMapEntry entry = (DbTypeMapEntry)_DbTypeList[i];
if (entry.SqlDbType == sqlDbType)
{
retObj = entry;
break;
}
}
if (retObj == null)
{
throw
new ApplicationException("Referenced an unsupported SqlDbType");
}
return (DbTypeMapEntry)retObj;
}
#endregion
}