C# 从没有一个字段的实体框架中检索对象

Retrieve an object from entityframework without ONE field

提问by J4N

I'm using entity framework to connect with the database. I've one little problem:

我正在使用实体框架来连接数据库。我有一个小问题：

I've one table which have one varbinary(MAX) column(with filestream).

我有一张表，其中有一个 varbinary(MAX) 列（带文件流）。

I'm using SQL request to manage the "Data" part, but EF for the rest(metadata of the file).

我正在使用 SQL 请求来管理“数据”部分，但使用 EF 来管理其余部分（文件的元数据）。

I've one code which has to get all files id, filename, guid, modification date, ... of a file. This doesn't need at all the "Data" field.

我有一个代码，它必须获取文件的所有文件 id、文件名、guid、修改日期……。这根本不需要“数据”字段。

Is there a way to retrieve a List but without this column filled?

有没有办法检索列表但没有填充此列？

Something like

就像是

context.Files.Where(f=>f.xyz).Exclude(f=>f.Data).ToList();

??

??

I know I can create anonymous objects, but I need to transmit the result to a method, so no anonymous methods. And I don't want to put this in a list of anonymous type, and then create a list of my non-anonymous type(File).

我知道我可以创建匿名对象，但我需要将结果传输给一个方法，所以没有匿名方法。我不想把它放在匿名类型的列表中，然后创建一个我的非匿名类型（文件）的列表。

The goal is to avoid this:

目标是避免这种情况：

using(RsSolutionsEntities context = new RsSolutionsEntities())
{
    var file = context.Files
        .Where(f => f.Id == idFile)
        .Select(f => new {
            f.Id, f.MimeType, f.Size, f.FileName, f.DataType,
            f.DateModification, f.FileId
        }).FirstOrDefault();

    return new File() {
        DataType = file.DataType, DateModification = file.DateModification,
        FileId = file.FileId, FileName = file.FileName, Id = file.Id,
        MimeType = file.MimeType, Size = file.Size
    };
}

