58184

LINQ Case statement with COUNT and GROUP [closed]

Question:

Can you help me translate this Mysql into Linq please :

SELECT distcode, COUNT(cid) as count_all_case ,count(case when labid = 1 then cid end) as count_id1 ,count(case when labid = 2 then cid end) as count_id2 ,count(case when labid = 3 then cid end) as count_id3 FROM labcase WHERE SEQ is not NULL AND date_serv BETWEEN 20160101 AND 20161001 GROUP BY distcode

Thanks

Answer1:

Well, you'd need to look at the generated SQL, but the filtering and grouping parts are simple - it's only the counting bit that's particularly tricky. You can probably do that within the projection of the group:

var start = new DateTime(2016, 1, 1); var end = new DateTime(2016, 10, 1); var query = from labCase in db.LabCase where labCase.Seq != null && labCase.DateServ >= start && labCase.DateServ <= end group labCase by labCase.DistCode into g select new { DistCode = g.Key, CountId1 = g.Count(x => x.LabId == 1), CountId2 = g.Count(x => x.LabId == 2), CountId3 = g.Count(x => x.LabId == 3) };

Answer2:

Here is the Lambda Query

var query = db.labcase .Where(p => p.SEQ != null && p.date_serv >= new Datetime(2016,01,01) && p.date_serv <= new Datetime(2016,01,10)) .GroupBy(f => new {distcode} , (key , elem) => new { key.distcode ,count_id1 = elem.Count(x => x.LabId == 1) ,count_id2 = elem.Count(x => x.LabId == 2) ,count_id3 = elem.Count(x => x.LabId == 3) })

Recommend

  • In Selenium IDE how can you select an option by index in a dropdown menu?
  • How to correct styling in a horizontal 2 level superfish menu
  • In Android, using java: determine string length of mix-bytes character String?
  • Combine loop values from different SOAP methods
  • Linux: Is it possible to sandbox shared library code
  • How do I set the Remember Token in Laravel with Google OAuth
  • How to zoom webView with pdf file
  • getting the current time at specific timezone
  • C++ printf Rounding?
  • How to work with duplicate entries in Pandas Data Frame?
  • How do you increment a count while using the sleep function?
  • How to use an EventToCommand with an editable Combobox to bind TextBoxBase.TextChanged with a comman
  • Python how to create a dict of dict of list with defaultdict
  • Unable to catch exception from TimerTask thread
  • Count the number of non-null values in a Spark DataFrame
  • Converting float[,] to list in f#?
  • How to model a mixture of finite components from different parametric families with JAGS?
  • how to sort an arraylist that contains string arrays?
  • How to customize whisker lines on a geom_box plot differently than the lines of the box itself
  • Extract data between rows r
  • Android Chronometer starts and stops but carries on counting when stopped
  • pyodbc doesn't report sql server error
  • How VBA declared Volatility works
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Read text file and split every line in MSBuild
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • How to set/get protobuf's extension field in Go?
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Cassandra Data Model
  • Trying to switch camera back to front but getting exception
  • Importing jscolor library in angular 2
  • How do you troubleshoot character encoding problems?
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • embed rChart in Markdown
  • LevelDB C iterator
  • Django query for large number of relationships
  • How can i traverse a binary tree from right to left in java?