Linq to sql table dynamically with ExecuteQuery VB

Question

I am using VB Framework 4.0 and Linq to sql.

I want to choose dynamycally the name of table. I have used the library namedtable.dll and I have mapped all the tables of database and it's Ok.

My problem is when I try to execute executequery. Here my code.

Imports Microsoft.VisualBasic
Imports System.Data.Linq
Imports Prototype.NamedTable.Data
Imports Prototype.NamedTable.Utility

    Public Class tipos

        Private _conexion As String = "conexion"

        Public Sub New()

        End Sub

        ...........

        Public Function getConsulta(byval tableName as String) As IList(Of TIPOS)

            Dim context As New DataContext(_conexion)

            sql = " select COD, NAME from " & tableName

            Dim a = context.ExecuteQuery(Of TIPOS)(sql)

            Return sql.ToList

        End Function
    End Class

but I have an error: "El tipo 'TIPOS' debe declarar un constructor predeterminado (sin parámetros) para que pueda construirse durante la asignación." that in English is:

"The type 'Type TIPOS' must declare a default (parameterless) constructor in order to be constructed during mapping"

I have defined "TIPOS" in other file:

Public Interface TIPOS
    <Column(CanBeNull:=False)> Property COD Integer
    <Column(CanBeNull:=False)> Property NAME As String
End Interface

Public Class ITIPO : Implements TIPO
    Private _cod As Integer
    Private _name As String


    Public Property COD As Integer Implements TIPO.COD
        Get
            Return _cod
        End Get
        Set(ByVal value As Integer)
            _cod = value
        End Set
    End Property

    Public Property NAME As String Implements TIPO.NAME
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property

End Class

I need help!

Sorry for my English.

Answer 1

The solution can be found on codeproject.com in article "Dynamic Table Mapping for LINQ-to-SQL." Below is a static class you can use. Please see the article for instructions on what you must do to use the 4 different generic methods. Here is an invocation example:

public interface IResult
{
    [Column(IsPrimaryKey = true)]
    int Id { get; set; }

    [Column]
    string Name { get; set; }

    [Column]
    double Value { get; set; }
}

public void TestThis()
{
    var connectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=YourDatabaseName;Integrated Security=True;Pooling=False";
    var context = new DataContext(connectionString);
    var table = context.GetTable<IResult>("YourTableName");
    var query = from r in table where r.Id == 108 select r;
    var list = query.ToList();
}

Class Code:

namespace Prototype.NamedTable
{
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Reflection.Emit;

/// <summary>
/// The utility.
/// </summary>
public static class Utility
{
    #region Constants and Fields

    /// <summary>
    /// The named types.
    /// </summary>
    private static readonly Dictionary<string, Type> NamedTypes = new Dictionary<string, Type>();

    /// <summary>
    /// The _assembly builder.
    /// </summary>
    private static AssemblyBuilder _assemblyBuilder;

    /// <summary>
    /// The _module builder.
    /// </summary>
    private static ModuleBuilder _moduleBuilder;

    #endregion

    #region Properties

    /// <summary>
    /// Gets or sets a value indicating whether Verbose.
    /// </summary>
    public static bool Verbose { get; set; }

    #endregion

    #region Public Methods

    /// <summary>
    /// The clear.
    /// </summary>
    public static void Clear()
    {
        _assemblyBuilder = null;
        NamedTypes.Clear();
    }

    /// <summary>
    /// Retrieve a table from the data context which implements ITable&lt;TEntity&gt; by T and use ITable&lt;TBack&gt;
    /// </summary>
    /// <typeparam name="TEntity">
    /// Entity Type
    /// </typeparam>
    /// <typeparam name="TBack">
    /// Backing Type
    /// </typeparam>
    /// <param name="context">
    /// Data Context
    /// </param>
    /// <returns>
    /// </returns>
    public static ATable<TEntity> GetTable<TEntity, TBack>(this DataContext context) where TEntity : class
        where TBack : class
    {
        // Create the backup table
        Table<TBack> refer = context.GetTable<TBack>();

        // Prepare the cloning method
        Delegate cloneFrom = CompileCloning(typeof(TEntity), typeof(TBack));

        // Construct the table wrapper
        return new ATable<TEntity>(refer, cloneFrom);
    }

