locked
LINQ to SQL: Dynamic SQL table name

    Question

  • Usually, the SQL table name in LINQ to SQL is statically provided by declaring the TableAttribute on an entity class.
    In my application, I want the table name to be dynamic: While admins use the normal table, if a non-admin user uses the same class, I want LINQ to use a view instead of a table. The view only contains the records which the user is allowed to see.

    How do I achieve this?

    An alternative to a "dynamic" table name would be to automatically insert certain Where predicates whenever a query is made to the tables in question. This of course would also have to include associated tables, not only the one actually queried. But I think the approach of the "dynamic" table names would be easier and more secure than this.

    Thanks for any help
    Urs
    Friday, December 28, 2007 9:07 AM

Answers

  • Yeah you're right, this function was missing. Here is the code:
            private static Dictionary<string, Type> _SearchType_Cache = new Dictionary<string, Type>();
    
            /// <summary>
            /// searches for type 'typename' in all loaded assemblies.
            /// </summary>
            /// <param name="typename"></param>
            /// <returns></returns>
            public static Type SearchType(string typename, bool doNotSearchSystemAssemblies)
            {
                Type type;
                if (_SearchType_Cache.TryGetValue(typename, out type)) return type;
    
                foreach (Assembly assy in AppDomain.CurrentDomain.GetAssemblies())
                {
                    if (doNotSearchSystemAssemblies)
                    {
                        if (assy.GetName().Name.StartsWith("System", StringComparison.OrdinalIgnoreCase) ||
                            assy.GetName().Name.StartsWith("Microsoft", StringComparison.OrdinalIgnoreCase) ||
                            assy.GetName().Name.StartsWith("mscorlib", StringComparison.OrdinalIgnoreCase))
                            continue;
                    }
    
                    foreach (Type t in assy.GetTypes())
                    {
                        if (t.FullName.Equals(typename, StringComparison.OrdinalIgnoreCase) || t.Name.Equals(typename, StringComparison.OrdinalIgnoreCase))
                        {
                            _SearchType_Cache[typename] = t;
                            return t;
                        }
                    }
                }
    
                throw new TypeLoadException("didn't find type '{0}' in loaded assemblies.".FormatSecure(typename));
            }
    • Proposed as answer by Urs Meili Saturday, March 13, 2010 4:48 PM
    • Marked as answer by Urs_Eichmann Saturday, March 13, 2010 4:50 PM
    Saturday, March 13, 2010 4:11 PM
  • Hi all,
    this is a working solution for this problem, based on the code of Jimmy (and also on some Reflector decompiled code...)

    Note that it creates instances of AttributedMetaTable which is a non-public type, so the code might not work with .NET Versions past 3.5 SP1.

    public class CustomMetaModel : MetaModel
        {
            private static CustomAttributeMapping _mapping = new CustomAttributeMapping();
    
            public CustomMetaModel(MetaModel org)
            {
                _orgmodel = org;
            }
    
            private MetaModel _orgmodel;
    
            public override Type ContextType
            {
                get { return _orgmodel.ContextType; }
            }
    
            public override string DatabaseName
            {
                get { return _orgmodel.DatabaseName; }
            }
    
            public override MetaFunction GetFunction(MethodInfo method)
            {
                return _orgmodel.GetFunction(method);
            }
    
            public override IEnumerable<MetaFunction> GetFunctions()
            {
                return _orgmodel.GetFunctions();
            }
    
            public override MetaType GetMetaType(Type type)
            {
                return _orgmodel.GetMetaType(type);
                //return new CustomMetaType(_orgmodel.GetMetaType(type), _orgmodel);
            }
    
            public override Type ProviderType
            {
                get { return _orgmodel.ProviderType; }
            }
    
            private Dictionary<Type, MetaTable> _MetaTables = new Dictionary<Type, MetaTable>();
    
            private Type _AttributedMetaTableType;
    
            public Type AttributedMetaTableType
            {
                get
                {
                    if (_AttributedMetaTableType == null)
                    {
                        lock (_MetaTables)
                        {
                            if (_AttributedMetaTableType == null)
                            {
                                _AttributedMetaTableType = TypeUtils.SearchType("System.Data.Linq.Mapping.AttributedMetaTable", false);
                            }
                        }
                    }
                    return _AttributedMetaTableType;
                }
            }
    
            private static Type GetRoot(Type derivedType)
            {
                while ((derivedType != null) && (derivedType != typeof(object)))
                {
                    TableAttribute[] customAttributes = (TableAttribute[])derivedType.GetCustomAttributes(typeof(TableAttribute), false);
                    if (customAttributes.Length > 0)
                    {
                        return derivedType;
                    }
                    derivedType = derivedType.BaseType;
                }
                return null;
            }
    
    
    
            public TableAttribute CustomizeTableAttribute(Type key, TableAttribute ta)
            {
    -- TODO RETURN A NEW, CUSTOMIZED TABLE ATTRIBUTE HERE
            }
    
    
            public override MetaTable GetTable([NotNull] Type rowType)
            {
                if (rowType == null) throw new ArgumentNullException("rowType");
                MetaTable tableNoLocks;
    
                if (!this._MetaTables.TryGetValue(rowType, out tableNoLocks))
                {
                    lock (_MetaTables)
                    {
                        if (!this._MetaTables.TryGetValue(rowType, out tableNoLocks))
                        {
                            Type key = GetRoot(rowType) ?? rowType;
                            TableAttribute[] customAttributes = (TableAttribute[])key.GetCustomAttributes(typeof(TableAttribute), true);
                            if (customAttributes.Length == 0)
                            {
                                this._MetaTables.Add(rowType, null);
                            }
                            else
                            {
                                var tableAttr = CustomizeTableAttribute(key, customAttributes[0]);
                                tableNoLocks = (MetaTable)Activator.CreateInstance(AttributedMetaTableType, BindingFlags.NonPublic | BindingFlags.Instance, null, new object[] { _orgmodel, tableAttr, key }, System.Threading.Thread.CurrentThread.CurrentCulture);
                                foreach (MetaType type2 in tableNoLocks.RowType.InheritanceTypes)
                                {
                                    this._MetaTables.Add(type2.Type, tableNoLocks);
                                }
                            }
                            if (tableNoLocks.RowType.GetInheritanceType(rowType) == null)
                            {
                                this._MetaTables.Add(rowType, null);
                                tableNoLocks = null;
                            }
                        }
                    }
                }
    
    
                return tableNoLocks;
            }
    
    
    
            public override IEnumerable<MetaTable> GetTables()
            {
                List<MetaTable> tables = null;
                lock (_MetaTables)
                {
                    tables = this._MetaTables.Where(t => t.Value != null).Select(t => t.Value).Distinct().ToList();
                }
    
                return tables;
            }
    
    
            public override MappingSource MappingSource
            {
                get { return _mapping; }
            }
    
        }
    
    public class CustomAttributeMapping : MappingSource
        {
            AttributeMappingSource mapping = new AttributeMappingSource();
    
    
            protected override MetaModel CreateModel(Type dataContextType)
            {
                MetaModel oldmodel = mapping.GetModel(dataContextType);
                CustomMetaModel newmodel = new CustomMetaModel(oldmodel);
                return newmodel;
            }
    
        }

    • Marked as answer by Urs_Eichmann Friday, March 05, 2010 1:58 PM
    Friday, March 05, 2010 1:58 PM

