EF Dynamic type - The SqlParameter is already contained by another SqlParameterCollection

  • Thread starter Thread starter DreamerCatcher2018
  • Start date Start date
D

DreamerCatcher2018

Guest
I have a stored procedure which gives a dynamic result set

(Eg1. storedProcedureNameXX 4 - sql server result may be 5 columns)
(Eg2. storedProcedureNameXX 1 - sql server result may be 3 columns)

Lets assume i have added the dynamic columns in the Type Builder: TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType")
//Todo:get the dynamic column names
TypeBuilder builder = CreateTypeBuilder("MyDynamicAssembly", "MyModule", "MyType")<br>
//Todo:get the dynamic column names
CreateAutoImplementedProperty(builder, "column1", typeof(string));
CreateAutoImplementedProperty(builder, "column2", typeof(string));
CreateAutoImplementedProperty(builder, "column3", typeof(string));

Type resultType = builder.CreateType();
var parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("parm1", 1));

var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.ToArray());

Error in P1 variable:
"The SqlParameter is already contained by another SqlParameterCollection" Unable to place ToList()


//Added methods used
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);
}

// 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);
}





Already tried solutions:

1. var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1",parameters.ToArray()).ToList();

2. var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.Select(x => ((ICloneable)x).Clone()).ToArray()).ToList();



note: resulttype is a dynamic class which will be created at run time.

In the above tried solutions the ".ToList()" does not appear in IntelliSense


Continue reading...
 
Back
Top