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

Question

I have a stored procedure will will give 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")<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);
    }

If you try to enumerate over p1 twice, it will throw that error. — DavidG
– DavidG, Commented Nov 7, 2018 at 9:35
Even just hovering over the p1 variable with your mouse may cause it to materialise the data, meaning that your code later on will try to do it again. — DavidG
– DavidG, Commented Nov 7, 2018 at 10:12

Tetsuya Yamamoto · Accepted Answer · 2018-11-07 10:09:09Z

1

Sounds like this occurred as the effect of deferred execution. Usually simple ToList() call should work:

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

If ToList() like above still doesn't work, try to clone existing SqlParameter instance and use it like this example:

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

Or with Database.SqlQuery<T>:

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

Reference: SqlParameter.ICloneable.Clone Method

Related issue: Database.SqlQuery gives the The SqlParameter is already contained by another SqlParameterCollection

answered Nov 7, 2018 at 10:09

Tetsuya Yamamoto

25.1k8 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

elizabeth Over a year ago

I have already tried<br>var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.ToArray()).ToList();<br>var p1 = entity.Database.SqlQuery(resultType, "exec storedProcedureNameXX @parm1", parameters.Select(x => ((ICloneable)x).Clone()).ToArray()).ToList();<br> the tolist() is not appearing

elizabeth Over a year ago

I can't use as Database.SqlQuery<T>: since the resulttype is dynamic columns/ class.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related