Answered by:

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.

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.

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.

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).

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...

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.

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.

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.

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.

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.

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.

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?