nhibernate "join"

Question

is any way to optimize this solution? whether there is any possibility of obtaining the same result by calling single query?

public List<Company> GetCompanies(DateTime maxDate, int stockQuotesCount)
    {
        List<Company> result = new List<Company>();
        IList<Company> company = null;
        DateTime lastSessionDate = new StockQuoteRepository().GetLastSessionDate(maxDate);

        using (ISession s = DataAccessFacade.OpenSesion())
        {
            String sqlQuery = string.Empty;
            sqlQuery = @"Select
                                    *
                                From 
                                    dbo.Company c
                                Where 
                                    c.Company_FirstQuotationDate <= :date and
                                    (c.Company_LastQuotationDate >= :date or c.Company_LastQuotationDate is Null)
                                Order By 
                                    c.Company_Name asc";

            company = s.CreateSQLQuery(sqlQuery)
                                .AddEntity(typeof(Company))
                                .SetDateTime("date", lastSessionDate)
                                .List<Company>();


            if (company != null)
            {
                for (int i = 0; i < company.Count; i++)
                {
                    sqlQuery = @"Select
                                    Top(:top)
                                    *
                                From 
                                    dbo.StockQuote sq
                                Where 
                                    sq.StockQuote_Company_Id = :cId
                                    and sq.StockQuote_Date <= :date
                                Order By 
                                    sq.StockQuote_Date desc";

                    company[i].StockQuotes = s.CreateSQLQuery(sqlQuery)
                                        .AddEntity(typeof(StockQuote))
                                        .SetParameter<int>("cId", company[i].Id)
                                        .SetParameter<int>("top", stockQuotesCount)
                                        .SetDateTime("date", lastSessionDate)
                                        .List<StockQuote>();
                }
            }

        }
        return (List<Company>)company;
    }

my fluent mapings:

    public class CompanyMap : ClassMap<Company>
{
    public CompanyMap()
    {
        this.Id(x => x.Id).Column("Company_Id");
        this.Map(x => x.Name).Column("Company_Name");
        this.Map(x => x.FirstQuotationDate).Column("Company_FirstQuotationDate");
        this.Map(x => x.LastQuotationDate).Column("Company_LastQuotationDate");
        this.HasMany(x => x.StockQuotes)
            .Cascade.All()
            .BatchSize(50)
            .Inverse();
    }
}


    public class StockQuoteMap : ClassMap<StockQuote>
{
    public StockQuoteMap()
    {
        this.Id(x => x.Id).Column("StockQuote_Id");
        this.Map(x => x.Open).Column("StockQuote_Open");
        this.Map(x => x.Low).Column("StockQuote_Low");
        this.Map(x => x.High).Column("StockQuote_High");
        this.Map(x => x.Close).Column("StockQuote_Close");
        this.Map(x => x.Volume).Column("StockQuote_Volume");
        this.Map(x => x.Date).Column("StockQuote_Date");
        this.References(x => x.Company).Column("Company_Id");
    }
}

Answer 1

ok, it is better query

using (ISession s = DataAccessFacade.OpenSesion())
        {
            String cHql = @"select 
                                distinct c
                            from 
                                Company c
                                join fetch c.StockQuotes s
                            where 
                                c.FirstQuotationDate <= :maxDate and
                                (c.LastQuotationDate >= :maxDate or c.LastQuotationDate is Null)
                                and s.Date >= :minDate
                            order by
                                c.Name asc,
                                s.Date desc";

          result = s.CreateQuery(cHql)
                .SetDateTime("maxDate", lastSessionDate)
                .SetDateTime("minDate", lastSessionDate.AddMonths(-2))
                .List<Company>().ToList();
        }

but there is one problem, instead of giving time period for selecting StockQuote by StockQuote.Date form Company.StockQuotes, wants to make the TOP() on this joined collection

really, I only know maxDate (in this case), the calculation minDate is very difficult