CODEWITHSUNDEEP
c#linqlinq-to-sql
Shaun Rowan
Shaun Rowan

Reputation: 9539

Linq to SQL - second order projections

Consider the following:

.

public class ItemSmall
{
    public string A { get; set; }
    public string B { get; set; }
    public string C { get; set; }
    public string D { get; set; }
    public string E { get; set; }
}

public class ItemSmaller
{
    public string A { get; set; }
    public string B { get; set; }
}

Let's say i then have a method to project the data into the first class:

IQueryable<ItemSmall> Function1() {
    return Context.Items.Select(i => new ItemSmall { A = i.A, B = i.B, C = i.RelatedTable.FirstOrDefautl(), D = i.RelatedTable.FirstOrDefautl(), E = i.E });
}

If I then instanciate this query it works beautifully - only columns A through E are queried. I am pleased.

Now consider the next function:

IQueryable<ItemSmaller> Function2() {
    return Function1().Select(i => new ItemSmaller { A = i.A, B = i.B });
}

This is where the magic seems to fail. Columns A through D are still queried. I guess I sort of expected that L2S would be smart enough to omit columns C thru J since they aren't projected onto the final object.

Is there any approach which allows daisy chaining projections like this in an efficient way? (I would prefer to only write the database-to-object mapping a single time as some of the projections are quite complex.)

Edit

Thanks to Sergey for verifying the original scenario described above works exactly as desired. The difference in my actual scenario is that properties C and D are actually more complex expressions which leverage FirstOrDefault operations against related one-to-many tables.

It seems that First / FirstOrDefault / SingleOrDefault cause their dependent columns to be queried no-matter-what.

I've updated the question accordingly.

Upvotes: 1

Views: 122

Answers (3)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236288

According to Classification of Standard Query Operators by Manner of Execution FirstOrDefault() operator is executed immediately, i.e. you force execution database query at the point where you write FirstOrDefault(). That's why you see all columns being downloaded. Further queries occur in memory.

Upvotes: 1

Hiệp L&#234;
Hiệp L&#234;

Reputation: 634

IQueryable<ItemSmall> Function1() {
    return Context.Items.Select(i => new ItemSmall { A = i.A, B = i.B, C =     i.RelatedTable.FirstOrDefautl(), D = i.RelatedTable.FirstOrDefautl(), E = i.E });
}

By calling i.RelatedTable.FirstOrDefault() or the likes; you have explicitly instructed the framework to query the first line (if any) for the RelatedTable, because they are eager loading.

In your case, you will want a lazy want, try to use Take(1), but that means you what to re-model your class and it looks a bit hacky.

Upvotes: 0

Luc Morin
Luc Morin

Reputation: 5380

My understanding is that in the second case, you still project into the bigger class before projecting into the smaller one, thus the need for all fields from the DB.

Actually, if what you want to do is cache the result of the first query, then you might want to call ToList() on the Function1 return value, and use that List in Function2. That way the DB is queried only once.

Hope this helps.

EDIT: After reading Sergey's comment, I too tested in LinqPad, and also running SQL Profiler, and it seems I am wrong and that only the smaller sets of fields are actually queried on the DB. Can you explain how you concluded that all fields were queried ?

Upvotes: 0

Related Questions