CODEWITHSUNDEEP
c#sqllinq
Rajeev Kumar
Rajeev Kumar

Reputation: 4963

Case Query in LINQ equivalent to sql query

I have a dataset that contains caller, callee fiels and i need some LINQ query to operate on this dataset equivalent to this sql query

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

Upvotes: 5

Views: 1261

Answers (3)

Seb Boulet
Seb Boulet

Reputation: 981

Is this what you are after?

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() });

Upvotes: 3

Doctor Jones
Doctor Jones

Reputation: 21664

This is what you're looking for, note the use of range variables which helps us reuse the case statement and simplify the LINQ query.

var query = from y in YourTable
            //place the result of the case statement into a range variable so we can reuse it for the grouping
            let caller1 = y.caller < y.callee ? y.callee : y.caller
            let caller2 = y.caller < y.callee ? y.caller : y.callee
            //group the results
            group y by new { caller1, caller2 } into grouping
            select new
            {            
                //get the range variables from the grouping key
                grouping.Key.caller1,
                grouping.Key.caller2,
                //get the count of the grouping
                Count = grouping.Count(),
            };

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47048

SELECT CASE
  WHEN caller < callee THEN callee
  ELSE caller
END      AS caller1

is translated into

caller1 = x.caller < x.callee ? x.callee : x.caller

Upvotes: 0

Related Questions