How do omit a column(s) from the results of a LINQ query using Entity Framework 5

Question

I have image data stored in a database. In addition to the image data there are fields which include meta information (image_size, description, name, etc). I would like to return only the meta information for the image and not the actual image byte data itself. I am hoping that there is a way to return all fields EXCEPT FOR the image data without having to select each column individually into an anonymous object. I am using Entity Framework 5.0 and Code First, so no designers or EDMX's or anything.

I saw this post ... selecting-all-but-one-property-using-linq-entity-framework ... but the answer is focused on a solution using the designer and my code is all code first with no designer involved. Also when I did a search on "deferring" individual columns I am directed to a page on MS site that deals with deferring loading on the class instead of the individual fields, besides it says it's information is out of date (presumably 5.0 does it differently?).

Any help is, as always, rewarded with much appreciation and a ceremony of incense and chanting. Thanks!!

Possible duplicate of http://stackoverflow.com/questions/5756890/lazy-load-for-nonnavigation-property-in-ef-4-1 — Wiktor Zychla, Oct 17 '13 at 16:11
You could create a new table with only image data and link them with the same primary key. Then your property becomes a navigational property and it can be lazily-loaded. — jamesSampica, Oct 17 '13 at 16:12
@Wiktor Sorry we posted so close that I didn't see your link. — jamesSampica, Oct 17 '13 at 16:18

Moho · Accepted Answer · 2014-02-03T20:13:47.653

The below creates a single table but allows for a reference/navigation property to a field you may not always wish to load (in this case, a byte array called Data). This is known as table splitting.

Key notes are to have both entities map to the same table (duh) using TableAnnotion and share the same primary key, which is used as the foreign key for the nav properties to one another.

    [Table("Document")]
    public class Document
    {
        [Key]
        [ForeignKey("Data")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int DocumentId { get; set; }

        public string FileName { get; set; }
        public DateTime Created { get; set; }

        public virtual DocumentData Data { get; set; }
    }

    [Table("Document")]
    public class DocumentData
    {
        [Key]
        [ForeignKey("Document")]
        public int DocumentId { get; set; }

        public byte[] Data { get; set; }

        public virtual Document Document { get; set; }
    }

    public class DocEntities : DbContext
    {
        public DocEntities()
            : base("name=TestEntitiesCodeFirst")
        {
        }

        public DbSet<Document> Documents { get; set; }
    }

    static void Main(string[] args)
    {
        using (var db = new InheritTest.DocEntities())
        {
            var doc = new Document()
            {
                Created = DateTime.Now,
                FileName = "Abc.txt",
                Data = new DocumentData()
                {
                    Data = new byte[] { 0x50, 0x51, 0x52, 0x53 }
                }
            };

            db.Documents.Add(doc);

            db.SaveChanges();
        }

        using (var db = new InheritTest.DocEntities())
        {
            db.Configuration.LazyLoadingEnabled = false;

            var doc = db.Documents.First();

            if (doc.Data == null)
            {
                Console.WriteLine("doc.Data is null");
            }

            db.Entry(doc).Reference(p => p.Data).Load();

            if (doc.Data != null)
            {
                Console.WriteLine("doc.Data is not null");
                Console.WriteLine(doc.Data.Data.Length);
            }
        }

        var input = Console.ReadLine();
    }

Resulting table:

CREATE TABLE [dbo].[Document](
    [DocumentId] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](max) NULL,
    [Created] [datetime] NOT NULL,
    [Data] [varbinary](max) NULL,
 CONSTRAINT [PK_dbo.Document] PRIMARY KEY CLUSTERED 
(
    [DocumentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Thank you for the detail. I think that this approach is preferable to actually splitting the tables and having to mess around with managing more relationships. — Gary O. Stenstrom, Oct 17 '13 at 17:29

How do omit a column(s) from the results of a LINQ query using Entity Framework 5

1 Answers1