CODEWITHSUNDEEP
c#.netlinqlinq-to-sqlorm
p.s.w.g
p.s.w.g

Reputation: 149010

Custom Linq-to-SQL MappingSource

I have a project which mixes compiled entity framework domain with dynamicly defined external databases. Users can supply the connection string, table name, and column names at run time to access an external system. I had previously been using dynamically generated SQL to access the external system (of course crafted with care to avoid SQL injection), and read the interested tables into DataTable's and materialized these into POCO's.

Now, 90% of my views used IQueryable objects to inspect the structure of DB queries and format / page / manipulate the views efficiently. This allowed me to do a fair amount of work with the query before executing it. For views which involve records from external systems, I just gritted my teeth and used IEnumerable.AsQueryable().

I'd very much like to use a different solution. I'm leaning toward Linq-to-SQL (I'm open to other suggestions), but I'm stuck at creating a dynamic MappingSource. Also, some columns are optional, and I don't know if Linq-to-SQL will blow up if I don't provide a mapping for every column, or not.

public class ExternalMappingSource : MappingSource
{
    public string[] KeyColumns { get; set; }

    public string NameColumn { get; set; }

    public string DescriptionColumn { get; set; }

    public string TimestampColumn { get; set; }

    protected override MetaModel CreateModel(Type dataContextType)
    {
        // ???
    }
}

I haven't been able to find any good resources on this online. How do I flush out this class?

Upvotes: 4

Views: 1753

Answers (1)

Jacob Proffitt
Jacob Proffitt

Reputation: 12768

If you know the relevant structure up front (table and field values), you can feed in a custom mapping source based on that data without having the object beforehand. For me, for a different reason, I ended up using the XmlMappingSource object because it has a .FromXml() method you can use to feed in arbitrary Xml. You can get a feel for the structure of the Xml needed if you check out the mapping file produced by sqlmetal:

  • open the VS Command Prompt (it’s in a Visual Studio Tools folder in the start menu).
  • Change the directory to your project.
  • Enter the command “sqlmetal /map:whatever.map /code yourlinqtosql.dbml”. This generates a whatever.map file with the formatted Xml.

Alter as needed programmatically to feed into the XmlMappingSource.FromXml() method. Then, you can create your context with the relevant connection string and the altered mapping source.

Upvotes: 4

Related Questions