17

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 ?

Improve this question

3 Answers 3

Reset to default
30

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I'll note that there are some school of thought (NHibernate) that tell that projection is for pantsy. If you have a big table, divide it in smaller tables. If you have a slow connection to SQL server, buy a bigger connection. In general if the sql isn't able to optimize the access by using indexes + and none of the fields are saved "externally" like blobs reading one field of a table or reading ten has the same "difficulty" for SQL. The only big difference is in the network time.
The file example was very convincing one. Thanks for detailed explanation.
You might want to add that it is not usefull in case of updates, see also my answer
+1, Although Linq-to-NHibernate (at least with NH 2.0) does support simple db-level projections (selecting only a couple of interesting properties at SQL level). However, more complicated expressions are not recognized by the LINQ provider.
@peru they are quite different. The first one will create an enumerable of ProductName (a string?), the second one will create an enumerable of an anonymous object with a property called ProductName that contains the productname. So: Enumerable<string> vs Enumerable<ObjectWithStringProperty>.
|
14

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.

Improve this answer

Comments

2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.