How to join two SQL Server tables in LINQ


  • I'm trying to join two tables together in Linq, but I'm struggling with the syntax in intellisense.

    Here is my query:


    var query = (IQueryable)db.FACT_SESSIONs.DefaultIfEmpty();

    query = db.FACT_SESSIONs



    "new(" + groupby + ")", "it")



    "new(" + select + ", Count() as Total)");

    I need join this table "query = db.FACT_SESSIONs" to another table: "DIM_VULNERABILITY_HIGH_LEVEL_MAP" and the joining is done on the following fields: "vuln_group" and "highLevelVulnerabilityId" respectively.  Can anybody help me with the syntax on this? I'm struggling. I need to then join this to another table (this is the intermediate table for a many-to-many mapping) but I assume the syntax will be the same.



    Wednesday, June 10, 2009 11:08 AM

All replies

  • I forgot to mention, these tables already exist in my DBML file and are relational and I need an outer join for this table. Outer in favour of the FACT_SESSION table

    Wednesday, June 10, 2009 11:57 AM
  • Hi,

    Not able to get you completely, but here's how we use Joins in LINQ.

    var result =
        from v in db.Dim_Vulnerablity_High_Level_Map 
        join f in db.Fact_Sessions 
            on new {VulnGroup=v.vuln_group, VulnID=v.highLevelVulnerabilityID }
               new {VulnGroup=f.vuln_group, VulnID=f.highLevelVulnerabilityID }
        select ....

    And outer joins are implemented using "join into" and "DefaultIfEmtpy()" clauses. See this post for some examples.

    Hope that helps.
    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, June 11, 2009 5:44 AM