2015年10月22日 星期四

【Entity Framework】Anonymous type query

Using stored procedure to select an anonymous type result set
在使用 EF 的 ASP.NET MVC 專案中需要跑一段預存程序然後回傳一組不具名型別的資料,
類似

SELECT Student.Name, Class.Name FROM Student INNER JOIN Class ON Student.ClassId = Class.Id

這個 Query 出來的結果就是一個新的型別(既不屬於 Student 也不是 Class),偏偏 EF 的設計就是希望你弄出來的東西是強型別的,而不是像 DataTable 這類的東西(我本來一直天真的以為一定會有可以 Query Result 是 DataSet 或 DataTable)。

感謝 stankovski(連結最下方的回覆) 提供的擴充方法,完成擴充後我們就可以利用 dynamic 型別去存取它動態產生出來的不具名型別物件。
  1. public static System.Collections.IEnumerable DynamicSqlQuery(this Database database, string sql, params object[] parameters)
  2. {
  3. TypeBuilder builder = _CreateTypeBuilder(
  4. "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
  5.  
  6. using (System.Data.IDbCommand command = database.Connection.CreateCommand())
  7. {
  8. try
  9. {
  10. if (database.Connection.State == ConnectionState.Closed)
  11. database.Connection.Open();
  12. command.CommandText = sql;
  13. command.CommandTimeout = command.Connection.ConnectionTimeout;
  14. foreach (var param in parameters)
  15. {
  16. command.Parameters.Add(param);
  17. }
  18.  
  19. using (System.Data.IDataReader reader = command.ExecuteReader())
  20. {
  21. var schema = reader.GetSchemaTable();
  22. foreach (System.Data.DataRow row in schema.Rows)
  23. {
  24. string name = (string)row["ColumnName"];
  25. Type type = (Type)row["DataType"];
  26. _CreateAutoImplementedProperty(builder, name, type);
  27. }
  28. }
  29. }
  30. finally
  31. {
  32. database.Connection.Close();
  33. command.Parameters.Clear();
  34. }
  35. }
  36.  
  37. Type resultType = builder.CreateType();
  38.  
  39. return database.SqlQuery(resultType, sql, parameters);
  40. }
  41.  
  42. #region Inner method of DynamicSqlQuery(...)
  43. private static TypeBuilder _CreateTypeBuilder(
  44. string assemblyName, string moduleName, string typeName)
  45. {
  46. TypeBuilder typeBuilder = AppDomain
  47. .CurrentDomain
  48. .DefineDynamicAssembly(new AssemblyName(assemblyName),
  49. AssemblyBuilderAccess.Run)
  50. .DefineDynamicModule(moduleName)
  51. .DefineType(typeName, TypeAttributes.Public);
  52. typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
  53. return typeBuilder;
  54. }
  55.  
  56. private static void _CreateAutoImplementedProperty(
  57. TypeBuilder builder, string propertyName, Type propertyType)
  58. {
  59. const string PrivateFieldPrefix = "m_";
  60. const string GetterPrefix = "get_";
  61. const string SetterPrefix = "set_";
  62.  
  63. // Generate the field.
  64. FieldBuilder fieldBuilder = builder.DefineField(
  65. string.Concat(PrivateFieldPrefix, propertyName),
  66. propertyType, FieldAttributes.Private);
  67.  
  68. // Generate the property
  69. PropertyBuilder propertyBuilder = builder.DefineProperty(
  70. propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);
  71.  
  72. // Property getter and setter attributes.
  73. MethodAttributes propertyMethodAttributes =
  74. MethodAttributes.Public | MethodAttributes.SpecialName |
  75. MethodAttributes.HideBySig;
  76.  
  77. // Define the getter method.
  78. MethodBuilder getterMethod = builder.DefineMethod(
  79. string.Concat(GetterPrefix, propertyName),
  80. propertyMethodAttributes, propertyType, Type.EmptyTypes);
  81.  
  82. // Emit the IL code.
  83. // ldarg.0
  84. // ldfld,_field
  85. // ret
  86. ILGenerator getterILCode = getterMethod.GetILGenerator();
  87. getterILCode.Emit(OpCodes.Ldarg_0);
  88. getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
  89. getterILCode.Emit(OpCodes.Ret);
  90.  
  91. // Define the setter method.
  92. MethodBuilder setterMethod = builder.DefineMethod(
  93. string.Concat(SetterPrefix, propertyName),
  94. propertyMethodAttributes, null, new Type[] { propertyType });
  95.  
  96. // Emit the IL code.
  97. // ldarg.0
  98. // ldarg.1
  99. // stfld,_field
  100. // ret
  101. ILGenerator setterILCode = setterMethod.GetILGenerator();
  102. setterILCode.Emit(OpCodes.Ldarg_0);
  103. setterILCode.Emit(OpCodes.Ldarg_1);
  104. setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
  105. setterILCode.Emit(OpCodes.Ret);
  106.  
  107. propertyBuilder.SetGetMethod(getterMethod);
  108. propertyBuilder.SetSetMethod(setterMethod);
  109. }
  110. #endregion
如果不知道擴充方法(Extension method)怎麼實作,可以參考 這篇

結果取用範例 (隨手打的,Copy Paste 的話要自己檢查一下!)
  1. string sqlCmd = "sp_myProc @p1, @p2";
  2. List<SqlParameter> lstOfPars = new List<SqlParameter>() {
  3. new SqlParameter() {
  4. Direction = ParameterDirection.Input,
  5. ParameterName = "p1",
  6. SqlDbType = SqlDbType.DateTime,
  7. Value = someDay
  8. },
  9. new SqlParameter() {
  10. Direction = ParameterDirection.Input,
  11. ParameterName = "p2",
  12. SqlDbType = SqlDbType.NVarChar,
  13. Value = someStr
  14. }
  15. };
  16. var myObjLst = new List<MyObj>();
  17. // 重點
  18. var lst = _entity.Database.DynamicSqlQuery(sqlCmd, lstOfPars.ToArray());
  19. foreach (dynamic item in lst)
  20. {
  21. MyObj obj = new MyObj();
  22. obj.Caption = item.Caption;
  23. obj.Num = Convert.ToInt32(item.Num);
  24. myObjLst.Add(obj);
  25. }

ref: stackoverflow

補充
其實你也可以手工建個複雜型別,然後用 entities.Database.SqlQuery() 或entities.usp_SomeProc() 選取該型別為回傳來做到,參考

沒有留言:

張貼留言