Hi, I'm using ASP.NET MVC, C#, NHibernate v2.0.50727 and FluentNHibernate. I have 2 tables that has many to many relationship and has a middle index table. I want to do a select query bbut I am getting the following error but I dont' even have any of these columns declared.
Invalid column name 'Application_id'.
Invalid column name 'ResearchInterest_id'.
Invalid column name 'Application_id'.
Invalid column name 'ResearchInterest_id'.

This is the query I ran,
public IList<Application> FindMembersByInterestIds(string ids)
{
var query = "select distinct a from Application a" +
" left join fetch a.ResearchInterests ar" +
" where ar.Id in( " + ids + ")";

IList<Application> applications = Database.Session.CreateQuery(query).List<Application>();

return applications;
}


using System.Collections.Generic;

namespace CtsiSearchMembership.Models
{
public class Application
{
public virtual long Id { get; set; }

//Contact Information
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
public virtual string EmailAddress { get; set; }
public virtual string PhoneNumber { get; set; }
public virtual string MailingAddressLine1 { get; set; }
public virtual string MailingAddressLine2 { get; set; }
public virtual string MailingAddressCity { get; set; }
public virtual string MailingAddressState { get; set; }
public virtual string MailingAddressZip { get; set; }

public virtual string Institution { get; set; }
public virtual string InstitutionOther { get; set; }
public virtual string CommunityOrganization { get; set; }
public virtual Title Title { get; set; }
public virtual string TitleOther { get; set; }
public virtual string Department { get; set; }

public virtual IList<ResearchInterest> ResearchInterests { get; set; }
public virtual IList<Degree> Degrees { get; set; }
public virtual string ProfessionalOrganizations { get; set; }

//Interest information
public virtual MembershipLevel MembershipLevel { get; set; }
public virtual IList<MembershipReason> MembershipReasons { get; set; }
public virtual string MembershipReasonOther { get; set; }
public virtual IList<MembershipServiceRequirement> MembershipServiceRequirements { get; set; }
public virtual int PhdTraineeCount { get; set; }
public virtual int MscTraineeCount { get; set; }

//Demographic information
public virtual Gender Gender { get; set; }
public virtual int YearOfBirth { get; set; }
public virtual Race Race { get; set; }
public virtual string RaceOther { get; set; }
public virtual Ethnicity Ethnicity { get; set; }

public Application()
{
ResearchInterests = new List<ResearchInterest>();
Degrees = new List<Degree>();
MembershipReasons = new List<MembershipReason>();
MembershipServiceRequirements = new List<MembershipServiceRequirement>();
}

public virtual void AddResearchInterest(ResearchInterest researchInterest)
{
//don't add a Research Interest already in the database.
foreach(var rInterest in ResearchInterests)
{
if (rInterest.Id == researchInterest.Id) return;
}

ResearchInterests.Add(researchInterest);
}

public virtual void AddDegree(Degree degree)
{
Degrees.Add(degree);
}

public virtual void AddMembershipReason(MembershipReason membershipReason)
{
MembershipReasons.Add(membershipReason);
}

public virtual void AddMembershipServiceRequirement(MembershipServiceRequirement membershipServiceRequirement)
{
MembershipServiceRequirements.Add(membershipServiceRequirement);
}
}
}

using CtsiSearchMembership.Models;
using FluentNHibernate.Mapping;

namespace CtsiSearchMembership.ModelMapping
{
public class ApplicationMap: ClassMap<Application>
{
public ApplicationMap()
{
WithTable("Applications");
Id(x => x.Id);
Map(x => x.FirstName);
Map(x => x.LastName);
Map(x => x.EmailAddress);
Map(x => x.PhoneNumber);
Map(x => x.MailingAddressLine1);
Map(x => x.MailingAddressLine2);
Map(x => x.MailingAddressCity);
Map(x => x.MailingAddressState);
Map(x => x.MailingAddressZip);
Map(x => x.Institution);
Map(x => x.InstitutionOther);
Map(x => x.CommunityOrganization);
References(x => x.Title);
Map(x => x.TitleOther);
Map(x => x.Department);
Map(x => x.ProfessionalOrganizations);
Map(x => x.MembershipLevel);
Map(x => x.MembershipReasonOther);
Map(x => x.PhdTraineeCount);
Map(x => x.MscTraineeCount);
Map(x => x.Gender);
Map(x => x.YearOfBirth);
Map(x => x.Race);
Map(x => x.RaceOther);
Map(x => x.Ethnicity);

//Many to Many mappings
HasManyToMany<ResearchInterest>(x => x.ResearchInterests).Cascade.All().WithTableName(
"ApplicationResearchInterests");
//HasManyToMany<ResearchInterest>(x => x.ResearchInterests).AsBag().WithTableName(
// "ApplicationResearchInterests").WithParentKeyColumn("ApplicationId").WithChildKeyColumn(
// "ResearchInterestId");
HasManyToMany<Degree>(x => x.Degrees).AsBag().WithTableName("ApplicationDegrees").WithParentKeyColumn(
"ApplicationId").WithChildKeyColumn("DegreeId");
HasManyToMany<MembershipReason>(x => x.MembershipReasons).AsBag().WithTableName(
"ApplicationMembershipReasons").WithParentKeyColumn("ApplicationId").WithChildKeyColumn(
"MembershipReasonId");
HasManyToMany<MembershipServiceRequirement>(x => x.MembershipServiceRequirements).AsBag().WithTableName(
"ApplicationMembershipServiceRequirements").WithParentKeyColumn("ApplicationId").WithChildKeyColumn(
"MembershipServiceRequirementId");
}
}
}

using System.Collections.Generic;

namespace CtsiSearchMembership.Models
{
public class ResearchInterest
{
public virtual ResearchInterest ParentResearchInterest { get; set; }
public virtual long Id { get; set; }
public virtual string Name { get; set; }
public virtual IList<Application> applications { get; set; }
}
}


using FluentNHibernate.Mapping;
using CtsiSearchMembership.Models;

namespace CtsiSearchMembership.ModelMapping
{
public class ResearchInterestMap : ClassMap<ResearchInterest>
{
public ResearchInterestMap()
{
WithTable("ResearchInterests");
Id(x => x.Id);
Map(x => x.Name);
References(x => x.ParentResearchInterest);
HasManyToMany<Application>(x => x.applications).Cascade.All().Inverse().WithTableName(
"ApplicationResearchInterests");
//HasManyToMany<Application>(x => x.applications).AsBag().WithTableName(
// "ApplicationResearchInterests").WithParentKeyColumn("ApplicationId").WithChildKeyColumn(
// "ResearchInterestId");
}
}
}


namespace CtsiSearchMembership.Models
{
public class ApplicationResearchInterests
{
public virtual long ApplicationId { set; get; }
public virtual long ResearchInterestId { set; get;}
}
}