    /// <summary>
    /// Retrieve a table from the data context which implements ITable&lt;TEntity&gt; uses specific backing table
    /// </summary>
    /// <typeparam name="TEntity">
    /// Entity Type
    /// </typeparam>
    /// <param name="context">
    /// Data context
    /// </param>
    /// <param name="name">
    /// Table name
    /// </param>
    /// <returns>
    /// </returns>
    public static ATable<TEntity> GetTable<TEntity>(this DataContext context, string name) where TEntity : class
    {
        // Create/Retrieve a type definition for the table using the TEntity type
        Type type = DefineEntityType(typeof(TEntity), name);

        // Create the backup table using the new type
        ITable refer = context.GetTable(type);

        // Prepare the cloning method
        Delegate cloneFrom = CompileCloning(typeof(TEntity), type);

        // Construct the table wrapper
        return new ATable<TEntity>(refer, cloneFrom);
    }

    /*
    /// <summary>
    /// The log.
    /// </summary>
    /// <param name="format">
    /// The format.
    /// </param>
    /// <param name="args">
    /// The args.
    /// </param>
    public static void Log(string format, params object[] args)
    {
        if (!Verbose)
        {
            return;
        }

        Console.Write("*** ");

        if ((args == null) || (args.Length == 0))
        {
            Console.WriteLine(format);
        }
        else
        {
            Console.WriteLine(format, args);
        }
    }*/

    #endregion

    #region Methods

    /// <summary>
    /// Clone an attribute
    /// </summary>
    /// <param name="attr">
    /// </param>
    /// <returns>
    /// </returns>
    private static CustomAttributeBuilder CloneColumn(object attr)
    {
        Type source = attr.GetType();
        Type target = typeof(ColumnAttribute);

        var props = new List<PropertyInfo>();
        var values = new List<object>();

        // Extract properties and their values
        foreach (PropertyInfo prop in source.GetProperties())
        {
            if (!prop.CanRead || !prop.CanWrite)
            {
                continue;
            }

            props.Add(target.GetProperty(prop.Name));
            values.Add(prop.GetValue(attr, null));
        }

        // Create a new attribute using the properties and values
        return new CustomAttributeBuilder(
            target.GetConstructor(Type.EmptyTypes), new object[0], props.ToArray(), values.ToArray());
    }

    /// <summary>
    /// Make a delegate that copy content from "source" to "dest"
    /// </summary>
    /// <param name="source">
    /// Source Type
    /// </param>
    /// <param name="dest">
    /// Destination Type
    /// </param>
    /// <returns>
    /// Executable delegate
    /// </returns>
    private static Delegate CompileCloning(Type source, Type dest)
    {
        // Input parameter
        ParameterExpression input = Expression.Parameter(source);

        // For every property, create a member binding
        List<MemberBinding> binds =
            source.GetProperties().Select(
                prop =>
                Expression.Bind(
                    dest.GetProperty(
                        prop.Name, BindingFlags.Instance | BindingFlags.Public | BindingFlags.DeclaredOnly), 
                    Expression.MakeMemberAccess(input, prop))).Cast<MemberBinding>().ToList();

        // Expression of creating the new object
        MemberInitExpression body = Expression.MemberInit(
            Expression.New(dest.GetConstructor(Type.EmptyTypes)), binds);

        // The final lambda
        LambdaExpression lambda = Expression.Lambda(body, input);

        // MJE
        //Log("{0}", lambda.ToString());

        // Return the executable delegate
        return lambda.Compile();
    }

