Create fluent mapping at runtime

[Ashley]

Hi,

We have a situation where there may be some tables in the DB that we haven't mapped (fluent mapping) as they would have been created after our solution. Is there any way that we can create these mappings at runtime? I am able to get the missing tables/columns using the SchemaReader API. From this I am able to create the assemblies for these classes. When I try and create the mappings I get the following error in the PrepareMapping() method:

Error 2 Value cannot be null.
Parameter name: source
ExceptionString:
System.ArgumentNullException: Value cannot be null.
Parameter name: source
   at System.Data.DataTableExtensions.AsEnumerable(DataTable source)
   at FluentModel.FluentModelMetadataSource.PrepareMapping() in c:\Test\TestTelerik2\FluentModel\FluentModelMetadataSource.cs:line 89
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.GetPreparedMappings()
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.PrepareMappingConfigurations()
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.CreateModel()
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.GetModelCore(MetadataContainer old)
   at Telerik.OpenAccess.Sdk.Enhancer.Enhancer.CrossDomainRunImpl(AssemblyLoader assemblyLoader)
   at Telerik.OpenAccess.Sdk.Enhancer.EnhancerBase.CrossDomainRun() C:\Test\TestTelerik2\FluentModel\obj\Debug\FluentModel.dll FluentModel

Please let me know if this is possible and help me solve this if it is.

[Yordan]

Hi Ashley,

It is possible to add/map additional classes to tables at runtime through the usage of artificial types. You have to map your artificial types in the PrepareMappings() method as shown in this article. You can see here how to use artificial types that have been added in the previously mentioned method.
The only requirement in your case is to specify the same table and column names as already specified in your backend as well as sql types if you are using anything different than the default ones. 

Please try this way of mapping types at runtime. If you have other questions regarding the usage of artificial types get back to us for help.
 
Regards,
Yordan
the Telerik team

[Ashley]

Hi Yordan,

What we have is a DataTable with the new tables that haven't been added which I was able to create through your SchemaReader API. When I try and loop through it to create the mappings we need I am getting a compiler-error.

The code I'm using in PrepareMapping() is:

var counter = 0;
string tableName = string.Empty;
MappingConfiguration config = null;
foreach (DataRow row in newDetails.Rows) {
    if (!string.IsNullOrEmpty(tableName) && tableName != row["TableName"].ToString()) {
        configurations.Add(config); //Add the previous table once we hit the new table's rows

        tableName = row["TableName"].ToString();  // Change the table name to that of the new table
    }

    // I'm only trying to add the primary key column at the moment to see if this actually works
    if (tableName != row["TableName"].ToString()) {
        var colName = row["ColumnName"].ToString(); // Assign the column name
        var assembly = assemblies[counter]; // Get the assembly I generated for this table based on the information I was able to get from the SchemaReaderAPI
        Type assemblyType = assembly.GetTypes().ToList().FirstOrDefault();
        config = new MappingConfiguration(tableName, "FluentModel"); // Create the mapping
        config.HasArtificialPrimitiveProperty(colName, assemblyType); // Add the primary key
    }
    counter++;
}

The error I'm getting is:

Error   1   Object reference not set to an instance of an object.
ExceptionString:
System.NullReferenceException: Object reference not set to an instance of an object.
   at FluentModel.FluentModelMetadataSource.PrepareMapping() in c:\Test\TestTelerik2\FluentModel\FluentModelMetadataSource.cs:line 89
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.GetPreparedMappings()
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.PrepareMappingConfigurations()
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.CreateModel()
   at Telerik.OpenAccess.Metadata.Fluent.FluentMetadataSource.GetModelCore(MetadataContainer old)
   at Telerik.OpenAccess.Sdk.Enhancer.Enhancer.CrossDomainRunImpl(AssemblyLoader assemblyLoader)
   at Telerik.OpenAccess.Sdk.Enhancer.EnhancerBase.CrossDomainRun() C:\Test\TestTelerik2\FluentModel\obj\Debug\FluentModel.dll  FluentModel

Please assist urgently with this as we need to know if this is possible or if there is any workaround for this that we can use in a hurry.

Thanks,
Ash

[Ashley]

Please also include how to save values using these new persistent types.

[Ashley]

Ok, I got the above code working by declaring the datatables as null.

The issue I have now is when I try and create an instance of the class I am getting an error on this line.

var contextInstance = context.CreateInstance("FluentModel.table2");

and the error I'm getting is:

Class 'FluentModel.table2' is marked as artificial, but neither a persistent base class was specified nor a single id field.

What am I doing wrong? This is urgent for us to know if OpenAccess is the right tool for us. Any assistance will be greatly appreciated.

Thanks,
Ash

[Ashley]

I added IsIdentity() to the end of the line where I declare the artificial type and now I get the following error:

No metadata has been registered for class 'FluentModel.table2, h5gai10b, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. (This usually indicates, that either this class is not declared persistent or it is declared persistent but not enhanced.)

How can I enhance the generated assembly at runtime? Or any other way to get this to work.

[Yordan]

Hi Ashley,

Thank you for the posted code. We analyzed it and we have two suggestions how to get things work.

1) Make sure that the project is enhanced. Basically you need to add the line 

<Import Condition="Exists('$(MSBuildExtensionsPath)\OpenAccess.targets')" Project="$(MSBuildExtensionsPath)\OpenAccess.targets" />

to your project file. More about the enhancer can be found in this article.

2) Change the line where the primary key of the artificial type is set:

config.HasArtificialPrimitiveProperty(colName, assemblyType); // Add the primary key

to the following:

config.HasArtificialPrimitiveProperty("id", typeof(int)).IsIdentity(); // Add the primary key

