Selecting specific columns using linq: What gets transferred?

Question

I refer to this example: Return selected specified columns

Quote: If BlobDetails isn't the LINQ entity, then you can do it directly:

var qry = from b in dc.Blobs
          orderby b.RowVersion descending
          select new BlobDetails {
              Id = b.Id, Size = b.Size,
              Signature = b.Signature, RowVersion = b.RowVersion};

return qry.ToList();

I see that they are selecting specific column in a query through the ORM-tool LINQ TO SQL. Critics of ORM-tools say that, if I remember correctly, that ORM-tools select and return entire objects from the table, and limits the options of selecting only specific columns as one can do through classic SQL-programming. Of course, I have my doubts about that when I see this example, but nevertheless, I still keep asking myself the question: Does the database return only the selected columns, or does it return the entire objects, leaving the column-filtering to the ORM-tool?

From this example, they also have a class called Blobdetails:

public class BlobDetails   
{  
    public int Id { get; set; }  
    public string Signature { get; set; }  
    public int Size { get; set; }  
    public System.Data.Linq.Binary RowVersion { get; set; }     
}

Do I need to create my own classes everytime I only wish to select a few columns from a table through LINQ?

Answer 1

You don't need to create new classes to select few columns from a table. You can use anonymous types for that.

var qry = from b in dc.Blobs
          orderby b.RowVersion descending
          select new { b.Id, b.Size, b.Signature, b.RowVersion};

return qry.ToList();

Only selected columns are transferred. There is no difference between using plain SQL and using LINQ to SQL. When you are executing LINQ query, it is converted to plain SQL and executed. Then result is mapped to your objects.

You can use SQL Server Profiler to see what query was generated and executed on server. Also you can use LINQPad to see what SQL will be generated from your query. In your case query will be same either you use BlobDetails or anonymous object:

SELECT [t0].[Id], [t0].[Size], [t0].[Signature], [t0].[RowVersion]
FROM [Blobs] AS [t0]
ORDER BY [t0].[RowVersion] DESC

Answer 2

when you do projections LINQ does indeed only select those columns and there is nothing preventing you from materializing it however you want. So in your example code

select new BlobDetails 
{
  Id = b.Id, 
  Size = b.Size,
  Signature = b.Signature, 
  RowVersion = b.RowVersion
};

Only b.id, b.size, b.signature, & b.rowversion are selected. You can verify this with sql profiler or your debugger, I seem to recall there is also a function you can call on the datacontext to get the last query that was ran.