我如何将多个MIN函数编码为一个LINQ到数据集查询

Question

好的，在寻求帮助之前，我已经研究了一段时间了。我有4个基于Oracle的SQL查询，它们相互遵循以提供所请求的数据。我在。net工作，我认为(我希望)我可以使用LINQ到数据集将第一个查询传递给第二个查询，第二个查询传递给第三个，最后传递给第四个。让我头疼的是如何从第一个传递到第二个。我可以通过普通ODP将第一个查询放入数据集。. NET和c#查询。

这是第二步

SELECT eid,
num_1,
MIN(cdts) keep (dense_rank FIRST ORDER BY eid) first_creation,
cpers,
curent,
ag_id,
beat,
group_priority,
disp_date,
MIN(disp_time) keep (dense_rank FIRST ORDER BY eid) first_call,
curent_16,
MIN(ad_sec) keep (dense_rank FIRST ORDER BY eid) first_entry,
MIN(ds_sec) keep (dense_rank FIRST ORDER BY eid) first_dispatched,
MIN(ar_sec) keep (dense_rank FIRST ORDER BY eid) first_arrival,
MIN(csec) keep (dense_rank FIRST ORDER BY unid) fastest_unit,
MIN(hold_sec) keep (dense_rank FIRST ORDER BY eid) fastest_dispatch,
MIN(drive_sec) keep (dense_rank FIRST ORDER BY unid) fastest_enroute,
MIN(resp_sec) keep (dense_rank FIRST ORDER BY unid) fastest_arrival,
unid FROM JC5A_STEP1 GROUP BY eid,
num_1,
cpers,
curent,
ag_id,
beat,
group_priority,
disp_date,
curent_16,
unid HAVING cpers <> 0 AND curent = 'T' AND curent_16 ='T' ORDER BY eid;

我一直在尝试写一个LINQ到数据集，这将填充到它自己的数据集。这是我到目前为止写的内容我知道我写得太远了

var query2 = (from row in query1.AsEnumerable() where row.Field<int32>("cpers") != 0 && row.Field<string>("curent") == "T" && row.Field<string>("curent_16") == "T" order by row.Field<Int32>("eid") select new { eid = row.Field<Int32>("eid"), ... }).Min(x => x.cdts); 

省略了所有非最小字段和为简洁而包含的字段。所以，我的问题是，我可以写一个LINQ到数据集查询，这将包括我在SELECT语句，如果是这样的话，我在哪里脱轨?如果不是，我是否需要在PL/SQL函数中开发所有这些，并从应用程序中将它们作为存储过程调用?

感谢大家的帮助，

t .

根据快速反馈，第一个查询在这里:

select a.eid, a.num_1, e.cdts, e.cpers, a.curent, a.tycod, a.ag_id, a.lev3 as beat, case when a.priority < '2' then 'high' when a.priority > '2' then 'low' else 'normal' end as group_priority, substr(e.cdts,5,2)||'/'||substr(e.cdts,7,2)||'/'||substr(e.cdts,1,4) as disp_date, substr(e.cdts,9,6) as disp_time, e.curent AS curent_16, a.ad_sec, a.ds_sec, a.ar_sec, u.csec, a.ds_sec - a.ad_sec as hold_sec, u.csec - a.ds_sec as drive_sec, u.csec - a.ad_sec as resp_sec, a.priority, u.unid from un_hi u join (aeven a join event e on a.eid = e.eid) on u.eid = a.eid where e.cdts between '20110101' and '20110201' and a.tycod not in ('ANIMAL', 'BUSINESS', 'CARCHECK', 'CARSTAT', 'CIVSERV', 'CKLIGHTS', 'COURT', 'ERRAND', 'FOLLOW', 'FOOTPURS', 'GREATMAL', 'HOUSE', 'INFO', 'K9', 'PEDCHECK', 'PRISONER', 'RESIDENC', 'SELF', 'SPECIAL', 'STATION', 'TELEPHON', 'TRAFFIC', 'TRASH', 'WARRANT', 'WEATHER') and u.unit_status = 'AR' and a.ag_id = 'JCSO' and a.lev3 = 'JC5' and a.ar_sec is not null and a.ds_sec is not null and a.ds_sec - a.ad_sec between 1 and 7200 and a.ar_sec - a.ds_sec between 1 and 18000 and a.ar_sec - a.ad_sec between 1 and 18000 and u.csec - a.ds_sec > 1 and u.csec - a.ad_sec > 2 order by e.cpers, a.eid, u.csec;

Answer 1

我会考虑使用linq到sql的问题，而不是linq到数据集。代替用sql编写查询，你可以直接使用c#，它可以为你提供更多的选项(自定义对象)，而没有处理数据集和朋友的限制和头痛。