General

Re: Collection re-query the last query result

howcool — Sat, 24 Oct 2009 02:19:49 GMT

Thanks for your help. It works as expected.

What I want accomplish to hit for the database again when the criteria for conditions was met, that's why I re-query the database under certain circumstances. If in standalone application, it was not a problem. When it involves concurrent users using the application, this would be an issue for us since some users may see other user changes result and some didn't since it was loaded before user make changes or adding new records. Althrough it was an expensive call to hit the database, but it reduces the issues for incident.

Re: Collection re-query the last query result

David.Parsons — Fri, 23 Oct 2009 15:04:23 GMT

In addition to using coll.Query.Load rather than coll.LoadAll as Mike pointed out, I think you need to test the checked/unchecked status of a radio button inside your CheckChanged events.

The user checks radiobutton 1 and that event fires and loads the query.
The user then checks radiobutton 2 and that event fires. But, the user action also un-checks radiobutton 1 so that event fires as well because it changed. You need to know what it changed to before you load.

Code:

private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
    if (radioButton1.Checked)
    {
        CustomerCollection col = new CustomerCollection();
        col.Query.Where(col.Query.LastName == "A");
        col.Query.Load();
    }
}

I have an additional question, though. You have already loaded all columns for all rows in your Form1_Load event. Why hit the database again as the user filters data with the RadioButtons? Can't you filter the already in-memory collection instead?

Code:

private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
    if (radioButton1.Checked)
    {
        col.filter = "[LastName] = 'A'";
    }
}

private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
    if (radioButton2.Checked)
    {
        col.filter = "[LastName] = 'B'";
    }
}

etc.

Re: Collection re-query the last query result

howcool — Fri, 23 Oct 2009 15:00:55 GMT

I even try out the code, but it still send 2 queries at SQL Profiler. 1st query show Last query string and send to SQL server, and 2nd query is the query result i want to achieve and retrieve the data that I want. Any idea how to prevent 1st query send to the SQL server? You can even try out the scenario as I illustrate before, then using SQL profiler to trace it.

Re: Collection re-query the last query result

Scott.Schecter — Fri, 23 Oct 2009 11:32:00 GMT

LastQuery is for viewing the emitted SQL from a dynamic query, not loading it.

Re: Collection re-query the last query result

Mike.Griffin — Fri, 23 Oct 2009 11:27:43 GMT

The code is incorrect, you're not even executing the queries ...

This

Code:

private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
    CustomerCollection col = new CustomerCollection();
    col.Query.Where(col.Query.LastName == "A");
    col.LoadAll();
}

Should be (notice the way we load it ...)

Code:

private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
    CustomerCollection col = new CustomerCollection();
    col.Query.Where(col.Query.LastName == "A");
    col.Query.Load();
}

Collection re-query the last query result

howcool — Fri, 23 Oct 2009 11:06:26 GMT

I have a group box with a few radioboxes. My SQL query will be based on radiobox checked status. I have found that the collection will send last query to SQL server once before new query execute it.

For example, in form load event and radiobox checked event

Code:

private void Form1_Load(object sender, EventArgs e)
{
            CustomerCollection col = new CustomerCollection();
            col.LoadAll();
}

private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
            CustomerCollection col = new CustomerCollection();
            col.Query.Where(col.Query.LastName == "A");
            col.LoadAll();
}

private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
            CustomerCollection col = new CustomerCollection();
            col.Query.Where(col.Query.LastName == "B");
            col.LoadAll();
}

private void radioButton3_CheckedChanged(object sender, EventArgs e)
{
            CustomerCollection col = new CustomerCollection();
            col.Query.Where(col.Query.LastName == "C");
            col.LoadAll();
}

When form executed, it will run the following SQL at SQL Profiler

Code:
SELECT * FROM Customer

When I checked radiobox2, it will execute 2 query at SQL Profiler

Code:
Query 1

SELECT * FROM Customer

Query 2

SELECT * FROM Customer Where LastName='B'

When I try to checked radiobox3, it will also send last query to SQL Profiler

Code:
Query 1
SELECT * FROM Customer Where LastName='B'

Query 2
SELECT * FROM Customer Where LastName='C'

I feel very curious on it. Althrough last query will not populate data into the collection, and
it will populate data using new query result into collection, but is it this situation is normal
or any issue to the ES?

Version: 2009.2.1012.0