23

I have to write a query in Fluent NHibernate for

Select * from Users where UserName = 'Abcd' AND Password = '123456'

How to create above query with session.CreateCriteria

Phill
  • 18,398
  • 7
  • 62
  • 102
Saad
  • 1,312
  • 5
  • 17
  • 40

3 Answers3

52

Fluent NHibernate is a alternative Mapping framework to the default HBM files (Hibernate Mapping)

NHibernate itself offers different Querying API's like

  • HQL - Hibernate Querying Language
  • Criteria
  • Query - (LINQ Equivalent)
  • QueryOver - (strongly typed Criteria)
  • SQL

For Criteria your query would be something along the lines of:

var users = session.CreateCriteria<Users>()
                   .Add(Restrictions.Eq("UserName", "Abcd"))
                   .Add(Restrictions.Eq("Password", "123456"))
                   .List<Users>();

Query:

var users = from u in session.Query<Users>()
            where u.UserName == "Abcd"
            && u.Password == "123456"
            select u;

or

var users = session.Query<Users>()
                   .Where(x => x.UserName == "Abcd" && u.Password == "123456");

QueryOver:

var users = session.QueryOver<Users>()
                   .Where(x => x.UserName == "Abcd")
                   .And(u.Password == "123456")
                   .List();
Phill
  • 18,398
  • 7
  • 62
  • 102
  • thanks for the great reply. Can i return a User as a type in the above queries ? – Saad Jul 12 '11 at 04:56
  • 1
    @Saad - A single user? Instead of a Collection? Sure. You can look at 'SingleOrDefault()' on the Query/QueryOver, instead of 'List()'. I'm not sure which method you call on the Criteria to do a single result. Don't have Visual Studio handy. – Phill Jul 12 '11 at 05:13
  • Can this be done in fluent mapping too? greatful if you could give an example of the same – keyr Jan 23 '14 at 10:11
  • @keyr - Fluent Mapping is just an in memory xml mapping. The mapping doesn't have any influence on your ability to run queries. Unless you get the mapping wrong. – Phill Jan 23 '14 at 11:49
0

Query Over

var users = session.QueryOver<Users>()
                   .Where(x => x.UserName == "Abcd" && x.Password == "123456")
                   .List();
Dayadra Dharmasena
  • 1
0

If you are using IQuery:

User user = new User();
user.username="Abcd";
user.password="123456";

IQuery q = session.CreateQuery("from foo in class Foo where
user.username=:username and user.password=:password");
q.SetProperties(user);
var users= q.List<User>();
vivek9237
  • 41
  • 2