Linqer has some problems with selects using IN

[jontatas]

Hi!

I was thinking of giving Linqer a spin and see if it could help us convert old SQL to LinQ.
However it seems it fails on the IN-statement.
The SQL looks like this:


SELECT c.decision_point_time FROM campaigns c
WHERE (c.cyc_id, c.vac_id, c.vac_prs_id) IN
( SELECT p.pip_cam_cyc_id_last, p.pip_cam_vac_id_last, p.pip_cam_vac_prs_id_last
 FROM planned_items p
 WHERE p.seqno = (SELECT poi.pli_seqno FROM   produced_items poi WHERE  (poi.id, poi.oru_id) IN
(SELECT pp.poi_id_parent, pp.poi_oru_id_parent
FROM   poi_poi pp
WHERE pp.poi_id     = '474106-4'
AND   pp.poi_oru_id = 'PPA') ))


I can run this query fine in SQL navigator, Toad and SQL+, any suggestion how to rewrite it to an efficient LinQ query as it seems Linqer don't support IN (yet?).

/J

[Mikhail Oumantsev]

Linqer doesn't support multiple fields in the IN-statement so far.
But IN-statement with single field can be converted.
For example SQL
select orderid from "Orders" O
where
O.orderid in (select orderid from "Order Details")

will be converted to

from orders in db.Orders
where
(from orderdetails in db.OrderDetails
select new {
orderdetails.OrderID
}).Contains(new { orders.OrderID })
select new {
orders.OrderID
}

To convert IN-statement with multiple fields you simply need to enumerate all fields in the select new {...} and
Contains(new {...}) operators.

For example ... select new {t1.f1,t1.f2,t1.f3}).Contains(new{t2.f1,t2.f2,t2.f3})