Using Substring in Nhibernate to SQL

Question

I am trying to find the prefixes of my account names so I can use them in a filter in my UI.

However whenever I execute the following linq statement it always brings back the whole account name and then performs the substring. Instead of writing the sql equivalent.

return this.Accounts(user).Select(x => x.Name.Substring(0, 1)).ToList().Distinct();

this.Accounts is IQueryable so it should not force a callback to the database.

Is there any reason why it would return them all and then substring the resulting names or is there a working alternative?

Edit

private IQueryable<Account> Accounts(User user)
{
  var accounts = this.SessionManager.GetActiveSession().Query<Account>().Where(x => x.Company.Id == user.Company.Id);
  if (!user.IsAdmin && user.AccountProfiles.Any())
  {
    accounts = accounts.Where(x => x.AssociatedProfiles.Any(y => y.Users.Any(z => z.Id == user.Id)));
  }

  return accounts;
}

Please show how you are declaring and obtaining this.Accounts. — Diego Mijelshon
– Diego Mijelshon, Commented May 28, 2012 at 15:32
The declaration of this.Accounts is still missing. The behavior you described would be consistent with that property being declared as IEnumerable<Account>. — Diego Mijelshon
– Diego Mijelshon, Commented May 28, 2012 at 17:05
this.Accounts() is a function within the same class as the original code, and is private because it does not have a signature declaration. Am I misinterpreting you? — JConstantine
– JConstantine, Commented May 28, 2012 at 17:45
My bad, I misread part of it. Let me do a reproduction and I'll come back to you. — Diego Mijelshon
– Diego Mijelshon, Commented May 28, 2012 at 18:17

Diego Mijelshon · Accepted Answer · 2012-05-28 18:30:51Z

1

I created a simple Account class containing only a string Name property.

Your first block, performed on session.Query<Account>() results in the SQL one would expect:

select
    substring(account0_.Name,
    @p0+1,
    @p1) as col_0_0_
from
    Account account0_;
@p0 = 0 [Type: Int32 (0)],
@p1 = 1 [Type: Int32 (0)]

It's worth noting that Distinct() was left after the ToList() call. Otherwise, you'd get something better:

select
    distinct substring(account0_.Name,
    @p0+1,
    @p1) as col_0_0_
from
    Account account0_;
@p0 = 0 [Type: Int32 (0)],
@p1 = 1 [Type: Int32 (0)]

My suggestion is that you reduce the Accounts(User) method to a single statement:

return SessionManager.GetActiveSession().Query<Account>();

...And start adding the rest of it until it fails.

Side note: all tests done using the latest NHibernate stable release (3.3.0 as of now)

answered May 28, 2012 at 18:30

Diego Mijelshon

52.8k16 gold badges119 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JConstantine Over a year ago

The reason I was using Distinct after ToList (although it is better the other way around) is because it throws an Expression type 'NhDistinctExpression' is not supported by this SelectClauseVisitor. error when I do.

JConstantine Over a year ago

return this.SessionManager.GetActiveSession().Query<Account>().Select(x => x.Name.Substring(1, 1)) produces the following Sql select account0_.Name as col_0_0_ from [Client].[dbo].Account account0_

JConstantine Over a year ago

Am now trying this and the substring function is working, but the distinct is not being applied

var q = this.SessionManager.GetActiveSession().QueryOver<Account>().Select(x => x.Name.Substr(1, 1)).TransformUsing(Transformers.DistinctRootEntity);

JConstantine Over a year ago

Fixed by projecting an sql function.

var q =         this.SessionManager.GetActiveSession().QueryOver<Account>().Select(           Projections.Distinct(Projections.SqlFunction("substring", NHibernateUtil.String, Projections.Property("Name"),                                                        Projections.Constant(1), Projections.Constant(1))));

Collectives™ on Stack Overflow

Using Substring in Nhibernate to SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related