How do i create a fluent Nhibernate mapping having join of multiple tables but select only selective colums from each table?

Question

I have four tables in database like

tableAnnual
primary - Key creationDate DATETIME,
primary - Key Id INTEGER,
column AnnualAmount,
column AnnualCurrency,
column Column3,
column Column4,
column Column5,
column Column6

tableMonthly
primary - Key creationDate DATETIME,
primary - Key Id INTEGER,
column MonthlyAmount,
column MonthlyCurrency,
column Column31,
column Column41,
column Column51,

tableSharevalue
primary - Key creationDate DATETIME,
column AverageSharevalueAmount,
column CurrentSharevalue,

tableMiscDetails
primary - Key creationDate DATETIME,
column clientType,
column clientName,
column MarketValueAmount,
column clientAddress,

I want to join all the four tables and get only few columns from each table like AnnualAmount and AnnualCurrency from tableAnnual, MonthlyAmount and MonthlyCurrency from tableMonthly, CurrentSharevalue from tableSharevalue and MarketValueAmount from tableMiscDetails on "creationDate" WHERE Id = "XXXXXXX"

I have defined the entity class in C# code as follows, based on the required columns from all four tables

public class ShareDetails
{
    public int Id { get; set; }
    public DateTime CreationDate { get; set; }
    public string AnnualAmount { get; set; }
    public string AnnualCurrency { get; set; }
    public string MonthlyAmount { get; set; }
    public string MonthlyCurrency { get; set; }
    public string CurrentSharevalue { get; set; }
    public string MarketValueAmount { get; set; }
}

How do I achieve the Mapping in Fluent Nhibernate ?

public class ShareDetailsMap : ClassMap<ShareDetails>
{

}

Please note that i only need few columns from each table.Also, Is the entity class that I have created is okay or do i need to change its structure as well?

All I need to do is to have the following query executed through QueryOver, JoinAlias

Select tableAnnual.AnnualAmount, tableAnnual.AnnualCurrency, tableMonthly.MonthlyAmount, MonthlyAmount.MonthlyCurrency, tableSharevalue.CurrentSharevalue, tableMiscDetails.clientType, tableMiscDetails.clientName, tableMiscDetails.MarketValueAmount from tableAnnual outer join tableMonthly on tableAnnual.creationDate = tableMonthly.creationDate outer join tableSharevalue on tableMonthly.creationDate = tableSharevalue.creationDate outer join tableMiscDetails on tableSharevalue.creationDate = tableMiscDetails.creationDate Where id = '123456'

Answer 1

I have worked out the solution myself. Following are the details.

Supposingly, I have entities and mappings for each database table. In one of the entities lets say AnnualInformation entity (tableAnnual).

I will create relationships of MonthlyInformation entity (tableMonthly), ShareValueInformation entity (tableSharevalue) and MiscDetails entity (tableMiscDetails).

The code of AnnualInformation entity will look Like below :

public class AnnualInformation
{
    public virtual int Id { get; set; }
    public virtual DateTime CreationDate { get; set; }
    public virtual decimal AnnualAmount { get; set; }
    public virtual string AnnualCurrency { get; set; }
    public virtual MonthlyInformation MonthlyInformation { get; set; }
    public virtual ShareValueInformation ShareValueInformation { get; set; }
    public virtual MiscDetails MiscDetails { get; set; }
}

The corresponding mapping class will be as follows:

public class AnnualInformationMap : ClassMap<AnnualInformation>
{
    public AnnualInformationMap()
    {
        Table("tableAnnual");
        Id(x => x.CreationDate, "creationDate ");
        Map(x => x.Id, "Id");
        Map(x => x.AnnualAmount, "AnnualAmount");
        Map(x => x.AnnualCurrency, "AnnualCurrency");
        References(x => x.MonthlyInformation).Column("creationDate");
        References(x => x.ShareValueInformation).Column("creationDate");
        References(x => x.MiscDetails).Column("creationDate");
    }
}

Now, we need to retrieve the data from all these four database tables with Left Outer Join. Therefore we will work the solution as follows

MonthlyInformation monthlyAlias = null;
ShareValueInformation shareAlias = null;
MiscDetails miscAlias = null;

// Create your db session...
using (session)
{
var result = session.QueryOver<AnnualInformation>()
             .JoinAlias(a => a.MonthlyInformation, () => monthlyAlias, JoinType.LeftOuterJoin)
             .JoinAlias(a => a.ShareValueInformation, () => shareAlias, JoinType.LeftOuterJoin)
             .JoinAlias(a => a.MiscDetails, () => miscAlias, JoinType.LeftOuterJoin)
             .SelectList(list => list
                         .Select(x => x.Id)
                         .Select(x => x.CreationDate)
                         .Select(x => x.AnnualAmount)
                         .Select(x => x.AnnualCurrency)
                         .Select(() => monthlyAlias.MonthlyAmount)
                         .Select(() => monthlyAlias.MonthlyCurrency)
                         .Select(() => shareAlias.CurrentSharevalue)
                         .Select(() => miscAlias.MarketValueAmount)
                         ).Where(a => a.Id == 123456).List<object[]>();
}

Here it is, the data that we were looking forward to is now a list of object stored in variable result, for completely unrelated entities. Thanks!!!

Answer 2

Assuming you have classes and mappings for your four tables, this might work for you. As said in the comment above, I'm not 100% sure about the ShareDetails mapping below, since it does not connect to an actual db table.

ShareDetails class:

public class ShareDetails
{
    public int Id { get; set; }
    public DateTime CreationDate { get; set; }
    public Annual Annual { get; set; }
    public Monthly Monthly { get; set; }
    public ShareValue ShareValue { get; set; }
    public MiscDetails MiscDetails { get; set; }
}

ShareDetails mapping:

public class ShareDetailsMap : ClassMap<ShareDetails>
    public ShareDetailsMap(){
        LazyLoad();
        //Don't think you need these since this isn't a db table
        //Id(x => x.DocumentId).GeneratedBy.Identity().Column("id");
        //Map(x => x.CreationDate).Column("creation_date");
        References(x => x.Annual).Column("creation_date");
        References(x => x.Monthly).Column("creation_date");
        References(x => x.ShareValue).Column("creation_date");
        References(x => x.MiscDetails).Column("creation_date");
    }
}

Query that pulls all the random properties into one array of objects:

Annual annualAlias = null;
Monthly monthlyAlias = null;
ShareValue shareAlias = null;
MiscDetails miscAlias = null;

// Create your db session...
using (session)
{
    return session.QueryOver<ShareDetails>()
        .JoinAlias(a => a.Annual, () => annualAlias)
        .JoinAlias(a => a.Monthly, () => monthlyAlias)
        .JoinAlias(a => a.ShareValue, () => shareAlias)
        .JoinAlias(a => a.MiscDetails, () => miscAlias)
        .SelectList(list => list
            .Select(() => annualAlias.AnnualAmount)
            .Select(() => annualAlias.AnnualCurrency)
            .Select(() => monthlyAlias.MonthlyAmount)
            .Select(() => monthlyAlias.MonthlyCurrency)
            .Select(() => shareAlias.CurrentShareValue)
            .Select(() => miscAlias.MarkeyValueAmount)
        ).List<object[]>();
}

Good reference on QueryOver