I have 3 table in a sample. i am going to join 2 table but i have problem the following is my code :
<?xml version="1.0"?>
</configSections>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">Server=(local);database=human;Integrated Security=SSPI;</property>
<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
<property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
<property name="show_sql">true</property>
</session-factory>
</hibernate-configuration>
using System.Collections.Generic;
using System.Text;
using System;
namespace human {
public class TblStudent {
public TblStudent() { }
public virtual int ID { get; set; }
public virtual IList<TblTerm> TblTerms { get; set; }
public virtual string name { get; set; }
public virtual string lastname { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Text;
using FluentNHibernate.Mapping;
namespace human {
public class TblStudentMap : ClassMap<TblStudent> {
public TblStudentMap() {
Table("TblStudent");
LazyLoad();
Id(x => x.ID).GeneratedBy.Identity().Column("ID");
Map(x => x.name).Column("name");
Map(x => x.lastname).Column("lastname");
HasMany(x => x.TblTerms);
}
}
}
using System.Collections.Generic;
using System.Text;
using System;
namespace human {
public class TblTerm {
public TblTerm() { }
public virtual int ID { get; set; }
public virtual TblLesson TblLesson { get; set; }
public virtual TblStudent TblStudent { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Text;
using FluentNHibernate.Mapping;
namespace human {
public class TblTermMap : ClassMap<TblTerm> {
public TblTermMap() {
Table("TblTerm");
LazyLoad();
Id(x => x.ID).GeneratedBy.Identity().Column("ID");
References(x => x.TblLesson).Column("LessonID");
References(x => x.TblStudent).Column("StudentID");
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using FluentNHibernate.Mapping;
namespace human {
public class TblTermMap : ClassMap<TblTerm> {
public TblTermMap() {
Table("TblTerm");
LazyLoad();
Id(x => x.ID).GeneratedBy.Identity().Column("ID");
References(x => x.TblLesson).Column("LessonID");
References(x => x.TblStudent).Column("StudentID");
}
}
}
private void button_JoinSelect_Click(object sender, EventArgs e)
{
var cfg = new Configuration();
PersistenceModel pm = new PersistenceModel();
pm.AddMappingsFromAssembly(typeof(human.TblStudent).Assembly);
pm.Configure(cfg);
ISessionFactory factory = cfg.BuildSessionFactory();
using (var session = factory.OpenSession())
{
using (var trans = session.BeginTransaction())
{
var query = session.QueryOver<TblStudent().JoinQueryOver<TblTerm>
(c=> c.TblTerms).List();
trans.Commit();
dataGridView1.DataSource = query;
}
}
}
My Error :
NHibernate.Exceptions.GenericADOException: could not execute query
[ SELECT this_.ID as ID1_1_, this_.name as name1_1_, this_.lastname as lastname1_1_, tblterm1_.ID as ID2_0_, tblterm1_.LessonID as LessonID2_0_, tblterm1_.StudentID as StudentID2_0_ FROM TblStudent this_ inner join TblTerm tblterm1_ on this_.ID=tblterm1_.TblStudent_id ]
[SQL: SELECT this_.ID as ID1_1_, this_.name as name1_1_, this_.lastname as lastname1_1_, tblterm1_.ID as ID2_0_, tblterm1_.LessonID as LessonID2_0_, tblterm1_.StudentID as StudentID2_0_ FROM TblStudent this_ inner join TblTerm tblterm1_ on this_.ID=tblterm1_.TblStudent_id] ---> System.Data.SqlClient.SqlException: Invalid column name 'TblStudent_id'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 247
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1349
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 413
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 243
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1694
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1703
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1601
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1595
at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Criteria\CriteriaLoader.cs:line 74
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1919
at NHibernate.Impl.CriteriaImpl.List(IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 265
at NHibernate.Impl.CriteriaImpl.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 276
at NHibernate.Impl.CriteriaImpl.Subcriteria.List[T]() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\CriteriaImpl.cs:line 813
at NHibernate.Criterion.QueryOver
1.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Criterion\QueryOver.cs:line 57
at NHibernate.Criterion.QueryOver`1.NHibernate.IQueryOver.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Criterion\QueryOver.cs:line 198
at human.Form1.button_JoinSelect_Click(Object sender, EventArgs e) in E:\desktop\Human\human\human\Form1.cs:line 84
Please advise me
exception.ToString()
notexception.Message
- Firo