All replies

  • To horizontally partition data with LINQ, I have found using Table Defined Functions a good alternative to views. In it, you pass the userID to the function and the function determines how to partition the values. The advantage here is that you the functions are IQueryable and you can join on functions just as you would tables or views. You can also deny SELECT on the table and allow it on the view to enforce security for all users (including Admins). The downside of functions is you have to manage updates manually rather than relying on LINQ to manage the CrUD for you.

     

    Alternatively, if you use XML mapping rather than attribute mapping, you should be able to update the XML on the fly and the new version will be picked up when you instantiate the DataContext. This option is not as good in a web environment, but should be fine for smart client (WPF/Winform) apps.

     

    Jim Wooley

    http://LinqInAction.net

     

    Friday, December 28, 2007 5:37 PM
  • Hi,

     

    Initally, I thought of using something like Reflection to dynamically call GetTable(). By doing something like:

     

    Code Block

    Type queryObject = Type.GetType("LinqPOC.Customer");

    Type genericClassType = typeof(Table<>).MakeGenericType(queryObject);

    Object genericClassDynamic = Activator.CreateInstance(genericClassType);

     

     

     

    However, this only leaves you with an Object when we actually wanted Table.  So, I don't think this is possible.

     

    Even something like

    Code Block
    IEnumerable e = dc.ExecuteQuery(queryObject, "SELECT * FROM CUSTOMERS", null);

     

     

    Still cannot be queried via Linq to SQL.

     

    I think because Linq to SQL are so dependent on IEnumerable<T> you will have problems doing it fully dynamic. 

     

    Would be interesting if the team had any ideas on this.  Sorry I can't be of more help.

     

    Ben

    Friday, December 28, 2007 5:56 PM
  • The naive solution is pretty good in many cases:

     

    If there are only 2-5 tables, and their names are known at design time,

    add them all...

    write queries for them all..

    write your conditional logic to choose which query gets run.

     

     

    Typical LINQ mappings will be accomplished by the TableAttribute

    This cannot be modified at runtime (but it can be over-ridden, see next solution).

     

    Another mapping possibility is External File Mapping

    I think this is modifiable at runtime, but I don't see how to attach this mapping file to a datacontext at this point.  Maybe someone else knows (or I'll find out later, when I have access to my 2008 tools).

    This would be a -very- good solution in the case when each user is using their own installation of your application.

     

     

    Another way to go, is to overload the tablename on the SQL Server side.  Just declare a table in one schema, and the views in different schemas... all with the same name.  When the code is executed as different database users, they will query different database objects... with the same name. Here's a link.

    Friday, December 28, 2007 6:31 PM
  • External file mapping sounds like a good solution.

     

    You can dynamically set the mappings using the MappingSource overload of the constructor for DataContext

    http://msdn2.microsoft.com/en-us/library/bb534562.aspx

    Friday, December 28, 2007 6:41 PM
  • One could get an xml mapping source from one of the many "From*" methods of XmlMappingSource...

    http://msdn2.microsoft.com/en-us/library/system.data.linq.mapping.xmlmappingsource_members.aspx

     

    Then, one could assign it in the way ben2004uk suggests.

     

     

    Other than a short but complete demo, I guess this is pretty much wrapped up.

    Oh, here's one.  http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/27/25294.aspx

     

    Friday, December 28, 2007 11:58 PM
  • thanks for all the suggestions. however, I cannot use XML mapping scheme since all our architecture is based on using the LINQ mapping attributes as [Table] and [Column]. It is a sad thing that [Table] is so unflexible, they could have borrowed a more flexible way from, for example, [TypeConverter], where you can specify the type of a class which does the conversions at runtime. How nice it would be if I could specify a class in [Table] which retrieves the actual table name at runtime (BTW the same would be true for the other LINQ mapping attributes).

    Saturday, December 29, 2007 10:59 AM
  • This would be a great opportunity for someone to implement a new MappingSource (http://msdn2.microsoft.com/en-us/library/bb534532.aspx) with matching MetaModel classes, whose purpose would be to consume an existing MappingSource, retrieve and present a modified MetaModel (copy + rewrite).  I imagine you could pass along delegates to govern the rewrite...

     

    Code Block

    var ruleSet = new RemappingRuleSet

        (

            (MetaTable table) => table.TableName == "FooTable" ? new { TableName = "FooTable_new" } : null,

            (MetaTable table) => table.RowType.Type == typeof(Bar) ? GetReTypedTable(table) : null,

            (MetaDataMember member) => new { UpdateCheck = UpdateCheck.Always }

            etc

        );

     

    var remappingSource = new RemappingSource(ruleSet);

     

    var db = new FooDataContext(remappingSource);

     

     

    The rules could be specified as functions which take a meta element and return an object (eg, an anonymously-typed object) from which new values are copied based on matching name and type.  In the above, I used null to specify no-change.

     

    You'd need to be clever about relationships between the various metas, but something like this wouldn't be too nasty, I imagine.

     

    An approach like this (assuming it worked) would avoid tying into any particular mapping source, since it instead does all its work based on the MetaModel retrieved.

    Friday, January 18, 2008 9:45 AM
  • I am struggling with a similar problem as Urs_Eichmann's original post.  I would LOVE to be able to be able to use LINQ to dynamically assign a Table at run time.  My scenario is slightly different.  I have a query to an initial table that returns a table name.  I would then like to query the second, just discovered, table.  The column names are the same in these tables, so what I really need is the dynamic portion to be the table name.  This way I can alter the initial table to include more tables as need be, without altering the program itself.  I have a friend who tackled the same concept (change only the database) by utilizing stored procedures a lot.  However, if one wants to ALSO use the SQL compact addition, that will not work, as CE does not let you use stored procedures.

     

    Any insights appreciated.  I am just wetting my toes on LINQ and it has been 15 years since I played around with SQL so I am still in ramp up mode.

    Thursday, February 07, 2008 7:37 AM
  •  stanislavf wrote:

    I am struggling with a similar problem as Urs_Eichmann's original post.  I would LOVE to be able to be able to use LINQ to dynamically assign a Table at run time.  My scenario is slightly different.  I have a query to an initial table that returns a table name.  I would then like to query the second, just discovered, table.  The column names are the same in these tables, so what I really need is the dynamic portion to be the table name.  This way I can alter the initial table to include more tables as need be, without altering the program itself.  I have a friend who tackled the same concept (change only the database) by utilizing stored procedures a lot.  However, if one wants to ALSO use the SQL compact addition, that will not work, as CE does not let you use stored procedures.

     

    Any insights appreciated.  I am just wetting my toes on LINQ and it has been 15 years since I played around with SQL so I am still in ramp up mode.

     

    Here's a class I created to handle my reference data. It uses the dynamic framework to query and update, but instead of storing the table name I store the class name of each ref table in the table that contains reftable defs. You can change it to look up the class name from table name from the data context using the mapping namespace.

     

     

    Code Snippet

    using System;
    using System.Linq;
    using System.Linq.Dynamic;
    using System.Collections.Generic;
    using System.Collections;
    using System.Linq.Expressions;
    using System.Data.Linq.Mapping;

    public static class ReferenceData
    {
        private static List<ReferenceTable> _referenceData = new List<ReferenceTable>();

        public static IEnumerable GetReferenceData(string tableCode)
        {
            return GetReferenceData(tableCode, null, null);
        }

        public static IEnumerable GetReferenceData(string tableCode, string filter, params object[] pval)
        {
            //load list of reftables if not already loaded
            LoadReftables();

            //get table info
            ReferenceTable rt = _referenceData.Single(rtb => rtb.Code == tableCode);
            Type dataClassType = Type.GetType(rt.ClassName);

            //get data context
            using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC())
            {
                //query the table
                if (filter != null)
                {
                    return dc.GetTable(dataClassType).
                           Where(filter, pval).
                           OrderBy("SortSeq").
                           Select("it").ToList();
                }
                else
                {
                    return dc.GetTable(dataClassType).
                           OrderBy("SortSeq").
                           Select("it").ToList();
                }
            }
        }

        public static object GetSingleReferenceData(string tableCode, string filter, params object[] pval)
        {
            //load list of reftables if not already loaded
            LoadReftables();

            //get table info
            ReferenceTable rt = _referenceData.Single(rtb => rtb.Code == tableCode);
            Type dataClassType = Type.GetType(rt.ClassName);

            //get data context
            using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC())
            {
                //query the table
                if (filter != null)
                {
                    return dc.GetTable(dataClassType).
                           Where(filter, pval).
                           OrderBy("SortSeq").
                           Select("it").ToList()[0];
                }
                else
                {
                    return dc.GetTable(dataClassType).
                           OrderBy("SortSeq").
                           Select("it").ToList()[0];
                }
            }
        }

        public static object CreateRefdataObject(string tableCode)
        {
            //load list of reftables if not already loaded
            LoadReftables();

            //get table info
            ReferenceTable rt = _referenceData.Single(rtb => rtb.Code == tableCode);
            Type dataClassType = Type.GetType(rt.ClassName);

            //create an instance of the refdata class
            return Activator.CreateInstance(dataClassType);
        }

        public static ReferenceTable GetRefTableInfo(string tableCode)
        {
            //load list of reftables if not already loaded
            LoadReftables();

            //get table info
            return _referenceData.Single(rtb => rtb.Code == tableCode);
        }

        public static System.Collections.ObjectModel.ReadOnlyCollection<MetaDataMember> GetRefdataMembers(string tableCode)
        {
            //load list of reftables if not already loaded
            LoadReftables();

            //get table info
            ReferenceTable rt = _referenceData.Single(rtb => rtb.Code == tableCode);
            Type dataClassType = Type.GetType(rt.ClassName);

            using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC())
            {
                MetaType metaType = dc.Mapping.GetTable(dataClassType).RowType;

                return metaType.DataMembers;
            }
        }

        private static void LoadReftables()
        {
            lock (_referenceData)
            {
                if (_referenceData.Count == 0)
                {
                    //get data context
                    using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC())
                    {
                        //load reftable info
                        IEnumerable<ReferenceTable> rts = from rd in dc.ReferenceTables orderby rd.SortSeq select rd;
                        _referenceData.AddRange(rts.ToList());
                    }
                }
            }
        }

        public static void InsertMember(object data)
        {
            using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC())
            {
                dc.GetTable(data.GetType()).InsertOnSubmit(data);
                dc.SubmitChanges();
            }
        }

        public static void DeleteMember(object data)
        {
            using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC())
            {
                dc.GetTable(data.GetType()).Attach(data);
                dc.GetTable(data.GetType()).DeleteOnSubmit(data);
                dc.SubmitChanges();
            }
        }

        public static void UpdateMember(object data)
        {
            using (HuagatiDataContext dc = HuagatiDataContext.GetHuagatiDC(false))
            {
                string whereClause = null;
                object[] pkValues = dc.GetPKMembers(data, out whereClause);
                object existingEntity = dc.GetTable(data.GetType()).Where(whereClause, pkValues).Select("it").ToList()[0];
                dc.CopyDataMembers(data, existingEntity);
                dc.SubmitChanges();
            }
        }
    }

     

     

    Thursday, February 07, 2008 1:49 PM
  • Thanks Kris, I will have to digest this to see if it will work for me (as I said, a bit of a ramp up mode here), but I suspected that I would have to do something like this rather than depend on dynamic queries or something built in.

     

    SKF

    Thursday, February 07, 2008 5:28 PM
  • How do I modify the value of TableAttribute?

     

    I can do the following:

     

    TableAttribute tattr = tableType.GetCustomAttribute(typeof(TableAttribute));

    tattr.name = "newtable";

     

    But how do I make the datacontext.GetTable<tableType>() use the tattr I created above? 

     

    Tuesday, June 03, 2008 7:14 PM
  •  

    Bump
    Tuesday, September 02, 2008 1:06 PM
  • My solution to changing the table name in attribute at run-time is to create a new custom MappingSource wrapper to wrap the original one, and a custom MetaModel to wrap the AttributedMetaModel, and a custom MetaTable to wrap the origianl MetaTable.  Since Linq to SQL use MetaModel to get the mapping information, that MetaModel is read-only, if wrap up with new one, then you can change the table name at run-time.

     

     create the mapping source, return the custom MetaModel wrapper

    Code Snippet

    public class CustomAttributeMapping : MappingSource
        {
            AttributeMappingSource mapping = new AttributeMappingSource();


            protected override MetaModel CreateModel(Type dataContextType)
            {
                MetaModel oldmodel = mapping.GetModel(dataContextType);
                CustomMetaModel newmodel = new CustomMetaModel(oldmodel);
                return newmodel;
            }

        }

     

     

    Create a custom MetaModel to wrap the orginal one, change the GetTable method to fetch custom MetaTable

     

    Code Snippet

    public class CustomMetaModel : MetaModel
        {
            private static CustomAttributeMapping _mapping = new CustomAttributeMapping();

            public CustomMetaModel(MetaModel org)
            {
                _orgmodel = org;
            }

            private MetaModel _orgmodel;

            public override Type ContextType
            {
                get { return _orgmodel.ContextType; }
            }

            // the other code is similar, and being cutted

     

           public override MetaTable GetTable(Type rowType)
            {

                 return new CustomMetaTable(_orgmodel.GetTable(rowType) );

            }

            public override IEnumerable GetTables()
            {
                    List newtable = new List();
                    foreach (MetaTable table in _orgmodel.GetTables())
                    {
                           newtable.Add( new  CustomMetaTable(table) );
                      }
                    return newtable;
            }

     

            // link back to the custom mapping source

            public override MappingSource MappingSource
            {
                get { return _mapping; }
            }

       }

     

     

    wrap the custom metatable, and change the table name at the properties TableName, at run time

     

    Code Snippet

    public class CustomMetaTable : MetaTable
        {
            private MetaTable _orgtable;

            private MetaModel _model;

            public CustomMetaTable(MetaTable table, MetaModel model)
            {
                _orgtable = table;

               _model = model
            }

            public override MethodInfo DeleteMethod
            {
                get { return _orgtable.DeleteMethod; }
            }

     

           // the other code is similar, and being cutted

     

            public override MetaModel Model
            {
                get { return _model; }
            }

     

           // real thing is change the Table Name here at runtime, using your rules or something to 

           // create the new table name

            public override string TableName
            {
                get
                {
                    string oldname = _orgtable.TableName;

     

                   // change the table name here

     

                    string newname = oldname;

                    return newname;

                 }
            }

        }

     

     

    change the MappingSource at DataContext when construct it

     

    Code Snippet

    string conStr = "...";

    CustomDataContext context = new CustomDataContext(conStr, new CustomAttributeMapping() );

     

     

    you can change the mapping info at runtime, so change the attribute setting.

     

     

    Thursday, December 11, 2008 3:09 AM
  • Jimmy, it not enough to override MetaTable class. LinqToSql will not use TableName property before you override also MetaType class (you should override property Model in this class and just make proxy calls of another classes like in classes before). And RowType property of MetaTable overriden class should return instance of a new overriden MetaType class.
    Monday, September 14, 2009 5:16 PM
  • Hi all,
    this is a working solution for this problem, based on the code of Jimmy (and also on some Reflector decompiled code...)

    Note that it creates instances of AttributedMetaTable which is a non-public type, so the code might not work with .NET Versions past 3.5 SP1.

    public class CustomMetaModel : MetaModel
        {
            private static CustomAttributeMapping _mapping = new CustomAttributeMapping();
    
            public CustomMetaModel(MetaModel org)
            {
                _orgmodel = org;
            }
    
            private MetaModel _orgmodel;
    
            public override Type ContextType
            {
                get { return _orgmodel.ContextType; }
            }
    
            public override string DatabaseName
            {
                get { return _orgmodel.DatabaseName; }
            }
    
            public override MetaFunction GetFunction(MethodInfo method)
            {
                return _orgmodel.GetFunction(method);
            }
    
            public override IEnumerable<MetaFunction> GetFunctions()
            {
                return _orgmodel.GetFunctions();
            }
    
            public override MetaType GetMetaType(Type type)
            {
                return _orgmodel.GetMetaType(type);
                //return new CustomMetaType(_orgmodel.GetMetaType(type), _orgmodel);
            }
    
            public override Type ProviderType
            {
                get { return _orgmodel.ProviderType; }
            }
    
            private Dictionary<Type, MetaTable> _MetaTables = new Dictionary<Type, MetaTable>();
    
            private Type _AttributedMetaTableType;
    
            public Type AttributedMetaTableType
            {
                get
                {
                    if (_AttributedMetaTableType == null)
                    {
                        lock (_MetaTables)
                        {
                            if (_AttributedMetaTableType == null)
                            {
                                _AttributedMetaTableType = TypeUtils.SearchType("System.Data.Linq.Mapping.AttributedMetaTable", false);
                            }
                        }
                    }
                    return _AttributedMetaTableType;
                }
            }
    
            private static Type GetRoot(Type derivedType)
            {
                while ((derivedType != null) && (derivedType != typeof(object)))
                {
                    TableAttribute[] customAttributes = (TableAttribute[])derivedType.GetCustomAttributes(typeof(TableAttribute), false);
                    if (customAttributes.Length > 0)
                    {
                        return derivedType;
                    }
                    derivedType = derivedType.BaseType;
                }
                return null;
            }
    
    
    
            public TableAttribute CustomizeTableAttribute(Type key, TableAttribute ta)
            {
    -- TODO RETURN A NEW, CUSTOMIZED TABLE ATTRIBUTE HERE
            }
    
    
            public override MetaTable GetTable([NotNull] Type rowType)
            {
                if (rowType == null) throw new ArgumentNullException("rowType");
                MetaTable tableNoLocks;
    
                if (!this._MetaTables.TryGetValue(rowType, out tableNoLocks))
                {
                    lock (_MetaTables)
                    {
                        if (!this._MetaTables.TryGetValue(rowType, out tableNoLocks))
                        {
                            Type key = GetRoot(rowType) ?? rowType;
                            TableAttribute[] customAttributes = (TableAttribute[])key.GetCustomAttributes(typeof(TableAttribute), true);
                            if (customAttributes.Length == 0)
                            {
                                this._MetaTables.Add(rowType, null);
                            }
                            else
                            {
                                var tableAttr = CustomizeTableAttribute(key, customAttributes[0]);
                                tableNoLocks = (MetaTable)Activator.CreateInstance(AttributedMetaTableType, BindingFlags.NonPublic | BindingFlags.Instance, null, new object[] { _orgmodel, tableAttr, key }, System.Threading.Thread.CurrentThread.CurrentCulture);
                                foreach (MetaType type2 in tableNoLocks.RowType.InheritanceTypes)
                                {
                                    this._MetaTables.Add(type2.Type, tableNoLocks);
                                }
                            }
                            if (tableNoLocks.RowType.GetInheritanceType(rowType) == null)
                            {
                                this._MetaTables.Add(rowType, null);
                                tableNoLocks = null;
                            }
                        }
                    }
                }
    
    
                return tableNoLocks;
            }
    
    
    
            public override IEnumerable<MetaTable> GetTables()
            {
                List<MetaTable> tables = null;
                lock (_MetaTables)
                {
                    tables = this._MetaTables.Where(t => t.Value != null).Select(t => t.Value).Distinct().ToList();
                }
    
                return tables;
            }
    
    
            public override MappingSource MappingSource
            {
                get { return _mapping; }
            }
    
        }
    
    public class CustomAttributeMapping : MappingSource
        {
            AttributeMappingSource mapping = new AttributeMappingSource();
    
    
            protected override MetaModel CreateModel(Type dataContextType)
            {
                MetaModel oldmodel = mapping.GetModel(dataContextType);
                CustomMetaModel newmodel = new CustomMetaModel(oldmodel);
                return newmodel;
            }
    
        }

    • Marked as answer by Urs_Eichmann Friday, March 05, 2010 1:58 PM
    Friday, March 05, 2010 1:58 PM
  • Yeah that looks great.  
    But what is this:  
    = TypeUtils.SearchType("System.Data.Linq.Mapping.AttributedMetaTable", false);
    supposed to be?

    Is that some sort of non-standard library or is that a user defined that you didn't include. 
    Either way this code is unworkable as-is and probably shouldn't have been marked an answer to the question, even though
    it LOOKS like it might be able to solve the problem...
    Thursday, March 11, 2010 9:20 PM
  • Yeah you're right, this function was missing. Here is the code:
            private static Dictionary<string, Type> _SearchType_Cache = new Dictionary<string, Type>();
    
            /// <summary>
            /// searches for type 'typename' in all loaded assemblies.
            /// </summary>
            /// <param name="typename"></param>
            /// <returns></returns>
            public static Type SearchType(string typename, bool doNotSearchSystemAssemblies)
            {
                Type type;
                if (_SearchType_Cache.TryGetValue(typename, out type)) return type;
    
                foreach (Assembly assy in AppDomain.CurrentDomain.GetAssemblies())
                {
                    if (doNotSearchSystemAssemblies)
                    {
                        if (assy.GetName().Name.StartsWith("System", StringComparison.OrdinalIgnoreCase) ||
                            assy.GetName().Name.StartsWith("Microsoft", StringComparison.OrdinalIgnoreCase) ||
                            assy.GetName().Name.StartsWith("mscorlib", StringComparison.OrdinalIgnoreCase))
                            continue;
                    }
    
                    foreach (Type t in assy.GetTypes())
                    {
                        if (t.FullName.Equals(typename, StringComparison.OrdinalIgnoreCase) || t.Name.Equals(typename, StringComparison.OrdinalIgnoreCase))
                        {
                            _SearchType_Cache[typename] = t;
                            return t;
                        }
                    }
                }
    
                throw new TypeLoadException("didn't find type '{0}' in loaded assemblies.".FormatSecure(typename));
            }
    • Proposed as answer by Urs Meili Saturday, March 13, 2010 4:48 PM
    • Marked as answer by Urs_Eichmann Saturday, March 13, 2010 4:50 PM
    Saturday, March 13, 2010 4:11 PM
  • Hi everyone,

     

    Interested only in being able to change the database schema at runtime, rather than the table name and this seems to offer a solution. One big concern I have though relates to compiled queries. Am I right to assume that using the above dynamic mapping the runtime performance achieved through compiled queries is lost?

    Thanks,

    Florin

    Thursday, June 10, 2010 3:52 PM
  • Hi Florin,

    I cannot tell for sure, since I don't use compiled queries in this context. But chances are high that it won't work together well with compiled queries.

    Urs

     

    Friday, June 11, 2010 11:57 AM
  • I have an interesting solution. It gets you a ITable object mapped to the table you want. The good thing about it, you can do all CRUD on it.

    http://www.codeproject.com/Articles/333249/Dynamic-Table-Mapping-for-Linq-to-SQL

    • Proposed as answer by Zimin Max Yang Wednesday, February 22, 2012 1:08 AM
    Wednesday, February 22, 2012 1:08 AM