Hi, i'll try to learn how to use queryspecs, but I have a problem to convert MySQL to QuerySpecs, someone con help me to go to correct way.

Here de SQL

SELECT a.botiga_codi,
  (SELECT ap.preu_cost * al.quantitat
   FROM article_preucost ap
   WHERE al.article_codi = ap.article_codi
     AND a.data_venda >= ap.data_canvi
   ORDER BY ap.data_canvi DESC
   LIMIT 1) AS import_cost
FROM albara_venda a
INNER JOIN albara_venda_lin al ON al.albara_venda_id = a.id

I try this

QueryFactory qf = new QueryFactory();   

DynamicQuery<EstadistiquesVendaBotiguesEntity> q = qf.Create()
    .Select(() => new EstadistiquesVendaBotiguesEntity
    {           
        BotigaCodi = AlbaraVendaFields.BotigaCodi.ToValue<short>(),
        ImportCost = qf.ArticlePreucost.CorrelatedOver(ArticlePreucostFields.ArticleCodi == AlbaraVendaLinFields.ArticleCodi)
        .Select(() => new { ImportCost=ArticlePreucostFields.PreuCost.ToValue<decimal>() })
        .Where(AlbaraVendaFields.DataVenda >= ArticlePreucostFields.DataCanvi)
        .OrderBy(ArticlePreucostFields.DataCanvi.Descending()).Limit(1).ToSingleResult(),
    })
    .From(qf.AlbaraVenda.InnerJoin(AlbaraVendaEntity.Relations.AlbaraVendaLinEntityUsingAlbaraVendaId));

With this code I have an error that cannon convert an "anonymous decimal" type to decimal.

I dont know if this queryspecs is the correct way to the result that I want in MySQL

Please any can help or have info about query specs, I'm reading the documentation section, but I don't find the solution, well I preferrer to understand how to do it, for future problems like this

Thanks

Select(() => new { ImportCost=ArticlePreucostFields.PreuCost.ToValue<decimal>() }) doesn't reflect what you have in the SQL query: SELECT ap.preu_cost * al.quantitat

Also, the error you're mentioning, where exactly is that coming from? From the database, or the compiler or in our runtime? Additionally, the SQL query projects fields from albara_venda, but you return EstadistiquesVendaBotiguesEntity instances, that likely doesn't match either?

I think the problem is that you return an anonymous type from the nested query. So you don't need to project it into an object if ImportCost in EstadistiquesVendaBotiguesEntity is a decimal.

I think also the nested select should contain a multiply between preu_cost and quantitat, so the query will become:

QueryFactory qf = new QueryFactory();   

DynamicQuery<EstadistiquesVendaBotiguesEntity> q = qf.Create()
    .Select(() => new EstadistiquesVendaBotiguesEntity
    {           
        BotigaCodi = AlbaraVendaFields.BotigaCodi.ToValue<short>(),
        ImportCost = qf.ArticlePreucost.CorrelatedOver(ArticlePreucostFields.ArticleCodi == AlbaraVendaLinFields.ArticleCodi)
                .Select(ArticlePreucostFields.PreuCost * ArticlePreucostFields.Quantitat)
                .Where(AlbaraVendaFields.DataVenda >= ArticlePreucostFields.DataCanvi)
                .OrderBy(ArticlePreucostFields.DataCanvi.Descending()).Limit(1).ToSingleResult(),
    })
    .From(qf.AlbaraVenda.InnerJoin(AlbaraVendaEntity.Relations.AlbaraVendaLinEntityUsingAlbaraVendaId));

E.g. in an example:

using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create()
              .Select(() => new
                            {
                                OrderId = OrderFields.OrderId.ToValue<int>(),
                                Total = qf.OrderDetail.CorrelatedOver(OrderFields.OrderId==OrderDetailFields.OrderId)
                                          .Select(()=> new
                                                       {
                                                           TotalO = (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice).ToValue<decimal>()
                                                       }).Limit(1).ToSingleResult()
                            })
              .Where(OrderFields.OrderId.Equal(10254));
    var results = adapter.FetchQuery(q);
}

'Total' in the outside resultset isn't a decimal, it's an anonymous type with 1 field, 'TotalO', of type decimal, due to the nested query returning an anonymous type with 1 field, not a decimal

Hi Otis, I'll try your solution with no luck

EstadistiquesVendaBotiguesEntity es a fake entity to make projection, need to put the result of projection in this class

}
[DataContract]
public class EstadistiquesVendaBotiguesEntity : EstadistiquesVendaCabEntity
{
    public string DataCobroIni { get; set; }
    public string DataCobroFin { get; set; }
    public string HoraCobroIni { get; set; }
    public string HoraCobroFin { get; set; }
    [DataMember]
    public short BotigaCodi { get; set; }
    [DataMember]
    public string BotigaNom { get; set; }
}

[DataContract]
public class EstadistiquesVendaCabEntity
{
    [DataMember]
    public decimal Marge 
    { 
        get 
        { 
            decimal _marge;

            if (CalculMarge == 0)
            {
                //Marge sobre Cost
                _marge = ImportCost == 0 ? 0 : Math.Round((ImportTotal - ImportCost) * 100 / ImportCost, 2, MidpointRounding.AwayFromZero);
            }
            else
            {
                //Marge sobre Venda
                _marge = 0;
            }

            return _marge;
        } 
    }
    [DataMember]
    public int Operacions { get; set; }
    [DataMember]
    public decimal ImportTotal { get; set; }
    [DataMember]
    public decimal ImportTotalImpost { get; set; }
    [DataMember]
    public decimal ImportDescompte { get; set; }
    [DataMember]
    public decimal ImportDescompteImpost { get; set; }
    //[DataMember]
    //public decimal ImportPendent { get; set; }
    [DataMember]
    public decimal ImportCost { get; set; }
    [DataMember]
    public decimal ImportAbono { get; set; }
    [DataMember]
    public decimal ImportAbonoImpost { get; set; }
    public short CalculMarge { get; set; }
}

