OK, I have given up banging my head on the wall trying to figure out the "best way" to move forward and would like some input from the masses. I apologize if the following is not clear as I have no idea what solution I am looking for, only the end product. Also please forgive my ignorance as I am not a VB.Net pro and know very little about Entity Framework. OK - time for the details:

I have a "search" WinForm which contains 1 DataGridView (results of a query) and 14 combo-boxes that are used to select search criteria. 8 of these are from values in the returned query and 5 are Yes/No.

My current code runs an SQL query (posted below) to pull all the data I need for the DGV from the database, then runs 14 additional queries to fill each combo-box from various tables in the database. Each time one of the combo-boxes changes, I run the same query to fill the DGV. I also re-load each combo-box with values that are only available in the DGV - this again is 14 different queries using the part of the search query and parameters. Needless to say, this is NOT the most efficient way to do things, but it works.

Thank to a slow running application, and jmcilhinney, I have decided to try the route of Entity Framework to try and improve performance. In this process I changed the search query to a stored procedure on the SQL Server. With using Entity Framework and the Stored Procedure I can pull all the data I need for the DGV. Awesome! However, I am running into issues trying to find a better way to fill all the combo-boxes with values that are ONLY available in the search results.

I have tried iterating through the DGV and adding all the values to a List item, but there is an issue with that. The search query, as you can see, is a 2-part query. The inner query returns 16 columns and non-distinct rows (several columns are unique and are needed for search criteria.) The outer query returns 13 columns and distinct rows that I want returned for the DGV. Since my Stored Procedure is not returning all the columns I need to fill all the combo-boxes, it makes it more difficult filling each with unique values.

The next thing I tried was using DataSets, DataTables and DataViews to somehow filter the results as the search criteria changed. Again, this presents the same issue - the returned results do not contain everything I need for all combo-boxes. I could not find a solution to filling a DataSet and then running a "query" against that to return a sub-set of data into a DataTable or DataView.

So this lead me to thinking that maybe I should perform the search query as TWO different queries - one to fill the all the combo-boxes and one to fill the DGV. But why query the database twice? I should be able to query the database once and use those results to do everything I need to - filling combo-boxes, DataSets, DataTables and/or DataViews. Correct?

This is where you, the masses, come in. I need ideas for how to use Entity Framework to: 1) perform the full query to return everything I need. 2) Fill DataSets, DataTables and/or DataViews to be used throughout the application instead of performing queries over and over again. 3) Fill the combo-boxes with unique values that are ONLY available in the returned search results. (this is to prevent users from selecting items that are NOT available, and help cascade their choices.)

Any and all help would be much appreciated!

Search box to show Combo-Boxes and their DataSource property:
Name: SearchForm.jpg Views: 440 Size: 18.5 KB

Current SQL Query/Stored Procedure:
Search Query.txt

Current code for initial load of combo-boxes:
Combobox Load.txt

Current code for RE-LOAD of combo-boxes:
Combobox Re-Load.txt