Is There any diference from getting values from Object or doing another query?

I am use nhibernate with fluent nhibernate.

I wondering if there is any difference between these 2 ways.

1. Do a query to get an collection of objects from the db(say collection of companies).
2. Now I need to get another table that products references(say products).

Option 1

 var companies = session.Query<Companies>().ToList();
companies.Where(x => x.products.Id == 1).toList();

So I am filtering on result object. I would be doing something with all the companies but later on I need to filter it down some more to do some other stuff

Option 2.

do another nhibera开发者_StackOverflow社区nte query all together.

 var companies = session.Query<Companies>().ToList();
 var products = session.Query<Companies>().Where(x => x.products == 1).ToList();

I am guessing if I fetch/eager load everything then there would be a difference(in performance,number of queries and etc).

But how about if I am lazy loading?

I assume that you want the second query to filter the Companies that have a Product with Id == 1. Then your queries should actually look like this:

Option 1:

var companies = session.Query<Companies>().ToList();
var companiesWithSpecificProductId = 
    companies.Where(x => x.Products.Any(p => p.Id == 1)).ToList();

Option 2:

var companies = session.Query<Companies>().ToList();
var companiesWithSpecificProductId = 
    session.Query<Companies>().Where(x => x.Products.Any(p => p.Id == 1)).ToList();

In the case of lazy loading, option 1 will result in a N+1 problem, because NHibernate will have to query the IList<Product> Products for each Invoice in the list. In most scenarios that will be much slower than option 2 where NHibernate will be able to do the whole thing in a single query, using an EXISTS subquery.

I think you have mistakes in code. Shouldn't it be:

var companies = session.Query<Companies>().ToList();
var products = companies.SelectMany(x => x.Products).Where(q => q.Id == 1).ToList();

and

var companies = session.Query<Companies>().ToList();
var products = session.Query<Products>().Where(x => x.Id == 1).ToList();

?

Even though, the answer is not unequivocally. That's because it's really up to you to judge if you will need to heavily query products or not. I would fire sql profiler and just compare both approaches during unit tests.

But trying to be helpful at least a little bit, I'm usually using Option 2. I choose Option 1 only in specific scenarios.