none
Group By/Having Problems RRS feed

  • Question

  • SELECT DocentShiftSchedule.DocentAssigned, Count(*) AS NumShifts, DocentInfo.[MaxDaysPerMonth]
     FROM (ScheduledEvent RIGHT JOIN DocentShiftSchedule
     ON ScheduledEvent.EventId = DocentShiftSchedule.EventId)
     INNER JOIN DocentInfo ON DocentShiftSchedule.DocentAssigned = DocentInfo.[Index]
     WHERE ((Month([ScheduledEvent].[Day])=6) AND (Year([ScheduledEvent].[Day])=2010))
     GROUP BY DocentShiftSchedule.DocentAssigned, DocentInfo.MaxDaysPerMonth, DocentInfo.MaxDaysPerMonth
     HAVING (Count(*)>=[DocentInfo].[MaxDaysPerMonth])


    How do I do this in LINQ C#? In particular, I'm having trouble with the "Group By" and "Having' part. I'm currently at this:

    from se in ScheduledEvents
    join dss in DocentShiftSchedules on se.EventId equals dss.EventId
    join d in DocentInfo on dss.DocentAssigned equals d.Index
    where se.Day >= new DateTime(2010, 6, 1) &&
    se.Day <= new DateTime(2010, 6, 30) 
    orderby d.Index
    select new
    {
    DIndex = d.Index,
    MaxDays = d.MaxDaysPerMonth
    }
    Sunday, February 27, 2011 1:50 PM

