Querying from the Controller a List<T> obtained fromt the repository increase coupling?

I have an ASP.NET MVC application coded with C#. The application is structured this way:

1. Controller

2. Repository

3. LINQ to Entities (Entity Framework)

4. View

I use the Repository (_ProductRep) to query the LINQ to Entities and give to the Controller actual entities or List<T>, not IQueriables<T>.

I would like to have some help about a situation where I have more than a doubt. I have the following code:

List<Monthly_Report> lproduct_monthlyReport = _ProductRep.GetArchiveReport(product.Prod_ID, lmonth, lyear);

After I get this lproduct_monthlyReport I need to query it inside a foreach and get a specific record. Currently I implemented the solution like this:

foreach (var item in litemList)
{    
   var lproductItem_monthlyReport = lproduct_monthlyReport.Single(m => m.Item_ID == item.Item_ID);
   // Other code
} 

Where litemList is the list of all the possible items a product can have.

I wanted to know whether this solution sensibly increase the coupling (and violates the law of Demeter) or it is acceptable because I am actually querying a List<T> and not an IQueriable<T>. Correct me if I am wrong, but I guess that since the List does not need to access the EF DataContext, there is no coupling between Controller and EF.

In case I am wrong, the only solution I can think about is to substitute the query with a Repository method (that still I have to implement):

var lproductItem_monthlyReport_ProductRep.GetArchiveReport(product.Prod_ID, lmonth, lyear, item.Item_ID);

with this solution however the Repository makes one query with 4 conditions every loop cycle whilst in the previous solution the repository was making a query with just one conditions.

May you please enlighten me on this issue? Thanks.

PS: I need both variables lproduct_monthlyReport and lproductItem_monthlyReport inside the loop, I cannot just use one of them

PPS: I know t开发者_如何学Pythonhat I should have a Business Service Layer between Controller and Repository, it is my next step.

---

Returning Lists from your repository will give you awful performance, because you lose the deferred execution behaviour. Basically your repository will retrieve every single record, and not related entities, into memory, and turn them into a List, which then gets processed in memory. If you want to access a related entity, it'll need another database hit. If you stick with IEnumerable (or IQueryable), then you are hiding the nuances of the entity framework behaviour from the client, but still getting the advantages like lazy loading and deferred execution.

Ignoring the specifics of your Repository for now, if you do this:

List<Product> products = MyEntities.Products.ToList();

Product product1 = products.Single(p => p.Id = 1);

it will perform much worse than this:

IEnumerable<Product> products = MyEntities.Products;

Product product1 = products.Single(p => p.Id = 1);

The first one will perform a SELECT in the database with no WHERE clauses, then instantiate .Net objects for every result, then query that in-memory list. The second will do nothing until you access a property on product1 and will at that point issue a database command to just retrieve the 1 product, and only instantiate that 1 product.

The difference between the 2 may not be noticeable with small data sets, but as the data set gets larger this will get worse and worse. Throw in a connected entity (or worse still entity collection), and you'll get potentially thousands of database hits, where if you stuck with IEnumerable you'd get 1.

---

I would probably have function like this GetArchiveReport(int prodID, int lmonth, int lyear, IEnumerable<int> itemIDs) that would do a itemIDs.Contains(tbl.ID) inside your query

var SelectedReports = _ProductRep.GetArchiveReport(product.Prod_ID, lmonth, lyear, litemList.Select(item => item.Item_ID));
foreach(var prodItem in SelectedReports)
{
  //Do code
}