none
Strugle with LINQ RRS feed

  • Question

  • Hi. I have some problem figure out how to construct the LINQ queris. I am making a program for a gold mine. They record noice level every 5 minutes. Here is some sample data recorded whitin 1 hour:

    Time Measurment Time LAeq LAE LAmax LAmin LA05 LA10 LA50 LA90 LA95
    11/07/2011 15:07 0:05:00 51.8 76.6 63.5 34 58.4 56.6 47 37.4 36.5
    11/07/2011 15:12 0:05:00 51.6 76.3 63.9 36.1 58.7 57.2 44.4 38.6 38.1
    11/07/2011 15:17 0:05:00 53.3 78 65.1 35.8 59.4 58.1 47.8 39 38
    11/07/2011 15:22 0:05:00 52.2 77 68.3 38.6 57.5 55.6 42.6 40.2 39.9
    11/07/2011 15:27 0:05:00 54.7 79.5 67.8 38.3 61.2 59.4 48.8 41.3 40.6
    11/07/2011 15:32 0:05:00 53.6 78.4 64.8 37.4 60.4 58.8 45.5 39.6 38.9
    11/07/2011 15:37 0:05:00 56.4 81.2 74.1 38.2 61.4 59.3 47.6 41.6 40.7
    11/07/2011 15:42 0:05:00 54.1 78.8 69 37.1 61.3 58.1 46.1 40.9 39.9
    11/07/2011 15:47 0:05:00 53 77.8 65.8 35.7 59.7 57.8 45.9 39.1 38.1
    11/07/2011 15:52 0:05:00 54.9 79.7 70 35.8 61.5 59.4 46.7 39.9 38.8
    11/07/2011 15:57 0:05:00 54.8 79.6 67.5 35 60.9 58.7 48.7 39.2 38.3

    My task is to make a average for every hour and the output should be someting like this:

    Time Date Measurment Time Average LAeq etc. etc
    15:07 - 15:57 11/7/2011 00:05:00 53.67 ….

    That means i have to group first messure and last messure for every hour recorded (its recording 24 hours/day) and calculate an average for the data.

    What i have done so fare is that the customer open the required exel sheet. I'll show the data in a DataGridView (only for visual control). I put the data into a List<t>

    List<Noice> noice = dal.queries.AddToList(ds1); //ds1 is a DataSet containing data from the DataGridView.

    Can please someone help me with the LINQ queries.

    Thank you in advanced

    Wednesday, July 20, 2011 7:02 AM

