Use linqtosql to load dynamic Columns

Requirement

Recently, I have a small demand for exploration: How can I dynamically Add a column to a database?

For example, we have a type news

news    public class News    {        public int Id { get; set; }        public string Title { get; set; }        public string Detail { get; set; }    }

What if the customer says they want to add another attribute called expireat?

Solution

Think about the following methods for database schema:

1. A schema like SharePoint separates the table definition from rows. It is indeed flexible, but the data is less readable and complex.
2. Create an extendedproperties column for the news table to store key-value pairs. This is simple but bad design.
3. Create a Sharepoint schema table and a values table when the news table remains unchanged. This table is used to store the schema and value of the extended attribute.
4. Create a new column expireat on the news table

This article introduces solution 4.

Technology Selection

1. As for solution 4, there are many technical options, from the perspective of ORM tools.
2. You don't need an Orm. Of course you can...
3. You can use the datacontext. gettable (type) method to obtain the itable object. The type can be determined at runtime, so it meets the requirements.
4. Nhib.pdf. You can use dynamic-component to modify XXXX. HBM. xml only when the domain model remains unchanged.

Of course, a dictionary must be added to the news definition to store "dynamic-component"

The problem with this solution is that you want to make the modified XXXX. HBM. XML (serialized or new conform can be used) must be re-built with sessionfactory (as far as I know). I don't know how to do this without affecting the existing sessions.

1. Others, not familiar... Maybe the code first mode of Entity Framework is OK?

Ideas

The original plan was to dynamically create a newsxxxxx inherited from news, add a property expireat, and add columnattribute Based on news that has added the attribute such as table and column. However, it is found that this inheritance is not supported by LINQ to SQL. Therefore, we plan to change it to only define the news of Poco. We need to make a message of wrap when passing in the gettable, and add the attribute such as table and column. When a user creates a new property, the user inherits the news of the new poco from the news, and then re-wrap. In the end, my implementation is roughly as follows.

news    public class News    {        public virtual int Id { get; set; }        public virtual string Title { get; set; }        public virtual string Detail { get; set; }    }    public class XXXNews : News    {        public virtual DateTime? ExprieAt { get; set; }    }    [Table(Name = "News")]    public class WrappedNews : News    {        private News entity;        public WrappedNews()        {            entity = new News();        }        [Column]        public override int Id        {            get { return entity.Id; }            set { entity.Id = value; }        }        [Column]        public override string Title        {            get { return entity.Title; }            set { entity.Title = value; }        }        [Column]        public override string Detail        {            get { return entity.Detail; }            set { entity.Detail = value; }        }    }    [Table(Name = "News")]    public class WrappedXXXNews : XXXNews    {        private XXXNews entity;        public WrappedXXXNews()        {            entity = new XXXNews();        }        [Column]        public override int Id        {            get { return entity.Id; }            set { entity.Id = value; }        }        [Column]        public override string Title        {            get { return entity.Title; }            set { entity.Title = value; }        }        [Column]        public override string Detail        {            get { return entity.Detail; }            set { entity.Detail = value; }        }        [Column]        public override DateTime? ExprieAt        {            get { return entity.ExprieAt; }            set { entity.ExprieAt = value; }        }    }

Of course, the above is just a diagram. The last three classes do not actually exist in my code. They are all created at runtime, and their names are also random. You can understand what they mean...

Note that all attributes in the original news class are virtual, because I want to inherit them at runtime, which is not as nice as nhib.pdf... Fortunately, in the code at the upper layer, I only use iNews instead of news, which will be discussed later.

Model Design

Direct

In order to shield implementation details between different Orm, I need the upper layer to see only interfaces that cannot see the implementation. When new is required, go to ientityfactory.

Here, icontententity is the interface to be implemented by all entity classes that can have extended attributes, such as iNews in this example. When the upper layer accesses its property, it can call its indexer, regardless of whether the property is a real property in the class or from the dictionary (such as nhib.pdf ).

Iproperty is a property that can be created by users. In this example, after a property is created successfully, an added event is triggered, and the listener inherits xxxnews and wrappedxxxnews.

