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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | // Define our table valued parametervar input = items.Select(x => new MyInputType(){ Prop1 = x.Prop1, Prop2 = x.Prop2, Prop3 = x.Prop3, Prop4 = x.Prop4,}).ToList();// Create our dynamicdynamic 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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.:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | 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} |