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:
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?
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.