CODEWITHSUNDEEP
c#sqlentity-framework
SamTech
SamTech

Reputation: 1313

Entity Framework: Skip few columns or select as null from related entities

I have three entities as below

public partial class Ticket
{
    public int TicketId { get; set; }
    ...
    public virtual ICollection<TicketComment> TicketComments { get; set; }
}

public partial class TicketComment
{
    public int CommentId { get; set; }
    public int TicketId { get; set; }
    ...
    public virtual ICollection<CommentAttachment> CommentAttachments { get; set; }
    public virtual Ticket Ticket { get; set; }
}

public partial class CommentAttachment
{
    public int FileId { get; set; }
    public int CommentID { get; set; }
    public string FileName { get; set; }
    public int FileSize { get; set; }
    public byte[] FileContents { get; set; }  // holds large data

    public virtual TicketComment TicketComment { get; set; }
}

Here each Ticket can have multiple comments and each Comment can have 1 or 0 attachment. I am trying to eager load all related entities for a given ticket with following code

var query = context.Tickets.Where(t => t.TicketId == ticketid)
             .Include(t => t.TicketComments.Select(c => c.CommentAttachments));

It is doing the job correctly.

Only the problem is that, it is also loading byte[] FileContents, which often has quite large data. I want to avoid it.

Is there any way i can select NULL for FileContents or skip this column at all?

I had tried with following

var query = context.Tickets.Where(t => t.TicketId == ticketid)
            .Include(t => t.TicketComments
                .Select(c => c.CommentAttachments
                    .Select(ca => new CommentAttachment()
                    {
                        CommentID = ca.CommentID,
                        FileContents = null,
                        FileId = ca.FileId,
                        FileName = ca.FileName,
                        FileSize = ca.FileSize
                    })));

But it is giving error

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

Any ideas to avoid loading FileContents column ?

Upvotes: 3

Views: 867

Answers (2)

Pynt
Pynt

Reputation: 2288

Returning a null getter but allowing setter to function in EF 6.0 and OData v4.

I've come across this same issue. The accepted answer is definitely a workable option and I almost implemented it today, but I was hoping to perform the task in code. My model differs slightly than what you have available in that a byte[] Timestamp column is available. Posting this workaround in the event that it can assist someone later.

For my case, the byte[] FileContents serves as a backup to a directory repository where we use the database to rebuild the directory structure and file contents if the front-end is wiped out for various reasons. So essentially we only POST/SET FileContents, we never read it through the front end program.

public partial class CommentAttachment
{
     //required for overriding get/set auto-property
     private byte[] _FileContents; 

     public int FileId { get; set; }
     public int CommentID { get; set; }
     public string FileName { get; set; }
     public int FileSize { get; set; }
     public byte[] FileContents 
     { 
          get
          {
               return Timestamp != null ? null : _FileContents;
          } 
          set
          {
               _FileContents = value;
          }
     }  // holds large data

     public virtual TicketComment TicketComment { get; set; }
     //Concurrency Token - triggered on create or update
     public byte[] Timestamp { get; set; }
 }

The above allowed our database table to stay the same, only the EF POCO changed. When creating a CommentAttachment record the Timestamp field is null/doesn't exist because it is triggered by the database; this allows _FileContents to be populated. When selecting a record, Timestamp is populated with a value from the database so FileContents is set to null to avoid querying the large data sets.

PUT/PATCH/POST works fine with this method when applied to OData v4 as well due to Timestamp not being passed for insert/update as it is a database triggered population.

Upvotes: 0

Bassam Alugili
Bassam Alugili

Reputation: 17033

public partial class CommentAttachment
{
    public int FileId { get; set; }
    public int CommentID { get; set; }
    public string FileName { get; set; }
    public int FileSize { get; set; }

    public virtual TicketComment TicketComment { get; set; }
}

public class FileContent
{
     FileContentId {get;set;}
     public int FileId { get; set; } // HERE IS THE FORGEIN KEY YOU HAVE TO UPDATE IT manually
     public byte[] FileContents { get; set; }  // holds large data
}

In this way you can load the FileContent only by need you have the CommentAttachment Id and you can include it any time.

Upvotes: 1

Related Questions