New "Orcas" Language Feature: Query Syntax

Last month I started a series of posts covering some of the new VB and C# language features that are coming as part of the Visual Studio and .NET Framework "Orcas" release.  Here are pointers to the first three posts in my series:

Today's blog post covers another fundamental new language feature: Query Syntax.

What is Query Syntax?

Query syntax is a convenient declarative shorthand for expressing queries using the standard LINQ query operators.  It offers a syntax that increases the readability and clarity of expressing queries in code, and can be easy to read and write correctly.  Visual Studio provides complete intellisense and compile-time checking support for query syntax.

Under the covers the C# and VB compilers take query syntax expressions and translate them into explicit method invocation code that utilizes the new Extension Method and Lambda Expression language features in "Orcas".

Query Syntax Example:

In my previous language series posts, I demonstrated how you could declare a "Person" class like below:

We could then use the below code to instantiate a List<Person> collection with people values, and then use query syntax to perform a LINQ query over the collection and fetch only those people whose last name starts with the letter "G", sorted by the people's firstname (in ascending order):

The query syntax expression above is semantically equivalent to the below code that uses LINQ extension methods and lambda expressions explicitly:

The benefit with using the query syntax approach is that it ends up being a little easier to read and write.  This is especially true as the expression gets richer and more descriptive.

Query Syntax - Understanding the From and Select Clauses:

Every syntactic query expression in C# begins with a "from" clause and ends with either a "select" or "group" clause.  The "from" clause indicates what data you want to query.  The "select" clause indicates what data you want returned, and what shape it should be in.

For example, let's look again at our query against the List<Person> collection:

In the above code snippet the "from p in people" clause is indicating that I want to perform a LINQ query against the "people" collection, and that I will use the parameter "p" to represent each item in the input sequence I am querying.  The fact that we named the parameter "p" is irrelevant - I could just have easily named it "o", "x", "person" or any other name I wanted.

In the above code snippet the "select p" clause at the end of the statement is indicating that I want to return an IEnumerable sequence of Person objects as the result of the query.  This is because the "people" collection contains objects of type "Person", and the p parameter represents Person objects within the input sequence.  The datatype result of this query syntax expression is consequently of type IEnumerable<Person>. 

If instead of returning Person objects, I wanted to return just the firstnames of the people in the collection, I could re-write my query like so:

Note above how I am no longer saying "select p", but am instead saying "select p.FirstName".  This indicates that I don't want to return back a sequence of Person objects - but rather I want to return a sequence of strings - populated from each Person object's FirstName property (which is a string).  The datatype result of this query syntax expression is consequently of type IEnumerable<string>. 

Sample Query Syntax Against a Database

The beauty of LINQ is that I can use the exact same query syntax against any type of data.  For example, I could use the new LINQ to SQL object relational mapper (ORM) support provided in "Orcas" to model the SQL "Northwind" database with classes like below (please watch my video here to learn how to-do this):

Once I've defined the class model above (and its mapping to/from the database), I can then write a query syntax expression to fetch all products whose unitprice is greater than $99:

In the above code snippet I am indicating that I want to perform a LINQ query against the "Products" table on the NorthwindDataContext class created by the ORM designer in Visual Studio "Orcas".  The "select p" indicates that I want to return a sequence of Product objects that match my query.  The datatype result of this query syntax expression is consequently of type IEnumerable<Product>.

Just like with the previous List<Person> collection query syntax example, the C# compiler will translate our declarative query syntax into explicit extension method invocations (using Lambda expressions as the arguments).  In the case of the above LINQ to SQL example, these Lambda expressions will then be converted into SQL commands and evaluated within SQL server (so that only those Product rows that match the query are returned to our application).  Details on the mechanism that enables this Lambda->SQL conversion can be found in my Lambda Expressions blog post under the "Lambda Expression Trees" section.

Query Syntax - Understanding the Where and OrderBy Clauses:

Between the opening "from" clause and closing "select" clause of a query syntax expression you can use the most common LINQ query operators to filter and transform the data you are querying.  Two of the most common clauses you'll end up using are "where" and "orderby".  These handle the filtering and ordering of results.

