ISO Ideas for Filling DGV & Comboboxes from Stored Procedure / Entity Framework

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:
Attachment 169931

Current SQL Query/Stored Procedure:
Attachment 169933

Current code for initial load of combo-boxes:
Attachment 169937

Current code for RE-LOAD of combo-boxes:
Attachment 169939

Hello,

Here are a couple of tips

Learn about AsNoTracking for read operations, no updating. You will need to have the following import System.Data.Entity. In some cases turning lazy loading off can assist e.g. query a customer with child tables (Eagerly loading) with lazy loading on loads the child tables, setting it to off does not load them. Couple that with using the Include method which can take the string name of a table or via drilling down into the model/context. Also look at Load extension method.

Quote:

---

Originally Posted by kareninstructor

Hello,

Here are a couple of tips

Learn about AsNoTracking for read operations, no updating. You will need to have the following import System.Data.Entity. In some cases turning lazy loading off can assist e.g. query a customer with child tables (Eagerly loading) with lazy loading on loads the child tables, setting it to off does not load them. Couple that with using the Include method which can take the string name of a table or via drilling down into the model/context. Also look at Load extension method.

---

Karen - would you be able to give an example, or two, of using these that is pertinent to my code? .AsNoTracking() does not work with my EF query to the stored procedure (see code below.)
I also have NO idea how to implement .Include(). I read through the link and I can't even begin to comprehend how I'm going to use it. YES - I need to do more reading on Entity Framework as it is new to me within the last week. I am trying to implement it as quick as possible while learning it.

Current EF query that does not allow .AsNoTracking()

Code:

---

Dim Results =
                (dbACL.DemogSearch(
                IIf(IsNothing(Me.cboxLastName.SelectedValue), DirectCast(Nothing, String), Me.cboxLastName.SelectedValue),
                IIf(IsNothing(Me.cboxFirstName.SelectedValue), DirectCast(Nothing, String), Me.cboxFirstName.SelectedValue),
                IIf(IsNothing(Me.cboxPhysician.SelectedValue), DirectCast(Nothing, Integer?), Me.cboxPhysician.SelectedValue),
                IIf(IsNothing(Me.cboxReasonForCall.SelectedValue), DirectCast(Nothing, Integer?), Me.cboxReasonForCall.SelectedValue),
                IIf(IsNothing(Me.cboxDOB.SelectedValue), DirectCast(Nothing, Date?), Me.cboxDOB.SelectedValue),
                IIf(IsNothing(Me.cboxDiseaseGroup.SelectedValue), DirectCast(Nothing, Integer?), Me.cboxDiseaseGroup.SelectedValue),
                IIf(IsNothing(Me.cboxDiseaseType.SelectedValue), DirectCast(Nothing, Integer?), Me.cboxDiseaseType.SelectedValue),
                IIf(IsNothing(Me.cboxSTComplete.SelectedValue), DirectCast(Nothing, Boolean?), Me.cboxSTComplete.SelectedValue),
                IIf(IsNothing(Me.cboxLTComplete.SelectedValue), DirectCast(Nothing, Boolean?), Me.cboxLTComplete.SelectedValue),
                IIf(IsNothing(Me.cboxLTPastDue.SelectedValue), DirectCast(Nothing, Boolean?), Me.cboxLTPastDue.SelectedValue),
                IIf(IsNothing(Me.cboxAssay.SelectedValue), DirectCast(Nothing, Integer?), Me.cboxAssay.SelectedValue),
                IIf(IsNothing(Me.cboxGCName.SelectedValue), DirectCast(Nothing, String), Me.cboxGCName.SelectedValue),
                IIf(IsNothing(Me.cboxIntlPatient.SelectedValue), DirectCast(Nothing, Boolean?), Me.cboxIntlPatient.SelectedValue),
                IIf(IsNothing(Me.cboxMayoPatient.SelectedValue), DirectCast(Nothing, Boolean?), Me.cboxMayoPatient.SelectedValue))
                ).ToList()

---

Hello,

Here is another word of advise, unless there is a compelling reason to use stored procedures then don't. Other than enterprise applications that are dealing with mass amount of data I don't subscribe to SP's.

The following examples are against the database in the following link
https://github.com/karenpayneoregon/...er/datascripts

Normally I would not have EF code in a form but instead a class project.

In button1, customers entity has Contact, ContactType and Country entities, LazyLoadingEnabled = False means none of the child entities will be loaded but we then use Include to include Country Entity, Contact and ContactType are not loaded. Button2 is kind of counter productive (and may be confusing) as what I'm doing with Include can be circumvented with removing this line context.Configuration.ProxyCreationEnabled = False.

NOTE Include in Button1 is the recommend way to go while Button2 is not within this context, what the Include in Button2 is great for is iterating properties of an entity checking for changes (which are store in OriginalValue and CurrentValue).

Code:

---

Imports System.Data.Entity

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) _
        Handles Button1.Click

        Using context As New NorthWindContext
            context.Configuration.LazyLoadingEnabled = False
            Dim customers = context.
                    Customers.
                    Include(Function(cust) cust.Country).
                    ToList()
        End Using
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) _
        Handles Button2.Click

        Dim customerList As New List(Of Customer)()
        Dim customerIdentifiers = Enumerable.Range(1, 10).ToArray()

        Using context As New NorthWindContext
            context.Configuration.ProxyCreationEnabled = False
            context.Configuration.LazyLoadingEnabled = True

            customerList = context.
                Customers.AsNoTracking().
                Include(Function(cust) cust.Contact).
                Include("Country").
                Include("ContactType").
                Where(Function(cust) customerIdentifiers.
                        Contains(cust.CustomerIdentifier)).ToList()
        End Using

    End Sub
End Class

---

I have nothing to show with stored procedures with EF. When doing Stored procedures is outside the realm of EF.