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

I think that the answer to your first question is already in the POST you mentioned. However...

If your BlobDetails is not LINQ entity you can simply use it in your select statement to define (shrink) your projection attributes. For example:

var qry = from b in dc.Blobs
          select new BlobDetails { Id = b.Id, Size = b.Size }

would compile to SQL query like SELECT Id, Size FROM Blob ....

But if BlobDetails is LINQ entity you will need to use that AsEnumerable() hack otherwise you will get NotSupportedException: Explicit construction of entity type in query is not allowed.

var qry = from b in dc.Blobs.AsEnumerable()
          select new BlobDetails { Id = b.Id, Size = b.Size }

Edit

As @Chris Pitman stated in his comment this AsEnumerable() approach could create serious bottleneck, beacause the whole table would be loaded in memory before applying the projection. So it is not recommended!

To your second question:

You will need to create custom class for objects that you want use easily outside the scope of the method. Properties of an anonymous object are visible only in the scope, where they have been declared and anonymous objects can be cast only to type object.

So if you want to return anonymous objects from method the return type would has to be an enumerable of object or dynamic as @xeondev stated in his comment.

Answer 3

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.

Answer 4

There's no need to create your own classes, you can return an anonymous type. You can write something like this

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

return qry.ToList();

Although the signature of the method should look to something like this

public  IEnumerable<object> GetItems()

or

public dynamic GetItems()

So if you are going to use the result of linq query in outer scope like you example suggest, it is highly recommended you create your own classes.