【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 型別去存取它動態產生出來的不具名型別物件。

public static System.Collections.IEnumerable DynamicSqlQuery(this Database database, string sql, params object[] parameters)
{
    TypeBuilder builder = _CreateTypeBuilder(
            "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");

    using (System.Data.IDbCommand command = database.Connection.CreateCommand())
    {
        try
        {
            if (database.Connection.State == ConnectionState.Closed)
                database.Connection.Open();
            command.CommandText = sql;
            command.CommandTimeout = command.Connection.ConnectionTimeout;
            foreach (var param in parameters)
            {
                command.Parameters.Add(param);
            }

            using (System.Data.IDataReader reader = command.ExecuteReader())
            {
                var schema = reader.GetSchemaTable();
                foreach (System.Data.DataRow row in schema.Rows)
                {
                    string name = (string)row["ColumnName"];
                    Type type = (Type)row["DataType"];
                    _CreateAutoImplementedProperty(builder, name, type);
                }
            }
        }
        finally
        {
            database.Connection.Close();
            command.Parameters.Clear();
        }
    }

    Type resultType = builder.CreateType();

    return database.SqlQuery(resultType, sql, parameters);
}

#region Inner method of DynamicSqlQuery(...)
private static TypeBuilder _CreateTypeBuilder(
    string assemblyName, string moduleName, string typeName)
{
    TypeBuilder typeBuilder = AppDomain
        .CurrentDomain
        .DefineDynamicAssembly(new AssemblyName(assemblyName),
                                AssemblyBuilderAccess.Run)
        .DefineDynamicModule(moduleName)
        .DefineType(typeName, TypeAttributes.Public);
    typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
    return typeBuilder;
}

private static void _CreateAutoImplementedProperty(
    TypeBuilder builder, string propertyName, Type propertyType)
{
    const string PrivateFieldPrefix = "m_";
    const string GetterPrefix = "get_";
    const string SetterPrefix = "set_";

    // Generate the field.
    FieldBuilder fieldBuilder = builder.DefineField(
        string.Concat(PrivateFieldPrefix, propertyName),
                        propertyType, FieldAttributes.Private);

    // Generate the property
    PropertyBuilder propertyBuilder = builder.DefineProperty(
        propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

    // Property getter and setter attributes.
    MethodAttributes propertyMethodAttributes =
        MethodAttributes.Public | MethodAttributes.SpecialName |
        MethodAttributes.HideBySig;

    // Define the getter method.
    MethodBuilder getterMethod = builder.DefineMethod(
        string.Concat(GetterPrefix, propertyName),
        propertyMethodAttributes, propertyType, Type.EmptyTypes);

    // Emit the IL code.
    // ldarg.0
    // ldfld,_field
    // ret
    ILGenerator getterILCode = getterMethod.GetILGenerator();
    getterILCode.Emit(OpCodes.Ldarg_0);
    getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
    getterILCode.Emit(OpCodes.Ret);

    // Define the setter method.
    MethodBuilder setterMethod = builder.DefineMethod(
        string.Concat(SetterPrefix, propertyName),
        propertyMethodAttributes, null, new Type[] { propertyType });

    // Emit the IL code.
    // ldarg.0
    // ldarg.1
    // stfld,_field
    // ret
    ILGenerator setterILCode = setterMethod.GetILGenerator();
    setterILCode.Emit(OpCodes.Ldarg_0);
    setterILCode.Emit(OpCodes.Ldarg_1);
    setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
    setterILCode.Emit(OpCodes.Ret);

    propertyBuilder.SetGetMethod(getterMethod);
    propertyBuilder.SetSetMethod(setterMethod);
}
#endregion

如果不知道擴充方法(Extension method)怎麼實作，可以參考 這篇。

結果取用範例 (隨手打的，Copy Paste 的話要自己檢查一下！)

string sqlCmd = "sp_myProc @p1, @p2";
List<SqlParameter> lstOfPars = new List<SqlParameter>() {
   new SqlParameter() {
       Direction = ParameterDirection.Input,
       ParameterName = "p1",
       SqlDbType = SqlDbType.DateTime,
       Value = someDay
   },
   new SqlParameter() {
       Direction = ParameterDirection.Input,
       ParameterName = "p2",
       SqlDbType = SqlDbType.NVarChar,
       Value = someStr
   }
};
var myObjLst = new List<MyObj>();
// 重點
var lst = _entity.Database.DynamicSqlQuery(sqlCmd, lstOfPars.ToArray());
foreach (dynamic item in lst)
{
    MyObj obj = new MyObj();
    obj.Caption = item.Caption;
    obj.Num = Convert.ToInt32(item.Num);
    myObjLst.Add(obj);
}

ref: stackoverflow

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