Showing many tables in many dropdown lists. c#, asp.net-mvc, linq2sql

Question

Hi, all

I want to use an example to explain what I want.

Assume I've following DB design:

Item (id, name, categoryID);
Category (id, name);

When user wants to create an Item (fill in form), I'll give a list of categories in a dropdownlist, and when user chooses one of the categories ASP.NET MVC will automatically bind categoryID, to the selected one. I need to present same dropdown list when editing the item with correct selected one.

Question: But my DB is very big, and it requires around 30-40 (maybe even more) category-like tables, that contain just "id" and "name", and all tables need to be shown in dropdown list while creating some other object, and also needs to be presented while editing the object. Definitely above schema doesn't work, because it's tedious to write same logic 100 times with just different table names. (I'm using Linq2SQL)

Currently my solution is: Make a view that's based in all such tables and in application I just call a function that construction dropdownlist from that single view. But it's still tedious to change view definition everytime I add a new table.

Do you guys think of a better solution for this tedious work, possibly using reflection or some other tecnologies.

Answer 1

It is not a problem "Definitely above schema doesn't work, because it's tedious to write same logic 100 times with just different table names."

If I were you, I will mark an addition interface on these class using "partial class" feature. Then, I will write few extension method for the partial class.

Answer 2

If anyone interested in the solution: I've used reflection to solve this problem. I use reflection over DataContext to get the Table (by string name), and get its fields and construct the optionlist.