For example: SQL: SELECT x = SUBSTRING('abcdef', 0, 3); RESULT: x =
'ab' and NOT x = 'abc'
Then try: SQL: SELECT x = SUBSTRING('abcdef', 0, 4); RESULT: x ='abc'
Just add 1 to the number of chars to be returned, so that if you want 1 then
use (0, 2)
John Davidson
On Mon, Jan 10, 2011 at 8:08 PM, Vikram Nayak <[email protected]>wrote:
> public void PersistPerson()
> {
> var sessionFactory = CreateSessionFactory();
> using (var session = sessionFactory.OpenSession())
> {
> using(var transaction = session.BeginTransaction())
> {
> session.Save(new Person {FirstName = "Foo", LastName =
> "Bar"});
> session.Save(new Person {FirstName = "Foo", LastName =
> "Dah"});
> session.Save(new Person {FirstName = "Foo", LastName =
> "Wah"});
> transaction.Commit();
> }
> }
> using (var session = sessionFactory.OpenSession())
> {
> using(var transaction = session.BeginTransaction())
> {
> var queryable = from p in session.Query<Person>()
> select p;
> var lastNames = new[]{"B", "D"};
> var result = queryable.Where(r =>
> lastNames.Contains(r.LastName.Substring(0, 1))).ToList();
> transaction.Commit();
>
> Assert.That(result[0].LastName, Is.EqualTo("Bar"));
> }
> }
> }
>
>
>
> There appears to be mismatch betweeen the SQL generated by NHibernate
> and SQL expected by SQL2008 in the following case:
>
> public void PersistPerson()
> {
> var sessionFactory = CreateSessionFactory();
> using (var session = sessionFactory.OpenSession())
> {
> using(var transaction = session.BeginTransaction())
> {
> session.Save(new Person {FirstName = "Foo", LastName =
> "Bar"});
> session.Save(new Person {FirstName = "Foo", LastName =
> "Dah"});
> session.Save(new Person {FirstName = "Foo", LastName =
> "Wah"});
> transaction.Commit();
> }
> }
> using (var session = sessionFactory.OpenSession())
> {
> using(var transaction = session.BeginTransaction())
> {
> var queryable = from p in session.Query<Person>()
> select p;
> var lastNames = new[]{"B", "D"};
> var result = queryable.Where(r =>
> lastNames.Contains(r.LastName.Substring(0, 1))).ToList();
> transaction.Commit();
>
> Assert.That(result[0].LastName, Is.EqualTo("Bar"));
> }
> }
> }
>
> The resulting sql query generated by NHibernate for
> var result = queryable.Where(r =>
> lastNames.Contains(r.LastName.Substring(0, 1))).ToList();
>
> select person0_.Id as Id0_,
> person0_.FirstName as FirstName0_,
> person0_.LastName as LastName0_ from [Person] person0_ where
> upper(substring(person0_.LastName,
> 0 /* @p0 */,
> 1 /* @p1 */)) in ('B' /* @p2 */)
>
> From the MSDN documentation for T-SQL SUBSTRING
> http://msdn.microsoft.com/en-us/library/ms187748.aspx SUBSTRING
> (value_expression ,start_expression ,length_expression )
>
> although the documentation says otherwise, from the comments posted
> start_expression appears to be 1 - based (not 0 indexed)
>
> For example: SQL: SELECT x = SUBSTRING('abcdef', 0, 3); RESULT: x =
> 'ab' and NOT x = 'abc'
>
> Any thoughts on how I can get around this ?
>
> On Jan 10, 2:24 pm, Vikram Nayak <[email protected]> wrote:
> > There appears to be mismatch between the SQL generated by NHibernate
> > and SQL expected by SQL2008 in the following case:
> >
> > [Test]
> > public void PersistPerson()
> > {
> > var sessionFactory = CreateSessionFactory();
> > using (var session =
> sessionFactory.OpenSession())
> > {
> > using (var transaction =
> session.BeginTransaction())
> > {
> > session.Save(new Person {
> FirstName = "Foo", LastName = "Bar" });
> > session.Save(new Person {
> FirstName = "Foo", LastName = "Dah" });
> > session.Save(new Person {
> FirstName = "Foo", LastName = "Wah" });
> > transaction.Commit();
> > }
> > }
> > using (var session =
> sessionFactory.OpenSession())
> > {
> > using (var transaction =
> session.BeginTransaction())
> > {
> > var queryable = from p in
> session.Query<Person>() select p;
> > var lastNameCriterion = new
> LastNameCriterion() { LastNameLetters
> > = "B" };
> > var persons =
> > queryable.Where(lastNameCriterion.WherePredicate()).ToList();
> > transaction.Commit();
> > Assert.That(persons.Count,
> Is.EqualTo(1)); //Expect to return
> > Person = "Foo Bar"; but returns Empty list
> > }
> > }
> > }
> >
> > public class LastNameCriterion
> > {
> > public virtual string LastNameLetters { get; set;
> }
> >
> > public Expression<Func<Person, bool>>
> WherePredicate()
> > {
> > string[] lastNames =
> LastNameLetters.Split(',');
> > return r =>
> lastNames.Contains(r.LastName.Substring(0,
> > 1).ToUpper()); // PROBLEM IN THIS LINE OF CODE ???
> > }
> > }
> >
> > public class Person
> > {
> > public virtual long Id { get; set; }
> > public virtual string FirstName { get; set; }
> > public virtual string LastName { get; set; }
> > }
> >
> > public class PersonMap : ClassMap<Person>
> > {
> > public PersonMap()
> > {
> > Id(p => p.Id);
> > Map(p => p.FirstName);
> > Map(p => p.LastName);
> > }
> > }
> >
> > The resulting sql query generated by NHibernate for
> >
> > var persons =
> > queryable.Where(lastNameCriterion.WherePredicate()).ToList();
> >
> > is:
> >
> > select person0_.Id as Id0_,
> > person0_.FirstName as FirstName0_,
> > person0_.LastName as LastName0_
> > from [Person] person0_
> > where upper(substring(person0_.LastName,
> > 0 /* @p0 */,
> > 1 /* @p1 */)) in ('B' /* @p2 */)
> >
> > But from the MSDN documentation for T-SQL SUBSTRINGhttp://
> msdn.microsoft.com/en-us/library/ms187748.aspx
> > SUBSTRING (value_expression ,start_expression ,length_expression )
> >
> > although the documentation says otherwise, from the comments posted
> > start_expression appears to be 1 - based (not 0 indexed)
> >
> > For example:
> > SQL: SELECT x = SUBSTRING('abcdef', 0, 3);
> > RESULT: x = 'ab'
> > and NOT x = 'abc'
> >
> > Any thoughts on how I can get around this ?
>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected]<nhusers%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
