none
Complicated Query RRS feed

  • Question

  • Hi

    Im trying to make the following query in LINQ but i have very little success.

    SELECT     Customer.UnitName1, Reply.ReplyDate, ReturnCalls.Days, ReturnCalls.Reply, ReturnCalls.Description  
    FROM         ReturnCalls INNER JOIN  
                          Reply ON ReturnCalls.Reply = Reply.Reply RIGHT OUTER JOIN  
                          Customer ON Reply.CustomerID = Customer.CustomerID  
    WHERE     (Customer.VisitAdressOrt = 'Hallsberg') AND (DATEADD(day, ReturnCalls.Days,  
                              (SELECT     MAX(ReplyDate) AS Expr1  
                                FROM          Reply AS Reply_1  
                                WHERE      (CustomerCustomerID = Customer.CustomerID))) < GETDATE())  
    ORDER BY Reply.ReplyDate DESC 

    The idea is that i want to search the customer table for customers that have not been called for some time. This time depends on when the last call was made and what answer the customer made last.

    The statement
    (SELECT     MAX(ReplyDate) AS Expr1     
                                FROM          Reply AS Reply_1     
                                WHERE      (CustomerCustomerCustomerID = Customer.CustomerID)) 
     

    collects the lastcall made, based on it date and then that date is used in the DATEADD statement as you can se. How do i do this query in linq?

    Any ideas?

    Regards
    -Håkan





    Saturday, January 3, 2009 2:55 AM

All replies

  • Untested so I may have a typo or two in there, but maybe something like this?

    from cust in dc.Customers  
    join repl in dc.Replies on cust.CustomerID equals repl.CustomerID  
    join rc in dc.ReturnCalls on repl.Reply equals rc.Reply  
    from mr in (  
      from maxRepl in dc.Reply  
      where maxRepl.CustomerID == cust.CustomerID  
      group maxRepl by new { foo = "" } into mg  
      select new { MaxReplyDate = mg.Max(m => m.ReplyDate) }  
    ).DefaultIfEmpty()  
    where cust.VisitAdressOrt == 'Rinkeby'  
      && mr.MaxReplyDate.AddDays(rc.Days) < DateTime.Now 
    orderby repl.ReplyDate desc  
    select new { cust.UnitName1, repl.ReplyDate, rc.Days, rc.Reply, rc.Description } 
    Saturday, January 3, 2009 4:30 AM
    Answerer
  • Hi Kristofer

    Thank you for the answer, impressive that you manage to get someting out of my SQL statment.

    When i run it i got two problems

    The first is that the line (second from the end) "order by repl.replydate desc" does not complie. I get a error message: "Error 1 A query body must end with a select clause or a group clause"

    if i remove it it does complie but then i get a runtime error saying that "A group by expression can only contain non-constant scalars that are comparable by the server. The expression with type '<>f__AnonymousType5`1[System.String]' is not comparable."

    You have any ideas what it could be?

    Im guessing that it must be in the :

    from mr in (  
      from maxRepl in dc.Reply  
      where maxRepl.CustomerID == cust.CustomerID  
      group maxRepl by new { foo = "" } into mg  
      select new { MaxReplyDate = mg.Max(m => m.ReplyDate) }  
    ).DefaultIfEmpty()

    statement. But since i dont understand much of it its difficult to know whats wrong :)

    Thanks
    -Håkan
    Saturday, January 3, 2009 6:30 AM
  • Knoen said:

    if i remove it it does complie but then i get a runtime error saying that "A group by expression can only contain non-constant scalars that are comparable by the server. The expression with type '<>f__AnonymousType5`1[System.String]' is not comparable."

    You have any ideas what it could be?


    Yes, change the grouping part to 'group maxRepl by 1 into mg'.
    Saturday, January 3, 2009 7:00 AM
    Answerer
  • Thank you!

    It works!

    But what does that part of the query mean? group maxRepl by 1 into mg does not make any sence to me :)

    Thank you again, u saved my day!
    Saturday, January 3, 2009 7:58 AM
  •  
    Knoen said:

    Thank you!

    It works!

    But what does that part of the query mean? group maxRepl by 1 into mg does not make any sence to me :)

    Thank you again, u saved my day!


    You're welcome. :)

    The "group maxRepl by 1 into mg" is just a side effect of linq requiring a 'group by' whenever using aggregates in a query. "group by 1" means it will all be grouped by a constant value, effectively the same as not doing any grouping at all. Maybe some future language update will support 'aggregate only - no groupings' queries but until then the workaround is to group by constant values.
    Sunday, January 4, 2009 3:42 AM
    Answerer