criteria

NHibernate Select in From Case

Hello I have problems with a query in NHibernate. The original SQL query looks like SELECT Id ,Table1_Id ,Table2_Id ,Table3_Id FROM ( SELECT Id ,Table1_Id ,Table2_Id ,Table3_Id FROM Table_123 WHERE Table2_Id = 72 UNION SELECT 100 As Id ,151 As Table1_Id ,72 As Table2_Id ,2...

NHibernate Criteria using Projections for Substring with in clause

I had a scenario in Oracle where i neeed to match a substring part of column with a list of values. i was using sqlfunction projection for applying the substring on the required column , and addeed that projection as part of an In Clause Restriction. Below is the simplified criteria i wrote for that. ICriteria criteriaQuery = session.C...

Removing Order from NHibernate Criteria Query

I have a criteria query that I am using to show pages of results. I also need to obtain the total count of all items. Rather than have two queries, one for paging the results and one for the count (since they are identical apart from the .AddOrder() public ICriteria StandardQuery { get { return NHibernateSesssionManager.Ge...

NHibernate Criteria Query - Select Distinct

I have a Person entity belongs to a person has a Country, I want to select all the distinct countries that have people in them. Easy in HQL select distinct p.Country from Person p How can I do this using a Criteria Query? ...

NHibernate Criteria Query - Select Distinct with Joined Entity

I have a Person entity. Every person has a country, I want to select all the distinct countries that have people in them. This Criteria Query returns all the distinct CountryID's criteria.SetProjection(Projections.Distinct(Projections.Property("Country"))); How do I alter it to join and fetch the Country entity, not just the ID? ...

NHibernate: Convert an ICriteria to a DetachedCriteria

Anyone know how to convert an ICriteria into a DetachedCriteria. I need to use an existing ICriteria as part of a subquery using: .Add(Subqueries.PropertyIn("Name", myDetachedCriteriaSubquery)) Is there any way to convert an ICriteria to a DetachedCriteria. I will accept 'no' with a credible reference. ...

NHibernate search specific subclass

Is it possible to filter on a particular joined-subclass in NHibernate? For example, I have the following classes: Pet { Name } Cat: Pet { Paws } Budgie: Pet { Wings } Person { Pets } I want to create an NHibernate search to give me Persons with Cats with 4 paws. I can only seem to be able to restrict on a Pet's attributes (Name)......

closures mean fully type-safe criteria?

combining closures (FCM) and generics, would it be possible to have fully type-safe criteria. // The following works without a cast as Foo.id is a 'long' field. List<Long> ids = session.createCriteria(Foo.class) .setProjection(Foo#id) .list(); // The following is a compilation error, as F...

Hibernate query a foreign key field with ID

For example, I have two entities: Employee and Address. Of these enitities, Employee has a foreign key AddressID references the ID column on Address. In the Java domain objects, Hibernate nicely wraps the forgein key integer field with a Address object field. But now, how could I query the Employee with a certain AddressID? I have trie...

NHibernate: How to use "IN" with CreateCriteria?

I have a parent-child table relationship: Elements -(1 to n)-> ContentBlocks. Each ContentBlock row has: unique Id (Id), ElementId, Version, and then some less relevant fields. I'm trying to get all Content rows that have the highest Version number (and Id) on them. I have this line of SQL that gives me what I want: SELECT * FROM Co...

NHibernate Polymorphic Query on a Collection

I'm trying to write a query in NHibernate. I don't really care if I use the Criteria API or HQL, I just can't figure out how to write the query. Here's my model: public class LogEntry { public DateTime TimeCreated { get; set; } } public class Note : LogEntry { public string Content { get; set; } } public class Workflow { public IList<...

NHibernate criteria to query parent objects with at least one child object?

In my project, Lines can be grouped and a Group has a type which can be either Crossing (1) or Parallel (2). I need to find all lines which has at least one group of a specified type (in this case, 1). The Id of a given line can be either on column LineA or LineB of a group. Here is where i got so far: Criteria crit = session.CreateCrit...

NHLambdaExtensions: Create a Criterion object to add to ICriteria later

My application creates a dynamically generated query at runtime based on user input by creating Criterion objects e.g: ICriterion criterion = Restrictions.Eq("Name", "John"); ...... detachedCriteriaSomewhereElse.Add(criterion); How do I do this in NHLambdaExtensions? what I really need to do is ICriterion criterion = Restrictions.Eq...

NHibernate Many-To-Many Criteria

Hi, I have a many to many relationship between Candidates and Positions. I am trying to limit the list of positions fetched to as follows ICriteria criteria = this.GetSession().CreateCriteria(typeof(Candidate), "c"); criteria.CreateAlias("c.Positions", "plist",NHibernate.SqlCommand.JoinType.InnerJoin); criteria.CreateAlias("plist.item...

Nhibernate versioning problem

I have a method that will return a list of suggested orders. If the user passes a null that criteria is ignored. public IList<SuggestedOrderItem> GetSuggestedOrderItemByCriteria ( int? itemNumber, int? deptNumber ) { try { NHibernate.ICriteria criteria =...

NHibernate Criteria, select by count of property

Sorry for the cryptic title.. Could you help me out, on how do a select, based on the count of a property, using Criteria? I have an object (Pool) with a property (PoolItems), and i want to select all the Pools w. more than 5 PoolItems. ...

nhibernate query to return a User by his Guid

I have a user class: public class User { public virtual int ID {get;set;} public virtual string UserGuid {get;set;} // its unique! } Can someone show me how to query using HQL and criteria to get the user by UserGuid? ...

NHibernate: Is it possible to add restriction based on alias name

Say I create a criteria query and transform it with the AliasToBean result transormer. session.CreateCriteria<Staff>("s") .CreateAlias("supervisor", "super") .SetProjection(Projections.ProjectionList() .Add(Projections.Property("s.name"), "name") .Add(Projections.Property("super.name"), "supervisor_name")) .SetResultTrans...

Hibernate cirteria, integer and like

Hi there, I'm migrating some of my hql-statements to Criterias now I'm figuring one problem: The entity property is type Integer but I need a like with wildcards search, so in hql I do session.createQuery("from P1 where id like :id").setString("id", "%"+s+"%") No problem at all, Hibernate casts String to Integer. If I try this in Cr...

NHibernate: how to retrieve an entity that "has" all entities with a certain predicate in Criteria

I have an Article with a Set of Category. How can I query, using the criteria interface, for all Articles that contain all Categories with a certain Id? This is not an "in", I need exclusively those who have all necessary categories - and others. Partial matches should not come in there. Currently my code is failing with this desperate...