使用EF代码首先从数据库中选择特定列 [英] Select Specific Columns from Database using EF Code First

问题描述

我们有一个客户非常大的表格，超过 500 列（我知道有人这样做）。

We have a customer very large table with over 500 columns (i know someone does that!)

这些列中有许多是事实其他表的外键。

Many of these columns are in fact foreign keys to other tables.

我们还要求热切加载某些相关表。

We also have the requirement to eager load some of the related tables.

Linq到SQL或Dynamic Linq有什么办法可以指定从数据库中检索哪些列？
我正在寻找一个实际上对生成的SQL语句产生这种影响的linq语句：

Is there any way in Linq to SQL or Dynamic Linq to specify what columns to be retrieved from the database? I am looking for a linq statement that actually HAS this effect on the generated SQL Statement:

SELECT Id, Name FROM Book

当我们运行由EF生成的reguar查询时，SQL Server会抛出一个您已达到的错误在查询中可以选择的最大列数!!!

When we run the reguar query generated by EF, SQL Server throws an error that you have reached the maximum number of columns that can be selected within a query!!!

任何帮助都非常感谢！

是的，这是这种情况，表有500列，并且自引用我们的工具自动加载第一级关系，并且这可以对可以是的列数的SQL限制查询。

Yes exactly this is the case, the table has 500 columns and is self referencing our tool automatically eager loads the first level relations and this hits the SQL limit on number of columns that can be queried.

我希望我可以设置为只加载相关实体的有限列，如Id和Name（用于UI中的查看记录用户）

I was hoping that I can set to only load limited columns of the related Entities such as Id and Name (which is used in the UI to view the record to user)

我猜另一个选择是控制什么FK列应该加载。但是，对于具有二进制或ntext列的表仍然存在问题，您可能不希望加载所有时间。

I guess the other option is to control what FK columns should be eager loaded. However this still remains problem for tables that has a binary or ntext column which you may not want to load all the times.

有没有办法挂钩多个模型（实体）到Code First的同一张表？我们尝试这样做，我觉得努力失败了。

Is there a way to hook multiple models (Entities) to the same table in Code First? We tried doing this I think the effort failed miserably.

推荐答案

是的，您只能使用投影返回列的子集：

Yes you can return only subset of columns by using projection:

var result = from x in context.LargeTable
             select new { x.Id, x.Name };

问题：投影和渴望加载不一起工作。一旦开始使用投影或自定义连接，您正在更改查询的形状，并且不能使用 Include （EF将忽略它）。在这种情况下，唯一的方法是在预计的结果集中手动包含关系：

The problem: projection and eager loading doesn't work together. Once you start using projections or custom joins you are changing shape of the query and you cannot use Include (EF will ignore it). The only way in such scenario is to manually include relations in the projected result set:

var result = from x in context.LargeTable
             select new {
                 Id = x.Id,
                 Name = x.Name,
                 // You can filter or project relations as well
                 RelatedEnitites = x.SomeRelation.Where(...) 
             };

您还可以投影到特定类型，但是不能映射特定类型（因此您不能从我的样品项目到 LargeTable 实体）。对于映射实体的投影只能在Linq对象中的实体数据上完成。

You can also project to specific type BUT that specific type must not be mapped (so you cannot for example project to LargeTable entity from my sample). Projection to the mapped entity can be done only on materialized data in Linq-to-objects.

编辑：

EF有什么误会呢？ EF在实体之上工作 - 实体是您映射的。如果您将500列映射到实体，则EF只要按照您定义的那样使用该实体。这意味着查询加载实体和持久保存实体。

There is probably some misunderstanding how EF works. EF works on top of entities - entity is what you have mapped. If you map 500 columns to the entity, EF simply use that entity as you defined it. It means that querying loads entity and persisting saves entity.

为什么这样工作？实体被认为是原子数据结构，其数据只能被加载和跟踪一次 - 这是将数据库正确保存更改的关键功能。这并不意味着你不应该只加载列的一部分，如果你需要它，但你应该明白，加载子集的列没有定义你的原始实体 - 它被认为是对你的实体数据的任意视图。该视图未被跟踪，并且不能在没有额外的努力的情况下持续回到数据库（仅因为EF不包含有关投影原点的任何信息）。

