Why use "select new " in LINQ

Question

I am very new to LINQ to SQL, so please forgive me if its a layman sort of question.

I see at many places that we use "select new" keyword in a query. For e.g.

var orders =  from o in db.Orders select new {
                o.OrderID,
                 o.CustomerID,
                 o.EmployeeID,
                 o.ShippedDate
           }

Why don't we just remove select new and just use "select o"

var orders =  from o in db.Orders select o;

What I can differentiate is performance difference in terms of speed, i.e. then second query will take more time in execution than the first one.

Are there any other "differences" or "better to use" concepts between them ?

Answer 1

With the new keyword they are building an anonymous object with only those four fields. Perhaps Orders has 1000 fields, and they only need 4 fields.

If you are doing it in LINQ-to-SQL or Entity Framework (or other similar ORMs) the SELECT it'll build and send to the SQL Server will only load those 4 fields (note that NHibernate doesn't exactly support projections at the db level. When you load an entity you have to load it completely). Less data transmitted on the network AND there is a small chance that this data is contained in an index (loading data from an index is normally faster than loading from the table, because the table could have 1000 fields while the index could contain EXACTLY those 4 fields).

The operation of selecting only some columns in SQL terminology is called PROJECTION.

A concrete case: let's say you build a file system on top of SQL. The fields are:

• filename VARCHAR(100)
• data BLOB

Now you want to read the list of the files. A simple SELECT filename FROM files in SQL. It would be useless to load the data for each file while you only need the filename. And remember that the data part could "weight" megabytes, while the filename part is up to 100 characters.

After reading how much "fun" is using new with anonymous objects, remember to read what @pleun has written, and remember: ORMs are like icebergs: 7/8 of their working is hidden below the surface and ready to bite you back.

Answer 2

The answer given is fine, however I would like to add another aspect.

Because, using the select new { }, you disconnect from the datacontext and that makes you loose the change tracking mechanism of Linq-to-Sql.

So for only displaying data, it is fine and will lead to performance increase.

BUT if you want to do updates, it is a no go.

Answer 3

In the select new, we're creating a new anonymous type with only the properties you need. They'll all get the property names and values from the matching Orders. This is helpful when you don't want to pull back all the properties from the source. Some may be large (think varchar(max), binary, or xml datatypes), and we might want to exclude those from our query.

If you were to select o, then you'd be selecting an Order with all its properties and behaviours.