    /// <summary>
    /// Create a class based on the template interface
    /// </summary>
    /// <param name="template">
    /// </param>
    /// <param name="name">
    /// </param>
    /// <returns>
    /// </returns>
    private static Type DefineEntityType(Type template, string name)
    {
        // Prepare the builders if not done
        if (_assemblyBuilder == null)
        {
            _assemblyBuilder =
                AppDomain.CurrentDomain.DefineDynamicAssembly(
                    new AssemblyName(Guid.NewGuid().ToString()), AssemblyBuilderAccess.Run);

            _moduleBuilder = _assemblyBuilder.DefineDynamicModule("Types");
        }

        // Check if there is already a type created for that table
        if (NamedTypes.ContainsKey(name))
        {
            return NamedTypes[name];
        }

        // Create the new type
        TypeBuilder tbuilder = null;
        if (template.IsInterface)
        {
            tbuilder = DefineInterfaceChild(name, template);
        }
        else
        {
            tbuilder = DefineOverriddenChild(name, template);
        }

        Type final = tbuilder.CreateType();

        NamedTypes[name] = final;

        return final;
    }

    /// <summary>
    /// The define interface child.
    /// </summary>
    /// <param name="name">
    /// The name.
    /// </param>
    /// <param name="template">
    /// The template.
    /// </param>
    /// <returns>
    /// </returns>
    private static TypeBuilder DefineInterfaceChild(string name, Type template)
    {
        TypeBuilder tbuilder = _moduleBuilder.DefineType(
            name, TypeAttributes.Public, typeof(Object), new[] { template });

        // Default constructor
        tbuilder.DefineDefaultConstructor(MethodAttributes.Public);

        // Attach Table attribute
        var abuilder = new CustomAttributeBuilder(
            typeof(TableAttribute).GetConstructor(Type.EmptyTypes), 
            new object[0], 
            new[] { typeof(TableAttribute).GetProperty("Name") }, 
            new object[] { name });
        tbuilder.SetCustomAttribute(abuilder);

        List<PropertyInfo> properties = template.GetProperties().ToList(); // May require sorting

        // Implement all properties));
        foreach (PropertyInfo prop in properties)
        {
            // Define backing field
            FieldBuilder fbuilder = tbuilder.DefineField(
                "_" + prop.Name, prop.PropertyType, FieldAttributes.Private);

            // Define get method
            MethodBuilder pgbuilder = tbuilder.DefineMethod(
                "get_" + prop.Name, 
                MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig
                | MethodAttributes.Virtual | MethodAttributes.Final, 
                prop.PropertyType, 
                Type.EmptyTypes);

            // Define get method body { return _field; }
            ILGenerator ilg = pgbuilder.GetILGenerator();
            ilg.Emit(OpCodes.Ldarg_0);
            ilg.Emit(OpCodes.Ldfld, fbuilder);
            ilg.Emit(OpCodes.Ret);

            // Define set method
            MethodBuilder psbuilder = tbuilder.DefineMethod(
                "set_" + prop.Name, 
                MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig
                | MethodAttributes.Virtual | MethodAttributes.Final, 
                null, 
                new[] { prop.PropertyType });

            // Define set method body { _field = value; }
            ILGenerator ils = psbuilder.GetILGenerator();
            ils.Emit(OpCodes.Ldarg_0);
            ils.Emit(OpCodes.Ldarg_1);
            ils.Emit(OpCodes.Stfld, fbuilder);
            ils.Emit(OpCodes.Ret);

            // Define the property
            PropertyBuilder pbuilder = tbuilder.DefineProperty(
                prop.Name, PropertyAttributes.None, CallingConventions.Standard, prop.PropertyType, null);

            // Set get/set method
            pbuilder.SetGetMethod(pgbuilder);
            pbuilder.SetSetMethod(psbuilder);

            // Attach Column attribute
            foreach (object attr in prop.GetCustomAttributes(false))
            {
                if (attr is ColumnAttribute || attr is AlterColumnAttribute)
                {
                    // MJE
                    //Log("Create column attribute for {0}", prop.Name);
                    pbuilder.SetCustomAttribute(CloneColumn(attr));
                    break;
                }
            }
        }

        return tbuilder;
    }

