Every Call To Load Method Add (Merge) Returned Entities To (With) Previous Record Sets

[hamiddd1980]

Hello.

In EF CORE , after calling Load method on a DbSet twice (or more) with Different conditions using WHERE clause and then use Local and ToObservableCollection respectively, the Results of first query (with its defined WHERE Condition) is Added to results of second query .
In the other words , the second result set doesn't satisfy it's WHERE condition and contains merged results of first query and second query.

With mentioned conditions , the only solution to to use LOAD , LOCAL and ToObservableCollection with different WHERE clauses (for example FILTERINF data) is Disposing and Recreating of DbContext class.

It is noteworthy ,this behavior of LOAD method is different from ToList method .every time we call ToList method on DbSet , the previous Result sets are cleared and a new Result sets according to defined condition is created.

I want to know is it possible to change the function of LOAD method so that it fills the DbContext based on defined condition and doesn't merge different Result sets?(I mean every call to LOAD clears internal used LIST without need to Dispose and Recreation of DbContext-a behavior like ToList method).
Thank You.

Steps to reproduce

1-Define an Entity (here Country Class with NAME property)
2-Define a DbContext Derived Class (here CountryDbContext ) with Countries of Type DbSet
3-Instansiate our CountryDbContext in Window Constructor
4-Define 2 methods to retrieve Records with different conditions using WHERE clause(for example one that get countries start with F(France) and the other one load countries that start with E(like England)
5-call above defined methods.
6-results of second method (countries start with E) contains France and England.

Let suppose we have a DbContext class named CountryDbContext with a DbSet which represents different Countries:
1-Defining our Country Class

public class Country
{
            public string Name{get ; set;}
}

2-Defining a DbContext class:

public class CountryDbContext : DbContext
    {
       public DbSet<Country> Countries { get; set; }
    } 

3-Creating CountryDbContext instance and populate it with sample data (for example in window Loaded event):

CountryDbContext =new CountryDbContext ();
CountryDbContext.Countries.Add(new Country {Name="France"}; // **Starts With F**
CountryDbContext.Countries.Add(new Country {Name="England"}; //**Starts With E**
CountryDbContext.SaveChanges();

4-a method to retrieve Countries that start with "F"

public void GetCountriesStartWithF()
{
                CountryDbContext .Countries
                .Where(c => c.Name.StartsWith("F"))
                .Load();

                 ObservableCollection<Country> countriesStartWith_F   =CountryDbContext .Countries.Local.ToObservableCollection();
}

5-a method to retrieve Countries that start with "E"

public void GetCountriesStartWithE()
{
                CountryDbContext .Countries
                .Where(c => c.Name.StartsWith("E"))
                .Load();

                 ObservableCollection<Country> countriesStartWith_E   =CountryDbContext .Countries.Local.ToObservableCollection();
}

6-now call above methos separately:
GetCountriesStartWithF():
Result = "France"

GetCountriesStartWithE()
Result= "France" and "England"

Further technical details

EF Core version: 5.0.0-preview.2.20120.8
Database provider: (Microsoft.EntityFrameworkCore.Sqlite)
Target framework: (.NET Core 3.0)
Operating system: Windows !0
IDE: Visual Studio 2019 16.6 Preview 2

[ajcvickers]

@hamiddd1980 The purpose of Load is to start tracking the entities returned by the query. Once Load has completed, then the context will be tracking the returned entities. Using Load again will add more entities to those tracked by the context. (ToList on a tracking query will do the same thing, even though the entities returned by the specific query are also returned as the List generated by ToList.)

In general, if there are two different units-of-work going on, which seems likely from your description, then a different context instance should be used for both.

[hamiddd1980]

Hello.I was very thankful you are considering my problem.
My aim is to retrieve Data from SQL Server based on **user input as a Search Phrase in WPF (Desktop App).
Would you please give me advises about the best way to retrieve data based on User Input from Database server in Desktop Apps which use DbContext in Connected Scenario (opposite to ASP.NET apps in Disconnected mode).
Simply Say , how can I RESET LOCAL in DbContext?
Let say we have a Textbox named textBox_Filter that we use its Text as a Filter to retrieve data from SQL Server in each KeyUp Event:(so it happens continually)

private void textBox_Filter_KeyUp(object sender, KeyEventArgs e)
        {   //Key up Event
            string filterString = textBox_Filter.Text;  //get Textbox Text as our Search Phrase
            using (CountryDbContext countryDbContext = new CountryDbContext())
            {
                countryDbContext.Countries
                .Where(c => c.Name.StartsWith(filterString))
                .Load();//filter in Databse based on Country Name

                countries = countryDbContext.Countries.Local.ToObservableCollection();//countries is Bound To a ListBox in WPF
            }
        }

My questions are :
1- Is it possible to Reset LOCAL for each query? and if Yes, how can I do that?
2-ِDoes The Continual Process of Disposing and Creating of DbContext Class (above Code) has Overload? and is it sensible (logical) to do so? (Create DbContext Instance -> Send a New Query To Database -> Disposing the DbContext Instance in each key up).

3-If the mentioned code is not sensible , What's the best solution (Pattern) you suggest to Load and Track data from Db Server according to search phrase entered by user in Desktop apps:
3-1)We don't want to load all data from database
3-2)We want to retrieve data from Sever based on a search phrase entered by user repeatedly and Continually?(filtering data according to the User Input by Database not on client Side.)
3-3)It is essential that the DbContext class Tracks the returned records because they are bound to our Views (in MVVM pattern)(DbContext in Connected mode)
Thank You Very Much.

[ajcvickers]

@hamiddd1980 Typically it would be unusual to filter by continually loading a new filtered collection from the database every time the filter string changes. Instead, it's more typical in this kind of dynamic scenario to load the data into the client and then filter there--for example, see the documentation on collection views.

Beyond this, we are looking into patterns and guidance for data binding--this is tracked by #19588. Some of this guidance will be similar to the docs we have for data binding with EF6, since EF Core behaves in a similar manner.