linq to sql design question

[Tem]

are the queries generated by the compiler or at runtime?

 

[Jon Skeet [C# MVP]]

are the queries generated by the compiler or at runtime?

Execution time. The compiler just makes method calls passing in
expression trees. The LINQ to SQL provider looks at the expression tree
and builds SQL from it.

 

[Marc Gravell]

Further to Jon's answer, the great thing about this is that you can
create queries at runtime - for instance, you can subfilter data:

if(someCondition) {
query = query.Where(x=>x.SomeProperty == 4);
}

and the subsequent SQL will adapt to suit with a WHERE clause.

Additionally, if you are just talking about IQueryable<T> (which the
compiler is, in essense) - then you don't actually know what the
backend is yet! In addition to the discussion of what the LINQ
provider is (LINQ-to-SQL, DbLinq, etc), there is also (within a single
provider) some variation depending on what the actual data-store is
(at runtime). For an example just talking about LINQ-to-SQL, the same
compiled code will use different SQL for "Skip" and "Take" depending
on whether it is using SQL Server 2000, or SQL Server 2005 (and
above), since the 2005 etc have CTE support ["ROW_NUMBER() OVER (ORDER
BY ...) AS ..."], where-as 2000 can only use TOP and nested
sub-queries.

Marc

 

[Tem]

Wouldn't it be better performance, if there was an option to specify what db
server you're running so the compiler can generate the exact queries you
need and not waste cpu cycles at runtime?

 

[Jon Skeet [C# MVP]]

Wouldn't it be better performance, if there was an option to specify what db
server you're running so the compiler can generate the exact queries you
need and not waste cpu cycles at runtime?

No. The compiler is a C# compiler, it doesn't know anything about SQL.
That's the best way to keep the separation clean. Don't forget that
LINQ isn't limited to SQL - it could be talking to LDAP, or SharePoint
etc.

You can ask LINQ to SQL to compile queries for you once, which doesn't
actually take very much time - you're losing very, very little by
doing it at execution time, and gaining a lot more flexibility, while
keeping the language compiler and the LINQ providers at arm's length
from each other.

Jon

 

[Marc Gravell]

Not really - the time taken for query generation is not normally very
significant (AFAIK), especially compared with the hit of talking
either IPC or over the network to a database server. In short, it
isn't going to be the bottleneck.

Add to that the flexibility of dynamic queries (this is a *huge*
benefit, and wouldn't be possible with pre-determined queries; hence
composability doesn't work with SPs for the same reason*).

There is also the advantage that your server is not tied directly to
the code; upgrade your SQL Server, and your C# will automatically
start using the new features without recompile.

And when you consider LINQ-to-entities which can (via plugin
providers) talk to a whole range of different vendor databases without
changing the code...

Marc

[*=I'm excluding LINQ-to-objects filtering once the SP has returned
the data - the being to minimise the IPC volume]