How to write the following query in LINQ

Question

I have a sql query and would like to convert it into linq

 SELECT CAST([Date] AS DATE),
 COUNT([ID]) AS 'Amount of Systems'
 FROM [DemoDB].[dbo].[Servers]
 WHERE [ServerID] IN ('ServerX') AND [Type] = 'Complete'  
 GROUP BY CAST([Date] AS DATE)
 ORDER BY CAST([Date] AS DATE)

this will return the result as follows

enter image description here

What I have tried

 //fromDP and toDP are the names of the Datepicker's
 var query = (this.db.Servers
                     .Where(x => x.Date >= fromDP.SelectedDate.Value && 
                     x.Date <= toDP.SelectedDate.Value));


 var query_Success = query.Count(p => p.Type == "Complete" 
                     && (p.ServerID == "ServerX"));

and I have the result as Count on the whole ( for example, if I select from from April 1st to April 15th , the result is the sum of all "complete"), but I need count for each day in this selected range. the result I will bind to the column chart. how to proceed ?

Why do you need to cast date? Is it not of type Date or Datetime on your database? — Steven Wexler, May 03 '13 at 10:09
http://stackoverflow.com/questions/12238423/linqpad-convert-sql-to-linq-command — user1659922, May 03 '13 at 10:11

Steven Wexler · Answer 1 · 2013-05-03T10:43:02.623

2

this.db.Servers.Where(s => s.ServerId == "ServerX" && s.Type == "Complete")
       .GroupBy(s => s.Date)
       .OrderBy(s => s.Key)
       .Select(g => new { Date = g.Key, AmountOfSystems = g.Count() });

Change the Where clause to read

Where(s => s.ServerId == "ServerX" && s.Type == "Complete" && s.Date >= fromDP.SelectedDate.Value && s.Date <= toDP.SelectedDate.Value)

to filter to a limited date range.

EDIT

As @vvs0205 suggested. Use EntityFunctions class to manipulate the date column as you please: http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx

edited May 03 '13 at 10:43

answered May 03 '13 at 10:17

Steven Wexler

16,589
8
53
80

Thanks for the update @steaks, but this query returns a LIST ? how to bind this list to my Chart value ? – Indhi May 03 '13 at 12:33

Vyacheslav Volkov · Accepted Answer · 2013-05-03T10:56:06.683

2

If I understood correctly the author wants to use only the date without the time. To do this with EF we can use the method EntityFunctions.TruncateTime for trimming the time portion. I will build on @steaks answer:

db.Servers.Where(s => s.ServerId == "ServerX" && s.Type == "Complete")
            .GroupBy(s => EntityFunctions.TruncateTime(s.Date))
            .OrderBy(s => s.Key)
            .Select(g => new {Date = g.Key, AmountOfSystems = g.Count()});

edited May 03 '13 at 10:56

answered May 03 '13 at 10:33

Vyacheslav Volkov

4,592
1
20
20

@ws0205 thanks for your update. but I just want to have distinct date values in return. – Indhi May 03 '13 at 12:34

score 1 · Answer 3 · answered May 03 '13 at 10:19

Something like this

var fromDate = fromDP.SelectedDate.Value;
var toDate= toDP.SelectedDate.Value;

var q = from server in this.db.Servers
                where (server.Date >= fromDate && server.Date<=toDate && server.ServerID="ServerX" && server.Type=="Complete")
                group server  by server.Date
                into g
                orderby g.Key
                select new
                    {
                        Date = g.Key,
                        Count = g.Count()
                    };

var results = q.ToList();

How to write the following query in LINQ

3 Answers3