c# - 如何将多个 MIN 函数编码为一个 LINQ to DataSet 查询

Question

好的，在寻求帮助之前，我已经研究了一段时间。我有 4 个基于 Oracle 的 SQL 查询，它们一个接一个地传递请求的数据。我在 .NET 中工作，我认为(我希望)我可以使用 LINQ to DataSet 将第一个查询传递给第二个，然后传递给第三个，最后传递给第四个。让我头疼的是从第一个到第二个。我可以通过正常的 ODP.NET 和 C# 查询将第一个查询获取到 DataSet 中。

这是第二步

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 to DataSet，它将把它填充到它自己的 DataSet 中。这就是我到目前为止所拥有的，我知道我离得远了

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 to DataSet 查询来包含我在 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 to sql 来解决您的问题，而不是 linq to datasets。不用在 sql 中编写查询，您可以直接使用 c# 来完成，这可以为您提供更多选项(自定义对象)，而没有处理数据集和 friend 的限制和麻烦。