Without ONE field, get an object from entityframework.

.net c# entity-framework sql

Question

Entity framework is what I'm using to connect to the database. I have a little issue:

One of my tables has a varbinary(MAX) column (with filestream).

I'm managing the "Data" portion using a SQL request, but EF handles the rest (metadata of the file).

I have a single piece of code that must get every file's ID, filename, GUID, modification date, etc. The "Data" field is not at all necessary for this.

Is it possible to obtain a List without having this field filled out?

something similar

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

??

I am aware that I may build anonymous objects, but because I must provide the result to a method, anonymous methods are not an option. Additionally, I don't intend to add this to a list of anonymous types before making a list of my non-anonymous types (File).

Avoiding this is the intention:

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 the anonymous type here because if you don't you'll receive an error saying that you can't build the entity or complex type "ProjectName.File" in a LINQ to Entities query.)

(For instance, this code throws the earlier exception:

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

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

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

My EF datacontext's class File is well-known.

public ObjectSet<File> Files
{
    get { return _files  ?? (_files = CreateObjectSet<File>("Files")); }
}
private ObjectSet<File> _files;
1
53
1/23/2012 6:51:27 PM

Accepted Answer

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

You want to avoid projecting, so don't. The column should naturally be a part of your object if it is mapped. Without this column, the entity is incomplete and represents a distinct data set or 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.

You cannot project to a mapped entity, as the exception states. I have highlighted the cause: EF dislikes "partial entities" and projection creates a separate data set.

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.

Simply removing a property from a designer is insufficient. Your model will become highly brittle if you don't open EDMX as XML and eliminate the associated columns from SSDL (each update from database will put your column back). Use a database view without the column instead of a table and map it instead if you don't want to map the column; however, you won't be able to input data.

as a fix for all of your issues Separate the troublesome binary column to another entity with a 1:1 relationship to your primary entity by using table division.File entity.

19
3/9/2020 5:42:32 PM

Popular Answer

I'd take the following action:

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, which EF may materialize. Given that the huge column is not required by the query, the subsequent SQL statement shouldn't include it in its result set.

You get the error NotSupportedException: A LINQ to Entities query cannot generate an entity or complex type named "ProjectName.File." as a result of your adjustments, EDIT, since you failed to map that class as an entity. You anticipate LINQ to Entities to construct the proper SQL statements when you include objects in your queries that EF is unaware of.

Another kind that does not include theVarBinary(MAX) column in its definition or use the aforementioned code.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow