none
A Question about Dynamic Groupby! RRS feed

  • Question

  • I'm a newer with LINQ TO Entites 3.5. I want to create a dynamic query with "group by".
    the code looks like that:
    IQueryable<TOHOSPITAL_TBL> res = from totbl in TOHOSPITAL_TBL where XXX;
    var rs = res.GroupBy(c => new {c.TOHOSPITAL_TIME.Year,Half = (c.TOHOSPITAL_TIME.Month -1)/ 6 })
      .Select(c => new { c.Key, gs =
         c.GroupBy(d => d.Sign).Select(m => new { m.Key, gs2 =
              m.GroupBy(n => new { n.TOHOSPITAL_ID }) }) });
    the condition of groupby is dynamic and the condition's count is dynamic,user can choose & add & remove the condition by UI,so
    How can i dynamicy group by the condition use linq?

    i have seen the content about GroupByMany Method,but it`s seemed to be used to deal with the IEnumerable<T>,and could not get the data from DB in one query.

    Thank you for help!


    Just On Life!
    Friday, June 24, 2011 4:38 PM

Answers

  • Hi Joe,

    I did not do many tests, but I think such a sample query can be helpful to you. 

    var query = context.Entities.AsQueryable().GroupBy("new((it.Data - 1) / 3 as Quarter, (@0 - it.Data2) as Year, it.ID)", "it", sysDate).Select("new(it.Key as Key, it.Count as Count)");

    You need to change the query to meet the detailed scenario.  Please remember to use the Dynamic LINQ library in your project.  Also, there will be a manual about how to use this library in the sample, it is named Dynamic Expression.html.  

    Please feel free to let me know if you have any questions.

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    • Marked as answer by YuanBin Joe Sunday, July 10, 2011 3:23 PM
    Wednesday, June 29, 2011 9:45 AM
    Moderator
  • Thank you Michael

    I downloaded the  Dynamic LINQ library and finded the Dynamic Expression.html,that's great!

    I have done the test ,the right query be as the follow

    var res2 = _analysisEntity.TOHOSPITAL_TBL.GroupBy("new ((TOHOSPITAL_TIME.Month - 1)/3 as Q,(@0-it.TOHOSPITAL_TIME.Year) as Y)", "it", sysDate.Year).Select("new (it.Key as Key,it.Count() as Count)");

    you're welcome!!

    Thanks a lot!

     


    Just On Life!
    • Marked as answer by YuanBin Joe Friday, July 15, 2011 3:02 PM
    Sunday, July 10, 2011 3:28 PM

All replies

  • I'm a newer with LINQ TO Entites 3.5. I want to create a dynamic query with "group by".
    the code looks like that:
    IQueryable<TOHOSPITAL_TBL> res = from totbl in TOHOSPITAL_TBL where XXX;
    var rs = res.GroupBy(c => new {c.TOHOSPITAL_TIME.Year,Half = (c.TOHOSPITAL_TIME.Month -1)/ 6 })
      .Select(c => new { c.Key, gs =
         c.GroupBy(d => d.Sign).Select(m => new { m.Key, gs2 =
              m.GroupBy(n => new { n.TOHOSPITAL_ID }) }) });
    the condition of groupby is dynamic and the condition's count is dynamic,user can choose & add & remove the condition by UI,so
    How can i dynamicy group by the condition use linq?

    i have seen the content about GroupByMany Method,but it`s seemed to be used to deal with the IEnumerable<T>,and could not get the data from DB in one query.

    Thank you for help!


    Just On Life!


    After i send a mail to Michael,i think a lot about this question,i'm not sure but a possible solution is that join all the tables which is needed,and use linq.dynamic to groupby("new (condition1,condition2)")。。。 ?

    I appreciate any help you can provide

     

     


    Just On Life!
    Friday, June 24, 2011 4:58 PM
  • Hi,

    Could you please make your request be more specific?   E.g.  What's the input and what's the dynamic GroupBy output?  

    I remember I used a dynamic GroupBy in a LINQ to XML query here, http://social.msdn.microsoft.com/Forums/en/xmlandnetfx/thread/7f60e779-165b-4447-9223-14cd911d7ac7.  

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Monday, June 27, 2011 3:26 AM
    Moderator
  • HI,Michael
    I appreciate your help very much!

    I'm sorry that i hadn't made the question clearly.
    now i clean it up!

    There is two tables in DB.one is TOHOSPITAL_TBL,the other is PATIENT_TBL.

    TOHOSPITAL_TBL contains 4 fields: TOHOSPITAL_ID,TOHOSPITAL_TIME,PATIENT_ID,UPTIME.
    PATIENT_TBL    contains 5 fields: PATIENT_ID,Name,BIRTHDAY,SEX_ID,UPTIME.

    Then,the user can chose the count period (of the five choice:Year,Half a Year,Quarter,Month,Day) and count item
    (multi check item: to hospital people's age & sex) .

    So,if the user chose count period as Quarter,and count item as age&sex , i think that i can do like the following code.(i use EF3.5)
    DateTime sysDate = DateTime.Now.Date;
    var rs1 = Entity.TOHOSPITAL_TBL.Join(Entity.PATIENT_TBL,tohos=>tohos.PATIENT_ID,pati=>pati.PATIENT_ID,(tohos,pati)=>new
    {TOHOSPITAL_TIME,TOHOSPITAL_ID,BIRTHDAY,SEX_ID});
    var rs2 = rs1.GroupBy(c => new {c.TOHOSPITAL_TIME.Year,Quarter = (c.TOHOSPITAL_TIME.Month -1)/ 3,Age = (sysDate-c.BIRTHDAY), SEX_ID});
    var rs3 = rs2.Select(d=>new {d.Key,Count = d.Sum(e=>e.Count)});

    Then i can display the result: d.Key.Year ,d.Key.Quarter,d.Key.Age,d.Key.Sex_ID,d.Count in pages.
    I have seen your answer in another thread,that's perfect.but i don't know how can use the linq.Dynamic to fit the caculate conditions.


    Just On Life!
    Monday, June 27, 2011 5:00 PM
  • Hi Joe,

    I did not do many tests, but I think such a sample query can be helpful to you. 

    var query = context.Entities.AsQueryable().GroupBy("new((it.Data - 1) / 3 as Quarter, (@0 - it.Data2) as Year, it.ID)", "it", sysDate).Select("new(it.Key as Key, it.Count as Count)");

    You need to change the query to meet the detailed scenario.  Please remember to use the Dynamic LINQ library in your project.  Also, there will be a manual about how to use this library in the sample, it is named Dynamic Expression.html.  

    Please feel free to let me know if you have any questions.

    Good day!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    • Marked as answer by YuanBin Joe Sunday, July 10, 2011 3:23 PM
    Wednesday, June 29, 2011 9:45 AM
    Moderator
  • Thank you Michael

    I downloaded the  Dynamic LINQ library and finded the Dynamic Expression.html,that's great!

    I have done the test ,the right query be as the follow

    var res2 = _analysisEntity.TOHOSPITAL_TBL.GroupBy("new ((TOHOSPITAL_TIME.Month - 1)/3 as Q,(@0-it.TOHOSPITAL_TIME.Year) as Y)", "it", sysDate.Year).Select("new (it.Key as Key,it.Count() as Count)");

    you're welcome!!

    Thanks a lot!

     


    Just On Life!
    • Marked as answer by YuanBin Joe Friday, July 15, 2011 3:02 PM
    Sunday, July 10, 2011 3:28 PM
  • It's my pleasure! 

    Have a nice day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Monday, July 11, 2011 2:52 AM
    Moderator
  • Now I have a new question that if i use the strong type, when the user choose the type of count period (of the five choice:Year,Half a Year,Quarter,Month,Day) how can i made the following four method into one method by some parameter or other else? any idea or impossible?
    new { tohos.TOHOSPITAL_TIME.Year } maybe Expression<Func<T,M>>,but how about new Dates { Year = gs.Key.Year, Half = gs.Key.Half }?
    private IEnumerable<StatisRet> cotVisitor_Time_Year(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }
    private IEnumerable<StatisRet> cotVisitor_Time_Half(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year, Half = (tohos.TOHOSPITAL_TIME.Month - 1) / 6 }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year, Half = gs.Key.Half },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }
    private IEnumerable<StatisRet> cotVisitor_Time_Quarter(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year, Quarter = (tohos.TOHOSPITAL_TIME.Month - 1) / 3 }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year, Quarter = gs.Key.Quarter },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }
    private IEnumerable<StatisRet> cotVisitor_Time_Month(DateTime start, DateTime end)
    {
    var q = from tohos in orgVisitor(start, end)
    group tohos by new { tohos.TOHOSPITAL_TIME.Year, tohos.TOHOSPITAL_TIME.Month }
    into gs
    select new StatisRet
    {
    DateTile = new Dates { Year = gs.Key.Year, Month = gs.Key.Month },
    Group = gs.GroupBy(c => new
    {
    c.TOHOSPITAL_TIME.Hour,
    })
    .Select(d => new StatisLine { Id = (int)d.Key.Hour, Count = d.Count() })
    .OrderBy(m => m.Id)
    };
    return q;
    }

    internal class StatisRet : IComparable
    {
    internal Dates DateTile { get; set; }
    internal IEnumerable<StatisLine> Group { get; set; }
    int IComparable.CompareTo(object right)
    {
    if (!(right is StatisRet))
    throw new ArgumentException("Argument not a StatisRet",
    "right");
    StatisRet rightCustomer = (StatisRet)right;
    return CompareTo(rightCustomer);
    }


    public int CompareTo(StatisRet input)
    {
    return DateTile.getBeginDT().CompareTo(input.DateTile.getBeginDT());
    }
    }

    internal class StatisLine
    {
    internal int Id { get; set; }
    internal double Count { get; set; }
    }
    internal class Dates : CountsBase, IComparable<Dates>
    {
    internal int Quarter { get; set; }
    internal int Half { get; set; }

    public int CompareTo(Dates input)
    {
      return XXX;
    }
    internal Dates()
    {
    Day = -1;
    Year = -1;
    Month = -1;
    Quarter = -1;
    Half = -1;
    }
    }


    Just On Life!
    Tuesday, July 12, 2011 12:41 AM
  • Hi Joe,

    Do you mean you want the retrieve a strong typed result using dynamic query?  The dynamic query returns IQueryable collection instead of IQueryable<T> collection which we cannot specify the return type.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Tuesday, July 12, 2011 5:23 AM
    Moderator
  • Hi,Michael

      I learned from you how to use dynamic.cs  to write linq  by the dynamic condition  ,

      But now I mean how to use the strong type to write linq by the dynamic condition like what i wrote above. for example to use Expression<Func<xx,xx>>.I'm not sure that whether  the strong type  can do the same thing like the dynamic api~

    Thanks!!

     

     


    Just On Life!
    Tuesday, July 12, 2011 9:30 AM
  • Oh, I know.  But it does not look like dynamic queries, right?  :) 

    Also, it's actually a new question about LINQ query, can you open a new thread, describe the problem in detaile and let me know the link?  I believe it will be benefit to more community members.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Wednesday, July 13, 2011 8:56 AM
    Moderator