NHibernate - How to QueryOver in joined table with restrictions

Question

I am stuck with a SQL query (using NHibernate 4).

I have 2 tables (Client and Technology) with many-to-many relationship so I created a junction table called ClientTechnology.

I am trying to retrieve all the Technologies available (that are non-custom) PLUS all the Technologies available (that are custom) and belong to a given Client.

In SQL this is the statement:

declare @clientId int = 1

        select * from
        [dbo].[Technology] t
        where t.IsCustom = 0
        union
        select t.* from
        [dbo].[Technology] t
        join [dbo].[ClientTechnology] ct
        on ct.TechnologyId = t.Id
        where t.IsCustom = 1 and ct.ClientId = @clientId

My Fluent Mapping for Client table is:

public ClientMap()
    {
        Id(x => x.Id);
        Map(x => x.Name).Not.Nullable();
    }

For Technology table is:

public TechnologyMap()
    {
        Id(x => x.Id);
        Map(x => x.Name).Not.Nullable();
        Map(x => x.IsCustom).Not.Nullable();

        HasMany(x => x.ClientTechnologies)
            .Access.ReadOnlyPropertyThroughCamelCaseField(Prefix.Underscore)
            .Table("ClientTechnology")
            .KeyColumn("TechnologyId");
    }

and finally the junction table ClientTechnology:

public ClientTechnologyMap()
    {
        Id(x => x.Id);
        Map(x => x.Alias).Not.Nullable();
        Map(x => x.IsDeleted).Not.Nullable();

        References<Client>(x => x.Client, "ClientId");
        References<Technology>(x => x.Technology, "TechnologyId");
    }

I a open to different options to achieve this. Assuming I have available a Client object (the ClientId) I could retrieve first a list of Technologies that match the requirement IsCustom = false and then retrieve a list of Technologies that match the requirement IsCustom = true AND "the provided client is the owner of this custom technology"

Within a method public IEnumerable<Technology> GetTechnologies(Client client) that must return the enumerable of Technology (given a Client instance)

I have tried the following to retrieve globalTechnologies:

var globalTechnologies = _session.QueryOver<Technology>()
            .WhereNot(x => x.IsDeleted)
            .WhereNot(x => x.IsCustom)
            .List();

And the following for customTechnologies whose owner is the client:

Technology technology = null;
        ClientTechnology clientTechnology = null;
        var customTechnologies = _session.QueryOver<Technology>(() => technology)
            .JoinAlias(() => technology.ClientTechnologies, () => clientTechnology)
            .WhereNot(x => x.IsDeleted)
            .Where(x => x.IsCustom)
            .Where(clientTechnology.Client == client) //this doesn't compile
            .List();

but I don't know how to access the junction table (joined) in order to apply the restriction.

Any help would be much appreciated. Thank you.

score 1 · Accepted Answer · edited May 23 '17 at 12:06

In your case, the only problem is, that you do not provide Expression inside of the .Where(), so this should do the job:

// instead of this
// .Where(clientTechnology.Client == client) //this doesn't compile
// use this
.Where(() => clientTechnology.Client == client)

But I would go even farther. We should be able to creat subquery, which

will return only such Techonology.Id which are belonging to client.
we then can use also OR and have one query which would either select these who are:
- NOT IsCustom or
- Do belong to Client

How to create subquery you can see here:

How to do a QueryOver in Nhibernate on child collection

And example with OR

Excellent! That was my missing piece. It worked great. Thank you. Very complete answer. I will investigate your suggestion as it seems more efficient. — diegosasw, Jan 20 '15 at 20:39

NHibernate - How to QueryOver in joined table with restrictions

1 Answers1