How to add a Restriction to an inner join?

Question

I have the following NHibernate DetatchedCriteria,

return DetachedCriteria.For<MMFund>()
    .CreateCriteria<MMFund>(x => x.DataUniverse)
    .Add<DataUniverse>(x => x.SiteId == 100)
    .SetProjection(LambdaProjection.Property<MMFund>(x => x.FundId));

which is producing the following SQL:

and
    this_.ShareClassReturn_ShareClassId in 
    (
        SELECT f.[Fund_ID] as y0_
        FROM   
            dbo.Fund f inner join CAP.DataUniverse du
              on f.[Fund_TypeID] = du.[DataUniverse_TypeId]
                 and f.[Fund_CountryID] = du.[DataUniverse_CountryID]
        WHERE  fu.[DataUniverse_SiteId] = 100
    )

There are many funds in a DataUniverse.

I need to filter this so that I can select only the funds with a country ID of 'ET', so that my query looks as follows:

and
    scr.ShareClassReturn_ShareClassId in 
    (
        /* Get funds in universe */
        SELECT f.[Fund_ID] as y0_
        FROM   dbo.Fund f inner join CAP.DataUniverse du
                    on f.[Fund_TypeID] = du.[DataUniverse_TypeId]
                    and f.[Fund_CountryID] = 'ET' // these are the guys I need
        WHERE  du.[DataUniverse_SiteId] = 100 
    )

However, I'm not sure what I need to do to the DetachedCriteria in order to make this happen. The problem I'm having is that no matter what I do, it's putting the clause in the wrong place, such as

WHERE  du.[DataUniverse_SiteId] = 100  and f.Fund_CountryId = 'ET'

when I add the line .Add(Restrictions.Eq("CountryId", "ET")) as follows

return DetachedCriteria.For<MMFund>()
    .Add(Restrictions.Eq("CountryId", "ET"))
    .CreateCriteria<MMFund>(x => x.DataUniverse)
    .Add<DataUniverse>(x => x.SiteId == 100)
    .SetProjection(LambdaProjection.Property<MMFund>(x => x.FundId));

or it attempts to filter on the wrong table entirely when I specify that the Restriction should be part of the second .CreateCriteria, such as

return DetachedCriteria.For<MMFund>()
    .CreateCriteria<MMFund>(x => x.DataUniverse)
        .Add(Restrictions.Eq("CountryId", "ET"))
    .Add<DataUniverse>(x => x.SiteId == 100)
    .SetProjection(LambdaProjection.Property<MMFund>(x => x.FundId));

which produces this;

WHERE  du.[DataUniverse_SiteId] = 100  and du.[DataUniverse_CountryID] = 'ET'

** note - as I'm using the Criteria API, this is actually the Restriction that I'm using:

.Add<MMFund>(f => f.CountryId == "ET")

I used the Restriction terminology because it's more explicit to what I'm trying to achieve. The Criteria API & the other way both produce the exact same results.

Answer 1

Why do you think Where is the wrong place for the filter? That's where filtering happens.

The generated SQL looks sound. You have two tables joined on their common fields. The Where clause is providing the appropriate filtering information. If your preferred SQL statement was in place, you'd have data joined on TypeID alone, not the CountryID.

For example, let's say your Fund table looks like this

TypeID    CountryID
1         1
1         2
2         1
2         2
3         1
4         1

And your DataUniverse table is the following

TypeID    CountryID
1         1
1         2
1         3
2         1
2         2
2         3
3         1
3         2
4         1
4         2

If you wrote SQL like you desire, you would produce a join based on TypeID and you would filter Fund.CountryID = 1, for example. What would your product look like?

F.TypeID    F.CountryID    D.TypeID    D.CountryID
1           1              1           1
1           1              1           2
1           1              1           3
2           1              2           1
2           1              2           2
2           1              2           3
3           1              3           1
3           1              3           2
4           1              4           1 
4           1              4           2

Is that your desired output? Yes, you've filtered Fund.CountryID, but your join was just on TypeID, so you've got all records from DataUniverse with that matching type for each Fund.

With the join on the two fields and the Where filtering the CountryID, the result will be the following

F.TypeID    F.CountryID    D.TypeID    D.CountryID
1           1              1           1
2           1              2           1
3           1              3           1
4           1              4           1 

The question is which set of data is the one you expect?