Search code examples
nhibernatefluent-nhibernatejoin

Getting records back from many to many fluent nhibernate?

I made a many to many relationship by following the fluent nhibernate Getting started tutorial .


(source: fluentnhibernate.org)

Now I am not sure how to retrieve data. For instance what happens if I want to get all the products a store carries.

So I would need to use the storeId on the products table. Yet there is no storeId in the products table and I don't have a class that actually contains mapping or properties for StoreProduct.

So I can't go

session.Query<StoreProduct>().Where(x => x.StoreId == "1").toList();

So do I need to do a join on Store and Products and then do a query on them?

Edit

Here is a watered down version of what I have.

      public class Student
        {
            public virtual Guid StudentId { get; private set; }
            public virtual IList<Course> Courses { get; set; }
            public virtual IList<Permission> Permissions{get; set;}
    
    
            public Student()
            {
               Courses = new List<Course>();
               Permissions = new List<Permission>();
            }

     public class StudentMap : ClassMap<Student>
        {
            public StudentMap()
            {
                Table("Students"); 
                Id(x => x.StudentId).Column("StudentId");
                HasManyToMany(x => x.Permissions).Table("PermissionLevel");
                HasManyToMany(x => x.Courses).Table("PermissionLevel");
            }
        }

  public class CourseMap : ClassMap<Course>
    {
        public CourseMap()
        {
            Table("Courses");
            Id(x => x.CourseId).Column("CourseId");
            HasManyToMany(x => x.Permissions ).Table("PermissionLevel");
            HasManyToMany(x => x.Students).Table("PermissionLevel");
        }
    }

    public class Course
    {
        public virtual int CourseId { get; private set; }
        public virtual IList<Permission> Permissions { get; set; }
        public virtual IList<Student> Students { get; set; }


        public Course()
        {
            Permissions = new List<Permission>();
            Students = new List<Student>();
        }
    }

  public class PermissionMap : ClassMap<Permission>
    {
        public PermissionMap()
        {
            Table("Permissions");
            Id(x => x.PermissionId).Column("PermissionId");
            HasManyToMany(x => x.Students).Table("PermissionLevel");
        }
    }

 public class Permission
    {
        public virtual int PermissionId { get; private set; }
        public virtual IList<Student> Students {get; set;}

        public Permission()
        {
            Students = new List<Student>();
        }
    }


 var a = session.Query<Student>().Where(x => x.Email == email).FirstOrDefault();
            var b = session.Get<Student>(a.StudentId).Courses;

error what I get when I look into b.

could not initialize a collection: [Student.Courses#757f27a2-e997-44f8-b2c2-6c0fd6ee2c2f][SQL: SELECT courses0_.Student_id as Student3_1_, courses0_.Course_id as Course1_1_, course1_.CourseId as CourseId2_0_, course1_.Prefix as Prefix2_0_, course1_.BackgroundColor as Backgrou3_2_0_ FROM PermissionLevel courses0_ left outer join Courses course1_ on courses0_.Course_id=course1_.CourseId WHERE courses0_.Student_id=?]"

Solution

  • You have wrong mappings for Student:

    HasManyToMany(x => x.Permissions).Table("PermissionLevel");
HasManyToMany(x => x.Courses).Table("PermissionLevel");

    It should be following:

    HasManyToMany(x => x.Courses).Table("StudentCourses");

    And you Student class is incomplete.