CODEWITHSUNDEEP
c#nhibernatefluent-nhibernate
Daniel Hilgarth
Daniel Hilgarth

Reputation: 174457

Complex mapping with tables without IDs

Short version:
Is it possible in NHibernate to create a mapping for a class that has no corresponding table in the database and specify for each property where the data should come from?

Long version:

I have the following class:

public class TaxAuditorSettings
{
    private readonly IList<Month> _allowedMonths = new List<Month>();
    private readonly IList<Company> _allowedVgs = new List<Company>();

    public IList<Month> AllowedMonths
    {
        get { return _allowedMonths; }
    }

    public IList<Company> AllowedVgs
    {
        get { return _allowedVgs; }
    }
}

The class Company is a normal entity that is mapped to a table.
The class Month is a simple class without ID or existing mapping (Constructor and error checking removed for brevity):

public class Month
{
    public int MonthNumber { get; set; }
    public int Year { get; set; }
}

My database has the following two tables:

Table TAX_AUDITOR_ALLOWED_COMPANIES has only one column COMPANY_ID that is a FK to the table COMPANY and has a UNIQUE index.

Table TAX_AUDITOR_ALLOWED_MONTHS has two columns MONTH_NUMBER and YEAR. There is a UNIQUE index spanning both columns.

I would like to map TaxAuditorSettings such that I can ask my NHibernate session for an object of this type and NHibernate then should put the contents of TAX_AUDITOR_ALLOWED_MONTHS into the list TaxAuditorSettings.AllowedMonths and the companies referenced in TAX_AUDITOR_ALLOWED_COMPANIES into the list TaxAuditorSettings.AllowedCompanies.

Is this even possible? If so, how? If not, how would you do it instead?

Please note: I can change the database if necessary.

Upvotes: 2

Views: 205

Answers (2)

Firo
Firo

Reputation: 30813

not quite what you requested for but here goes

public TaxAuditorSettings GetAuditorSettings(ISession session)
{
    // assuming there is a ctor taking the enumerables as parameter
    return new TaxAuditorSettings(
        session.CreateSQLQuery("SELECT MONTH_NUMBER, YEAR FROM TAX_AUDITOR_ALLOWED_MONTHS")
            .SetResultTransformer(new MonthResultTransformer())
            .Future<Month>(),
        session.CreateCriteria<Company>()
            .Add(NHibernate.Criterion.Expression.Sql("Id IN (SELECT COMPANY_ID FROM TAX_AUDITOR_ALLOWED_COMPANIES)"))
            .Future<Company>())
}

class MonthResultTransformer : IResultTransformer
{
    public IList TransformList(IList collection)
    {
        return collection;
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        return new Month
        {
            MonthNumber = (int)tuple[0],
            Year = (int)tuple[1],
        }
    }
}

Update: saving

public void SaveOrUpdate(ISession session, TaxAuditorSettings settings)
{
    using (var tx = session.BeginTransaction())
    {
        // get whats in the database first because we dont have change tracking
        var enabledIds = session
            .CreateSqlQuery("SELECT * FROM TAX_AUDITOR_ALLOWED_COMPANIES")
            .Future<int>();

        var savedMonths = session
            .CreateSQLQuery("SELECT MONTH_NUMBER, YEAR FROM TAX_AUDITOR_ALLOWED_MONTHS")
            .SetResultTransformer(new MonthResultTransformer())
            .Future<Month>();

        foreach (var id in settings.AllowedVgs.Except(enabledIds))
        {
            session.CreateSqlQuery("INSERT INTO TAX_AUDITOR_ALLOWED_COMPANIES Values (:Id)")
                .SetParameter("id", id).ExecuteUpdate();
        }

        foreach (var month in settings.AllowedMonths.Except(savedMonths))
        {
            session.CreateSqlQuery("INSERT INTO TAX_AUDITOR_ALLOWED_MONTHS Values (:number, :year)")
                .SetParameter("number", month.Number)
                .SetParameter("year", month.Year)
                .ExecuteUpdate();
        }

        tx.Commit();
    }
}

Note: if you can change the database it would be much easier and performant to sanitise the tables

Upvotes: 2

Mark Perry
Mark Perry

Reputation: 1735

I would do it this way.

public class MonthMap : ClassMap<Month>{
  public MonthMap(){
    CompositeId()
      .KeyProperty(x=>x.MonthNumber,"MONTH_NUMBER")
      .KeyProperty(x=>x.Year);
    Table("TAX_AUDITOR_ALLOWED_MONTHS");
  }
}

Add a column to the COMPANY table called TaxAuditable and map it to a bool property. Update the column to be 1 where a matching row is found in TAX_AUDITOR_ALLOWED_COMPANIES. Then remove the table TAX_AUDITOR_ALLOWED_COMPANIES as it serves no real purpose.

Now you have a Company with an appropriate property on it you can query Company's where TaxAuditable is true and pass them into a method along with the Months to do your work/calculations etc... Something like this perhaps?

var taxAuditableCompanies = session.QueryOver<Company>()
  .Where(x=>x.TaxAuditable==true).Future();
var months=session.QueryOver<Month>().Future();

var myService = new MyService();
myService.DoSomeWork(taxAuditableCompanies, months);

Upvotes: 2

Related Questions