Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# LINQ
Hi,
 
I am hoping somebody can shine some light on this matter, maybe it's not strange at all.
 
This will cause the query to select every column inside Purchase not just Id and Name.
foreach (Customer c in db.Customers.Where(x => x.Active.Equals(true)))
{
  foreach (Purchase p in c.Purchases.Select(x => new { i.Id, i.Name }))
  {
    Log(string.format("{0} {1}", p.Id, i.Name));
  }
}
 
This query will only select Id and name properly
foreach (Customer c in db.Customers.Where(x => x.Active.Equals(true)))
{
  foreach (Purchase p in db.Purchases.Where(x => x.Customer.Equals(c))
  .Select(x => new { i.Id, i.Name }))
  {
    Log(string.format("{0} {1}", p.Id, i.Name));
  }
}
Posted 25-Sep-12 23:53pm
Scalee789
Comments
Timberbird at 26-Sep-12 5:27am
   
Well, in foreach (Purchase p in c.Purchases.Select(x => new { i.Id, i.Name })) you have cycle variable p which has explicitly defined type - Purchase. Strange thing it can be cast from anonymous type... I can only guess due to cast Select() returns original objects, not created ones, or Purchase has corresponding constructor/cast operator (unlikely)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

When you're doing a query on something from a database context, the query is constructed as you call Linq methods on it, including Where and Select. So you end up with a query like "select id, name from purchases where customerID={c.ID}".
 
In the second case, you're asking for c.Purchases, which is presumably declared as a List<Purchase> or similar in your domain object. Requesting that property will cause a query to fire to get all the purchases for that customer, due to lazy loading: something like "select * from purchases where customerID={c.ID}" (or, if not *, then all the columns needed to fully populate a Purchase object). The Select is then running on an in memory object (i.e. Linq to Objects, not Linq to SQL).
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You should avoid foreach loops like this, it will cause more queries to be executed, more data to be fetched from SQL and LINQ can't optimize queries.
If you write it like this:
from p in db.Purchases where p.Customer.Active.Equals(true) select new {p.Id, p.Name}
 
it will be a single query fetching only two required columns.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

i would do :
 
var query = from p in db.Purchases
            join c in db.Customers on p.Customer.Id equals c.Id
            where c.Active.Equals(true)
            select new { PurchaseId = p.Id, PurchaseName = p.Name };
 
// concretize
var list = query.ToList();
 
list.foreach(i=> Log(string.format("{0} {1}", i.PurchaseId, i.PurchaseName )));
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Mathew Soji 274
1 BillWoodruff 270
2 Afzaal Ahmad Zeeshan 244
3 Sergey Alexandrovich Kryukov 240
4 Raul Iloc 160
0 OriginalGriff 6,219
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,103
3 Manas Bhardwaj 4,549
4 Maciej Los 3,845


Advertise | Privacy | Mobile
Web04 | 2.8.1411019.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100