Converting a my-sql query to nhibernate queryover

Could someone please help me or get me started with converting this query to an nhibernate queryover query?

SELECT
    Campaign.Id,
    Location.Name,
    Location.PrimaryPostcode,
    Inventory.Section,
    Campaign.Sov,
    IFNULL(Contracts.Spend, 0) AS Spend,
    IFNULL(Contracts.Impressions, 0) AS Impressions,
    IFNULL(Days, 0) Days,
    RenewalDate,
    LastContract.OptIn
FROM
    Contract AS Campaign
    JOIN Inventory ON Campaign.InventoryId = Inventory.Id
    JOIN Location ON Inventory.LocationId = Location.Id
    LEFT OUTER JOIN (
        SELECT
            CampaignId,
            SUM(RackRate) AS Spend,
            SUM(Impressions) AS Impressions,
            1 + DATEDIFF(Now(), MIN(StartDate)) AS Days,
            DATE_SUB(MAX(EndDate), INTERVAL 11 Day) AS RenewalDate,
            MAX(StartDate) AS CurrentOrNextStartDate
        开发者_C百科FROM
            Contract
            LEFT OUTER JOIN Creative On Creative.ContractId = Contract.Id
            LEFT OUTER JOIN CreativeLog On Creative.Id = CreativeLog.CreativeId            
        GROUP BY
            CampaignId
    ) AS Contracts ON Campaign.Id = Contracts.CampaignId
    LEFT OUTER JOIN (
        SELECT
            CampaignId,
            Id,
            Optin,
            StartDate
        FROM
            Contract
    ) AS LastContract ON Campaign.Id = LastContract.CampaignId AND Contracts.CurrentOrNextStartDate = LastContract.StartDate
WHERE
    Campaign.AgencyId = '04ba6b28-a7a0-4448-b21f-9f2b00a4621b'
ORDER BY
    RenewalDate,
    Location.Name,
    Inventory.Section

I haven't made it past here:

_session.QueryOver<Contract>()
    .Left.JoinQueryOver<Contract>(campaign => campaign.Contracts).List()

Namely because nhibernate is generating an on clause between there two table alias Id == Id when it should be CampaignId == Id.

FROM Contract this_ left outer join Contract contract1_ on this_.Id=contract1_.Id 

Contract's "Contracts" property is a self referencing relationship. It is not 1:1 tho.

you need to show or explain how your mappings are done. If you are going to be joining Contract to itself, then you need to tell it what to join on, otherwise it does it on the primary key. If you are on the latest nhibernate I believe you can do a a .With and tell it to join to the different column. If its no there yet you can do it with the .UnderlyingCriteria

I've pretty much concluded this can't be done in queryover. At least not in an optimal way. The inability to join a projection and then select from it is pretty much a show stopper.