I had problems using C #, nhibernate and link. In the example below, I am doing SELECT on BrandTable, but I only need the "Name" and "Id" columns. But he always makes a choice with all the columns of the table. Using EntityFramework, the same code generates a selection with only these two columns.
How to do it 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 cannot use query understanding with QueryOver because it is not a Linq provider. In your example, you actually select all the records, and then use LINQ to Objects. Add the NHibernate.Linq namespace to the file and rewrite the query as
from b in session.Query<BrandTable>() orderby b.Name select new Brand {Id = b.id, Name = b.Name};
This is an example of 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 is an example of using QueryOver:
NHibernate QueryOver select entity and aggregates
[edit] In addition, there is currently an error while caching icriteria forecasts. (if you try to cache the request, you will get an exception) https://nhibernate.jira.com/browse/NH-1090 [/ Edit]
Repeated Messages:
NHibernate criteria: how to exclude certain display properties / collections?
Only load specific columns when using Critera queries?
LINQ-NHibernate - select only a few fields (including assembly) for a complex object
to make your queries refactorically safe (no “magic lines”), you can implement something like this:
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 as follows:
MyEntity entity = null; // don't need a 'valid' instance. string propName = entity.NameOf(x => x.SomePropertyName);
You can also do this with QueryOver in NH. Below is taken http://nhforge.org/blogs/nhibernate/archive/2009/12/17/queryover-in-nh-3-0.aspx
Projections
Simple projections of root type properties can be added using the .Select method, which can take several arguments of a Lambda expression:
IList selection = session.QueryOver<Cat>() .Select( c => c.Name, c => c.Age) .List<object[]>();
Since this query no longer returns 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 using forecasts in the link above.
You can select one column for one domain using the following step:
private class LeadInformation { public string Id { get; set; } public DateTime AdmissionDate { get; set; } }
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 object we want to retrieve. Note that the destination has the same LeadInformation type of the target class. This returns the requested LeadInformation list of the target class with only two columns of the source domain class.