You are currently browsing legacy 5.2 version of documentation. Click here to switch to the newest 6.0 version.
    We can help you with migration to the latest RavenDB
    Contact Us Now
    see on GitHub

    Project Query Results

    Instead of pulling full documents in query results you can just grab some pieces of data from documents. You can also transform the projected results. The projections are defined in LINQ with the usage of:

    Select

    The most common way to perform a query with projection is to use the Select method. You can specify what fields from a document you want to retrieve and even provide complex expression.

    Example I - Projecting Individual Fields of the Document

    // request Name, City and Country for all entities from 'Companies' collection
var results = session
    .Query<Company>()
    .Select(x => new
    {
        Name = x.Name,
        City = x.Address.City,
        Country = x.Address.Country
    })
    .ToList();
    // request Name, City and Country for all entities from 'Companies' collection
var results = await asyncSession
    .Query<Company>()
    .Select(x => new
    {
        Name = x.Name,
        City = x.Address.City,
        Country = x.Address.Country
    })
    .ToListAsync();
    from Companies
select Name, Address.City as City, Address.Country as Country

    Example II - Projecting Arrays and Objects

    var results = session
    .Query<Order>()
    .Select(x => new
    {
        ShipTo = x.ShipTo,
        Products = x.Lines.Select(y => y.ProductName),
    })
    .ToList();
    var results = await asyncSession
    .Query<Order>()
    .Select(x => new
    {
        ShipTo = x.ShipTo,
        Products = x.Lines.Select(y => y.ProductName),
    })
    .ToListAsync();
    from Orders
select ShipTo, Lines[].ProductName as Products

    Example III - Projection with Expression

    var results = (from e in session.Query<Employee>()
               select new
               {
                   FullName = e.FirstName + " " + e.LastName,
               }).ToList();
    var results = await (from e in asyncSession.Query<Employee>()
                     select new
                     {
                         FullName = e.FirstName + " " + e.LastName,
                     }).ToListAsync();
    from Employees as e
select {
    FullName : e.FirstName + " " + e.LastName
}

    Example IV - Projection with let

    var results = (from e in session.Query<Employee>()
               let format = (Func<Employee, string>)(p => p.FirstName + " " + p.LastName)
               select new
               {
                   FullName = format(e)
               }).ToList();
    var results = await (from e in asyncSession.Query<Employee>()
                     let format = (Func<Employee, string>)(p => p.FirstName + " " + p.LastName)
                     select new
                     {
                         FullName = format(e)
                     }).ToListAsync();
    declare function output(e) {
	var format = function(p){ return p.FirstName + " " + p.LastName; };
	return { FullName : format(e) };
}
from Employees as e select output(e)

    Example V - Projection with Calculation

    var results = session
    .Query<Order>()
    .Select(x => new
    {
        Total = x.Lines.Sum(l => l.PricePerUnit * l.Quantity),
    })
    .ToList();
    var results = await asyncSession
    .Query<Order>()
    .Select(x => new
    {
        Total = x.Lines.Sum(l => l.PricePerUnit * l.Quantity),
    })
    .ToListAsync();
    from Orders as o
select {
    Total : o.Lines.reduce(
        (acc , l) => acc += l.PricePerUnit * l.Quantity, 0)
}

    Example VI - Projection Using a Loaded Document

    var results = (from o in session.Query<Order>()
               let c = RavenQuery.Load<Company>(o.Company)
               select new
               {
                   CompanyName = c.Name,
                   ShippedAt = o.ShippedAt
               }).ToList();
    var results = (from o in asyncSession.Query<Order>()
               let c = RavenQuery.Load<Company>(o.Company)
               select new
               {
                   CompanyName = c.Name,
                   ShippedAt = o.ShippedAt
               }).ToListAsync();
    from Orders as o
load o.Company as c
select {
	CompanyName: c.Name,
	ShippedAt: o.ShippedAt
}

    Example VII - Projection with Dates

    var results = session
    .Query<Employee>()
    .Select(e => new
    {
        DayOfBirth = e.Birthday.Day,
        MonthOfBirth = e.Birthday.Month,
        Age = DateTime.Today.Year - e.Birthday.Year
    })
    .ToList();
    var results = await asyncSession
    .Query<Employee>()
    .Select(e => new
    {
        DayOfBirth = e.Birthday.Day,
        MonthOfBirth = e.Birthday.Month,
        Age = DateTime.Today.Year - e.Birthday.Year
    })
    .ToListAsync();
    from Employees as e 
select { 
    DayOfBirth : new Date(Date.parse(e.Birthday)).getDate(), 
    MonthOfBirth : new Date(Date.parse(e.Birthday)).getMonth() + 1,
    Age : new Date().getFullYear() - new Date(Date.parse(e.Birthday)).getFullYear() 
}

    Example VIII - Projection with Raw JavaScript Code

    var results = from e in session.Query<Employee>()
              select new
              {
                  Date = RavenQuery.Raw<DateTime>("new Date(Date.parse(e.Birthday))"),
                  Name = RavenQuery.Raw(e.FirstName, "substr(0,3)"),
              };
    var results = await (from e in asyncSession.Query<Employee>()
                     select new
                     {
                         Date = RavenQuery.Raw<DateTime>("new Date(Date.parse(e.Birthday))"),
                         Name = RavenQuery.Raw(e.FirstName, "substr(0,3)"),
                     }).ToListAsync();
    from Employees as e 
