How to: Read a Subset of Columns

The How to: Query Data topic demonstrates you some basic techniques for reading, filtering and ordering data. It shows you how to retrieve full entities (all properties/columns) from the database.

In this topic, you will learn how to retrieve only a subset of data (specific properties/columns) from a table:

How to: Retrieve Only Some of the Properties of a Domain Class

The following example demonstrates you how to retrieve only some of the properties of a domain class. The rest of the properties will remain null (not initialized).

Suppose, you have the following domain class:

public partial class Category
{
    public virtual int CategoryID { get; set; }
    public virtual string CategoryName { get; set; }
    public virtual string ImageFileName { get; set; }
}

Partial Public Class Category
    Public Overridable Property CategoryID() As Integer
    Public Overridable Property CategoryName() As String
    Public Overridable Property ImageFileName() As String
End Class

To retrieve only specific properties:

using ( FluentModel dbContext = new FluentModel() )
{
   List<Category> categories = (from category in dbContext.Categories
                                select new Category
                                {
                                   CategoryID = category.CategoryID,
                                   ImageFileName = category.ImageFileName
                                }).ToList();
}

Using dbContext As New FluentModel()
 Dim categories As List(Of Category) = (
  From category In dbContext.Categories
  Select New Category With {
   .CategoryID = category.CategoryID,
   .ImageFileName = category.ImageFileName}).ToList()
End Using

Only the CategoryID and ImageFileName properties will be initialized. The CategoryName property will be null.

Another thing you should be aware of is that the initialized Category objects will not be managed by the context. It is true that the Category class is part of the model. However, with this query you will not retrieve full entities. Instead, you will get projections, which cannot be change-tracked, and therefore cannot be updated. In other words, if you try to modify a category and invoke the SaveChanges method, nothing will happen.

How to: Execute a Query that Returns an Anonymous Type

Alternatively, you could execute queries that return a collection of instances of an anonymous type. For example:

using ( FluentModel dbContext = new FluentModel() )
{
   var query = from category in dbContext.Categories
               select new
               {
                   Id = category.CategoryID,
                   Name = category.CategoryName
               };
}

Using dbContext As New FluentModel()
 Dim query = From category In dbContext.Categories
    Select New With {
     Key .Id = category.CategoryID,
     Key .Name = category.CategoryName}
End Using