LINQ and dynamic data models
One of the applications I currently work in supports a dynamic data model. That is, we allow users in the field to add custom fields to tables and our app will hapily add these fields to maintenance forms, reports, custom filters, etc...
This was implemented before my time and in my opinion it turned out to be a bad idea for many reasons I don't want to get into now. Regardless, has anybody come up with stategies for supporting and dynamic data model using LINQ?
[522 byte] By [
dctc42] at [2008-1-10]
Great question.
How would you intend to use it? i.e. make it possible to program against specific user created fields? Write code that attempts to "duck type" at expected fields? Can you give us an example?
It sounds like you need to use Dynamic LINQ. If you look at the Visual Studio Samples with Orcas Beta2, there is a folder called CSharpSamples, within that a folder called LinqSamples, and within that a folder called DynamicQuery. Good luck! Suppose one of our users is a jewelry store. Our software does not have a field for “Anniversary date” in the customers table out of the box but we allow you to add one.
At runtime, when you launch the customer maintenance form, our app will detect this custom field and create an edit box control for it based on the underlying field’s data type. The control is added to a “Custom” tab in the maintenance form where users can edit this field.
Additionally, when you go the customers report, our app will let you define a filter condition that includes the “Anniversary date” field.
In essence, after adding this custom field, our client can generate a mailing list for customers with upcoming anniversaries.
LINQ looks very promising but we’d like to retain a way of working against dynamic table structures.
Got it.
If you were looking to, say, duck type against an anticipated field, or something like that (i.e. you have an object that uses LINQ in a DAL) - that would be one thing.
Given that is not what you are doing, I would use the DynamicQuery referenced above, and give that a shot.
This is a 10 year old app. We're just now making plans to port certain modules to .NET
I would love to be able to have a standart Customer class and expand on it. Something like the following:
public class CustomColumn
{
string ColumnName;
Type ColumnType;
// etc..
}
public class Customer
{
int CustomerId;
string Name;
// etc... and
// something like
List<CustomColumn> CustomColumns;
}
CustomColumn objects would be added to the list after peeking at the table's schema.
Is this class structure compatible with LINQ?
Hi
As far as I know, this would be a hard task, but it is possible if the database structure is also concepted for dynamic data models.
Manuel Bauer
I think you have in minimum 3 alternatives:
1. Add some collection of non-name custom columns and use them in given customer context.
For example
Add columns: custom1 ... customN of type string
For customer 1 custom1 column could be used to store translated version of description to polish
and for customer 2 the same column could be used to store translated version of description to english.
Somewhere in configuration file or database You should also keep some useful properties of this column:
- Description
- HeaderText (text displayed in UI when column is binded to it)
...
2. Use ADO.NET (DataTable, DataSet) to read/update tables with custom columns. There you are free to read columns by the name.
You could also use code generation in runtime, but because you add ability to customer to add columns I don't think You can use this.
If you want to use LINQ to SQL with a dynamic model (dynamic at runtime) you are going to have to dynamically generate entity types using Reflection.Emit and dynamically add mapping attributes to them. LINQ to SQL will always constrain the data is asks for in a query to the columns known to be mapped at runtime and will only return them in mapped fields of an existing CLR object.
