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 ?
Best Answer
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:
Now you want to read the list of the files. A simple
SELECT filename FROM files
in SQL. It would be useless to load thedata
for each file while you only need thefilename
. And remember that thedata
part could "weight" megabytes, while thefilename
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.