none
How to join two SQL Server tables in LINQ

    Question

  • 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

    .Where(condition)

    .GroupBy(

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

    .OrderBy(orderby)

    .Select(

    "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.

    Thanks,

    Martyn

    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

    Thanks
    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 }
                equals
               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