none
How to translate query designer SEQUAL syntax into vb LINQ to SQL RRS feed

  • Question

  • Hello there, I want to convert a piece of SEQUAL code (that was produced from within the query designer built into VB2010 express) into LINQ to SQL syntax so i can use it in a vb code block.

    The db contains columns MemberID, MemberName, Divetime1, DiveTime2, DateStamp. and a few others but they are not wanted in this report. The idea is to group by memberID and then aggregate divetime1 and divetime2 and sum the result to produce the diver with the most minutes. I've been playing around with it myself and can sum divetime1 or divetime2 ok but I need to add the to results together to give a TotalDivetime, oh and the results are to be shown on a datagrid.

    Dim Q = From p In db.Triplogs _
     Group p By p.MemberID Into g = Group _
     Select New With {g, MemberID, .divetime1 = g.Sum(Function(p) p.DiveTime1)}
    
     Me.TripTable2BindingSource.DataSource = q
    The above is addapted from 101 linq to sequal examples.

    SELECT MemberID, Name, SUM(DiveTime1) AS DiveTime1, SUM(DiveTime2) AS DiveTime2, SUM(DiveTime1 + DiveTime2) AS Total
    FROM  Triplog
    GROUP BY MemberID, Name
    ORDER BY Total DESC

    The above is the query builder sequal, which did  produce the correct result.

    I also posted this in VB forum but the moderator suggested I use this forum.

    Also I know that using "name" as a datafield name was a bad idea :) 

    Thanking you in advance for any help / time/ suggestions.

    Kind regards

    Paul


    paul
    Friday, September 24, 2010 8:17 AM

Answers

  • Hi Paul;


    I believe this will give you what you want.

     

    Dim Q = From p In db.Triplogs
         Group p By MemID = p.MemberID Into g = Group
         Select New With {
           .MemberID = MemID, 
           .Name = g.First().Name,
           .divetime1 = g.Sum(Function(p) p.DiveTime1),
           .divetime2 = g.Sum(Function(p) p.DiveTime2),
           .Total = g.Sum(Function(p) p.DiveTime1) + g.Sum(Function(p) p.DiveTime2)
         }
    

    Fernando

    Monday, February 21, 2011 5:58 PM

All replies

  • Hi Paul;


    I believe this will give you what you want.

     

    Dim Q = From p In db.Triplogs
         Group p By MemID = p.MemberID Into g = Group
         Select New With {
           .MemberID = MemID, 
           .Name = g.First().Name,
           .divetime1 = g.Sum(Function(p) p.DiveTime1),
           .divetime2 = g.Sum(Function(p) p.DiveTime2),
           .Total = g.Sum(Function(p) p.DiveTime1) + g.Sum(Function(p) p.DiveTime2)
         }
    

    Fernando

    Monday, February 21, 2011 5:58 PM
  • Thank you Fernando, it looks excellent, just a question for you:

     are those little curley braces there because you are using C#

    or because of linq To sequal syntax?

    regards

     

     


    paul
    Saturday, March 5, 2011 6:45 PM
  • Hi Paul;

    They are there because of Linq to SQL syntax, it delimits an object, in this case a new anonymous type.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, March 5, 2011 9:32 PM