Dynamic LINQ ITable

[EDBrian]

Problem: We enable our clients to create custom fields they wish to collect.
We want to use Dynamic LINQ to query this table (Just one).
My initial post and suggestion(s) are here: http://shrinkster.com/tzp

So I have a class called "ClientTable". All tables have some common fields,
but I need to add more at runtime.
Now I have a class that looks like:
--------------------
[Table(Name="ClientTable")]
public class ClientTable{
[Column(DbType = "VarChar(400) NOT NULL")]
public string FirstName { get; set; }
}
--------------------

Now this works great in dynamic LINQ when I do something like:
--------------------
Table<ClientTable> table = db.GetTable(typeof(ClientTable));
table.Where("FirstName == @0", "Bill");
--------------------

But if a client has a column in that table called "Age" I can't do something
like:
--------------------
table.Where("Age > @0", 30);
--------------------
I get an error saying that "Age" doesn't exist, even though it exists in the
database. If I add the one Age property it works fine (which is
understandable).
So now, how can I dynamically add properties to ClientTable class at
runtime?

Is there a way to do something like this:
--------------------
Table<ClientTable> table = db.GetTable(typeof(Facade.CreateDynamicType))) as
Table<ClientTable>;
--------------------

As I'm sure you know the problem is the typeof(Facade.CreateDynamicType)
because "CreateDynamicType" is a property and not a class.
I've tried some quick tests by making the CreateDynamicType a method that
returns a Type and other stupid ideas that just didn't work.

Can something like this be done, should I pursue a solution to my problem
this way or should I just stick to a normal method like the:
Table<ClientTable> table = db.GetTable(typeof(ClientTable));
and then try to dynamically destory and create a new "ClientTable" object at
runtime?

Can I delete a type then create a new one on the fly? Any suggestions?

 

[Marc Gravell]

Can I delete a type then create a new one on the fly? Any suggestions?

You can create new ones, but you can't substitute them for old.

As has been repeatedly stressed, most ORM tools simply don't like you
dynamically editing the database schema.

Generally, the extra columns aren't critical to the system - they are
just a few extra feilds that the client wants to store.
When I have this type of requirement, there are two solutions I tend
to use:
1: a name/value pair table hanging off the side of the main table -
but this is trickier to code against (as it involves lots of joins
etc)
2: a single composite column for the extra data

With SQL Server 2005, I'm more inclined towards the second option -
for example, I can use a single xml column in the database (perhaps
just a string in my OO model) allowing me to store all sorts of semi-
structured data, without affecting my database schema or complicating
the queries. If you are writing your own SQL you can query this xml
directly (assuming you have indexed it etc) - but most ORM tools don't
like peeking inside an xml datatype for a search. In such a scenario
you can also promote an xpath expression to a persisted, indexed
column, so that whenever you update the xml a genuine table column is
*also* updated automatically; but, by this point you could have just
added a regular column...

If you know what attributes are in the semi-structured data, it would
also be possible to use the component-model to represent the values as
dynamic property-descriptors, suitable for throwing into a
DataGridView or similar.

Because you are only talking about a simple string property in your
object model, LINQ will still be able to read and write the row (but
not search/order upon specific values unless you override the LINQ
query-provider).

In case that was vague, I might have (at the database):

CREATE TABLE CLIENT (Id {blah}, FirstName {blah}, ..., ExtendedData
xml NULL)
-- and add some indexes, including a primary xml index

with rows like
1, 'Fred', ..., '<ext shoeSize="12" favColor="blue"/>'

and in the C# world (via LINQ-to-SQL, not showing the metadata
attributes)

public class Client {
public int Id {get;set;}
public string FirstName {get;set;}
// ...
public string ExtendedData {get; set;}
}

The work to present the extended data as virtual properties (for data-
binding) is more involved; if you are interested I could knock
something together, but it would take a few moments...

