3 table join how to question RRS feed

  • Question

  • I have an sql statement I want to convert to LINQ (VB.Net).  I cannot figure it out and was hoping for some help.

    SELECT   COUNT(*) AS Expr1, SiteInfo.SiteName
    FROM     Advice INNER JOIN
    InformixSource ON Advice.DSSERVER = InformixSource.PODServerName INNER JOIN
        SiteInfo ON InformixSource.SiteNum = SiteInfo.SiteNum
    GROUP BY SiteInfo.SiteName, SiteInfo.SiteNum
    ORDER BY SiteInfo.SiteNum

    *** Please allow me to mark threads as answered and I will, Thank you ***
    Thursday, September 16, 2010 1:43 PM


  • In C# that would be:

    var q = from adv in dc.Advice
      join iso in dc.InformixSource on adv.DSSERVER equals iso.PODServerName
      join sif in dc.SiteInfo on iso.SiteNum equals sif.SiteNum
      group sif by new { sif.SiteName, sif.SiteNum } into sg
      orderby sg.Key.SiteNum
      select new { count = sg.Count(), sg.key.SiteName };

    In VB (sorry if I get something wrong, I'm not very good at VB linq syntax), it would be something like:

    dim q = From adv In dc.Advice _
      Join iso In dc.InformixSource on adv.DSSERVER Equals iso.PODServerName  _
      Join sif In dc.SiteInfo on iso.SiteNum Equals sif.SiteNum _
      Group New With { sif.SiteName, sif.SiteNum } By key = New With { sif.SiteName, sif.SiteNum } Into Group _
      Order By key.SiteNum _
      Select New With { .count = Group.Count(), .SiteName = key.SiteName }

    A great reference to commonly used Linq syntax is Damien Guard's "linq to sql cheat sheet": http://damieng.com/blog/2009/08/12/linq-to-sql-cheat-sheet

       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Edited by KristoferAEditor Friday, September 17, 2010 2:38 AM fixed a couple of typos in the VB query
    • Marked as answer by ski_freak Friday, September 17, 2010 11:44 AM
    Friday, September 17, 2010 2:37 AM