Remove From My Forums

3 table join how to question

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

Answers

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

Answerer

Answered by:

3 table join how to question

Question

Answers