problem with a LINQ To SQL query

Question

Consider a new CMS and one of the features that we must add to the system is the option to create a custom forms with different inputs like text, select box etc.

We managed to finish this part and now we are building the result part which needs to show the result that sent through the custom form.

We have a FormsOrders table which stores the answer that sent through the custom form in this way:

• OrderID - just an id saved as GUID
• NumOrder – the address's id (every time someone fill the form I increase it by one) save as int
• ControlID – GUID of the control. Each control (like textbox, select box etc.) Have is own ControlID and it's stored in "FormsControls" table.
• Value – the value of the specific control id the address.

I have a contact form with 3 textbox field (first name, last name, message). The "FormsControls" table will look like this:

After someone submits an address, the "FormsOrders" table will look like this:

And another address will look like this:

Now, I am trying to join the "FormsControls" value and "FormsOrders" value and get something like this:

I can join them with linq using the same ControlID but than my table look like this:

It's not helping me because I am using this example, using the MvcContib Grid and Pager.

The goal is to get the

• FormsControls values as a column and
• FormsOrders as a value and

and group all the related NumOrder in one row, as shown:

We tried join by NumOrder (which only give us the key and value column – not good for us), We tried to make new datatable with the columns and rows (we manage to make the datatable but the MvcContib grid is not working with datatable).

We tried dynamic LINQ, but didn't succeed with that.

I just want to get all the address, to show each address in one row and to be able to sort them and search in those rows.