How to work with NHibernate.SqlCommand.JoinType.RightOuterJoin?

Question

i have Project and Customer Model. Project model have Customer object with <many-to-one> mapping.in database this may b the case where customer is exist but it not have entry in project table.

i m doing CustomerSearch functionality in which user can enter project or customer name.if user enters customername only then i want all the records which are matching with CustomerName without considering that customer have project or not.

i tried this in Service.

ICriteria criteria = session.CreateCriteria(typeof(Project),"Project")
                    .CreateAlias("Project.customer","customer",NHibernate.SqlCommand.JoinType.RightOuterJoin)
                    .CreateAlias("Project.Coordinator", "Coordinator", NHibernate.SqlCommand.JoinType.InnerJoin);

                if (!string.IsNullOrEmpty(custName))
                {
                    criteria.Add(Expression.Like("customer.Name", custName, MatchMode.Start));
                }
                if (!string.IsNullOrEmpty(projectName))
                {
                    criteria.Add(Expression.Like("Project.Name", projectName, MatchMode.Start));
                }

                projectList = criteria.List<Project>().ToList();

i used RightOuterJoin to Customer because project table may not have record of customer but customer table must have those records.

This code is not giving any error also if i see projectList it have list but some items who don't have record in project table that is null.

How can i get such records?

score 0 · Answer 1 · edited May 30 '13 at 16:19

0

using right join all fields of a project are null so NH can not build up a valid project object (id missing) so it returns null.

you have to either project the values into some object so NH knows what to do with null projects or query the customers without projects seperatly (using Futures doesnt require a seperate roundtrip).

class SearchResult
{
    public int ProjectId { get; set; }
    public string ProjectName { get; set; }
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
}


ICriteria criteria = session.CreateCriteria(typeof(Project))
    .CreateAlias("Customer","customer", JoinType.RightOuterJoin)
    .CreateAlias("Coordinator", "Coordinator", NHibernate.SqlCommand.JoinType.InnerJoin);

if (!string.IsNullOrEmpty(custName))
{
    criteria.Add(Expression.Like("customer.Name", custName, MatchMode.Start));
}
if (!string.IsNullOrEmpty(projectName))
{
    criteria.Add(Expression.Like("Name", projectName, MatchMode.Start));
}

criteria.SetProjection(Projection.List()
    .Add(Projections.Property("Id"), "ProjectId")
    .Add(Projections.Property("Name"), "ProjectName")
    .Add(Projections.Property("customer.Id"), "CustomerId")
    .Add(Projections.Property("customer.Name"), "CustomerName"));

projectList = criteria
    .SetTransforer(Transformers.AliasToBean<SearchResult>())
    .List<Project>();

edited May 30 '13 at 16:19

burnt1ce

14,387
33
102
162

answered Oct 26 '12 at 09:30

Firo

30,626
4
55
94

Thanks for giving Reply. can you tell how to project the values into some object so NH knows what to do with null projects.It will Help me a lot. – Amogh Oct 26 '12 at 12:31
added code. now you always get a valid object back, it's just that there might be ProjectId and ProjectName empty – Firo Oct 26 '12 at 12:47
It works.Is it necessary to take separate properties like ProjectId,ProjectName,CustomerName,CustomerId. can't we take Project and Customer as object. – Amogh Oct 26 '12 at 14:56
no because aliastobean can only project into flat objects, no hierarchies. Or you switch to Linq or hql – Firo Oct 26 '12 at 15:51
By LINQ means using QueryOver API? – Amogh Oct 27 '12 at 17:44
no LINQ and queryover are totally different. LINQ is like HQL as QueryOver is for Criteria: compiler safe queries – Firo Oct 27 '12 at 18:27

How to work with NHibernate.SqlCommand.JoinType.RightOuterJoin?

1 Answers1