How does linq actually execute the code to retrieve data from the data source?

Question

I will start working on xamarin shortly and will be transferring a lot of code from android studio's java to c#.

In java I am using a custom classes which are given arguments conditions etc, convert them to SQL statements and then loads the results to the objects in the project's model

What I am unsure of is wether linq is a better option for filtering such data.

For example what would happen currently is somethng along these lines

List<Customer> customers = (new CustomerDAO()).get_all()

Or if I have a condition

List<Customer> customers = (new CustomerDAO()).get(new Condition(CustomerDAO.Code, equals, "code1")

Now let us assume I have transferred the classes to c# and I wish to do somethng similar to the second case.

So I will probably write something along the lines of:

var customers = from customer 
    in (new CustomerDAO()).get_all() 
    where customer.code.equals("code1")
    select customer

I know that the query will only be executed when I actually try to access customers, but if I have multiple accesses to customers ( let us say that I use 4 foreach loops later on) will the get_all method be called 4 times? or are the results stored at the first execution?

Also is it more efficient (time wise because memory wise it is probably not) to just keep the get_all() method and use linq to filter the results? Or use my existing setup which in effect executes

Select * from Customers where code = 'code1'

And loads the results to an object?

Thanks in advance for any help you can provide

Edit: yes I do know there is sqlite.net which pretty much does what my daos do but probably better, and at some point I will probably convert all my objects to use it, I just need to know for the sake of knowing

Answer 1

if I have multiple accesses to customers ( let us say that I use 4 foreach loops later on) will the get_all method be called 4 times? or are the results stored at the first execution?

Each time you enumerate the enumerator (using foreach in your example), the query will re-execute, unless you store the materialized result somewhere. For example, if on the first query you'd do:

var customerSource = new CustomerDAO();
List<Customer> customerSource.Where(customer => customer.Code.Equals("code1")).ToList();

Then now you'll be working with an in-memory List<Customer> without executing the query over again.

On the contrary, if each time you'd do:

var filteredCustomers = customerSource.Where(customer => customer.Code.Equals("code1"))
foreach (var customer in filteredCustomers)
{
    // Do stuff
}

Then for each enumeration you'll be exeucting the said query over again.

Also is it more efficient (time wise because memory wise it is probably not) to just keep the get_all() method and use linq to filter the results? Or use my existing setup which in effect executes

That really depends on your use-case. Lets imagine you were using LINQ to EF, and the customer table has a million rows, do you really want to be bringing all of them in-memory and only then filtering them out to use a subset of data? It would usually be better to full filtered query.