I have 6 tables with the same structure but with different data. In each table are stored more o less one million of record.
For performance purpose i put only one year in each table. So i have my tables like this Document_2005, Document_2006, ecc... I just done the procedure for import new years and everything work fine but the aspxGridView (DevExpress) is extremely slow.
So i had to enable ServerMode and effectively the performance is so far better but i can't use anymore SQL to access the right table String.Format("Document_{0}",year); because ServerMode need LINQ TO SQL Class.
now i put all my classes inside the dbml file and everything works fine but how can i map new tables (with the same structure) in next upcoming years I have to add manually and rebuild every year? I tried to change source table name in the auto-generated class and if i change TableAttribute to a different table name and rebuild everything works fine.
How can I achieve the same result without rebuild project every time?
I try to use TypeDescriptor.AddAttributes to change the TableAttribute of the generated class but it doesn't work. I'm thinking about reflection but it seems reflection can't do anything in this case...
Yes, Linq-2-sql is code based so you would need to add them manually and redeploy. I guess that would be the case for most ORM frameworks.
You could of course add all of them for the next 10 years and be done with it.
Unless you really move to partitioning etc. this would be the best approach. Not that much of a problem I guess.