none
Converting SQL To Linq RRS feed

  • Question

  • I have some SQL which returns Subject items in a descending order with the price and only returns the subject once if listed twice (in order to get the total price (so using SUM))

    I have tried doing the same with Linq to SQL but having problems in achieving the same result

    query = from si in myDc.SubjectItems
    			join o in myDc.Orders on si.OrderId equals o.Id
    			join t in myDc.Transports on o.Id equals t.OrderID
    			join s in myDc.Subjects on si.SubjectId equals s.id
    			where (t.State == 1)
    			group si by si.Subject.Price into gp // my SQL has multiple group bys so not sure how to add them to this query?
    			select new Subject
    			{
    				 SubjectName = s.SubjectName,
    				 Price = s.Price
    			};
    
    return query.ToList();

    1. Does anyone know how i could add more group by items?
    2. How to add the equivalent SQL Sum function to the above?
    Friday, June 8, 2018 8:31 AM

All replies

  • Hi Pure Deal,

    >>1. Does anyone know how i could add more group by items?2. How to add the equivalent SQL Sum function to the above?

    Please refer to the following code, which use new to group by multiple fields, and use sum to calculate total price.


    query = from si in myDc.SubjectItems
    			join o in myDc.Orders on si.OrderId equals o.Id
    			join t in myDc.Transports on o.Id equals t.OrderID
    			join s in myDc.Subjects on si.SubjectId equals s.id
    			where (t.State == 1)
    			group new { si, o, t, s} by new { si.Subject.Price, si.OtherFileld} into gp // my SQL has multiple group bys so not sure how to add them to this query?
    			select new Subject
    			{
    				 SubjectName = 
                                     gp.Key.SubjectName,
    				 Price = gp.Sum(t=>t.si.Price)
    			};
    
    return query.ToList();

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 8, 2018 9:32 AM
    Moderator
  • I now get "Explicit construction of entity type 'Subject' in query is not allowed.". I was going to look at the Func delegate but not sure if ive done something wrong?
    Friday, June 8, 2018 9:45 AM
  • Hi Pure Deal,

    Could you please show the complete code with related model class.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 11, 2018 12:59 AM
    Moderator