I'm having a problem using NHibernate (1.0.4.0) with SQL Server 2005.
Sorry for the size of this post, but i think you can help me better with more information than with less.
The problem is: I have a table named Page, that is accessed by users. Thus, I log all the accesses in the Access table. The pages belong to a site, so I have also a table named Site.
Here is the
SQL code for this:
Code:
create table site(
url nvarchar(50) constraint pk_site primary key,
)
create table page(
filepath nvarchar(50),
url nvarchar(50) constraint fk_page_site foreign key references site(url),
constraint pk_page primary key (filepath, url)
)
create table access(
date datetime,
url nvarchar(50),
filepath nvarchar(50),
constraint fk_access_page foreign key (filepath, url) references page(filepath, url),
constraint pk_access primary key (date, url, filepath)
)
Thus, the Access table has a composite primary key, composed by a datetime and a composite foreign key for the table Page. The Page table has a composite primary key, composed by its relative path and the url of the associated site. This means that both Access and Page are 'weak entities'.
The
C# classes look like the following:
Code:
public class Site{
private string url;
private IList pages;
public string Url{
get{return url;}
}
public IList Pages{
get{return pages;}
}
}
public class Page{
private string filepath;
private Site site;
private IList accesses;
public string Filepath{
get{return filepath;}
}
public Site Site{
get{return site;}
}
public IList Accesses{
get{return accesses;}
}
}
public class Access{
private DateTime date;
private Page page;
public DateTime Date{
get{return date;}
}
public Page Page{
get{return page;}
}
}
Nothing special about that. Site and Page are connected in both directions, just like Page and Access. (Note that I have omitted the constructors for brevity. I know that it has to have at least a no-arg constructor).
Now the
mapping files:
Site.hbm.xml
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Site" table="site">
<id name="url" column="url" type="String" length="50" access="field">
<generator class="assigned" />
</id>
<bag name="pages" inverse="true" lazy="true" order-by="filepath" cascade="all" access="field">
<key column="url" />
<one-to-many class="Page" />
</bag>
</class>
</hibernate-mapping>
Page.hbm.xml
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Page" table="page">
<composite-id>
<key-many-to-one name="site" column="url" class="Site" access="field" />
<key-property name="filepath" column="filepath" type="string" length="50" access="field" />
</composite-id>
<bag name="accesses" inverse="true" order-by="date" lazy="true" cascade="all" access="field">
<key>
<column name="url" />
<column name="filepath" />
</key>
<one-to-many class="Access" />
</bag>
</class>
</hibernate-mapping>
Access.hbm.xml
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Access" table="access">
<composite-id>
<key-property name="date" column="date" type="DateTime" access="field" />
<key-many-to-one class="Page" name="page" access="field">
<column name="url" not-null="true" length="50"/>
<column name="filepath" not-null="true" length="50"/>
</key-many-to-one>
</composite-id>
</class>
</hibernate-mapping>
For testing this, i have the following
test code to populate the database:
Code:
Configuration cfg = new Configuration();
cfg.AddAssembly("CoolNHibernateDAL");
ISessionFactory factory = cfg.BuildSessionFactory();
ISession session = factory.OpenSession();
DateTime date = DateTime.Now;
string url = "http://nhibernate.test.com";
string filepath = "/xpto/xptu.html";
Site site = new Site(domainUrl);
Page page = new Page(site, filepath);
Access access = new Access(date, page);
session.Save(site);
session.Save(page);
session.Save(access);
session.Flush();
session.Close();
If I manually query the database, i can se those values there, so it seems good.
The problem is when i try to retrieve the values using NHibernate, which i do with the following code (assume that i've open the session, like i did previously):
Code:
Site site = session.Load(typeof(Site), url) as Site;
IList pages = site.Pages;
Page page = new Page(site, filepath);
session.Load(page, page);
IList accesses = page.Accesses;
Access access = new Access(date, page);
session.Load(access, access);
This all works well, with one exception: the line " IList accesses = page.Accesses;". The accesses IList is returned with a count of 0, when it has to have one element! All the other values are retrieved successfuly (including the collection pages).
There are two lines int this last piece of code, that I "don't like", and that were the only way I found for loading the page and the access:
Code:
session.Load(page, page);
session.Load(access, access);
Given the fact that both relations have a composite id, and all the Session.Load methods accept one (and only one) id object, i didn't found another way for doing it.
So, the questions i have are:
- Is that other way for getting the page and access values, without passing a reference to the page/access object in the id place?
- Why can't I retrieve the accesses collection? Why it return always 0?
I hopefully expect that you could help me with this "nasty" problem.
Thanks for the time reading this!