Selecting only a few columns from a table

Question

I had problems using c#, nhibernate and link. In the example bellow, I do a SELECT in the BrandTable, but I need only the "Name" and "Id" columns. But it always do a select with all columns of the table. Using EntityFramework, the same code bellow generates a select with only this two columns.

How to do this in nhibernate?

 using (ISession session = MyConnection.GetCurrentSession())
        {
            var brands = from b in session.QueryOver<BrandTable>().List()
                                 orderby b.Name
                                 select new Brand {Id = b.id, Name = b.Name};

            return brands.ToList();
        }

You are ordering and applying projection in memory, that's why is doesn't work (the .List() method selects all records. — Vasea, Jul 22 '11 at 10:34

score 6 · Answer 1 · answered Jul 22 '11 at 10:37

You can't use query comprehensions with QueryOver, because it is not a Linq provider. In your example you're actually selecting all records, and then using LINQ to Objects. Add the NHibernate.Linq namespace to your file and rewrite the query as

from b in session.Query<BrandTable>()
orderby b.Name
select new Brand {Id = b.id, Name = b.Name};

score 4 · Answer 2 · edited May 23 '17 at 11:51

This is an example using projection:

List results = session.CreateCriteria<BrandTable>()
.SetProjection( Projections.ProjectionList()
     .Add( Projections.Id(), "Id" )
     .Add( Projections.Property("Name"), "Name" )
)
.SetResultTransformer(Transformers.AliasToBean<BrandTable>()); // edit - don't forget the result transformer! 
.List();

here an example using QueryOver:

NHibernate QueryOver select entity and aggregates

[edit] also, there is currently a bug when caching icriteria projections. (if you try to cache the query, you get an exception) https://nhibernate.jira.com/browse/NH-1090 [/edit]

Releted posts:

NHibernate Criteria: howto exclude certain mapped properties/collections?

Only retrieve specific columns when using Critera queries?

LINQ-NHibernate - Selecting only a few fields (including a Collection) for a complex object

to make your queries refactor-safe (no 'magic strings'), you can implement something like so:

public static class ObjectExtensions
{
    public static string NameOf<T>(this T target, Expression<Func<T, object>> propertyExpression)
    {
        MemberExpression body = null;
        if (propertyExpression.Body is UnaryExpression)
        {
            var unary = propertyExpression.Body as UnaryExpression;
            if (unary.Operand is MemberExpression)
                body = unary.Operand as MemberExpression;
        }
        else if (propertyExpression.Body is MemberExpression)
        {
            body = propertyExpression.Body as MemberExpression;
        }
        if (body == null)
            throw new ArgumentException("'propertyExpression' should be a member expression");

        // Extract the right part (after "=>")
        var vmExpression = body.Expression as ConstantExpression;

        // Extract the name of the property
        return body.Member.Name;
    }

}

using it like this:

MyEntity entity = null; // don't need a 'valid' instance.
string propName = entity.NameOf(x => x.SomePropertyName);

This second example does not works. It sends all columns to query. — Beetlejuice, Jul 21 '11 at 13:38
The second example doesn't work because it is filtering the collection in memory — Vasea, Jul 22 '11 at 10:34

score 2 · Answer 3 · answered Jul 21 '11 at 13:47

You can also do this with QueryOver in NH. Below is taken from http://nhforge.org/blogs/nhibernate/archive/2009/12/17/queryover-in-nh-3-0.aspx

Projections

Simple projections of the properties of the root type can be added using the .Select method which can take multiple Lambda Expression arguments:

IList selection =
    session.QueryOver<Cat>()
        .Select(
            c => c.Name,
            c => c.Age)
        .List<object[]>();

Because this query no longer returns a Cat, the return type must be explicitly specified. If a single property is projected, the return type can be specified using:

IList<int> ages =
    session.QueryOver<Cat>()
        .Select(c => c.Age)
        .List<int>();

There are several more examples of how to use projections in the link above.

score 0 · Answer 4 · answered Dec 28 '15 at 21:05

You can select some column for one domain using the following step:

Create a small class with the fields that you wants: Example:


 private class LeadInformation
  {
            public string Id { get; set; }
            public DateTime AdmissionDate { get; set; }
  }

Query the domain entity where do you want to extract the information


 IQueryable leads =
                repository.Query()
                .Where(x => x.AdmissionRepUserObj.ID.ToString() == filter.UserId
                    ).Select(lead => new LeadInformation 
                     {AdmissionDate = lead.DateApplied.Value,
                     Id = lead.ID.ToString()});

In the example Lead is the entity that we want to extract the information. Observe that the destination is of the same type of the target class LeadInformation. This return a queryable list of target class LeadInformation with only two column of the source domain class.

Selecting only a few columns from a table

4 Answers4