After much heartache, I have finally figured out a way to do this. It is NOT trivial or easy in my opinion and I still wouldn't be surprised if someone else saw this blog posting and pointed out to me that I am yet doing it wrong. Anyways, let's go over what I have. I started with an application which successfully interfaced with one hydrated object called Record. The database was built form this SQL:
CREATE TABLE dbo.Record
(
Id uniqueidentifier NOT NULL,
FirstInput varchar(255) NOT NULL,
SecondInput varchar(255) NOT NULL,
Multiplication varchar(255) NOT NULL,
Time datetime NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Record ADD CONSTRAINT
PK_Details PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Here is my object in C#:
using System;
namespace Yin.Core.Objects
{
public class Record
{
public virtual Guid Id { get; set; }
public virtual string FirstInput{ get; set; }
public virtual string SecondInput { get; set; }
public virtual string Multiplication { get; set; }
public virtual DateTime Time { get; set; }
}
}
...and here is my map:
using FluentNHibernate.Mapping;
using Yin.Core.Objects;
namespace Yin.Infrastructure.Map
{
public class RecordMap : ClassMap<Record>
{
public RecordMap()
{
Id(x => x.Id);
Map(x => x.FirstInput);
Map(x => x.SecondInput);
Map(x => x.Multiplication);
Map(x => x.Time);
}
}
}
...and here is my repository:
using System.Collections.Generic;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using Yin.Core.Interfaces;
using Yin.Core.Objects;
namespace Yin.Infrastructure.Services
{
public class RecordRepository : IRecordRepository
{
public List<Record> GetRecords()
{
List<Record> result = new List<Record>();
using (var sessionFactory = CreateSessionFactory())
{
using (var session = sessionFactory.OpenSession())
{
foreach (Record record in session.CreateCriteria(typeof(Record)).List())
{
result.Add(record);
}
return result;
}
}
}
public void WriteRecord(Record record)
{
using (var sessionFactory = CreateSessionFactory())
{
using (var session = sessionFactory.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
session.Save(record);
session.Flush();
transaction.Commit();
}
}
}
}
private static ISessionFactory CreateSessionFactory()
{
return Fluently.Configure().Database(MsSqlConfiguration.MsSql2008
.ConnectionString("server=JAESCHKEDB;database=Whatever;
Trusted_Connection=Yes;")).Mappings(m => m.FluentMappings
.AddFromAssemblyOf<RecordRepository>()).BuildSessionFactory();
}
}
}
I interact with the repository like this:
recordRepository.WriteRecord(record);
return ReturnAllRecordsSortedByTime(recordRepository);
...and this all works great! Alright, how may we get a Record to hold n number of SupportingNote types? Let's go over the SQL first. First and foremost this is NOT good SQL.
CREATE TABLE dbo.Record
(
Id uniqueidentifier NOT NULL,
FirstInput varchar(255) NOT NULL,
SecondInput varchar(255) NOT NULL,
Multiplication varchar(255) NOT NULL,
Time datetime NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Record ADD CONSTRAINT
KeyOfRecord PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE dbo.SupportingNote
(
AnotherId uniqueidentifier NOT NULL,
Record_id uniqueidentifier NOT NULL,
Information varchar(255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.SupportingNote ADD CONSTRAINT
KeyOfSupportingNote PRIMARY KEY CLUSTERED
(
AnotherId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.SupportingNote ADD CONSTRAINT
KeyOfCohesion FOREIGN KEY
(
Record_id
) REFERENCES dbo.Record
(
Id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
What is wrong with it? The composite key. Do not create a parent-child association at SQL itself. This has been my biggest mistake from the beginning in working this stuff out. Here is a better way to go:
CREATE TABLE dbo.Record
(
Id uniqueidentifier NOT NULL,
FirstInput varchar(255) NOT NULL,
SecondInput varchar(255) NOT NULL,
Multiplication varchar(255) NOT NULL,
Time datetime NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Record ADD CONSTRAINT
KeyOfRecord PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE dbo.SupportingNote
(
AnotherId uniqueidentifier NOT NULL,
Record_id uniqueidentifier NOT NULL,
Information varchar(255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.SupportingNote ADD CONSTRAINT
KeyOfSupportingNote PRIMARY KEY CLUSTERED
(
AnotherId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Alright, let's step through everything in the refactoring. Record now looks like this:
using System;
using System.Collections.Generic;
namespace Yin.Core.Objects
{
public class Record
{
public virtual Guid Id { get; set; }
public virtual string FirstInput{ get; set; }
public virtual string SecondInput { get; set; }
public virtual string Multiplication { get; set; }
public virtual DateTime Time { get; set; }
public virtual IList<SupportingNote> SupportingNotes { get; set; }
}
}
SupportingNote looks like this:
using System;
namespace Yin.Core.Objects
{
public class SupportingNote
{
public virtual Guid AnotherId { get; set; }
public virtual Record Record { get; set; }
public virtual string Information { get; set; }
public virtual Guid Record_id { get; set; }
}
}
I'm not in love with the Record_id shape of the naming convention for a dance partner for a parent's id, but some errors I saw before I got everything working tipped me off that this was the default expectation for a name for a Guid tying a child back to a parent. It didn't try to fight it and find another way. I just went with it. My map for Record now looks like this:
using FluentNHibernate.Mapping;
using Yin.Core.Objects;
namespace Yin.Infrastructure.Map
{
public class RecordMap : ClassMap<Record>
{
public RecordMap()
{
Id(x => x.Id);
Map(x => x.FirstInput);
Map(x => x.SecondInput);
Map(x => x.Multiplication);
Map(x => x.Time);
HasMany(x => x.SupportingNotes).Cascade.All();
}
}
}
My map for SupportingNote:
using FluentNHibernate.Mapping;
using Yin.Core.Objects;
namespace Yin.Infrastructure.Map
{
public class SupportingNoteMap : ClassMap<SupportingNote>
{
public SupportingNoteMap()
{
Id(x => x.AnotherId);
Map(x => x.Information);
Map(x => x.Record_id);
References(oi => oi.Record, "Record_id").Not.Insert();
}
}
}
That .Not.Insert() is also supercritical. Moving on, the previous repository is revamped here:
using System.Collections.Generic;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using Yin.Core.Interfaces;
using Yin.Core.Objects;
namespace Yin.Infrastructure.Services
{
public class RecordRepository : IRecordRepository
{
public List<Record> GetRecords()
{
List<Record> result = new List<Record>();
using (var sessionFactory = CreateSessionFactory())
{
using (var session = sessionFactory.OpenSession())
{
foreach (Record record in session.CreateCriteria(typeof(Record)).List())
{
List<SupportingNote> notes = new List<SupportingNote>();
foreach (SupportingNote supportingNote in record.SupportingNotes)
{
notes.Add(supportingNote);
}
result.Add(record);
}
return result;
}
}
}
public void WriteRecord(Record record)
{
using (var sessionFactory = CreateSessionFactory())
{
using (var session = sessionFactory.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
session.Save(record);
session.Flush();
transaction.Commit();
}
}
}
}
private static ISessionFactory CreateSessionFactory()
{
return Fluently.Configure().Database(MsSqlConfiguration.MsSql2008
.ConnectionString("server=JAESCHKEDB;database=Whatever;
Trusted_Connection=Yes;")).Mappings(m => m.FluentMappings
.AddFromAssemblyOf<RecordRepository>()).BuildSessionFactory();
}
}
}
Note that all I have to do to return the child objects here is wake them up from not being lazy loaded. I'm actually doing anything with the notes collection once I populate it! My new repository is given here:
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using Yin.Core.Interfaces;
using Yin.Core.Objects;
namespace Yin.Infrastructure.Services
{
public class SupportingNoteRepository : ISupportingNoteRepository
{
public void WriteRecord(SupportingNote supportingNote)
{
using (var sessionFactory = CreateSessionFactory())
{
using (var session = sessionFactory.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
session.Save(supportingNote);
session.Flush();
transaction.Commit();
}
}
}
}
private static ISessionFactory CreateSessionFactory()
{
return Fluently.Configure().Database(MsSqlConfiguration.MsSql2008
.ConnectionString("server=JAESCHKEDB;database=Whatever;
Trusted_Connection=Yes;")).Mappings(m => m.FluentMappings
.AddFromAssemblyOf<RecordRepository>()).BuildSessionFactory();
}
}
}
Yay! This all works! I interact with the repositories like so:
recordRepository.WriteRecord(record);
record.SupportingNotes = new List<SupportingNote>() {foo, bar};
supportingNoteRepository.WriteRecord(foo);
supportingNoteRepository.WriteRecord(bar);
return ReturnAllRecordsSortedByTime(recordRepository);
No comments:
Post a Comment