Control the Sql that Linq sends to db explanation

Hello I have the a linq query that I have created for a left outer join. I am wondering why linq creates the Sql it does, and how to make it a better query.

here’s the c# query:

  • Dynamically changing parameter based on another in an SSRS report
  • Create mssql trigger from java program
  • How to set the default schema of a database in SQL Server 2005?
  • Invisible Foreign Key in SQL Server 2005
  • Force the TCP/IP protocol in connection string
  • SQL Server trigger doesn't fire when a record is inserted from ASP.NET web page
  • var query=
    (
        from subject in subjects
        join statement in statements.DefaultIfEmpty() on subject.Id equals statement.SubjectId
        select subject
    );
    query.Take(100).Dump();
    

    and the Sql that it sends:

    SELECT TOP (100) 
    --some fields here
    FROM [Subject] AS [t0]
    INNER JOIN ((
            SELECT NULL AS [EMPTY]
            ) AS [t1]
        LEFT OUTER JOIN [SubjectStatement] AS [t2] ON 1=1 ) ON [t0].[id] = [t2].[SubjectId]
    

    What I would like to see sent is

    SELECT TOP(100)
       --some fields here
    FROM Subject
    LEFT OUTER JOIN SubjectStatemnt ON Subject.Id = SubjectStatement.Id
    

    Is there a way to control the Sql that is being passed to Sql Server?

  • MVC 4 SimpleMembership Model first
  • Trigger Error: The current transaction cannot be committed and cannot support operations that write to the log file
  • Parse sql parameters from commandtext
  • Import Excel spreadsheet columns into SQL Server database
  • Adding a where clause to Sql Query using delimiters
  • How to set SQL Server connection string?
  • One Solution collect form web for “Control the Sql that Linq sends to db explanation”

    You are using the syntax of an inner join and while that might work out some times, you would normally create a left join using the following syntax:

    var query =
    (
        from subject in subjects
        join statement in statements on subject.Id equals statement.SubjectId into ljStatement
        from statement in ljStatement.DefaultIfEmpty()
        select subject
    );
    query.Take(100).Dump();
    

    This would result in:

    SELECT TOP (100) [t0].[Id]
    FROM [Subject] AS [t0]
    LEFT OUTER JOIN [SubjectStatement] AS [t1] ON [t0].[Id] = [t1].[SubjectId]
    

    into (C# Reference)

    The into contextual keyword can be used to create a temporary
    identifier to store the results of a group, join or select clause into
    a new identifier.

    join clause (C# Reference)

    A join clause with an into expression is called a group join.

    A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.
    If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.