none
LINQ joins RRS feed

  • Question


  • I am contemplating moving from ADO.NET to LINQ. Mainly to reduce the maintenance of sprocs and be able to unit test more of the code (we do not have the Database version of Team System here). Of course, if this does not work, we will have to look into purchasing the Team Suite.


    What would be the best way to perform a LINQ query that is similar to what I have in my sproc (table and field names have been changed)?

        SELECT DISTINCT SP.Type
        FROM SpTable SP
        INNER JOIN SuTable SU ON
            SU.SON = @user
        LEFT JOIN SupTable SUP ON
            SP.SpID = SUP.SpID
            AND SUP.SuID = SU.SuID
        LEFT JOIN SurTable SUR ON
            SUR.SuID = SU.SuID
        LEFT JOIN SrpTable SRP ON
            SP.SpID = SRP.SpID
            AND SUR.SrID = SRP.SrID   
            AND SUP.SuID IS NULL
        WHERE
            NOT (SRP.SpID IS NULL AND SUP.SpID IS NULL)
            AND (SUP.X = 1 OR SUP.X IS NULL)

    I have tried the following, but I do not see a way to have more than one condition present on a join in LINQ

                var plugins = from sp in this.SpTable
                              join su in this.SuTable on user equals su.SON
                              join sup in this.SupTable on sp.SpID equals sup.SpID && sup.SuID equals su.SuID
                              select new { sp.Type };

    I receive the following errors due to the highlighted portion of code.



    Is there an easy way to accomplish this in LINQ? I know I could call .ExecuteQuery() and put my SQL in there, but if I am going to have that, then I might as well keep the sprocs in my opinion.

    Help and opinions are appreciated.

    TIA,

    Scribs

    Friday, August 22, 2008 12:17 PM

Answers

  • The LINQ join syntax in C# only allows you to specify 'equals' once.  You cannot include multiple conditions with '&&' operators.

     

    What you can do is form anonymous types out of mulitple values on either side of the 'equals' like this.

     

    var plugins = from sp in this.SpTable
                              join su in this.SuTable on user equals su.SON
                              join sup in this.SupTable on new { sp.SpID, su.SuID } equals new { sup.SpID, sup.SuID }

                              select new { sp.Type };

     

    Alternatively, you can use another 'from' clause followed by a 'where' clause and have the join condition as you originally wrote it.

     

                var plugins = from sp in this.SpTable
                              join su in this.SuTable on user equals su.SON
                              from sup in this.SupTable

                              where sp.SpID == sup.SpID && sup.SuID == su.SuID

                              select new { sp.Type };

     

    The reason for this difference is that the 'join' syntax maps to the use of the LINQ Join method pattern, which only enables equi-joins.  The join operator is optimal for use with LINQ to Objects where there is no query processor.

     

    Saturday, August 23, 2008 9:04 PM
    Moderator

All replies

  • Can you try putting "==" instead of "equals" in your LINQ query?

     

    [)amien

    Friday, August 22, 2008 10:31 PM
    Moderator
  • The LINQ join syntax in C# only allows you to specify 'equals' once.  You cannot include multiple conditions with '&&' operators.

     

    What you can do is form anonymous types out of mulitple values on either side of the 'equals' like this.

     

    var plugins = from sp in this.SpTable
                              join su in this.SuTable on user equals su.SON
                              join sup in this.SupTable on new { sp.SpID, su.SuID } equals new { sup.SpID, sup.SuID }

                              select new { sp.Type };

     

    Alternatively, you can use another 'from' clause followed by a 'where' clause and have the join condition as you originally wrote it.

     

                var plugins = from sp in this.SpTable
                              join su in this.SuTable on user equals su.SON
                              from sup in this.SupTable

                              where sp.SpID == sup.SpID && sup.SuID == su.SuID

                              select new { sp.Type };

     

    The reason for this difference is that the 'join' syntax maps to the use of the LINQ Join method pattern, which only enables equi-joins.  The join operator is optimal for use with LINQ to Objects where there is no query processor.

     

    Saturday, August 23, 2008 9:04 PM
    Moderator
  • Hi,

    How can I write the "AND" condition in Join like the one below:

    JOIN [dbo].[Table1] AS [t1] ON [t0].[Id] = [t1].[Id] AND T1.Key=variable

    "variable" here is the string type variable.
    Friday, September 12, 2008 9:02 AM