    /// <summary>
    /// The define overridden child.
    /// </summary>
    /// <param name="name">
    /// The name.
    /// </param>
    /// <param name="template">
    /// The template.
    /// </param>
    /// <returns>
    /// </returns>
    private static TypeBuilder DefineOverriddenChild(string name, Type template)
    {
        TypeBuilder tbuilder = _moduleBuilder.DefineType(name, TypeAttributes.Public, template);

        // Default constructor
        tbuilder.DefineDefaultConstructor(MethodAttributes.Public);

        // Attach Table attribute
        var abuilder = new CustomAttributeBuilder(
            typeof(TableAttribute).GetConstructor(Type.EmptyTypes), 
            new object[0], 
            new[] { typeof(TableAttribute).GetProperty("Name") }, 
            new object[] { name });
        tbuilder.SetCustomAttribute(abuilder);

        List<PropertyInfo> properties = template.GetProperties().ToList(); // May require sorting

        // Implement all properties));
        foreach (PropertyInfo prop in properties)
        {
            // Define get method
            MethodBuilder pgbuilder = tbuilder.DefineMethod(
                "get_" + prop.Name, 
                MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig
                | MethodAttributes.Virtual | MethodAttributes.Final, 
                prop.PropertyType, 
                Type.EmptyTypes);

            // Define get method body { return _field; }
            ILGenerator ilg = pgbuilder.GetILGenerator();
            ilg.Emit(OpCodes.Ldarg_0);
            ilg.Emit(OpCodes.Call, template.GetMethod("get_" + prop.Name));
            ilg.Emit(OpCodes.Ret);

            // Define set method
            MethodBuilder psbuilder = tbuilder.DefineMethod(
                "set_" + prop.Name, 
                MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig
                | MethodAttributes.Virtual | MethodAttributes.Final, 
                null, 
                new[] { prop.PropertyType });

            // Define set method body { _field = value; }
            ILGenerator ils = psbuilder.GetILGenerator();
            ils.Emit(OpCodes.Ldarg_0);
            ils.Emit(OpCodes.Ldarg_1);
            ils.Emit(OpCodes.Call, template.GetMethod("set_" + prop.Name));
            ils.Emit(OpCodes.Ret);

            // Define the property
            PropertyBuilder pbuilder = tbuilder.DefineProperty(
                prop.Name, PropertyAttributes.None, CallingConventions.Standard, prop.PropertyType, null);

            // Set get/set method
            pbuilder.SetGetMethod(pgbuilder);
            pbuilder.SetSetMethod(psbuilder);

            // Attach Column attribute
            foreach (object attr in prop.GetCustomAttributes(false))
            {
                if (attr is ColumnAttribute || attr is AlterColumnAttribute)
                {
                    // MJE
                    //Log("Create column attribute for {0}", prop.Name);
                    pbuilder.SetCustomAttribute(CloneColumn(attr));
                    break;
                }
            }
        }

        return tbuilder;
    }

    #endregion

