0

For my current project I'm learning NHibernate and I have trouble translating the below query

select person.Firstname, person.Lastname
from Person
    inner join Contract         on contract.PersonId =      person.Id
    inner join Budget           on budget.ContractId =      contract.Id
    inner join Choice           on choice.BudgetId =        budget.Id
    inner join ChosenBenefit    on ChosenBenefit.ChoiceId = choice.Id
where ChosenBenefit.BenefitImplementationId = 77

I thought the below would do the trick, but I get a nullreference with a stacktrace I can't make head or tails from.

Choice choice = null;
Budget budget = null;
Contract contract = null;
Person person = null;

var peopleThatChose = Session.QueryOver<ChosenBenefit>()
            .JoinAlias(chosenBenefit => chosenBenefit.Choice, () => choice)
            .JoinAlias(chosenBenefit => choice.Budget, () => budget)
            .JoinAlias(chosenBenefit => budget.Contract, () => contract)
            .JoinAlias(chosenBenefit => contract.Person, () => person)
        .Where(chosenBenefit => chosenBenefit.BenefitImplementation.Id == 77)
        .Select(benefit => person);

From all the examples everywhere, it seams I'm supposed to QueryOver<Person>, but then I can't work my way down to where I want to put my restriction.

What am I missing?

Boris Callens
  • 90,659
  • 85
  • 207
  • 305

2 Answers2

1

If I do read your model correctly: a Person could have more contracts (contract.PersonId). So what we will do in this case, is to split the query into 2 parts.

  1. Subquery, to obtain the PerosnId from a contract (filtered by Benefit) and
  2. The Person outer query retrieving just Persons...

1) the subquery

 var subquery = Session.QueryOver<ChosenBenefit>()
         // the path from Benefit to Contract is the same
        .JoinAlias(chosenBenefit => chosenBenefit.Choice, () => choice)
        .JoinAlias(chosenBenefit => choice.Budget, () => budget)
        .JoinAlias(chosenBenefit => budget.Contract, () => contract)

    // we do filter over choosen benefit, to get correct contracts
    .Where(chosenBenefit => chosenBenefit.BenefitImplementation.Id == 77)

     // lets select the PerosnId from a contract
    .Select(benefit => contract.PersonId);

2) Now we will query the Person, and filter with the subquery result (on DB server)

var peopleThatChose = session.QueryOver<Person>()
    .WithSubquery
    .WhereProperty(p => p.Id)
    .In(subquery);

So, what have we done:

  • we used the many-to-one navigation from the ChoosenBenefit to Contract... to get the PersonId
  • we filtered the pure Person query with the result, and now we can do even the proper Paging (Take(), Skip()) over the Person entity
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • So, I have to re-formulate my straight forward join query into an where in query? – Boris Callens Jan 15 '14 at 16:20
  • As I said, if I do understand you correctly... we have a Person with many contracts. If this is the true: yes, convert the query into outer query and subquery - as shown above. If I am wrong, and realtion is all the way many-to-one... it could be one query, as we have in my answer represented by subquery. There we do select from one start (benefit) the very far end contract... What I suggested above is usual approach how to fitler an item, which does have a collection. This way we really can achieve a lot, mostly paging – Radim Köhler Jan 15 '14 at 16:21
  • Subqueries can give you lot of freedom. I do not think it is a complication. Here, please, check how complex SQL we can generate with subqueries http://stackoverflow.com/questions/20528760/query-on-hasmany-reference ... while having the outer most query flat, ready for paging not extended with the cartesian product – Radim Köhler Jan 15 '14 at 16:30
  • No, you are probably right. I'm just having some trouble switching my mind from SQL to NHibernate thinking. – Boris Callens Jan 16 '14 at 08:39
  • 1
    In case, you later realize, that the count of your results is growing over the expected Person count (using the Andrew approach), I would say, that the subquery approach could help. Anyhow good luck with NHibernate Boris ;) – Radim Köhler Jan 17 '14 at 08:58
  • So, what you're saying is that your solution would scale better on large result sets? – Boris Callens Jan 17 '14 at 09:11
  • 1
    No. What I say is, if you have a Person, with 2 Contracts .... **both** leading to the benefit 77 you will have this Person twice in the list. Because this starts to lead to cartesian product. I know that you like the Andrew solution because it is **nice and simple**. But *(I would be rude right now)* it is wrong solution. You say that you are coming from SQL world... I am sure you know what I am talking about ... – Radim Köhler Jan 17 '14 at 09:14
  • 1
    Ah yes, I know what you mean now. I had hoped that NHibernate would be smart about this ;) I'll have to look into this. Thanks for your update – Boris Callens Jan 17 '14 at 09:33
  • Boris, I would rather put it here, then to your latest question. I really would like to help, not to push **my** way. But the solution, provided in my answer here, is **the answer**. We do query the FLAT Peson table. We can do paging we can load only the related data to person. And we can filter it properly: with a subquery. I do respect that you will search more on internet.. good. But I am almsot sure, that you'll end up here ;) Please, take this as a good, positive comment, please ;) – Radim Köhler Jan 17 '14 at 09:52
  • Hey @Radim, thanks for understanding I need a few steps in between before I end up here anyway. I'm trying to find what NHibernate can give me over raw SQL or simple micro ORMs like say Dapper. If I have to think about all the problems raw SQL give me, like duplicate records, optimal querying strategy, custom mapping but now with a new syntax and a few more abstractions, then why should I use NHib in the first place? – Boris Callens Jan 17 '14 at 10:05
  • Also having to join on ID's feels wrong. I'm already having the feeling I shouldn't have ID's in my models in the first place. – Boris Callens Jan 17 '14 at 10:06
  • My knowledge is not so wide. I do have experience with ADO.NET and ORM represented by NHiberante To be honest, I would say that I have a strong SQL Feeling... I mean, I do know what I wish to get as a SQL statement. But surprisingly, I can get it with NHiberante much more easier then with ADO.NET == raw SQL So, what I wanted to tell you is: I do try to query as few columns as possible. So the laziness and the projections are a best way. If ever I need to get some results based on a setting inside of the collection... I do always use the Subquery. – Radim Köhler Jan 17 '14 at 10:22
  • And to improve overall NHibernate behavior, I do use batch fetching http://nhforge.org/doc/nh/en/index.html#performance-fetching-batch The trick with NHibernate, that once the mapping is done (I do not use any exotic: ie no many-to-many, no dictionary - but each table has ID, only one-to-many and many-to-one allowed) so having good mapping in place, the rest of the days I do only play with QueryOver... subqueries... and all the JOINS and FROM and column names are injected there for me NHibernate can be hardly expressed, but it is great – Radim Köhler Jan 17 '14 at 10:23
1

Assuming some things about your mappings, I'm pretty sure you can do this without a subqueries:

Person person = null;

var peopleThatChose =
    Session.QueryOver<Person>(() => person)
        .JoinQueryOver<Contract>(person => person.Contracts)
        .JoinQueryOver<Budget>(contract => contract.Budget)
        .JoinQueryOver<Choice>(budget => budget.Choice)
        .JoinQueryOver<ChosenBenefit>(choice => choice.ChosenBenefit)
            .Where(chosen => chosen.BenefitImplementation.Id == 77)
        .SelectList(list => list
            .Select(() => person.FirstName)
            .Select(() => person.LastName))
        .List<object[]>();
Andrew Whitaker
  • 124,656
  • 32
  • 289
  • 307