CODEWITHSUNDEEP
c#nhibernatequeryover
Michał J. Gąsior
Michał J. Gąsior

Reputation: 1679

Too many results when eager fetching data by nHibernate

I'm trying to test my test tools with a sample of code below:

public void Test_tools()
{
     // Arrange
     DateTime appointmentDate = DateTime.Now;    
     DateTime appointmentDate2 = DateTime.Now - TimeSpan.FromDays(5);
     DateTime appointmentDate3 = DateTime.Now - TimeSpan.FromDays(10);

     string firstName = "Frank";
     string lastName = "Carter";

     DatabaseTools.AddPatientToDatabase(firstName, lastName,
             appointmentDate, appointmentDate2, appointmentDate3);

     var patients = DatabaseTools.GetPatientFromDatabase(firstName, lastName);
}

At first I'm adding a PatientEntity with 3 AppointmentEntity classes to the database (AddPatientToDatabase method):

public static void AddPatientToDatabase(string firstName, 
         string lastName, params DateTime[] appointmentDate)
{
    PatientEntity patient = new PatientEntity
    {
        FirstName = firstName,
        LastName = lastName
    };

    foreach (DateTime item in appointmentDate)
    {
        var appointment = new AppointmentEntity
        {
            Date = item
        };
        patient.AddAppointment(appointment);
    }

    ISessionFactory sessionForTests =
              NHibernateConfig.CreateSessionFactory(Database.TEST_DB_NAME);

    using (ISession session = sessionForTests.OpenSession())
    {
        using (ITransaction transaction = session.BeginTransaction())
        {
            session.SaveOrUpdate(patient);
            transaction.Commit();
        }
    }
}

Next I am trying to get the PatientEntity from the database using the FirstName and LastName condition using the GetPatientFromDatabase method, which looks like this:

public static IList<PatientEntity> GetPatientFromDatabase(string firstName, string lastName)
{
    ISessionFactory sessionForTests = 
            NHibernateConfig.CreateSessionFactory(Database.TEST_DB_NAME);

    using (ISession session = sessionForTests.OpenSession())
    {
        return session.QueryOver<PatientEntity>()
                 .Where(k => k.FirstName == firstName && k.LastName == lastName)
                 .Fetch(x => x.Appointments).Eager.List();
    }
}

The problem is, that I recieve as many PatientEntity classes as AppointmentEntity classes were addes (instead of only one). In every of 3 results the patient is the same with the same ID and set of appointments. What is my mistake in the Query I have set? I am using PostgreSQL database, where (using pgAdmin) I see, that everying is set properly. The question is, how should I Query to get only one PatientEntity with three AppointmentEntity classes in his Appointments collection.

EDIT:

I have also tried to use CreateCriteria but once I set eager fetching of the appointment list, I get 3 same patients instead of 1. Is possibly the error inside the eager appointments collection initialization?

public static IList<PatientEntity> GetPatientFromDatabase(string firstName, string lastName)
{
    ISessionFactory sessionForTests = NHibernateConfig.CreateSessionFactory(Database.TEST_DB_NAME);
    using (ISession session = sessionForTests.OpenSession())
    {
        return session.CreateCriteria<PatientEntity>()
            .Add(Expression.Eq("FirstName", firstName))
            .Add(Expression.Eq("LastName", lastName))
            .SetFetchMode("Appointments", FetchMode.Eager)
            .List<PatientEntity>();
    }
}

Upvotes: 0

Views: 138

Answers (1)

Michał J. Gąsior
Michał J. Gąsior

Reputation: 1679

With the help of @jdweng I was able to come up with another query which allows me to get initialized appointments collection without duplicates in patients list:

    public static IList<PatientEntity> GetPatientFromDatabase(string firstName, 
                    string lastName)
    {
        ISessionFactory sessionForTests = 
               NHibernateConfig.CreateSessionFactory(Database.TEST_DB_NAME);

        using (ISession session = sessionForTests.OpenSession())
        {
            return session.QueryOver<PatientEntity>()
                .Where(k => k.FirstName == firstName && k.LastName == lastName)
                .Fetch(x => x.Appointments).Eager
                .TransformUsing(Transformers.DistinctRootEntity).List();
        }
    }

Upvotes: 1

Related Questions