Hi,

I have following query, I tried hours to translate it in Linq, without success

select tb.tb_idtbe,
       tb.tb_tbename,
       tb.tb_electionyear
  from t_web_firm wf
       join t_tbe_firm tf on tf.fi_idfirm = wf.wf_phoenix_firm_id
       join t_tbe tb on tb.tb_idtbe = tf.tb_idtbe
 where wf.wf_id = :wf_id
   and tb.tb_electionyear = (select max(tb.tb_electionyear) as tb_electionyear
                               from t_tbe_firm tf
                                    join t_tbe tb on tb.tb_idtbe = tf.tb_idtbe
                              where tf.fi_idfirm = wf.wf_phoenix_firm_id
                             group by
                               tf.fi_idfirm)

De correlation is between tf.fi_idfirm = wf.wf_phoenix_firm_id (outer query). Can this be written in Linq, if not how then using the traditional predicate system of LLBLGen Pro.

thx in Advance Danny

Hi Danny,

I don't know what exactly your query do. But consider this:

If I want all OrderDetails from german clients, and I want those OrderDetails in which they bought the bigger amount of products, I would want something like this:

select od.*
from [order details] od
    inner join orders o on od.orderId = o.orderId
    inner join customers c on o.customerId = c.customerId
where c.country = 'Germany'
and od.quantity = (select max(od2.quantity) as maxQuantity
                             from [order details] od2
                                    inner join orders o2 on od2.orderId = o2.orderId
                             where o2.customerId = o.customerId)

Which would be written in LINQ2LLBL as:

var q = from od in metaData.OrderDetails
        
        join o in metaData.Orders on od.OrderId equals o.OrderId
        join c in metaData.Customers on o.CustomerId equals c.CustomerId
        
        where c.Country == "Germany" && od.Quantity ==
            (from od2 in metaData.OrderDetails
             join o2 in metaData.Orders on od2.OrderId equals o2.OrderId
             where o2.CustomerId == o.CustomerId
             select od2.Quantity).Max()
        
        select od;

Please give it a spin with your entities. Or elaborate more so we can help you better (what type of relations are involved?, what do you want to fetch?, what is what you have so far?)

Hi daelmo,

Thx, your example solved my issue, I had made a syntax error before the sub-query, used 'equal' instead of the == operator, I didn't saw it directly so things got worser. My first taught was that correlated sub-query's weren't supported. Now seeing again the code after a few days (i m still evaluating the product on my spare time) I saw my fault thx to your code

                var q = from wf in metaData.WebFirm
                        join tf in metaData.TBEFirm on wf.FirmID equals tf.FirmID
                        join t in metaData.TBE on tf.TBEID equals t.TBEID
                        where wf.WebFirmID == ID
                            && t.ElectionYear == /* Here I had written equals instead of == */ (
                                from tf2 in metaData.TBEFirm
                                join t2 in metaData.TBE on tf2.TBEID equals t2.TBEID
                                where tf2.FirmID == wf.FirmID
                                select t2.ElectionYear).Max()
                        select new
                        {
                            t.TBEID,
                            t.Name,
                            t.ElectionYear
                        };

Danny

Great!