For example, to return a list of alphabetically descending category names from the Northwind database - filtered to only include those categories where there are more than 5 products associated with the category - we could write the below query syntax that uses LINQ to SQL to query our database:

In the above expression we are adding a "where c.Products.Count > 5" clause to indicate that we only want to return category names where there are more than 5 products in the category.  This takes advantage of the LINQ to SQL ORM mapping association between products and categories in our database.  In the above expression I also added a "orderby c.CategoryName descending" clause to indicate that I want to sort the results in descending order.

LINQ to SQL will then generate the below SQL when querying the database using this expression:

SELECT [t0].[CategoryName] FROM [dbo].[Categories] AS [t0]
    FROM [dbo].[Products] AS [t1]
    WHERE [t1].[CategoryID] = [t0].[CategoryID]
)) > 5
ORDER BY [t0].[CategoryName] DESC

Notice how LINQ to SQL is smart and only returns back the single column we need (the categoryname).  It also does all of the filtering and ordering in the database layer - which makes it very efficient.

Query Syntax - Transforming Data with Projections

One of the points I made earlier was that the "select" clause indicates what data you want returned, and what shape it should be in.

For example, if you have a "select p" clause like below - where p is of type Person - then it will return a sequence of Person objects:

One of the really powerful capabilities provided by LINQ and query syntax is the ability for you to define new classes that are separate from the data being queried, and to then use them to control the shape and structure of the data being returned by the query. 

For example, assume that we define a new "AlternatePerson" class that has a single "FullName" property instead of the separate "FirstName" and "LastName" properties that our origional "Person" class had:

I could then use the below LINQ query syntax to query my origional List<Person> collection, and transform the results to be a sequence of AlternatePerson objects using the query syntax below:

Notice how we can use the new Object Initializer syntax I talked about in the first post in my language series to create a new AlternatePerson instance and set its properties within the "select" clause of our expression above.  Note also how I am assigning the "FullName" property by concatenating the FirstName and LastName properties of our origional Person class.

Using Query Syntax Projections with a Database

This projection feature ends up being incredibly useful when working with data pulled from a remote data provider like a database, since it provides us with an elegant way to indicate which columns of data our ORM should actually fetch from a database.

For example, assume I use the LINQ to SQL ORM provider to model the "Northwind" database with classes like below:

By writing the LINQ query below, I am telling LINQ to SQL that I want a sequence of "Product" objects returned:

