2

Disclaimer : I could not find all the answers from another question on SO.

While preparing a query in LINQPad I observed that some results were returned as IQueryable where as some in DbSet.

All the versions of the query can be found here : https://dotnetfiddle.net/r1ghBv

// returns IQueryable<Message>
// Time : 133 ms
from m in Messages
    where m.MessageStatusId < 5 && (    
        from mm in Messages
        where mm.MessageStatusId < 5
        group mm by mm.F_Id into g
        select g.Key
    ).Contains(m.F_Id)
    select m

vs

// returns DbQuery<Message>
// Time : 112 ms
from mm in Messages
where mm.MessageStatusId < 5
group mm by mm.F_Id into g
from m in g
select m

In LINQPad, DbQuery version had 89 lines of IL where as for IQueryable it was 171.

DbQuery is a class vs IQueryable is an interface, and DbQuery clearly seem to have more methods. But I am interested to know which might have better SQL performance if I was to use following queries in C# .NET 4.5.2 and Entity Framework 6.x ?

Questions :

  1. How does the return type got decided in queries above ?
  2. The DbQuery version had 7 extra SQL Queries in LINQPad's SQL tab before the actual query where as in IQueryable version LINQPad simply generated the query straight up even-though why DbQuery was a little faster ? Is that because DbQuery, an EF Library class ?
Community
  • 1
  • 1
ablaze
  • 722
  • 7
  • 30
  • I tried running similar queries in Linqpad but I got the IOrderedQueryable<> as the type of the queries. Can you share eactly what you've written in the linqpad? – Radu Pascal Jan 14 '15 at 13:04
  • I've referenced my EF model's dll in LINQPad. The queries you're seeing on https://dotnetfiddle.net/r1ghBv are exactly what I had in 4 individual LINQPad tabs, did I answer your question @RaduPascal ? – ablaze Jan 14 '15 at 13:46
  • Possible duplicate of [Whats the difference between IQueryable and DbQuery?](https://stackoverflow.com/questions/27624426/whats-the-difference-between-iqueryable-and-dbquery) – Mustafa Salih ASLIM Jul 30 '19 at 13:31

1 Answers1

0

The return type is the same as one row of the type Message. The time difference between the 2 queries can be explained from the 2nd 'from' in the query. In the first query you are enumerating through N x N, while the 2nd query you are enumerating through N x M. The M is the 2nd 'from'. In the 2nd query 'g' is a subset of all the Message.

user4453083
  • 1
  • I support what you're saying, but what confuses me is (as I said above ... ) the 1st LINQ query in LINQPad shows just 1 actual SQL Query and returns IQueryable in the SQL tab, where-as after running the 2nd LINQ query in LINQPad, I see several (7) SQL queries on INFORMATION_SCHEMA.TABLES, [app].[__MigrationHistory] (app is my schema), [dbo].[EdmMetadata] starting with an IF SQL statement on sys.databases ... in the SQL tab of LINQPad and it returns DbQuery Despite that, I observed the 2nd LINQ Query executing a little faster. I welcome more precise / sassy perf analysis. – ablaze Jan 14 '15 at 14:10