Retrieve an object from entityframework without ONE field

Question

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

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

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.

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

(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:

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

File is a known class of my EF datacontext:

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

Solution 1

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.

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

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.

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.

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

Solution 2

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

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't include objects in LINQ to Entities queries that EF doesn't know about and expect it to generate appropriate SQL statements.

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

Solution 3

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

Solution 4

I had this requirement because I have a Document entity which has a Content field 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.

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 results object to a common Pagination function, which applies a .Skip, .Take and a .ToList.

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

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:

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:

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:

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.

Solution 5

For EF Core 2 I implemented a solution like this:

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

View more solutions