LINQ Dynamic Data Model

[EDBrian]

My problem is this. Our clients create different fields they want to
collect and we allow them build dynamic filters, reports etc...
We run some TSQL to actually create the column and all works very well. We
are now adding a lot more functionality to our filters and could really
benefit from using the LINQ to SQL.

I have experimented with the Dynamic Linq
(http://weblogs.asp.net/scottgu/arch...t-1-using-the-linq-dynamic-query-library.aspx)
and it is exactly what we need to let them create their own filters and
such. But when I try to filter on a column that does not exist in the Data
Context I get an error, which is understandable.

They have one table that they can add columns to, so I am going to attempt
to dynamically add the new properties and attributes before running the LINQ
query.

For example, if a client adds a new column called Age of type Int, I want to
add a new property to the object.
So it looks like I need to add a property that looks something like:

[Column(Storage="Age", DbType="Int NOT NULL")]
public int Age { get; set;}

I've been reading different reflection techniques and examples for the past
two days and seems like I'm going around in circles..
Does anyone have any suggestions on how I might accomplish this? Anything
to point me in the right direction would be greatly appreciated.

 

[Nicholas Paldino [.NET/C# MVP]]

I'm not sure that using LINQ here is the way to go. Because of the
dynamic nature of the entities involved (the custom fields that clients can
add to your tables), your code isn't going to have a need for the hard-typed
representations of them. You can't possibly know in advance what they are
going to be, so you can't use them early-bound in code.

Because of this, generating types (in this case, I would derive a class
from the base class with the appropriate property implementations if you
really want to go this route) doesn't give you much benefit over working
dynamically. Yes, you will get a strongly typed representation of the data,
but you can't use it anywhere, at least not in code, and not without going
through reflection.

 

[EDBrian]

Yeah, I actually just want to utilize the SQL generated. I really want to
take advantage of the SQL that is generated. I would love to do someething
like:

var query = db.CustomerDB.Where("Age == @0", age);
query = query.Where("OtherField > @0", something);

Then I can take advantage of the Skip(), Take(), OrderBy(), etc... Right now
we generate the SQL manually and it to get rid of that responsibility would
be priceless. I hope some of that makes sence...but maybe I'm thinking
about it all wrong.

Does that make sence?

 

[Marc Gravell]

For example, if a client adds a new column called Age of type Int, I want to

add a new property to the object.

The System.ComponentModel supports this type of thing (via adhoc
PropertyDescriptors), but LINQ-to-SQL does not - it is deeply tied to
the actual MemberInfo; likewise, so is NHibernate and ActiveRecord
(Andrus had a similar requirement a while ago, so I investigated).

The general concensus seems to be that this approach simply isn't
going to help you much. Even if you subclass the entities (dynamic
compilation) the LINQ-to-SQL data-cotext is still going to want to
talk about the /base/ class, so it won't work. You might want to read
the archives from Andrus's experiences with DbLinq and dynamic
compilation, but again I stress: my suspicion is that this is going to
make life harder, not easier.

Marc

 

[Nicholas Paldino [.NET/C# MVP]]

Ok, I see what you are doing. To be honest, it seems like a bit of a
roundabout way to go to get rid of SQL generation responsibilities, but this
is definitely doable. You will still have to generate the strings that get
passed to the Where/OrderBy/etc/etc methods.

I would suggest using my initial suggestion. Take your base types, and
create new derived instances of them based on the custom fields that are in
the database. Of course, you have to make sure that they have the
appropriate attributes for the mapping as well.

You are going to want to look in the System.Reflection.Emit namespace in
order to generate these types dynamically.

The good thing about this is that since they are deriving from base
types, you can cast them down to instances of your base type and use them
appropriately.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Yeah, I actually just want to utilize the SQL generated. I really want to
take advantage of the SQL that is generated. I would love to do
someething like:

var query = db.CustomerDB.Where("Age == @0", age);
query = query.Where("OtherField > @0", something);

Then I can take advantage of the Skip(), Take(), OrderBy(), etc... Right
now we generate the SQL manually and it to get rid of that responsibility
would be priceless. I hope some of that makes sence...but maybe I'm
thinking about it all wrong.

Does that make sence?

 

[EDBrian]

That is the worst news I've heard in a while....any suggestions on what
might be a good solution...

 

[Andrus]

For example, if a client adds a new column called Age of type Int, I want

The System.ComponentModel supports this type of thing (via adhoc
PropertyDescriptors), but LINQ-to-SQL does not - it is deeply tied to
the actual MemberInfo;

Dynamic Linq library referenced in link in first message has method
CreateClass().
This allows to create derived type dynamically and you can add Age property
easily.
Also you can use dynamic compilation to create wrapper assembly containing
derived type with Age property.

There is no problem. Both methods work OK.

The general concensus seems to be that this approach simply isn't
going to help you much. Even if you subclass the entities (dynamic
compilation) the LINQ-to-SQL data-cotext is still going to want to
talk about the /base/ class, so it won't work.

I have opposite experience.

You can create child entity class with Age attribute based on some sqlmetal
generated entity class.
Only requirement is to add Table property to generated child class and
ColumnMapping Attributes to added Age property.

You can use both child and base class properties to create linq queries
using MS Dynamic Linq library Where() etc methods or Mark dynamic query
extensions. Both of them will allow pass user-defined property names as
strings.

DbLinq data context can use derived class and base class properties without
any difference.

Andrus.

 

[Marc Gravell]

I have opposite experience.

I was meaning coding against a known data-context in the c#, not a
base-class - is this still the case? Or are you generating the context
at runtime too? (in which case, coding gets very hard)

But your thoughts are definitely useful here - I suspect you've spent
more time trying to get this scenario working than the rest of us
combined ;-p

Marc

 

[EDBrian]

Should I use the Reflection.Emit to create the derived type/properties?
Like I said in my original post, I've been looking into reflection and
creating a new class dynamically for a couple days now.
Do you have any suggestions or links that might help....

And thanks for the input.

 

[Frans Bouma [C# MVP]]

Should I use the Reflection.Emit to create the derived
type/properties? Like I said in my original post, I've been looking
into reflection and creating a new class dynamically for a couple
days now. Do you have any suggestions or links that might help....

And how are you going to refer to these new properties in your code,
which only appear at runtime?

Dynamic columns at runtime should be solved differently: with a couple
of related tables, one for the values and one for the fields. It is a
bit hard on the querying side, but it's what's possible as the language
used in the program is a statically typed language.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andrus]

Should I use the Reflection.Emit to create the derived type/properties?

Like I said in my original post, I've been looking into reflection and
creating a new class dynamically for a couple days now.
Do you have any suggestions or links that might help....

As I wrote there are simpler things than Reflection.Emit:

1. Dynamic Compilation to create assembly. I think I have posted some sample
earlier for Marc in this newsgroup.

2. I just discovered from you link Dynamic Linq library CreateClass()
method, thanks a lot.
Maybe this can be used, if it allows to specify base class and Attributes to
properties, any experiences ?

Sample in doc is very promising:

DynamicProperty[] props = new DynamicProperty[] {
new DynamicProperty("Name", typeof(string)),
new DynamicProperty("Birthday", typeof(DateTime)) };
Type type = DynamicExpression.CreateClass(props);
object obj = Activator.CreateInstance(type);
t.GetProperty("Name").SetValue(obj, "Albert", null);
t.GetProperty("Birthday").SetValue(obj, new DateTime(1879, 3, 14), null);
Console.WriteLine(obj);
I think we must create some class which allows this easily.

Andrus.

 

[Andrus]

I was meaning coding against a known data-context in the c#, not a

base-class - is this still the case? Or are you generating the context
at runtime too? (in which case, coding gets very hard)

I'm using context generated at design time.

I generate only child entity classes dynamically containing few properties
corresponding to columns added by users to their on site database.
Those child classes are based on the base entity classes generated at design
time.

I have also need to add tables to this Context dynamically at run time at
appl startup. Some developers in customer sites like to add their own tables
and want that my application uses them. This is similar like word can use
external data for mail merge.
I'm currently researching a way to implement this.

Andrus.

 

[Andrus]

Frans,

And how are you going to refer to these new properties in your code,
which only appear at runtime?

MS Dynamic Linq Library usage contains of samples about this. First sample
in its doc looks like:

var query =
db.Customers.
Where("City = @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("new(CompanyName as Name, Phone)");

You can replace string constans with variables determined at runtime.

Dynamic columns at runtime should be solved differently: with a couple
of related tables, one for the values and one for the fields. It is a
bit hard on the querying side, but it's what's possible as the language
used in the program is a statically typed language.

No. Never.
This is too hard to code.

Andrus.

 

[EDBrian]

Nothing yet. I can't figure out how to specify a base class...then I'm
thinking of using reflection to copy all the properties in my static class
to the new one.

Should I use the Reflection.Emit to create the derived type/properties?
Like I said in my original post, I've been looking into reflection and
creating a new class dynamically for a couple days now.
Do you have any suggestions or links that might help....

As I wrote there are simpler things than Reflection.Emit:

1. Dynamic Compilation to create assembly. I think I have posted some
sample earlier for Marc in this newsgroup.

2. I just discovered from you link Dynamic Linq library CreateClass()
method, thanks a lot.
Maybe this can be used, if it allows to specify base class and Attributes
to properties, any experiences ?

Sample in doc is very promising:

DynamicProperty[] props = new DynamicProperty[] {
new DynamicProperty("Name", typeof(string)),
new DynamicProperty("Birthday", typeof(DateTime)) };
Type type = DynamicExpression.CreateClass(props);
object obj = Activator.CreateInstance(type);
t.GetProperty("Name").SetValue(obj, "Albert", null);
t.GetProperty("Birthday").SetValue(obj, new DateTime(1879, 3, 14), null);
Console.WriteLine(obj);
I think we must create some class which allows this easily.

Andrus.

 

[Andrus]

EDBrian,

Why not to use use dynamic compilation ?

In design time create wrapper assembly used to satisfy MSBUILD.

At runtime:

1. In start of Main() delete this wrapper assembly

2. In AssemblyResolve event:
2.1 create source code cs file in code for child class, specify base class
from other assembly.
2.2 compile it to assembly
2.3 return this assembly reference in in assemblyresolve event thus
replacing original assembly.

Andrus.

 

[EDBrian]

Is this a good idea even in a web appliation?
We don't have that much traffic, I'm not worried about the possible
execution time, but if I destroy the "currnet type" then create a new type
would it effect other clients if they happen to be accessing the object?

As an experiment I created another partial class (with the same name) and
simply added:

[Column(DbType = "Int")]
public int Age { get; set; }

And I can successfully query my database using the "Age" field. I am trying
to figure out how to add a property to a class dynamically. I am out of
element so it's taking a very long time.
I did find this post:
http://blog.devstone.com/aaron/archive/2005/10/24/1323.aspx
But I think it confused me even more. Can I use the
TypeBuilder/PropertyBuilder on an already existing type or should I go back
to the drawing board?

I even stepped through the System.Linq.Dynamic.ExpressionParser and found
this on line 1336:
MemberInfo[] members = t.FindMembers(MemberTypes.Property |
MemberTypes.Field, flags, Type.FilterNameIgnoreCase, memberName);

So now I'm thinking that maybe I can add another method to accomplish
this...something like:

if(members.length == 0){
//Look for a specific property or method that I can add "Custom Fields"
to..
}

But that is a reach....

 

[Andrus]

Is this a good idea even in a web appliation?

Yes, this is a good idea.

We don't have that much traffic, I'm not worried about the possible
execution time, but if I destroy the "currnet type" then create a new type
would it effect other clients if they happen to be accessing the object?

You base type and base properties reside is assembly which is not destoryed.
You create wrapper assembly contianing initially no properties.
You delete this wrapper assembly and replace it with new wrapper assembly
containing additional properties defined by web user at run time.

As an experiment I created another partial class (with the same name) and
simply added:

[Column(DbType = "Int")]
public int Age { get; set; }

..NET does not support partial assemblies.
Partial classes require the full source code of whole class to build
assembly.
It is not reasonable to distribute source code with application.
So using partial classes is not possible.

And I can successfully query my database using the "Age" field. I am
trying to figure out how to add a property to a class dynamically. I am
out of element so it's taking a very long time.
I did find this post:
http://blog.devstone.com/aaron/archive/2005/10/24/1323.aspx
But I think it confused me even more. Can I use the
TypeBuilder/PropertyBuilder on an already existing type or should I go
back to the drawing board?

As Marc wrote, Linq does not support properties created using
TypeDescriptor.
So this is not possible.
You can try dynamic compiling example I posted is this newsgroup some time
ago.
If you encounter issues with this I can help you since I use this example
based approach.

I even stepped through the System.Linq.Dynamic.ExpressionParser and found
this on line 1336:
MemberInfo[] members = t.FindMembers(MemberTypes.Property |
MemberTypes.Field, flags, Type.FilterNameIgnoreCase, memberName);

So now I'm thinking that maybe I can add another method to accomplish
this...something like:

if(members.length == 0){
//Look for a specific property or method that I can add "Custom Fields"
to..
}

You can add property by implementing custom TypeDescriptor.
Unfortunately Linq current implementation is not capable to use properties
defined through TypeDesciptor interfase.
Linq current implementation it uses Reflection GetProperties() directly.

Your property would be used only for consumers which use TypeDescriptor
interface like DataGridView or data binding.

Only way is to use dynamic compiling or Reflection.Emit to create new type
in new assembly.

Andrus.

 

[EDBrian]

I'm sold...with your suggestion of:

You base type and base properties reside is assembly which is not
destoryed.
You create wrapper assembly contianing initially no properties.
You delete this wrapper assembly and replace it with new wrapper assembly
containing additional properties defined by web user at run time.

I can't find your post from the past...do you happen to have a link to it?
I'm stepping out of my comfort zone here and would love to see an
example/explination to point me in the right direction.

Any pointers or good reads you can help me with?

 

[Andrus]

You base type and base properties reside is assembly which is not

I can't find your post from the past...do you happen to have a link to it?
I'm stepping out of my comfort zone here and would love to see an
example/explination to point me in the right direction.

Any pointers or good reads you can help me with?

I can send vs2008 solution to you by e-mail.

Andrus.

 

[EDBrian]

I'd appreciate it...

how about...prizeloop at yahoo . com