This have to be done because currently every artificial type has to have primary key with name "id" and with type one of the following -  System.Byte, System.Int16, System.Int32 or System.Int64.

Please apply the suggested approaches how artificial types can be used. If those do nоt help in your scenario or if there are any further questions do not hesitate to get back to us.
 
Regards,
Yordan
Telerik

[Ashley]

Hi Yordan,

I have created a new table called testTable with the following columns:
id (int)
testField (varchar(100)).

I am creating the class at runtime using:

// extraTables is a DataTable containing the table definitions for the tables I want to create at runtime
var tableNames = (from r in extraTables.AsEnumerable() select r["TableName"]).Distinct().ToList();

foreach (var tableName in tableNames) {
    var sb = new StringBuilder();

    sb.AppendLine("using System;");
    sb.AppendLine("using System.Collections.Generic;");
    sb.AppendLine("using System.Linq;");
    sb.AppendLine("using System.Text;");
    sb.AppendLine("using System.Threading.Tasks;");
    sb.AppendLine("using Telerik.OpenAccess;");

    sb.AppendLine("namespace FluentModel {");
    sb.AppendLine("[Persistent()]");
    sb.AppendLine(string.Format("public class {0} {{", tableName));

    var cols = (from r in extraTables.AsEnumerable() where r["TableName"].ToString() == tableName.ToString() select r).ToList();
    foreach (var row in cols) {
        var columnName = (string)row["ColumnName"];
        var columnType = (Type)row["Type"];
        sb.AppendLine(string.Format("public {0} {1} {{ get; set; }}", columnType.ToString(), columnName));
    }

    sb.AppendLine("}}");
    var compiler = new CSharpCodeProvider();
    var compilerParams = new System.CodeDom.Compiler.CompilerParameters();
    compilerParams.TempFiles.KeepFiles = false;
    compilerParams.GenerateInMemory = true;
    compilerParams.GenerateExecutable = false;
    compilerParams.TreatWarningsAsErrors = true;

    // Optimize the code for faster execution
    compilerParams.CompilerOptions = "/Optimize+";

    compilerParams.ReferencedAssemblies.Add(@"System.Core.dll");
    compilerParams.ReferencedAssemblies.Add(@"FluentModel.dll");
    compilerParams.ReferencedAssemblies.Add(@"C:\Program Files (x86)\Telerik\OpenAccess ORM\bin\Telerik.OpenAccess.dll");
    compilerParams.ReferencedAssemblies.Add(@"C:\Program Files (x86)\Telerik\OpenAccess ORM\bin\Telerik.OpenAccess.35.Extensions.dll");
    var result = compiler.CompileAssemblyFromSource(compilerParams, sb.ToString());
    var sbErrors = new StringBuilder();

    if (result.Errors.Count > 0) {
        foreach (CompilerError CompErr in result.Errors) {
            //Hooray a list of compile errors
            sbErrors.AppendLine(CompErr.ErrorText);
        }
    }
    else {
        assemblies.Add(result.CompiledAssembly);
    }
}

return assemblies;

I am creating the mappings for this at runtime using:

string tableName = string.Empty;
// newDetails is a DataTable containing the schema of the tables that I have had to create classes for at runtime.
foreach (DataRow row in newDetails.Rows) {
    // This check is so that only the first column is added
    if (tableName != row["TableName"].ToString()) {
        tableName = row["TableName"].ToString();
        config = new MappingConfiguration(tableName, "FluentModel");
        config.HasArtificialPrimitiveProperty("id", typeof(int)).IsIdentity();
        configurations.Add(config);
    }
}

The project is enhanced as specified but I am still getting an error at the following line:

var contextInstance = context.CreateInstance("FluentModel.testTable");

the error is:

No metadata has been registered for class 'FluentModel.testTable, tf0iw3um, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'. (This usually indicates, that either this class is not declared persistent or it is declared persistent but not enhanced.

I get the impession it has something to do with the enhancer not being able to be run on my assembly that has been generated at runtime. Please advise on this.

Thanks,
Ash

[Yordan]

Hi Ashley,

When OpenAccess ORM creates artificial types an assembly with name artificial[TimeStamp] is generated in the current directory of the executing process. In this assembly every artificial type that was created is defined in an ordinary .NET class. I wouldn't recommend you to create your own assembly dynamically with artificial types unless you have a really compelling reason to do so.

The line 

context.CreateInstance("FluentModel.testTable");

uses the assembly that was generated (and enhanced) and that contains the definitions of artificial types. You can use that assembly instead.

Please find the attached project as an example. If you have any more questions do not hesitate to contact us again.
 
Regards,
Yordan
Telerik

[Ashley]

Hi Yordan,

Thanks for the feedback. That allowed us to get what we needed working properly.

Thanks,
Ash

[Philippe]

Hi,

I've read some posts about the artificial types and tried them; it works great.
But what if what I want to add is not known at design time, but must come from a file?

The method PrepareMapping in the MetadataSource doesn't accept any arguments, how can we create/update/delete new artificial types at runtime based on what we have defined in a custom XML file ?

We are evaluating the Data Access framework to see if we can control it by feeding it external configuration definition on how to build the entities and database.

Kind regards,
Philippe

[Boris Georgiev]

Hi Erik,

I have prepared and attached a sample application which demonstrates how to create artificial mapping in runtime using xml file which contains the schema information. The sample is written in C# .Net Framework 4.5 and targeting MS SQL. In the archive you can also find an SQL script which creates a database with one table and xml mapping in the Artificials.xml file.

The method which reads from XML and create Artificial configuration is GetArtificialMappingFromXml() which you can find in the FluentMetadataSource.cs.

I hope that helps. Please let me know if you have any questions.

Regards,
Boris Georgiev
Telerik