0
// the following approach takes sooooooo long
var result = (
    from t in (
        from a in ctx.t1
        from b in ctx.t2
        where a.id == b.id
        select new { a, b }
    ).ToArray()
    from c in ctx.t3.ToArray()
    where c.name.Contains(t.a.name)
    select new { t.a, t.b, c }
).ToArray();

// while the following approach takes time in seconds
var tSet = (
    from a in ctx.t1
    from b in ctx.t2
    where a.id == b.id
    select new { a, b }
).ToArray();
var cSet = ctx.t3.ToArray();
var result = (
    from t in tSet
    from c in cSet 
    where c.name.Contains(t.a.name)
    select new { t.a, t.b, c }
).ToArray();

Please consider the codes above. FMPOV, these two approaches are the same as dataset (t1 & t2) and t3 in both approaches will be evaluated before "second" where caluse is applying on to them.

However, the actual case is that the first approach is taking way so longer than second approach. May I know why?

Thanks in advance.

mannok
  • 1,712
  • 1
  • 20
  • 30
  • 3
    That's because the first one is translated into something like `tSet.SelectMany(t => ctx.t3.ToArray().Select(...))` so it's doing the `ToArray` on the `t3` table for each row in the `t1` table that you get from your first query meaning way more than just two SQL queries. Doing intermediate calls to `ToArray` or `ToList` is generally a bad idea. – juharr Jan 11 '21 at 16:29
  • Why do you put `ToArray` inside the LINQ query? – Svyatoslav Danyliv Jan 11 '21 at 16:36
  • @juharr Thanks. That should be the correct answer. – mannok Jan 12 '21 at 03:44

1 Answers1

0

When you enumerate your data (calling ToArray, ToList, etc), that data must be fetched from the database first. Think of these, and other enumerating methods that are not implemented within the database (either as native operations or stored procedures), as being the end of your database transaction.

You should put off enumerating your data until absolutely necessary.

hijinxbassist
  • 3,667
  • 1
  • 18
  • 23