    /// <summary>
    /// A table wrapper implements ITable&lt;TEntity&gt; backed by other ITable object
    /// </summary>
    /// <typeparam name="TEntity">
    /// </typeparam>
    public class ATable<TEntity> : ITable<TEntity>
        where TEntity : class
    {
        #region Constants and Fields

        /// <summary>
        /// Cloning method
        /// </summary>
        private readonly Delegate _clone;

        /// <summary>
        /// Backing table
        /// </summary>
        private readonly ITable _internal;

        #endregion

        #region Constructors and Destructors

        /// <summary>
        /// Initializes a new instance of the <see cref="ATable{TEntity}"/> class. 
        /// Construct from backing table
        /// </summary>
        /// <param name="inter">
        /// </param>
        /// <param name="from">
        /// </param>
        public ATable(ITable inter, Delegate from)
        {
            this._internal = inter;
            this._clone = from;
        }

        #endregion

        #region Properties

        /// <summary>
        /// Gets ElementType.
        /// </summary>
        public Type ElementType
        {
            get
            {
                // Use the backing table element
                return this._internal.ElementType;
            }
        }

        /// <summary>
        /// Gets Expression.
        /// </summary>
        public Expression Expression
        {
            get
            {
                // Use the backing table expression
                return this._internal.Expression;
            }
        }

        /// <summary>
        /// Gets Provider.
        /// </summary>
        public IQueryProvider Provider
        {
            get
            {
                // Use the backing table provider
                return this._internal.Provider;
            }
        }

        #endregion

        #region Implemented Interfaces

        #region IEnumerable

        /// <summary>
        /// The get enumerator.
        /// </summary>
        /// <returns>
        /// </returns>
        /// <exception cref="NotImplementedException">
        /// </exception>
        IEnumerator IEnumerable.GetEnumerator()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IEnumerable<TEntity>

        /// <summary>
        /// The get enumerator.
        /// </summary>
        /// <returns>
        /// </returns>
        /// <exception cref="NotImplementedException">
        /// </exception>
        public IEnumerator<TEntity> GetEnumerator()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region ITable<TEntity>

        /// <summary>
        /// The attach.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        /// <exception cref="NotImplementedException">
        /// </exception>
        public void Attach(TEntity entity)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// The delete on submit.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        public void DeleteOnSubmit(TEntity entity)
        {
            // Directly invoke the backing table
            this._internal.DeleteOnSubmit(entity);
        }

        /// <summary>
        /// The insert on submit.
        /// </summary>
        /// <param name="entity">
        /// The entity.
        /// </param>
        public void InsertOnSubmit(TEntity entity)
        {
            // Input entity must be changed to backing type
            object v = this._clone.DynamicInvoke(entity);

            // Invoke the backing table
            this._internal.InsertOnSubmit(v);
        }

        #endregion

        #endregion
    }

    /// <summary>
    /// The alter column attribute.
    /// </summary>
    public class AlterColumnAttribute : Attribute
    {
        #region Constants and Fields

        /// <summary>
        /// The _can be null.
        /// </summary>
        private bool _canBeNull = true;

        /// <summary>
        /// The _update check.
        /// </summary>
        private UpdateCheck _updateCheck = UpdateCheck.Always;

        #endregion

        #region Properties

        /// <summary>
        /// Gets or sets AutoSync.
        /// </summary>
        public AutoSync AutoSync { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether CanBeNull.
        /// </summary>
        public bool CanBeNull
        {
            get
            {
                return this._canBeNull;
            }

            set
            {
                this._canBeNull = value;
            }
        }

        /// <summary>
        /// Gets or sets DbType.
        /// </summary>
        public string DbType { get; set; }

        /// <summary>
        /// Gets or sets Expression.
        /// </summary>
        public string Expression { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether IsDbGenerated.
        /// </summary>
        public bool IsDbGenerated { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether IsDiscriminator.
        /// </summary>
        public bool IsDiscriminator { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether IsPrimaryKey.
        /// </summary>
        public bool IsPrimaryKey { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether IsVersion.
        /// </summary>
        public bool IsVersion { get; set; }

        /// <summary>
        /// Gets or sets UpdateCheck.
        /// </summary>
        public UpdateCheck UpdateCheck
        {
            get
            {
                return this._updateCheck;
            }

            set
            {
                this._updateCheck = value;
            }
        }

        #endregion
    }
}
}

Answer 2

Linq-to-Sql cannot materialize interfaces. It needs a class specification to know what instances it should create from a query. The exception message is elusive, to say the least. I don't know why it isn't more to the point.

Note that the class you want to materialize must have been mapped, or: it must be in the dbml. I say this because your ITIPO class is not partial, which makes me wonder how you can make it implement an interface (well, maybe you just slimmed down the code).

Side note: don't use all capitals for class names, and prefix an interface specification with "I", not a class.