SQL 使用 linq 选择特定列：传输了什么？

Selecting specific columns using linq: What gets transferred?

提问by Darth_Sygnious

I refer to this example: Return selected specified columns

我参考这个例子：返回选定的指定列

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

引用：如果 BlobDetails 不是 LINQ 实体，那么您可以直接执行此操作：

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?

我看到他们通过 ORM 工具 LINQ TO SQL 在查询中选择特定列。ORM 工具的批评者说，如果我没记错的话，ORM 工具从表中选择并返回整个对象，并限制了通过经典 SQL 编程只能选择特定列的选项。当然，当我看到这个例子时，我对此表示怀疑，但尽管如此，我仍然不断问自己一个问题：数据库是只返回选定的列，还是返回整个对象，将列过滤留给ORM工具？

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

从这个例子中，他们还有一个叫做 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?

每次我只想通过 LINQ 从表中选择几列时，是否都需要创建自己的类？

回答by Sergey Berezovskiy

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.

仅传输选定的列。使用普通 SQL 和使用 LINQ to SQL 没有区别。当您执行 LINQ 查询时，它会转换为纯 SQL 并执行。然后结果映射到您的对象。

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

您可以使用 SQL Server Profiler 查看在服务器上生成和执行的查询。您也可以使用LINQPad来查看将从您的查询中生成什么 SQL。在您的情况下，无论您使用 BlobDetails 还是匿名对象，查询都将相同：

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

回答by mipe34

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

我认为您的第一个问题的答案已经在您提到的 POST 中。然而...

If your BlobDetailsis not LINQ entity you can simply use it in your selectstatement to define (shrink) your projection attributes. For example:

如果您BlobDetails不是 LINQ 实体，您可以简单地在您的select语句中使用它来定义（缩小）您的投影属性。例如：

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 ....

将编译为 SQL 查询，如 SELECT Id, Size FROM Blob ....

But if BlobDetailsis 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.

但是如果BlobDetails是 LINQ 实体，您将需要使用该AsEnumerable()hack 否则您将获得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!

正如@Chris Pitman 在他的评论中所说，这种AsEnumerable()方法可能会造成严重的瓶颈，因为在应用投影之前，整个表将被加载到内存中。所以不推荐！

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.

您需要为要在方法范围之外轻松使用的对象创建自定义类。匿名对象的属性仅在已声明的范围内可见，并且匿名对象只能强制转换为 type object。

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

因此，如果您想从方法返回匿名对象，则返回类型必须是可枚举的，object或者dynamic如@xeondev 在他的评论中所述。

回答by iamkrillin

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

当您进行投影时，LINQ 确实只选择了这些列，并且没有什么可以阻止您根据需要实现它。所以在你的示例代码中

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.

仅选择 b.id、b.size、b.signature 和 b.rowversion。您可以使用 sql profiler 或您的调试器来验证这一点，我似乎记得还有一个函数可以在数据上下文上调用以获取运行的最后一个查询。

回答by Esteban Elverdin

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.

因此，如果您要像示例建议的那样在外部范围内使用 linq 查询的结果，强烈建议您创建自己的类。