Best practices to create different projections from database for use with C#

So, my problem is that I have a bunch of interrelated tables in my database. Now, according to what the user wants, I need to create different projections from them.

Lets say that I have 10 tables and need 6 different projections. These projections are directly used as a DataSource of a view component I use. Basically, so far I have figured out two ways to accomplish this.

1) I create a DataSet with the designer that contains all the different tables that I need. Then I create the projections with Linq2DataSet and hand the results to the view component.

2) I create a DataSet that contains 6 custom TableAdapters, one for each projection, and then fill datatables with them and hand the tables to the view component.

For solution 1, it's obviously more flexible and I can more easily create new projections from it. However, if the amount of data rises high, this could become a problem.

For solution 2, it is lighter but very hard to make any changes to. I am not expecting that many changes any time soon, but flexibility is always nice.

So, which of these solutions would be better, and are there other, better solutions available as well?

EDIT: Adding some context

So I figured I'd give an example of the type of data I'm working with. Basically, I have a table that contains resul开发者_如何学Pythonts. A result has a type, and it can have one or more relations. These are defined as, for example, truckID, routeID or countryID.

Now, depending on the type of the result, I need to fetch either one or more of the relations. Examples of these could be a result type of truck average speed, which could have relations to all three, meaning I would like to join all the aforementioned tables. Another example could be a result type of number of truck deliveries, which could have relations to routes and countries. The un-used relations are always 0 or null.

Also, these cases are further divided to scenarios. This means that I should be able to change the queries that fetch the data to be displayed to account for only the given scenario.

So my problem is, how to read all this data and use it appropriately. I suppose the SQL views could be one option. I'm quite new to database access things, so any help is appreciated.

the first one is not appropriate as the projection is being done at client side using LINQ. the second one can be used but you are still populating 6 tables. as i understand from your post you can live with only one as there is only one dataview. But my suggestion is if your client is sending you the id of the kind of projection it is expecting, you can use the projection on the database side by defining 6 views or may be dynamic query. use horizontal filtering for columns that are required and vertical filtering by using the where clause.

i will go with the views as the queries are externalized to DB thus easier to change as compared to embedded queres in code.