All of the columns necessary to populate the Product class would be returned from the database as part of the above query, and the raw SQL executed by the LINQ to SQL ORM would look like below:

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID],
              [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],
              [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[UnitPrice] > 99

If I didn't need/want all of these columns for some scenarios, I could alternatively define a new "MyProduct" class like below that has a subset of the properties that the Product class has, as well as one additional property - "TotalRevenue" -- that the Product class doesn't have (note: for people not familiar with C#, the Decimal? syntax indicates that UnitPrice property is a nullable value):


I can then use the projection capability of query syntax to shape the data I want returned from the database using a query like below:

This is indicating that instead of returning a sequence of "Product" objects, I instead want "MyProduct" objects, and that I only need 3 properties of them filled.  LINQ to SQL is then smart enough to adjust the raw SQL to execute to only return those three needed product columns from the database:

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[UnitPrice]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[UnitPrice] > 99

Just to show-off, I could also populate the 4th property of the MyProduct class - which is the "TotalRevenue" property.  I want this value to be the aggregate amount of revenue that our products have sold for.  This value isn't stored anywhere as a pre-computed column within the Northwind database.  Instead you need to perform a join between the "Products" table and the "Order Details" table and sum up all of the Order Detail rows associated with a given product.

What is cool is that I can use the LINQ "Sum" extension method on the Product class's OrderDetails association and write a multiplication Lambda expression as part of my query syntax projection to compute this value:

LINQ to SQL is then smart enough to use the below SQL to perform the calculation in the SQL database:

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[UnitPrice], (
        SELECT SUM([t2].[value])
        FROM (
                 SELECT [t1].[UnitPrice] * (CONVERT(Decimal(29,4),[t1].[Quantity])) AS [value], [t1].[ProductID]
                 FROM [dbo].[Order Details] AS [t1]
                 ) AS [t2]
        WHERE [t2].[ProductID] = [t0].[ProductID]
        ) AS [value]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[UnitPrice] > 99

Query Syntax - Understanding Deferred Execution, and using ToList() and ToArray()

By default the result of a query syntax expression is a variable of type IEnumerable<T>.  In my samples above you'll notice that all of the query syntax assignments are to IEnumerable<Product>, IEnumerable<string>, IEnumerable<Person>, IEnumerable<AlternatePerson>, and IEnumerable<MyProduct> variables. 

One of the nice characteristics of IEnumerable<T> interfaces is that objects that implement them can defer the actual execution of the queries until a developer first attempts to iterate over the values (this is accomplished using the "yield" construct that was first introduced with C# 2.0 in VS 2005).  LINQ and query syntax expressions take advantage of this feature, and defer the actual execution of queries until the first time you loop over the results.  If you never iterate over the IEnumerable<T> result, then the query is never executed.

For example, consider the below LINQ to SQL example:

The database will be hit and the values to populate our Category objects will be retrieved not when the query syntax expression is declared - but rather when we first try and loop over the results (indicated above with the red arrow).

This deferred execution behavior ends up being really useful because it enables some powerful composition scenarios where we can "chain" multiple LINQ queries and expressions together.  For example, we could feed the result of one expression into another - and by deferring the execution allow an ORM like LINQ to SQL to optimize the raw SQL based on the entire expression tree.  I'll show examples of how to use this in a later blog post.

How to evaluate the query syntax expression immediately

If you don't want to defer the execution of queries, and instead want to execute them immediately, you can use the built-in ToList() and ToArray() operators to return either a List<T> or an array containing the results. 

For example, to return a generic-based List<T> collection:

and to return an array:

In both cases above the database will be hit and the Category objects populated immediately. 


Query syntax provides a very convenient declarative shorthand for expressing queries using the standard LINQ query operators.  It offers a syntax that is very readable, and which works against any type of data (any in-memory collection, array, XML content, or against remote data providers like databases, web-services, etc).  Once you become familiar with the syntax, you can immediately apply the knowledge everywhere.

In the not too distant future I'll finish the last segment of this language series - which will cover the new "anonymous types" feature.  I'll then move on to cover some super practical examples of using all of these language features in the real world (especially using LINQ against databases and XML files). 

Hope this helps,



  • Scott,

    Is there much overhead in using LINQ? If so, can you pro/con why to use linq as opposed to using the traditional methods of data retrieval.

  • Scott,

    This is great post.
    I'm waiting your next screen cast.

    Is it coming soon?

  • Thanks for the great article Scott. Very nice feature. Just can't wait to start using this.

  • awesome!

    I prefer straight syntax like this to other metaprogramming languages in trend right now

  • Scott,
    You are the best in explaining and selling new concepts.
    How does it work with joins?

  • Hi Jared,

    The LINQ to SQL ORM implementation that is built-in with "orcas" is really, really fast. They've done a ton of optimization work to make sure there isn't any runtime overhead that would prevent you from using it versus straight DataReaders.

    Once I finish the next segment in this language series (which will introduce the final language feature in Orcas that I think people should understand), I'll be turning my attention to writing a bunch of posts that really show off working with databases using LINQ.

    I think these LINQ to databases series will really show off the potentials provided by LINQ.

    Hope this helps,


  • Hi Ranji,

    Query Syntax fully supports defining joins as well as grouping support as part of LINQ query expressions (there is a join clause that you can use).

    I'll include a sample of this as part of my next language series post.

    Hope this helps,


  • Scott,

    Another great post, thanks.

    One question... Could I define my own extension methods and use the friendly "Query Syntax" or is it limited to the Linq methods: from, select, where, orderby, etc?

  • Hi Scott,

    Excellent post.

    Not sure if how is it possible but I am wondering if you can support a 'query builder' for LINQ queries in the designer, instead of coding simple queries like these by hand.

  • Hi Greg,

    You can't add new keywords into the query syntax, but you can definitely define your own extension methods and add them into the query syntax expressions.

    Some examples:

    // Add a IsDeprecatedProduct() extender method onto Person types and checking it as part of an expression

    from p in products
    where p.IsDeprecatedProduct() == true
    select p

    // Add and use a custom SortAndPage() extender method that takes the sort params as string arguments (useful with dynamic queries from a UI):

    (from p in products
    where p.UnitPrice > 50
    select p).SortAndPage("firstname", "asc")

    // Use projections to create a hierarchical result, where you include the last 5 orders placed for a product as part of the result, using the above custom "SortAndPage" extender method on the sub-results:

    from p in products
    where p.UnitPrice > 50
    select new CustomProductClass {
    ProductID = p.ProductID,
    ProductName = p.ProductName,
    LastFiveOrders = p.OrderDetails.SortAndPage("firstname", "asc").Take(5)

    As you can see it is pretty flexible and powerful. :-)

    Hope this helps,


  • Hi Zubair,

    One of the features shipping in Orcas is a new control that supports data-binding to LINQ entities.

    One of the features it also provides is a designer that allows you to graphically construct LINQ queries.

    Hope this helps,


  • Another amazing post. Thanks for taking the time to put this together!

  • Yes, that seems exactly what I was looking for.


  • I love how the previous posts were a build up that culminates (so far) with the LINQ explanation.
    Great work.

  • Hi Juan,

    Thanks again for the excellent translation - I really appreciate it!


  • Scott Gu Said.....

    " Once I finish the next segment in this language series (which will introduce the final language feature in Orcas that I think people should understand), I'll be turning my attention to writing a bunch of posts that really show off working with databases using LINQ."

    Just great news, exactly what i expected from you. Can you kindly write those posts, in connection with cuurently available tutorials in database. This would help in comparing LINQ and Current SQL easily.

    Just my views

  • Great article. In the future it would be helpful if you could demonstrate how to make LINQ work with existing stored procedures.

  • I have some questions.

    Can you also do this: var result = select.... and var would be the correct IEnumerable?

    Is it really better to do the calculations (total price for instance) in the database? Why?

    In one of your comments you talk about defining an extender method IsDeprecated(), maybe in the future you can think about extender properties?

    Thanks for the examples!

  • Hi Scott,

    Excellent post as usual.
    When I looked at the AlternatePerson example I couldn't help but wonder why you couldn't do something like this:

    var friends =
    from p in people
    where p.LastName.StartsWith("G")
    select FullName = p.FirstName + " " + p.LastName, Age = p.Age;

  • Hi Roger,

    I'll definitely blog about using stored procedures in the future (the good news is that it works really well).



  • Hi Mike/Kyle,

    Yep - you can definitely use the new var keyword with anonymous type support when doing some of my samples above.

    The main reason I used explicit types vs. anonymous types were:

    1) I haven't covered anonymous types and the var keyword yet. My plan is to cover this in my final language series post. I wanted to-do this last so that I could use the projection feature of query syntax to motivate the "why this is a useful feature" discussion.

    2) There are a number of cases where you can't use anonymous/var types and must use explicit type names (like my AlternatePerson example). Specifically, if you are building a data access layer that you encapsulate as a class library, then you'll need to use named types to return data from methods/classes and over web-services.

    Stay tuned for the next post in the series, though, and I'll talk more about anonymous types and the var keyword and shows some useful examples of it in action.



  • great post once again Scott.

  • Hi Scott,

    In the case of the sample LINQ query with the IsDeprecatedProduct(), supposing that this is a C# function, not a stored procedure, how would the SQL query behind it be generated? Would it make a select * from products and then iterate through the resulting list to apply IsDeprecatedProduct() on each item, or would it somehow include IsDeprecatedProduct() in the query itself?

    Thanks for the excellent, didactic posts.

  • Hi Rod,

    I think it would depend on how you write your extension method. I know it is possible to have a method like my custom SortAndPage() example modify the SQL query:

    from p in products
    where p.UnitPrice > 50
    select new CustomProductClass {
    ProductID = p.ProductID,
    ProductName = p.ProductName,
    LastFiveOrders = p.OrderDetails.SortAndPage("firstname", "asc").Take(5)

    I'm not 100% sure if you can use the IsDeprecatedProduct() syntax and modify the SQL query though (I know it does work with in-memory LINQ to Objects queries though).

    I'll try and look into it somemore in the weeks ahead and see if I can put together more samples that show how to modify the underlying SQL query.



  • Hi James,

    The good news is that LINQ to SQL supports both querying as well as updates, inserts and deletes.

    By default updates will be batched together in an implicit transaction, or you can bring your own transactionscope and make it explicit.

    As part of updates you also have a few options with regard to how concurrency is managed. You can also optionally configure your entities to use SPROCs for updates/inserts/deletes - so if you really want to you can completely customize this management.

    Once I get through this current language series (only one more to go!), I'll be drilling down much more into how LINQ to SQL and some of the database scenarios work with LINQ, and start to cover this.



  • when would you write blogs about WF/WCF

  • Scott, it is very exciting to read this you need to take any special precautions regarding SQL Injection when using LINQ and Query Syntax?

  • Hi Scott,

    Quick question around the Object Initializer example. I understand that you're just showing how to return a different object than the one being processed, however the example looks awkward. What if Person contained 20 fields and all you wanted to add was a FullName calculated field? Is there any way to extend the table ORM to add this field?

    The easiest way I would see this being done would be to make the Person class partial and then allow class extension that way. I've done this with a code generator I wrote some time ago and updated for 2.0. The great thing about that solution is that you are still dealing with Person entities.

    Just a thought.


  • Hi Craig,

    The nice thing about an ORM mapper like LINQ to SQL is that it is 100% type-safe - which means it provides you with full protection against things like SQL Injection attacks. So you shouldn't need to take any special steps to guard against those.

    Hope this helps,


  • Hi Richard,

    The nice thing about LINQ queries is that they are composable (because of the deffered execution pattern I mentioned above). This means you can do an initial query, and then add a further filter to it dynamically depending on some scenario. It isn't until you try and access the data that the query is actually executed.

    Mike Taulty just blogged about how you can do this type of dynamic query here:

    Hope this helps,


  • Hi Jason,

    Yep - you can definitely extend the entity model itself to add a property. The good news is that the LINQ to SQL designer actually makes this easy (just click on the entity and add a new property - or drop down into code and add a partial class).

    There are still some cases, though, where you want to avoid changing your entity (often for one off cases or where you are consuming a model that someone else built and doesn't want to update). The scenario I did above is one way to handle these cases.

    Hope this helps,


  • Scott, thanks for answering my question and its beautiful, it didn't click in my head what deffered execution on the SQL statements actually meant until you pointed out how it would apply in a scenario common to "me". :)

    My next question then would be how LINQ deals with paging? Does it have some clever way of knowing to create a temp table to use the "between" statement like you described in your "efficient data paging" post or will I need to create stored procedures for it to use?

  • Scott,

    I am interested to find out if you support, I guess via keywords, populating arrays immediately using async commands (e.g. BeginFrom).

    Idealy I would like the LINQ framework to handle the threading/pooling plumbling for me.

    Has this been done or will we as developers have to implement this?


  • Scott,
    I reread some old posts and see that paging is somehow handled, though I have yet to see example code, but I'm willing to accept paging has been planned for and somehow works (posting this before you've replied to my previous post so you can ignore the previous one if you see this one before that one).

    BUT, a coworker pointed out to me his doubts because of situations where you might need to do something like Select * from employee where ID not in (Select empID from additionalinfo) to say... get a list of employees who haven't updated their additional information. Is there a way for LINQ to handle this without doing multiple trips to the database?

  • Hi Scott, me agsin.

    I tested extending a table's class by adding a roll-up property and while it compiles correctly, I get a run-time error if I use the property in a query. Ironically, the property even appears in IntelliSense while constructing the query!

    For example:

    Assume a db table Person containing FirstName and LastName nvarchar fields. I would like to extend the class for display purposes, such as:

    public partial class Person
    public string FullName
    get { return FirstName + LastName; }

    I would then like to use the new property in the query:

    TestDBContext db = new TestDBContext();

    List people =
    (from p in db.Persons
    where p.FullName.Equals("")
    select p).ToList();

    This compiles fine, but produces the following runtime error:

    Binding error : Member 'WindowsApplication1.Person.FullName' is not a mapped member of 'WindowsApplication1.Person'.

    While this makes sense from the db's perspective, how can I add this dynamic property to the table's class AND allow it to be used in the query?

  • One of the nice feature to have with dlinq or linq in general is a linq builder. I have been using paul wilson for ormapper for sometime which support similar concept called opath based queries on objects. I have seen myself and many other developers in my team rollout their own versions of opath builder. Opath builder is simply a way to dynamically generate opath statemets based on search criter user puts. like
    OpathBuilder opath = new OpathBuilder();
    if(some criteria)
    and then return something like opath.Tostring()
    to get actual Opath.

    I am sure with linq i am going to have to rollout my own linq builder to accomdate a wrapper for dynamic dlinq quries which changes based on user input.

    Zeeshan Hirani

  • Hi Adam,

    LINQ doesn't currently have built-in async keywords - but that is something that could be built on top (the deferred execution model of LINQ easily supports this).

    We are also looking more at async and parallel execution of queries in the future.

    Hope this helps,


  • Hi Richard,

    You could handle this scenario:

    >>>> Select * from employee where ID not in (Select empID from additionalinfo) to say... get a list of employees who haven't updated their additional information.

    Using the join keyword in LINQ. This would avoid you having to make multiple round-trips to the database for this scenario.

    Hope this helps,


  • Well, I was hoping you'd say the LINQ to SQL layer was as well thought out as it sounds, but I was expecting to be disappointed, and told "next version" ;)

    Really looking forward to RTM.

    Our existing ORM solution is going to look quite plain compared to this, from the sounds of things.

  • I feel both hungry and tears in my eyes. anybody having the same feeling?

    I'm already designing my next application in my head while reading this post.. this is kick ass stuff.. no more dba's writing stored procs.. no code generators to generate entities to just to hold result sets.. no more opening and closing connections .. just worry about implementing application logic. this will enable developers to build great applications faster with tons of code stripped. wow wow wow wow wow!!!!!!!!!!

  • I like the LINQ to SQL syntax and ease of developing self contained data queries.

    What I still need to discover however is how to use LINQ to SQL in an asyncronious (sp) fashion as our ASP.Net development effert will be using async calls to the database in order to keep IIS threads available as much as possible.

  • Nice article, I am looking forward to the release of Orcas to utilize such a rich querry set derived straight from a database. I am not going to try it now though, I have been stuck by beta and RTM build changes before...Still working on updating Atlas to the RTM release.

  • Regarding dynamic queries: if you look in the linq forum you can find the following code for AND and OR. Maybe it's a good idea if microsoft gave this subject some more structural attention.

    var q = db.Hotels.Where(h => !h.FullyBooked);
    if (criteria.RequiresGolfCourse)
    q = q.Where(h => h.HasGolfCourse);

    if (criteria.RequiresPool)
    q = q.Where(h => h.HasPool);

    if (criteria.RequiresSeaViews)
    q = q.Where(h => h.HasSeaViews);
    return q;

    Query q = ...;

    ParameterExpression p = Expression.Parameter(typeof(Profile),"p");
    Expression tagId = Expression.Property(p, typeof(Profile).GetProperty("tagId"));
    Expression predicate = null;

    foreach(Tag tag in tags) {
    Expression expr = Expression.EQ(tagId, Expression.Constant(tag.Id));
    predicate = (predicate == null) ? expr : Expression.Or(predicate, expr);

    q = q.Where(Expression.Lambda<Func>(predicate, p));

Comments have been disabled for this content.