0

The following linq call in plain linq to sql results in 1 SQL query to the database

Table1.Select(t => new {Table2.First().Property1})

But I can't seem to get Dynamic Linq to to the same, the below produces 2 separate queries.

Table1.Select("new(@0.Property1)", Table2.First())

This does not work either

Table1.Select("@0", Table2.First().Property1)

or this

Table1.Select("new(@0 as MyField)", Table2.First().Property1)

What am I missing ?

OrdinaryOrange
  • 2,420
  • 1
  • 16
  • 25

2 Answers2

0

It's generating two separate queries because in that context, Table2.First() is a separate call from the rest of the query. It's not integrated into the generated expression. It's as if you did this:

var first = Table2.First(); // evaluated eagerly due to the First() call
var query = Table1.Select("new(@0.Property1)", first);

You can achieve the desired result if you rewrote it in terms of a cartesian product with the second table.

var query = Table1.SelectMany(t1 =>
        Table2.Take(1).Select(t2 => new { t1, t2 })
    )
    .Select("new(t2.Property1 as MyField)");
Jeff Mercado
  • 129,526
  • 32
  • 251
  • 272
  • I get an error, "No Property or Field t2 exists on IQueryable`1" – OrdinaryOrange May 22 '16 at 07:40
  • Sorry, there should be an `it` there. – Jeff Mercado May 22 '16 at 16:26
  • Should not need the it, as the context is already implicit. Either way this still did not work. Reason why was that the query was returning an IOrderedQueryable>, so no wonder it could not find the property t2. Found the correct query as posted below. – OrdinaryOrange May 23 '16 at 02:23
  • The query does not produce an `IOrderedQueryable>`. Were you actually using `SelectMany()`? That was the whole point. – Jeff Mercado May 23 '16 at 02:34
  • Nope, just using Select(). Just rewrote the above code to make sure again (without the last dynamic select as last time) and I definitely get a IOrderedQueryable>. I'm just referencing 2 random tables from my SQL server via LinqPad. – OrdinaryOrange May 23 '16 at 06:29
  • Well that's the problem. You would have needed to use it exactly as I wrote it out. Anyway, we'll get nowhere if you don't actually provide the actual query you are using. You'll get the _expected_ result if you followed the example. – Jeff Mercado May 23 '16 at 06:32
0

Thanks to Jeff's insights the correct query arrangement should be

Table1.Select(t => new {t2 = Table2.First()}).Select("new(t2.Property1)")

which produces a single query call to the DB

OrdinaryOrange
  • 2,420
  • 1
  • 16
  • 25