c# - LINQ中的Case查询相当于sql查询

Question

我有一个包含调用者、被调用者字段的数据集，我需要一些 LINQ 查询来操作此数据集，相当于此 SQL 查询

SELECT CASE
     WHEN caller < callee THEN callee
     ELSE caller
   END      AS caller1,
   CASE
     WHEN caller < callee THEN caller
     ELSE callee
   END      AS caller2,
   Count(*) AS [Count]
    FROM   YourTable
   GROUP  BY CASE
        WHEN caller < callee THEN callee
        ELSE caller
      END,
      CASE
        WHEN caller < callee THEN caller
        ELSE callee
      END 

Answer 1

这就是你所追求的吗？

DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable("dataTable");

dataTable.Columns.Add("caller", typeof(String));
dataTable.Columns.Add("callee", typeof(String));

dataTable.Rows.Add("999", "888");
dataTable.Rows.Add("888", "999");
dataTable.Rows.Add("999", "555");
dataTable.Rows.Add("555", "333");
dataTable.Rows.Add("555", "999");

dataSet.Tables.Add(dataTable);

string filter = "999";

var result = dataSet.Tables["dataTable"].Select().Select(dr =>
    new
    {
        caller1 = StringComparer.CurrentCultureIgnoreCase.Compare(dr["caller"], dr["callee"]) < 0 ? dr["callee"] : dr["caller"],
        caller2 = StringComparer.CurrentCultureIgnoreCase.Compare(dr["caller"], dr["callee"]) < 0 ? dr["caller"] : dr["callee"]
    })
        .Where(dr => String.IsNullOrEmpty(filter) || dr.caller1 == filter || dr.caller2 == filter)
        .GroupBy(drg => new { drg.caller1, drg.caller2 } )
        .Select(drg => new { drg.Key.caller1, drg.Key.caller2, count = drg.Count() });