Iview controls the properties displayed on the interface. In my implementation, http: // site/news/only displays the three properties defined in iNews (excluding indexer ), however, if you access http: // site/news/views/viewid, The viewid determines which properties are displayed.

Ilistview further provides the filter capability. In fact, the filter should be used as another separate interface...

 

Technical details

Implementation of entityfactory

The Code is as follows:

entity factory    public class EntityFactory : IEntityFactory    {        public static IDictionary<Type, Func<IEntity>> TypeInitializers { get; private set; }        static EntityFactory()        {            TypeInitializers = new Dictionary<Type, Func<IEntity>>();        }        public IEntity Create(Type entityType)        {            return TypeInitializers[entityType].Invoke();        }    }

 

Each entity (except contententity) registers its own constructor in its own type initializer, for example

 

type initialier        static Property()        {            EntityFactory.TypeInitializers.Add(typeof(IProperty<TEntity>), () => new Property<TEntity>());        }

 

However, the content entity is registered after wrap, because the type in Wrap contains the table and column attributes required by the Orm. The Code is as follows:

 

wrap        private Type Wrap()        {            var wrapper = new ContentEntityWrapper<TEntity>            {                EntityPropertyRepository = EntityPropertyRepository            };            var wrappedType = wrapper.Wrap(CurrentType);            EntityFactory.TypeInitializers[typeof(TEntity)] = () => Activator.CreateInstance(wrappedType) as IEntity;            return wrappedType;        }

 

 

Dynamic type generation

In this example, two types of dynamic generation are used.

News-> xxxnews

News-> wrappednews this process uses codedom

One thing to note about the former is:

Since xxxnews needs to be further packaged as wrappedxxxnews, The emit Assembly needs to be saved to the hard disk. Where does it exist? I used the default value (environment. currentdirectory), but when xxxnews-> wrappedxxxnews, it is reported that the Assembly from emit cannot be found (this exception is good for concealment... It took me a long time ...). So I tried to save it to the bin directory. However, changes to the bin directory will cause the re-Compilation of ASP. NET. For more information, see bin2... I also know that this is a very good solution... Set in Web. config

<probing privatePath="bin;bin2"/>

You can.

With regard to the latter, I used expressions to codedom, And the use process was not satisfactory... To recommend a better codedom class library.

 

Controller dependency Injection

Liu Dong (spring.net guru) in the garden has already introduced the use of spring.net 1.3.1 to inject controller.

I will discuss two questions here

1. It seems that we still need to rely on spring. Core. Is it estimated that Liu Dong has added a lot of this Assembly to GAC?
2. I still don't know how to inject to global. asax. Currently, this method is ugly.

Contextregistry. getcontext (). GetObject ("someobj"), and can only be used after application_start

Next steps

In this example, there are many outstanding issues, such

Lambdaexpressionbinder is not complete, so the lambda expression entered by the user cannot be converted to func when creating a list view. I believe that codedom can also be used, but I am too lazy to do it.

You are not authorized to enter expireat...

When wrap content entity type is used, the corresponding property should be read, and the corresponding column parameter should be added...

There are also many basic aspects, such as newsproperty and newslistview, which are not mapped to the database at all ...... I'm too lazy to get it...

And so on.

How lazy are you! Hello!

However, I am not lazy in some places.

For example, I don't know how spring. Net's controllerfactory is implemented (take a look at it later). I have to write the following three controllers and specify the generic controller in the configuration file. Try mvc3 directly later.

NewsPropertyController

NewsListViewController

NewsController

 

Although there are many shortcomings in this example, I will not update it again (this is not a pitfall...

It is actually a technical learning thing, and it is very unlikely to be applied in practice (for example, we will not use LINQ to SQL now ). I have learned a lot in this process, which is enough for me.

Code download and Declaration

This example uses spring. Net to implement IOC and Singleton.

This example uses the expressions to codedom to implement codedom.

In this example, the typebinder is basically copied from the implementation in pro ASP. net mvc 2 Framework 2nd edition.

Before running this example, please bring your own database structure as follows (just a table)

Modify the connection string in Web. config.

Press F5 to display the news list first

Then, enter news properties.

Then add the expireat we need

After the save operation is successful, the system enters news again. The list will not change. If you try to enter view, you will see expireat.

 

Finally, download this example.