select {
    Date : new Date(Date.parse(e.Birthday)), 
    Name : e.FirstName.substr(0,3)
}

    Example IX - Projection with Metadata

    var results = (from e in session.Query<Employee>()
               select new
               {
                   Name = e.FirstName,
                   Metadata = RavenQuery.Metadata(e),
               }).ToList();
    var results = await (from e in asyncSession.Query<Employee>()
                     select new
                     {
                         Name = e.FirstName,
                         Metadata = RavenQuery.Metadata(e),
                     }).ToListAsync();
    from Employees as e 
select {
     Name : e.FirstName, 
     Metadata : getMetadata(e)
}

    SelectFields

    The SelectFields method can only be used with the Document Query. It has two overloads:

    // 1) By array of fields
IDocumentQuery<TProjection> SelectFields<TProjection>(params string[] fields);
// 2) By projection type
IDocumentQuery<TProjection> SelectFields<TProjection>();

    1) The fields of the projection are specified as a string array of field names. It also takes the type of the projection as a generic parameter. 

    var fields = new string[]{
    "Name",
    "Phone"
};

var results = session
    .Advanced
    .DocumentQuery<Company, Companies_ByContact>()
    .SelectFields<ContactDetails>(fields)
    .ToList();
    var fields = new string[]{
    "Name",
    "Phone"
};

var results = await asyncSession
    .Advanced
    .AsyncDocumentQuery<Company, Companies_ByContact>()
    .SelectFields<ContactDetails>(fields)
    .ToListAsync();
    public class Companies_ByContact : AbstractIndexCreationTask<Company>
{
    public Companies_ByContact()
    {
        Map = companies => companies
            .Select(x => new
            {
                Name = x.Contact.Name,
                x.Phone
            });

        StoreAllFields(FieldStorage.Yes); // Name and Phone fields can be retrieved directly from index
    }
}
    public class ContactDetails
{
    public string Name { get; set; }

    public string Phone { get; set; }
}
    from index 'Companies/ByContact'
select Name, Phone

    2) The projection is defined by simply passing the projection type as the generic parameter. 

    var results = session
    .Advanced
    .DocumentQuery<Company, Companies_ByContact>()
    .SelectFields<ContactDetails>()
    .ToList();
    var results = await asyncSession
    .Advanced
    .AsyncDocumentQuery<Company, Companies_ByContact>()
    .SelectFields<ContactDetails>()
    .ToListAsync();
    public class Companies_ByContact : AbstractIndexCreationTask<Company>
{
    public Companies_ByContact()
    {
        Map = companies => companies
            .Select(x => new
            {
                Name = x.Contact.Name,
                x.Phone
            });

        StoreAllFields(FieldStorage.Yes); // Name and Phone fields can be retrieved directly from index
    }
}
    public class ContactDetails
{
    public string Name { get; set; }

    public string Phone { get; set; }
}
    from index 'Companies/ByContact'
select Name, Phone

    Projection Behavior

    The SelectFields methods can also take a ProjectionBehavior parameter, which determines whether the query should retrieve indexed data or directly retrieve document data, and what to do when the data can't be retrieved. Learn more here. 

    IDocumentQuery<TProjection> SelectFields<TProjection>(ProjectionBehavior projectionBehavior,
                                                      params string[] fields);

IDocumentQuery<TProjection> SelectFields<TProjection>(ProjectionBehavior projectionBehavior);

    ProjectInto

    This extension method retrieves all public fields and properties of the type given in generic and uses them to perform projection to the requested type. You can use this method instead of using Select together with all fields of the projection class.

    Example

    var results = session.Query<Company, Companies_ByContact>()
    .ProjectInto<ContactDetails>()
    .ToList();
    var results = await asyncSession.Query<Company, Companies_ByContact>()
    .ProjectInto<ContactDetails>()
    .ToListAsync();
    public class Companies_ByContact : AbstractIndexCreationTask<Company>
{
    public Companies_ByContact()
    {
        Map = companies => companies
            .Select(x => new
            {
                Name = x.Contact.Name,
                x.Phone
            });

        StoreAllFields(FieldStorage.Yes); // Name and Phone fields can be retrieved directly from index
    }
}
    public class ContactDetails
{
    public string Name { get; set; }

    public string Phone { get; set; }
}
    from index 'Companies/ByContact' 
select Name, Phone

    OfType (As) - simple projection

    OfType or As is a client-side projection. The easiest explanation of how it works is to take the results that the server returns and map them to given type. This may become useful when querying an index that contains fields that are not available in mapped type.

    Example

    // query index 'Products_BySupplierName' 
// return documents from collection 'Products' that have a supplier 'Norske Meierier'
// project them to 'Products'
List<Product> results = session
    .Query<Products_BySupplierName.Result, Products_BySupplierName>()
    .Where(x => x.Name == "Norske Meierier")
    .OfType<Product>()
    .ToList();
    // query index 'Products_BySupplierName' 
// return documents from collection 'Products' that have a supplier 'Norske Meierier'
// project them to 'Products'
List<Product> results = await asyncSession
    .Query<Products_BySupplierName.Result, Products_BySupplierName>()
    .Where(x => x.Name == "Norske Meierier")
    .OfType<Product>()
    .ToListAsync();
    public class Products_BySupplierName : AbstractIndexCreationTask<Product>
{
    public class Result
    {
        public string Name { get; set; }
    }

    public Products_BySupplierName()
    {
        Map =
            products =>
                from product in products
                let supplier = LoadDocument<Supplier>(product.Supplier)
                select new
                {
                    Name = supplier.Name
                };
    }
}

    Note

    Projected entities (even named types) are not tracked by the session.

    Note

    If the projected fields are stored inside the index itself (FieldStorage.Yes in the index definition), then the query results will be created directly from there instead of retrieving documents in order to project.

    see on GitHub

    related articlesRelated Articles

    Session

    Querying

    Server

    Home / Docs / Client Api / Session / Querying / How To Project Query Results