Why it works this way? Entity is considered as atomic data structure and its data can be loaded and tracked only once - that is a key feature for ability to correctly persist changes back to the database. It doesn't mean that you should not load only subset of columns if you need it but you should understand that loading subset of columns doesn't define your original entity - it is considered as arbitrary view on data in your entity. This view is not tracked and cannot be persisted back to database without some additional effort (simply because EF doesn't hold any information about the origin of the projection).

EF还会对实体映射的能力增加一些限制。

EF also place some additional constraints on the ability to map the entity

• 每个表格通常只能映射一次。为什么？再次，因为映射表多次到不同的实体可能会破坏正确保存这些实体的能力 - 例如，如果任何非密钥列被映射两次，并且您将两个实体的实例映射到同一个记录，您将使用哪些映射值保存更改？


• 有两个例外，允许您多次映射表
  
  
  
  
  • 每层次继承的表 - 这是一个映射，其中表可以包含在继承层次结构中定义的多个实体类型的记录。映射到层次结构中基础实体的列必须由所有实体共享。每个派生实体类型都可以将其自己的列映射到其特定属性（其他实体类型具有这些列始终为空）。不可能在多个实体之间共享派生属性的列。还必须有一个额外的列，称为标识符，告诉EF哪个实体类型存储在记录中 - 此列不能作为属性映射，因为它已经映射为类型鉴别器。
  
  
  • 表拆分 - 这是单表映射限制的直接解决方案。它允许您将表拆分成多个具有一些约束的实体：
    
    
    
    
    • 实体之间必须有一对一的关系。您有一个中央实体用于加载核心数据，所有其他实体可通过此实体的导航属性访问。渴望加载，延迟加载和显式加载正常工作。
    
    
    • 关系是真正的，所以这两个部分或关系必须始终存在。
    
    
    • 实体不得共享除键之外的任何属性 - 此约束将解决初始问题，因为每个可修改属性只映射一次
    
    
    • 每个实体从分割表必须具有映射关键字属性
    
    
    • 插入需要填充对象图形，因为其他实体可以包含映射的必需列
    
    
    • Each table can be normally mapped only once. Why? Again because mapping table multiple times to different entities can break ability to correctly persist those entities - for example if any non-key column is mapped twice and you load instance of both entities mapped to the same record, which of mapped values will you use during saving changes?
    • There are two exceptions which allow you mapping table multiple times
      • Table per hierarchy inheritance - this is a mapping where table can contains records from multiple entity types defined in inheritance hierarchy. Columns mapped to the base entity in the hierarchy must be shared by all entities. Every derived entity type can have its own columns mapped to its specific properties (other entity types have these columns always empty). It is not possible to share column for derived properties among multiple entities. There must also be one additional column called discriminator telling EF which entity type is stored in the record - this columns cannot be mapped as property because it is already mapped as type discriminator.
      • Table splitting - this is direct solution for the single table mapping limitation. It allows you to split table into multiple entities with some constraints:
        • There must be one-to-one relation between entities. You have one central entity used to load the core data and all other entities are accessible through navigation properties from this entity. Eager loading, lazy loading and explicit loading works normally.
        • The relation is real 1-1 so both parts or relation must always exists.
        • Entities must not share any property except the key - this constraint will solve the initial problem because each modifiable property is mapped only once
        • Every entity from the split table must have a mapped key property
        • Insertion requires whole object graph to be populated because other entities can contain mapped required columns

        Linq-to-Sql还包含将列标记为延迟加载的功能，但这功能目前在EF中不可用 - 您可以表决该功能。

        Linq-to-Sql also contains ability to mark a column as lazy loaded but this feature is currently not available in EF - you can vote for that feature.

        它导致您的优化选项

        
        
        • 使用预测获取实体只读查看
          
          
          
          
          • 可以在Linq查询中执行此操作正如我在上一部分中所展示的那样nswer
          
          
          • 您可以创建数据库视图并将其映射为新的实体
          
          
          • 在EDMX中，还可以使用定义查询或查询视图封装您的映射中的SQL或ESQL投影
          
          
          
          
          • EDMX允许你将表分解成许多实体，而不会有任何问题。
          
          
          • 代码首先允许你拆分表，但是当你将表拆分成两个以上的实体时，有一些问题（我认为它需要每个实体类型具有导航属性到所有$ / $>
            
          • EDMX allows you splitting table to many entities without any problem
          • Code first allows you splitting table as well but there are some problems when you split table to more than two entities (I think it requires each entity type to have navigation property to all other entity types from split table - that makes it really hard to use).

          这篇关于使用EF代码首先从数据库中选择特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！