Strange linq selection behavior

Question

0.00/5 (No votes)

See more:

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.

C#

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

C#

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 22:53pm

Scalee

Add a Solution

Comments

Timberbird 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)

3 solutions

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:

C#

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.

Posted 26-Sep-12 2:02am

sjelen

Updated 26-Sep-12 2:07am

v2

Solution 3

i would do :

C#

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 )));

Posted 26-Sep-12 23:32pm

nrgjack

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

BobJanova · Accepted Answer · 2012-09-26T00:29:00

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).