Answers

  • Hi Rune;

    The Data you posted was a little confusing with the Time and Measurment Time columns. so not knowing the data type of Measurment Time I made it as a string in the query below. Also you posted the question in Linq to SQL but when you talk about the data its coming from a data set so the below query is assuming a DataSet with a table called goldMine. If that is the case then the below query should be close to what you want.

    var query = from g in ds.Tables["goldMine].AsEnumerable()
          group g by g.Field<DateTime>("Time").Hour into goldGroup      
          select new {    
            Time = goldGroup.First ().Field<DateTime>("Time").TimeOfDay.ToString() + " - " +
                goldGroup.Reverse().First().Field<DateTime>("Time").TimeOfDay.ToString(),
            Date = goldGroup.First().Field<DateTime>("Time").Date.ToShortDateString(),
            MeasurmentTime = goldGroup.First ().Field<string>("Measurment Time"),
            LAeq = goldGroup.Average (g => g.Field<Decimal>("LAeq")).ToString("##0.000"),
            LAE = goldGroup.Average (g => g.Field<Decimal>("LAE")).ToString("##0.000"),
            LAmax = goldGroup.Average (g => g.Field<Decimal>("LAmax")).ToString("##0.000"),
            LAmin = goldGroup.Average (g => g.Field<Decimal>("LAmin")).ToString("##0.000"),
            LA05 = goldGroup.Average (g => g.Field<Decimal>("LA05")).ToString("##0.000"),
            LA10 = goldGroup.Average (g => g.Field<Decimal>("LA10")).ToString("##0.000"),
            LA50 = goldGroup.Average (g => g.Field<Decimal>("LA50")).ToString("##0.000"),
            LA90 = goldGroup.Average (g => g.Field<Decimal>("LA90")).ToString("##0.000"),
            LA95 = goldGroup.Average (g => g.Field<Decimal>("LA95")).ToString("##0.000")
          };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Rune Lisether Thursday, July 21, 2011 3:24 AM
    Wednesday, July 20, 2011 6:05 PM

All replies

  • Hi

     

    The date time format looks little different. If the datetime is in general format then can use the following queries to fetch the results

          DateTime start=DateTime.Parse("11/7/2011 15:07:00");
          DateTime end =DateTime.Parse("11/7/2011 14:07:05");
    
          using (GlodMineDataContext dc = new GlodMineDataContext())
          {
    
            Result result1 = new Result
            {
              AvgLeaQ = dc.GoldMines.Where(x => x.Time >= start && x.Time <= end).Select(x=>x.LAeq).Average(),
              DateString = dc.GoldMines.Where(x => x.Time >= start && x.Time <= end).Select(x => x.Time.ToShortDateString()).First(),
              TimeRange=dc.GoldMines.Where(x => x.Time >= start && x.Time <= end).Min(x => x.Time).ToShortTimeString()+
              " - " + dc.GoldMines.Where(x => x.Time >= start && x.Time <= end).Max(x => x.Time).ToShortTimeString(),
     
            };
    
     
          }
    
    //The result object
    
      public class Result
      {
        public string TimeRange { get; set; }
        public string DateString { get; set; }
    
        public decimal AvgLeaQ { get; set; }
    
      }
    


    Wednesday, July 20, 2011 8:20 AM
  • Sometimes aggregate functions becomes nicer if they get their own line also.

    I have just build on the code from Albin

     

    var query = from row in dc.Goldmines
    		where row.Time >= start &&
    			row.Time <= end
    		select row;
    
    var avgLeaQuery = from row in query select row.LA;
    
    var avgLea = avgLeaQuery.Average;
    

     

    Wednesday, July 20, 2011 8:58 AM
  • Hi Rune;

    The Data you posted was a little confusing with the Time and Measurment Time columns. so not knowing the data type of Measurment Time I made it as a string in the query below. Also you posted the question in Linq to SQL but when you talk about the data its coming from a data set so the below query is assuming a DataSet with a table called goldMine. If that is the case then the below query should be close to what you want.

    var query = from g in ds.Tables["goldMine].AsEnumerable()
          group g by g.Field<DateTime>("Time").Hour into goldGroup      
          select new {    
            Time = goldGroup.First ().Field<DateTime>("Time").TimeOfDay.ToString() + " - " +
                goldGroup.Reverse().First().Field<DateTime>("Time").TimeOfDay.ToString(),
            Date = goldGroup.First().Field<DateTime>("Time").Date.ToShortDateString(),
            MeasurmentTime = goldGroup.First ().Field<string>("Measurment Time"),
            LAeq = goldGroup.Average (g => g.Field<Decimal>("LAeq")).ToString("##0.000"),
            LAE = goldGroup.Average (g => g.Field<Decimal>("LAE")).ToString("##0.000"),
            LAmax = goldGroup.Average (g => g.Field<Decimal>("LAmax")).ToString("##0.000"),
            LAmin = goldGroup.Average (g => g.Field<Decimal>("LAmin")).ToString("##0.000"),
            LA05 = goldGroup.Average (g => g.Field<Decimal>("LA05")).ToString("##0.000"),
            LA10 = goldGroup.Average (g => g.Field<Decimal>("LA10")).ToString("##0.000"),
            LA50 = goldGroup.Average (g => g.Field<Decimal>("LA50")).ToString("##0.000"),
            LA90 = goldGroup.Average (g => g.Field<Decimal>("LA90")).ToString("##0.000"),
            LA95 = goldGroup.Average (g => g.Field<Decimal>("LA95")).ToString("##0.000")
          };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by Rune Lisether Thursday, July 21, 2011 3:24 AM
    Wednesday, July 20, 2011 6:05 PM
  • Thanks to all of you and a spesical thanks to Fernando. Thank you its function perfect

     

    Regards Rune

    Thursday, July 21, 2011 3:25 AM
  • Not a problem, glad I was able to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, July 21, 2011 3:39 AM