nhibernate query logic tests

I'm trying to get setup to use nhibernate against our oracle database. One of my goals is to test query logic. I started off using sqlite, but ran into problems with many-to-one relationships no pulling in foreign object. Thinking this was a sqlite problem I set up a new oracle database to test against, but I get the same symptoms.

When I run a query against a populated database, it works fine and returns results which properly lazy load foreign objects as they are referenced in code.

the query logic tests(contained with in a mstest assembly) return nulls instead of foreign object.

The differences between the two querys:

1. The test query creates,saves and flushes the data just before 
   calling the query.
2. The test configuration includes steps needed to export the mapped schema 
   into test database, uses a different default schema and connection string.
3. The test query happens in a mstest unit test assembly.

I have confirmed:

1. That running query against real database works even when run from the unit test       
   assembly.
2. That the test data is being save in the test database schema.

3. That the foreign key reference is created in the test database.

4. That the primary keys are being created in both tables used in query.

I suspect that im missing a step when saving the data to the test database, so I will share that code, I can add the configuration code if requested.

        var session = DbSession.Load(ConnModes.UnitTest);

        var uil = new List<UserInfo>();
        uil.Add(new UserInfo { Id = -1, FullName = "Fred Flintstone", LoginName = "fredflint" });
        uil.Add(new UserInfo { Id = -1, FullName = "Barney Ruble", LoginName = "barnrubl" });
        uil.Add(new UserInfo { Id = -1, FullName = "Bam Bam", LoginName = "bambam" });

        foreach (var f in uil)
        {
            session.Save(f);
        }

        var rightNow = DateTime.Now;
        var x = new List<FacilityRouteFormula>();
        x.Add(new FacilityRouteFormula { Id = -1, TrackingFacilityId = 2, Formula = "a formula1", ComponentGroupName = "Comp 0", UserId = 1, CreationDate = rightNow.AddMinutes(5) });
        x.Add(new FacilityRouteFormula { Id = -1, TrackingFacilityId = 2, Formula = "a formula2", ComponentGroupName = "Comp 0", UserId = 1, CreationDate = rightNow.AddMinutes(3) });
        x.Add(new FacilityRouteFormula { Id = -1, TrackingFacilityId = 2, Formula = "a formula3", ComponentGroupName = "Comp 0", UserId = 1, CreationDate = rightNow.AddMinutes(4) });
        x.Add(new FacilityRouteFormula { Id = -1, TrackingFacilityId = 2, Formula = "a formula4", 开发者_JAVA百科ComponentGroupName = "Comp 0", UserId = 1, CreationDate = rightNow.AddDays(1) });
        x.Add(new FacilityRouteFormula { Id = -1, TrackingFacilityId = 2, Formula = "a formula5", ComponentGroupName = "Comp 0", UserId = 1, CreationDate = rightNow });

        foreach (var f in x)
        {
            session.Save(f);
        }

        session.Flush();

        var uidata2 = (from uid in session.Query<UserInfo>() select uid).ToList();
        var data = (from uid in session.Query<FacilityRouteFormula>() select uid).ToList();

        var q = (from frf in session.Query<FacilityRouteFormula>()
                 where frf.ComponentGroupName == "Comp 0" &&
                 frf.TrackingFacilityId == 2
                 orderby frf.CreationDate descending
                 select frf).ToList();

        Assert.IsNotNull(q[0].User);
        Assert.AreEqual("fredflint", q[0].User.LoginName);

Here is the query against "real" database.

        var s = DbSession.Load(ConnModes.Dev );

        var q = (from frf in s.Query<FacilityRouteFormula>()
                 where frf.ComponentGroupName == "Comp 0" &&
                 frf.TrackingFacilityId == 2
                 orderby frf.CreationDate descending
                 select frf).ToList();

mapping files as requested:

FacilityRouteFormula:

<id name="Id"  column="N_FACILITY_ROUTE_FORMULA_ID" type="long"  unsaved-value="-1">
  <generator class="native" >
    <param name="sequence">SEQ_FACILITY_ROUTE_FORMULA</param>
  </generator>
</id>

<property  name="TrackingFacilityId" column="N_TRACKING_FACILITY_ID" type="long" />
<property  name ="ComponentGroupName" column="C_COMPONENT_GROUP_NAME" type="string"/>
<property name="CreationDate" column="D_CREATION_DATE"  type="DateTime"/>
<property name="UserId" column="N_USER_ID" type="int" not-null="true" />
<property name="Formula" column="C_FORMULA" type="string" not-null="true" />

<many-to-one insert="false" update="false" lazy="false" name="User" fetch="select">
  <column name="N_USER_ID" sql-type="NUMBER" not-null="true" />
</many-to-one>

UserInfo:

<id name="Id"  column="N_USER_ID" type="int" unsaved-value="-1"  >
  <generator class="native" >
    <param name="sequence">SEQ_USER_ID</param>
  </generator>
</id>

<property name="FullName" column="C_USER_FULL_NAME" type="string"  not-null="true" />
<property name="LoginName" column="C_LOGIN_NAME" type="string"  not-null="true"  />

---

I think you need to clear the cache something like this.

session.Save(f);
session.Flush();
session.Evict(f);