Joining Lists using Linq returns different result than corresponding SQL query?
<p>I have 2 tables</p>

<p>TableA:</p>

<pre class="lang-none prettyprint-override"><code>TableAID int,
Col1 varchar(8)
</code></pre>

<p>TableB:</p>

<pre class="lang-none prettyprint-override"><code>TableBID int
Col1 char(8),
Col2 varchar(40)
</code></pre>

<p>When I run a SQL query on the 2 tables it returns the following number of rows</p>

<pre class="lang-sql prettyprint-override"><code>SELECT * FROM tableA (7200 rows)
select * FROM tableB (28030 rows)
</code></pre>

<p>When joined on col1 and selects the data it returns the following number of rows</p>

<pre><code>select DISTINCT a.Col1,b.Col2 FROM tableA a
join tableB b on a.Col1=b.Col1 (6578 rows)
</code></pre>

<p>The above 2 tables on different databases so I created 2 EF models and retried the data separately and tried to join them in the code using linq with the following funtion. Surprisingly it returns 2886 records instead of 6578 records. Am I doing something wrong?
The individual lists seems to return the correct data but when I join them SQL query and linq query differs in the number of records.</p>

<p>Any help on this greatly appreciated.</p>



<pre><code>// This function is returning 2886 records      
public List&lt;tableC_POCO_Object&gt; Get_TableC() 
{
    IEnumerable&lt;tableC_POCO_Object&gt; result = null;
    List&lt;TableA&gt; tableA_POCO_Object = Get_TableA(); // Returns 7200 records
    List&lt;TableB&gt; tableB_POCO_Object = Get_TableB(); // Returns 28030 records
    result = from tbla in tableA_POCO_Object
         join tblb in tableB_POCO_Object on tbla.Col1 equals tblb.Col1
         select new tableC_POCO_Object 
         {
         Col1 = tblb.Col1,
         Col2 = tbla.Col2
         };
    return result.Distinct().ToList();
}
</code></pre>
