none
Complicated query in Linq RRS feed

  • Question

  • Hi,

    I have the following tables:

    1. Units table:

    • ID
    • Serial number
    • Name
    • Location

    2. UnitsData table:

    • ID
    • UnitID (int)
    • DateOfData (datetime)
    • Value (float)

    I have to show the following information in a gridview and I do not succeed doing it through Linq and need help:

    I have to show a grid with one line for every unit with the following information about it:

    1. Unit’s name.
    2. Unit’s serial number
    3. Unit’s location.
    4. Average of the value column for the unit in the last 30 days.
    5. The latest DateOfData of the unit.
    6. The value in the latest dateofdata.
    7. true if the latest data was received in the last 48 hours. else - false
    8. true if I got the same value in different dates in the past 48 hours.

    I tried to do something like this but didn't succeed:

    var result =
        db.UnitsDatas.Select(data => new
        {
            UnitName = data.Unit.Name,
            SerialNumber = data.Unit.SerialNumber,
            Location = data.Unit.Location,
            //HourlyAvarageValue = from row in db.UnitsDatas.AsEnumerable()
            //                     group row by new { y = row.DateOfData.Value.Year, m = row.DateOfData.Value.Month, d = row.DateOfData.Value.Day } into grp
            //                     select grp.Zip(grp.Skip(1), (row1, row2) => row2.PowerConsumptionValue - row1.PowerConsumptionValue).Average(),
            LastDate = from row in db.UnitsDatas.AsEnumerable()
                       group row by new { id = row.UnitID } into grp
                       select grp.OrderBy(a => a.DateOfData).First(),
    
            LastValue = from row in db.UnitsDatas.AsEnumerable()
                        group row by new { id = row.UnitID } into grp
                        select  grp.OrderBy(a => a.DateOfData).First().PowerConsumptionValue
    
        });
     

    I also posted a question in the SQL but decided to do it through linq in the end because I think is is simpler for me.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8feaccd8-c745-4bf0-8055-acf7852483a8/need-help-in-writing-a-query?forum=transactsql

    I know it is a big question.

    I will appreciate any help about it.

    Thanks

    Tuesday, October 8, 2013 12:13 PM

Answers

  • Hi YI1010;

    This Linq to SQL query should be what you are looking for or very close to it.

    using System.Data.Linq.SqlClient;
    
    // Dates used in query
    DateTime nowLess30 = DateTime.Now.AddDays(-30);
    DateTime nowLess2 = DateTime.Now.AddDays(-2);
    
    var results = from u in context.Units
                  join d in context.UnitsData on u.ID equals d.UnitID
                  group new { u, d} by new {ID = u.ID, Data = d.UnitID} into g
                  select new 
                  {
                      Name = g.Select (x => x.u.Name ).FirstOrDefault( ),
                      SerialNumber = g.Select (x => x.u.SerialNumber ).FirstOrDefault( ),
                      Location = g.Select (x => x.u.Location ).FirstOrDefault( ),
                      AverageValue = g.Where (x => SqlMethods.DateDiffDay(nowLess30, x.d.DateOfData) <= 30).Average (x => x.d.Value ),
                      LatestDateOfData = g.OrderByDescending(x => x.d.DateOfData ).Select (x => x.d.DateOfData).FirstOrDefault(),                 
                      LatestValue = g.OrderByDescending(x => x.d.DateOfData ).Select (x => x.d.Value).FirstOrDefault(),
                      Last48Hours = g.Any(x => SqlMethods.DateDiffDay(nowLess2, x.d.DateOfData) <= 2) ? true : false,
                      SameValueLast48 = (from v in g.Select(x => x.d)
                                         group v by v.Value into val
                                         from v2 in val
                                         where SqlMethods.DateDiffDay(nowLess2, v2.DateOfData) <= 2
                                         select v2.DateOfData).Count() > 1 ? true : false
                  };

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by YI1010 Sunday, October 13, 2013 10:55 AM
    Wednesday, October 9, 2013 4:41 AM