Search code examples
c#joinnhibernatefluent-nhibernatequeryover

NHibernate - How to QueryOver in joined table with restrictions

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.

Solution

  • 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

    1. will return only such Techonology.Id which are belonging to client.
    2. 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:

    And example with OR