All replies

  • Hello ZooGuy1492,

     

    Welcome to the LINQ to SQL Forum.

    According to your description, about Group By and Having there is a expert's blog talking about them, please see this:

    While querying with LINQ, some times we will have to use the group By clause. But many a times we also want to use the having clause of SQL with the group by clause in the LINQ. There is no direct having keyword in LINQ to do this we need to use the where clause itself.

    You can query the LINQ to SQL to have both Group By and Having like this.

    var categories =   from p in db.Products
                   group p by p.CategoryID into g
                   where g.Count() >= 10
                   select new {
                          g.Key,
                          ProductCount = g.Count()
                         };

    But there are occasion when you might not to group with an existing column but with a column which calculated at the run time itself. For those conditions you can write the query like this.

    var categories =   from p in db.Products
     
                              group p by new { Criterion = p.UnitPrice > 10 } into g
                               where g.Count() >= 10
                               select new {
                                        g.Key,
                                        ProductCount = g.Count()
                                       }; 

    I hope this can help you.

     

    Have a ncie day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 28, 2011 6:38 AM
    Moderator
  • Thanks for the post Jackie.

    I had seen that blog article but I couldn't get it to work in my case where I was using several joins across several databases.

    Paul P.

    Monday, February 28, 2011 10:15 PM
  • Hi Paul,

     

    Thanks for your feedback.

    If you have any time could you please tell us what do you mean of "using several joins across several databases" ? As far as I know, there's no way to use several joins across several databases in one query.

    So if my understanding is correct, I think you could choose another way to reach your goal.

    Please feel free to let me know if I understood you!

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 1, 2011 3:37 AM
    Moderator
  • Okay.. So I mis-typed.. I meant "using several joins across several tables in the same database". Sorry for the confusion.

     

    Wednesday, March 2, 2011 12:39 AM
  • Hi Paul,

     

    Well, I think you would like to know how to use multiple joins in linq. Am I right? If so, I can show you an example as below:

    T-SQL:

    SELECT     dbo.MealType.mealTypeName, dbo.menuCategories.menuCategoryName, dbo.MenuItems.MenuItemName, dbo.MenuItems.TodayMenu,
                          dbo.MenuItems.MenuItemDescription
    FROM         dbo.MenuItems INNER JOIN
                          dbo.menuCategories ON dbo.MenuItems.MenuCategoryID = dbo.menuCategories.MenuCategoryID INNER JOIN
                          dbo.MealType ON dbo.MenuItems.MealTypeID = dbo.MealType.mealTypeID AND dbo.menuCategories.MealTypeID = dbo.MealType.mealTypeID
    WHERE     (dbo.MealType.mealTypeName = 'lunch') AND (dbo.MenuItems.TodayMenu = 1)

     

    Convert to LINQ:

    var myMenu = (from m in db.MenuItems
      join c in db.MenuCategories on m.MenuCategoryID equals c.MenuCategoryID
      join t in db.MealTypes on m.MealTypeID equals t.MealTypeID
      where c.MealTypeID == t.MealTypeID && m.TodayMenu == true && t.MealTypeName == "lunch"
      select new
      {
       t.MealTypeName,
       c.MenuCategoryName,
       m.MenuItemName,
       m.TodayMenu, 
       m.MenuItemDescription  
      });
    
    
    I hope this can help you.
    Have a nice day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 2, 2011 2:42 AM
    Moderator
  • Jackie,

    You might want to look back to my original post. It contained the exact SQL I'm trying to implement in LINQ.  In short, I need multiple joins AND a GroupBy clause.

    Paul P.

    Wednesday, March 2, 2011 10:58 PM
  • Hi Paul,

     

    Sorry for miss the key point. How about this sample code below?

    var queryDestination =

     

    from tl1 in lHotelL_1

     

    join tl2 in lHotelL_2 on tl1.Field<int>("Resort_Id") equals tl2.Field<int>("Resort_Id")

     

    join t1 in lHotels_1 on tl2.Field<string>("Hotel_ID") equals t1.Field<string>("Id")

     

    join fp in

    (

    from fp1 in(

     

    from t2 in lHotels_2

     

    select new {hotel_id = t2.Field<int>("Hotel_Id"), picture_id = t2.Field<int>("Photos_Id")})

     

    group fp1 by new {fp1.hotel_id} into r

     

    select new {hotel_ID = r.Key.hotel_id,picture_ID = r.Max(fp1=>fp1.picture_id)}) on t1.Field<int>("Hotel_Id") equals fp.hotel_ID

     

    join t3 in lHotels_3 on fp.picture_ID equals t3.Field<int>("Photos_Id")

     

    where tl1.Field<int>("Destination_Id") == int.Parse(strCity)

     

     

    select new

    {

    yt_resort_id = tl1.Field<

    int>("Resort_Id")

    ,

    yt_hotel_id = tl2.Field<

    string>("Hotel_ID")

    ,

    yt_hotel_name = t1.Field<

    string>("Name")

    ,

    yt_hotel_description = t1.Field<

    string>("Description")

    ,

    yt_hotel_picture = t3.Field<

    string>("Photo_Text")

    }

     I hope this can help.

     

    Regards,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 3, 2011 3:39 AM
    Moderator
  • Jackie,

    I couldn't figure out how to implement my SQL statement using LINQ based on your example. For example, I didn't understand the following syntax:

    yt_resort_id = tl1.Field<int>("Resort_Id")

    Instead, I used  SqlDataReader() with my SQL syntax.

    Paul P.

    Monday, March 7, 2011 11:10 PM
  • Hi Paul,

     

    These codes aims to help you translate SQL statement to LINQ statement, we're not talking about SqlDataReader .

    And I think you could not care about “yt_resort_id = tl1.Field<int>("Resort_Id")“ you could write your own filter and set what you want in the result collection. What I want to show you in that code is the method how to use multiple joins and group by in translating SQL to LINQ.

    I hope you could understand this kind of method.

    Thanks for your feedback.

     

    Have a nice day,

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 8, 2011 3:09 AM
    Moderator
  • Let me rephrase part of my response. How do I get the tl1.Field<int>("Resort_Id") syntax to work in my LINQ to SQL application. I can not seem to find a "Field" method?

    In particular, the table I'm interested in querying has 21 fields (SunA, SunP, SunE, MonA, MonP, MonE, TueA, TueP, TueE, etc) and I want to be able to select only those records that have one of these 21 fields set (e.g. SunA=1).

    How do I do a query where the field name is a string in LINQ to SQL? 

    Paul P.

    Tuesday, March 15, 2011 11:37 PM
  • Hi Paul,

    Actually, I am very confused now. Sorry for misunderstanding, what do you mean of "I want to be able to select only those records that have one of these 21 fields set "?

    Could you show us the conditions you want to add to the query or the result you want to get?

    Thanks for your reply!

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 16, 2011 9:26 AM
    Moderator
  • I'm looking for a better solution than these cascading if statements in this:

        public static void SelectDocentInfoByBasicAvail(String BasicAvail, out List<DocentInfo> docentInfos)
        {
    
          DocentDataDataContext con = new DocentDataDataContext();
          
          //var query =
            IQueryable<DocentInfo> query =
              (from d in con.DocentInfos
               where (d.OnLeave == false)
               orderby d.NVFlag ascending,
                   d.LastName,
                   d.FirstName
               select d);
          if (BasicAvail == "SunA") query = query.Where(p => p.SunA == true);
          else if (BasicAvail == "MonA") query = query.Where(p => p.MonA == true);
          else if (BasicAvail == "TueA") query = query.Where(p => p.TueA == true);
          else if (BasicAvail == "WedA") query = query.Where(p => p.WedA == true);
          else if (BasicAvail == "ThuA") query = query.Where(p => p.ThuA == true);
          else if (BasicAvail == "FriA") query = query.Where(p => p.FriA == true);
          else if (BasicAvail == "SatA") query = query.Where(p => p.SatA == true);
          else if (BasicAvail == "SunP") query = query.Where(p => p.SunP == true);
          else if (BasicAvail == "MonP") query = query.Where(p => p.MonP == true);
          else if (BasicAvail == "TueP") query = query.Where(p => p.TueP == true);
          else if (BasicAvail == "WedP") query = query.Where(p => p.WedP == true);
          else if (BasicAvail == "ThuP") query = query.Where(p => p.ThuP == true);
          else if (BasicAvail == "FriP") query = query.Where(p => p.FriP == true);
          else if (BasicAvail == "SatP") query = query.Where(p => p.SatP == true);
          else if (BasicAvail == "SunE") query = query.Where(p => p.SunE == true);
          else if (BasicAvail == "MonE") query = query.Where(p => p.MonE == true);
          else if (BasicAvail == "TueE") query = query.Where(p => p.TueE == true);
          else if (BasicAvail == "WedE") query = query.Where(p => p.WedE == true);
          else if (BasicAvail == "ThuE") query = query.Where(p => p.ThuE == true);
          else if (BasicAvail == "FriE") query = query.Where(p => p.FriE == true);
          else if (BasicAvail == "SatE") query = query.Where(p => p.SatE == true);
    
          docentInfos = query.ToList();
          return;
        }
    
    Thursday, March 17, 2011 12:35 AM
  • Hi,

    Your query would be very much smaller if you use Dynamic Linq extension.

    Use the Linq Dynamic Query Library (download includes examples).

    Check out ScottGu's blog for more examples.

    Kind regards,


    Saturday, April 2, 2011 8:39 PM