How to concatenate two columns using hibernate queryover using linq
I want to concat the first and last name of an employee in the select clause, but it gives:
Could not determine member from new <> f__AnonymousType0`1 (name = Format ("{0} {1}", x.FirstName, x.LastName))
var returnData = UnitOfWork.CurrentSession.QueryOver<Employee>()
.OrderBy(x => x.Id).Asc
.SelectList(u => u.Select(x => x.Id).WithAlias(() =>
businessSectorItem.id)
.Select(x => new { name = string.Format("{0} {1}",
x.FirstName, x.LastName) })
.WithAlias(() => businessSectorItem.text))
.Where(x => (x.FirstName.IsInsensitiveLike
("%" + searchTerm + "%") ||
x.LastName.IsInsensitiveLike
("%" + searchTerm + "%")) &&
( x.Account == null || x.Account.Id ==
accountId))
.TransformUsing(Transformers
.AliasToBean<SearchEmployeeItemDto>())
.Take(limit)
.List<SearchEmployeeItemDto>();
+3
source to share
2 answers
The syntax QueryOver
will look like this:
// instead of this
.Select(x => new { name = string.Format("{0} {1}",
x.FirstName, x.LastName) })
.WithAlias(() => businessSectorItem.text))
// we should use this
.Select(
Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property<Employee>(e => e.FirstName),
Projections.Constant(" "),
Projections.Property<Employee>(e => e.LastName)
)).WithAlias(() => businessSectorItem.text)
We profit from the sql concat function. We pass the instruction Projections.SqlFunction
to Select()
and create the part using the standard / baseProjections
+5
source to share