To obtain the same MySQL Command that I posted, maybe nested query is not the best solution or correct solution

I think is impossible to do with nested query, because we need relation between two selects

Is not possible to reacrete this MySQL with queryspec??

SELECT a.botiga_codi,
  (SELECT ap.preu_cost * al.quantitat
   FROM article_preucost ap
   WHERE al.article_codi = ap.article_codi AND a.data_venda >= ap.data_canvi
   ORDER BY ap.data_canvi desc
   LIMIT 1) AS z  
FROM albara_venda a
INNER JOIN albara_venda_lin al ON al.albara_venda_id = a.id

at this moment I'll use this, is not the correct solution

    var result = db.Adapter.FetchQuery<EstadistiquesVendaBotiguesEntity>("SELECT a.botiga_codi AS BotigaCodi,  (SELECT ap.preu_cost * al.quantitat FROM article_preucost ap  WHERE al.article_codi = ap.article_codi AND a.data_venda >= ap.data_canvi ORDER BY ap.data_canvi desc LIMIT 1) AS ImportCost FROM albara_venda a INNER JOIN albara_venda_lin al ON al.albara_venda_id = a.id");

The code sample provided by Otis should map to your SQL query.

So when you say you have tried it with no luck, would you like to explain the outcome?

The error in your original message comes from the following:

ImportCost = qf.ArticlePreucost.CorrelatedOver(ArticlePreucostFields.ArticleCodi == AlbaraVendaLinFields.ArticleCodi)
        .Select(() => new { ImportCost=ArticlePreucostFields.PreuCost.ToValue<decimal>() })
        .Where(AlbaraVendaFields.DataVenda >= ArticlePreucostFields.DataCanvi)
        .OrderBy(ArticlePreucostFields.DataCanvi.Descending()).Limit(1).ToSingleResult(),

Import Cost is a decimal field, and the query assigned to it is not returning a decimal type, it's returning an anonymous type .Select(() => new), which has one decimal field.

Hi Walaa

At first thankyou for support, here the test code that I user based on Otis sample

using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create()
              .Select(() => new
              {
                  OrderId = AlbaraVendaFields.BotigaCodi.ToValue<int>(),
                  Total = qf.AlbaraVendaLin.CorrelatedOver(AlbaraVendaFields.Id == AlbaraVendaLinFields.AlbaraVendaId)
                                          .Select(() => new
                                          {
                                              TotalO = (AlbaraVendaLinFields.Quantitat * ArticlePreucostFields.PreuCost).ToValue<decimal>()
                                          }).From(qf.ArticlePreucost).Where(AlbaraVendaFields.DataVenda >= ArticlePreucostFields.DataCanvi)
                                            .OrderBy(ArticlePreucostFields.DataCanvi.Descending()).Limit(1).ToSingleResult()
              }).From(qf.AlbaraVenda.InnerJoin(AlbaraVendaEntity.Relations.AlbaraVendaLinEntityUsingAlbaraVendaId));

    var results = adapter.FetchQuery(q);
}

On compile, no error, all ok

When execute

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: 'An exception was caught during the execution of a retrieval query: 
Unknown column 'albara_venda_lin.quantitat' in 'field list'. 
Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.'

Two query sended to server

SELECT `albara_venda`.`botiga_codi` AS `BotigaCodi`,
       1 AS `LLBLV_2`,
       `albara_venda`.`id` AS `Id`
FROM (`albara_venda`
      INNER JOIN `albara_venda_lin` ON `albara_venda`.`id`=`albara_venda_lin`.`albara_venda_id`)

SELECT (`albara_venda_lin`.`quantitat` * `article_preucost`.`preu_cost`) AS `LLBLV_3`,
       `albara_venda_lin`.`albara_venda_id` AS `AlbaraVendaId`
FROM
  (SELECT `article_preucost`.`article_codi` AS `ArticleCodi`,
          `article_preucost`.`data_canvi` AS `DataCanvi`,
          `article_preucost`.`id` AS `Id`,
          `article_preucost`.`preu_cost` AS `PreuCost`
   FROM `article_preucost`) `LPA_L1`
WHERE ((`albara_venda`.`data_venda` >= `article_preucost`.`data_canvi`)
       AND (`albara_venda_lin`.`albara_venda_id` IN (94,
                                                     95,
                                                     96,
                                                     97)))
ORDER BY `article_preucost`.`data_canvi` DESC

Second generated the error excepcion

The Query that I want to repoduce in ORM have three tables: - AlbaraVenda (Order) - AlbaraVendaLin (OrderDetails) - ArticlePreuCost (Price)

For design database we need to find the price on (ArticlePreuCost) based on date on (Order.data_venda) greater or igual than date on (ArticlePreuCost.data_canvi) and relation (ArticlePreuCost.productcode) iqual (OrdersDetails.productcode), finally the quantity on (OrderDetails)

Sorry for my English explanation

Best regards

You have AlbaraVendaLinFields.Quantitat in the TotalO nested query but it's not in the FROM clause, you have to join that table with ArticlePreucost as you can see in the second SQL query you posted