(I'm using here the anonymous type because otherwise you will get a NotSupportedException: The entity or complex type 'ProjectName.File' cannot be constructed in a LINQ to Entities query.)

（我在这里使用匿名类型，否则您将得到 NotSupportedException：实体或复杂类型“ProjectName.File”无法在 LINQ to Entities 查询中构造。）

(e.g. this code throw the previous exception:

（例如，此代码抛出先前的异常：

File file2 = context.Files.Where(f => f.Id == idFile)
  .Select(f => new File() {Id = f.Id, DataType = f.DataType}).FirstOrDefault();

and "File" is the type I get with a context.Files.ToList(). This is the good class:

“文件”是我用context.Files.ToList(). 这是好课：

using File = MyProjectNamespace.Common.Data.DataModel.File;

File is a known class of my EF datacontext:

文件是我的 EF 数据上下文的已知类：

public ObjectSet<File> Files
{
    get { return _files  ?? (_files = CreateObjectSet<File>("Files")); }
}
private ObjectSet<File> _files;

采纳答案by Ladislav Mrnka

Is there a way to retrieve a List but without this column filled?

有没有办法检索列表但没有填充此列？

Not without projection which you want to avoid. If the column is mapped it is natural part of your entity. Entity without this column is not complete - it is different data set = projection.

并非没有您想要避免的投影。如果该列已映射，则它是实体的自然组成部分。没有此列的实体是不完整的 - 它是不同的数据集 = 投影。

I'm using here the anonymous type because otherwise you will get a NotSupportedException: The entity or complex type 'ProjectName.File' cannot be constructed in a LINQ to Entities query.

我在这里使用匿名类型，否则您将收到 NotSupportedException：无法在 LINQ to Entities 查询中构造实体或复杂类型“ProjectName.File”。

As exception says you cannot project to mapped entity. I mentioned reason above - projection make different data set and EF don't like "partial entities".

作为例外，您不能投影到映射实体。我上面提到了原因 - 投影制作不同的数据集，EF 不喜欢“部分实体”。

Error 16 Error 3023: Problem in mapping fragments starting at line 2717:Column Files.Data in table Files must be mapped: It has no default value and is not nullable.

错误 16 错误 3023：映射从第 2717 行开始的片段时出现问题：必须映射表文件中的列 Files.Data：它没有默认值且不可为空。

It is not enough to delete property from designer. You must open EDMX as XML and delete column from SSDL as well which will make your model very fragile (each update from database will put your column back). If you don't want to map the column you should use database view without the column and map the view instead of the table but you will not be able to insert data.

从设计器中删除属性是不够的。您必须以 XML 格式打开 EDMX 并从 SSDL 中删除列，这将使您的模型非常脆弱（数据库的每次更新都会将您的列放回原处）。如果您不想映射列，则应使用没有列的数据库视图并映射视图而不是表，但您将无法插入数据。

As a workaround to all your problems use table splittingand separate the problematic binary column to another entity with 1 : 1 relation to your main Fileentity.

作为解决所有问题的方法，请使用表拆分并将有问题的二进制列分离到另一个与您的主File实体具有 1:1 关系的实体。

回答by Yuck

I'd do something like this:

我会做这样的事情：

var result = from thing in dbContext.Things
             select new Thing {
                 PropertyA = thing.PropertyA,
                 Another = thing.Another
                 // and so on, skipping the VarBinary(MAX) property
             };

Where Thingis your entity that EF knows how to materialize. The resulting SQL statement shouldn't include the large column in its result set, since it's not needed in the query.

ThingEF 知道如何实现的实体在哪里。生成的 SQL 语句不应在其结果集中包含大列，因为查询中不需要它。

EDIT: From your edits, you get the error NotSupportedException: The entity or complex type 'ProjectName.File' cannot be constructed in a LINQ to Entities query.because you haven't mapped that class as an entity. You can'tinclude objects in LINQ to Entities queries that EF doesn't know about and expect it to generate appropriate SQL statements.

编辑：从您的编辑中，您会收到错误NotSupportedException：无法在 LINQ to Entities 查询中构造实体或复杂类型“ProjectName.File”。因为您尚未将该类映射为实体。您不能在 LINQ to Entities 查询中包含 EF 不知道的对象并期望它生成适当的 SQL 语句。

You can map another type that excludes the VarBinary(MAX)column in its definition or use the code above.

您可以映射VarBinary(MAX)在其定义中排除列的另一种类型或使用上面的代码。

回答by Jeremy Danyow

you can do this:

你可以这样做：

var files = dbContext.Database.SqlQuery<File>("select FileId, DataType, MimeType from Files");

or this:

或这个：

var files = objectContext.ExecuteStoreQuery<File>("select FileId, DataType, MimeType from Files");

depending on your version of EF

取决于您的 EF 版本

回答by Andrew

I'd like to share my attempts to workaround this problem in case somebody else is in the same situation.

如果其他人处于相同的情况，我想分享我解决此问题的尝试。

I started with what Jeremy Danyowsuggested, which to me is the less painful option.

我从Jeremy Danyow 的建议开始，这对我来说是不那么痛苦的选择。

// You need to include all fields in the query, just make null the ones you don't want.
var results = context.Database.SqlQuery<myEntity>("SELECT Field1, Field2, Field3, HugeField4 = NULL, Field5 FROM TableName");

In my case, I needed a IQueryable<>result object so I added AsQueryable()at the end. This of course let me add calls to .Where, .Take, and the other commands we all know, and they worked fine. But there's a caveat:

就我而言，我需要一个IQueryable<>结果对象，所以我AsQueryable()在最后添加。这当然让我添加对.Where、.Take和我们都知道的其他命令的调用，并且它们运行良好。但有一个警告：

The normal code (basically context.myEntity.AsQueryable()) returned a System.Data.Entity.DbSet<Data.DataModel.myEntity>, while this approach returned System.Linq.EnumerableQuery<Data.DataModel.myEntity>.

正常代码（基本上context.myEntity.AsQueryable()）返回 a System.Data.Entity.DbSet<Data.DataModel.myEntity>，而这种方法返回System.Linq.EnumerableQuery<Data.DataModel.myEntity>。

Apparently this means that my custom query gets executed "as is" as soon as needed and the filtering I added later is done afterwards and not in the database.

显然，这意味着我的自定义查询会在需要时“按原样”执行，并且我稍后添加的过滤是在之后完成的，而不是在数据库中完成。

Therefore I tried to mimic Entity Framework's object by using the exact query EF creates, even with those [Extent1]aliases, but it didn't work. When analyzing the resulting object, its query ended like

因此，我尝试通过使用 EF 创建的确切查询来模拟实体框架的对象，即使使用这些[Extent1]别名也是如此，但它不起作用。在分析结果对象时，它的查询结束了

FROM [dbo].[TableName] AS [Extent1].Where(c => ...

FROM [dbo].[TableName] AS [Extent1].Where(c => ...

instead of the expected

而不是预期的

FROM [dbo].[TableName] AS [Extent1] WHERE ([Extent1]...

FROM [dbo].[TableName] AS [Extent1] WHERE ([Extent1]...

Anyway, this works, and as long as the table is not huge, this method will be fast enough. Otherwise you have no option than to manually add the conditions by concatenating strings, like classic dynamic SQL. A very basic example in case you don't know what I'm talking about:

无论如何，这是有效的，只要表不是很大，这种方法就足够快了。否则，您别无选择，只能通过连接字符串来手动添加条件，例如经典的动态 SQL。一个非常基本的例子，以防你不知道我在说什么：

string query = "SELECT Field1, Field2, Field3, HugeField4 = NULL, Field5 FROM TableName";
if (parameterId.HasValue)
    query += " WHERE Field1 = " + parameterId.Value.ToString();
var results = context.Database.SqlQuery<myEntity>(query);

In case your method sometimes needs this field, you can add a boolparameter and then do something like this:

如果您的方法有时需要此字段，您可以添加一个bool参数，然后执行以下操作：

IQueryable<myEntity> results;
if (excludeBigData)
    results = context.Database.SqlQuery<myEntity>("SELECT Field1, Field2, Field3, HugeField4 = NULL, Field5 FROM TableName").AsQueryable();
else
    results = context.myEntity.AsQueryable();

If anyone manages to make the Linq extensions work properly like if it was the original EF object, please comment so I can update the answer.

如果有人设法使 Linq 扩展正常工作，就像它是原始 EF 对象一样，请发表评论，以便我可以更新答案。

回答by Sean

I had this requirement because I have a Documententity which has a Contentfield with the content of the file, i.e. some 100MB in size, and I have a Search function that I wanted to return the rest of the columns.

我有这个要求，因为我有一个Document实体，它有一个Content包含文件内容的字段，即大约 100MB 的大小，并且我有一个搜索功能，我想返回其余的列。

I chose to use projection:

我选择使用投影：

IQueryable<Document> results = dbContext.Documents.Include(o => o.UploadedBy).Select(o => new {
    Content = (string)null,
    ContentType = o.ContentType,
    DocumentTypeId = o.DocumentTypeId,
    FileName = o.FileName,
    Id = o.Id,
    // etc. even with related entities here like:
    UploadedBy = o.UploadedBy
});

Then my WebApi controller passes this resultsobject to a common Pagination function, which applies a .Skip, .Takeand a .ToList.

然后我的WebAPI控制器通过这个results目的是共同的分页功能，它适用一个.Skip，.Take和一个.ToList。

This means that when the query gets executed, it doesn't access the Contentcolumn, so the 100MB data is not being touched, and the query is as fast as you'd want/expect it to be.

这意味着当查询被执行时，它不会访问该Content列，因此 100MB 的数据不会被触及，并且查询的速度与您希望/期望的一样快。

Next, I cast it back to my DTO class, which in this case is pretty much exactly the same as the entity class, so this might not be a step you need to implement, but it's follows my typical WebApi coding pattern, so:

接下来，我将它转换回我的 DTO 类，在这种情况下，它与实体类几乎完全相同，因此这可能不是您需要实现的步骤，但它遵循我典型的 WebApi 编码模式，因此：

var dtos = paginated.Select(o => new DocumentDTO
{
    Content = o.Content,
    ContentType = o.ContentType,
    DocumentTypeId = o.DocumentTypeId,
    FileName = o.FileName,
    Id = o.Id,
    UploadedBy = o.UploadedBy == null ? null : ModelFactory.Create(o.UploadedBy)
});

Then I return the DTO list:

然后我返回 DTO 列表：

return Ok(dtos);

So it uses projection, which might not fit the original poster's requirements, but if you're using DTO classes, you're converting anyway. You could just as easily do the following to return them as your actual entities:

因此它使用投影，这可能不符合原始海报的要求，但如果您使用的是 DTO 类，则无论如何您都在进行转换。您可以轻松地执行以下操作以将它们作为您的实际实体返回：

var dtos = paginated.Select(o => new Document
{
    Content = o.Content,
    ContentType = o.ContentType,
    DocumentTypeId = o.DocumentTypeId,
    //...

Just a few extra steps but this is working nicely for me.

只是一些额外的步骤，但这对我来说很好用。

回答by mikesoft

I tried this:

我试过这个：

From the edmx diagram (EF 6), I clicked the column I wanted to hide from EF and on their properties you can set their getter and setter to private. That way, for me it works.

在 edmx 图表 (EF 6) 中，我单击了我想对 EF 隐藏的列，在它们的属性上，您可以将它们的 getter 和 setter 设置为私有。这样，对我来说它有效。

I return some data which includes a User reference, so I wanted to hide the Password field even though it's encrypted and salted, I just didn't want it on my json, and I didn't want to do a:

我返回了一些包含用户引用的数据，所以我想隐藏密码字段，即使它是加密和加盐的，我只是不想在我的 json 中使用它，我不想做：

Select(col => new {}) 

because that's a pain to create and maintain, especially for big tables with a lot of relationships.

因为创建和维护很痛苦，特别是对于有很多关系的大表。

The downside with this method is that if you ever regenerate your model, you would need to modify their getter and setter again.

这种方法的缺点是，如果您重新生成模型，则需要再次修改它们的 getter 和 setter。

回答by skjagini

I'm using here the anonymous type because otherwise you will get a NotSupportedException: The entity or complex type 'ProjectName.File' cannot be constructed in a LINQ to Entities query.

我在这里使用匿名类型，否则您将收到 NotSupportedException：无法在 LINQ to Entities 查询中构造实体或复杂类型“ProjectName.File”。

var file = context.Files
        .Where(f => f.Id == idFile)
        .FirstOrDefault() // You need to exeucte the query if you want to reuse the type
        .Select(f => new {
            f.Id, f.MimeType, f.Size, f.FileName, f.DataType,
            f.DateModification, f.FileId
        }).FirstOrDefault();

And also its not a bad practice to de-normalize the table into further, i.e one with metadata and one with payload to avoid projection. Projection would work, the only issue is, need to edit any time a new column is added to the table.

而且将表进一步非规范化也不是一种坏习惯，即一个带有元数据的一个带有有效载荷以避免投影。投影会起作用，唯一的问题是，每当向表中添加新列时都需要进行编辑。

回答by HamedH

For EF Core 2 I implemented a solution like this:

对于 EF Core 2，我实现了这样的解决方案：

var files = context.Files.AsNoTracking()
                         .IgnoreProperty(f => f.Report)
                         .ToList();

The base idea is to turn for example this query:

基本思想是将例如这个查询：

SELECT [f].[Id], [f].[Report], [f].[CreationDate]
FROM [File] AS [f]

into this:

进入这个：

SELECT [f].[Id], '' as [Report], [f].[CreationDate]
FROM [File] AS [f]

you can see the full source code in here: https://github.com/aspnet/EntityFrameworkCore/issues/1387#issuecomment-495630292

你可以在这里看到完整的源代码：https: //github.com/aspnet/EntityFrameworkCore/issues/1387#issuecomment-495630292