Likewise, at the database layer I can provide more info on querying
inside the xml, or promoting an xml expression to a regular column -
but it would be better suited to the sqlserver.xml forum where I have
posted a few similar examples.

Marc

 

[Marc Gravell]

For info, the use of a *string* for ExtendedData is two-fold; first,
every ORM tool is going to be happy with string (but might not like
XmlDocument, XDocument, etc) - second, it minimised the footprint on
the assumption that this data is supplemental. Even if we want to
expose the data via a PropertyDescriptor - most data access is "read",
not "write" - so I'd probably use an XmlReader to obtain the value,
not the heavier XmlDocument. I guess the values could also be cached
(once read) if absolutely needed, but then you'd have to worry about
keeping the cache up to date; I wouldn't add this caching complexity
unless profiling indicates a problem.

Marc

 

[Steven Cheng[MSFT]]

Hi EDBrian,

Based on your issue description, I think the main problem here is that if
you want to use a certain column property in expression, that property need
to be statically exists in the table class(you can not dynamically add it
at runtime, at least can not do it at LINQ level).

Therefore, IMO, what you need here is dynamically define and build a
certain table class(assembly) and referece the class in it. In .net, the
way to create type/assembly dynamically is using Reflection and CodeDom
feature:

#Reflection and Dynamic Classes
http://www.osix.net/modules/article/?id=126

#Dynamic building and execution of assembly using CodeDome & Reflection
http://www.c-sharpcorner.com/UploadFile/pradeep.tiwari/DynamicBuildingandExe
cutionOfAssemblyUsingCodeDomeAndReflection08212006072200AM/DynamicBuildingan
dExecutionOfAssemblyUsingCodeDomeAndReflection.aspx

Also, here I found a web article mentioned something combine CodeDom and
Linq:

#CodeDom extensions and dynamic LINQ (string/script to LINQ emitting)
http://igorshare.wordpress.com/2008/01/11/codedom-extensions-and-dynamic-lin
q-stringscript-to-linq-emitting/

Hope this helps some.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Reply-To: "EDBrian" <[email protected]>
From: "EDBrian" <[email protected]>
Subject: Dynamic LINQ ITable
Date: Fri, 18 Jan 2008 13:55:30 -0800

Problem: We enable our clients to create custom fields they wish to collect.
We want to use Dynamic LINQ to query this table (Just one).
My initial post and suggestion(s) are here: http://shrinkster.com/tzp

So I have a class called "ClientTable". All tables have some common fields,
but I need to add more at runtime.
Now I have a class that looks like:
--------------------
[Table(Name="ClientTable")]
public class ClientTable{
[Column(DbType = "VarChar(400) NOT NULL")]
public string FirstName { get; set; }
}
--------------------

Now this works great in dynamic LINQ when I do something like:
--------------------
Table<ClientTable> table = db.GetTable(typeof(ClientTable));
table.Where("FirstName == @0", "Bill");
--------------------

But if a client has a column in that table called "Age" I can't do something
like:
--------------------
table.Where("Age > @0", 30);
--------------------
I get an error saying that "Age" doesn't exist, even though it exists in the
database. If I add the one Age property it works fine (which is
understandable).
So now, how can I dynamically add properties to ClientTable class at
runtime?

Is there a way to do something like this:
--------------------
Table<ClientTable> table = db.GetTable(typeof(Facade.CreateDynamicType))) as
Table<ClientTable>;
--------------------

As I'm sure you know the problem is the typeof(Facade.CreateDynamicType)
because "CreateDynamicType" is a property and not a class.
I've tried some quick tests by making the CreateDynamicType a method that
returns a Type and other stupid ideas that just didn't work.

Can something like this be done, should I pursue a solution to my problem
this way or should I just stick to a normal method like the:
Table<ClientTable> table = db.GetTable(typeof(ClientTable));
and then try to dynamically destory and create a new "ClientTable" object at
runtime?

Can I delete a type then create a new one on the fly? Any suggestions?