C# LINQ INNER JOIN is returning wrong count

user_10354765 0
Viewed 66

I have the following sql with JOIN but which returns a record count for 50 but when I convert it to LINQ, I am not getting the matching count. I noticed that when I add the ON clause, the visual studio intellisense dropdown does not show the ID property for the 2nd table. I am wondering if that is an issue.

Here is the simple SQL

SELECT * FROM Table1 T1 JOIN Table2 T2 ON T1.MyId = T2.MyId WHERE T1.IsCompleted

Here is my lamba LINQ with the comment where the VC Intellisense is not working right. For table2, the intellisense dropdown, it is only showing Equals, GetHashCode, GetType, and ToString. Just manually type the MyId and everything successfully builds but the count is too high. Thanks

var test = this.myDbContent.Table1
           .Join (this.myDbContent.Table2
             table1 => table1.MyId,
             table2 => table2.MyId, 
             (table1, table2) => new { table1, table2}
           )
           .Where (joinedTable => joinedTable.Table1.IsCompleted == 1)
sql linq c#
1 Answers

Join is the simple LINQ operation, but better understandable via LINQ Query.

var query = 
    from t1 in myDbContent.Table1
    join t2 in myDbContent.table2 on t1.MyId equals t2.MyId
    where t1.IsCompleted
    select new 
    {
        t1,
        t2
    };

If you still have different results, probably MyId is nullable field and EF also matches nulls.

It can be disabled via options configuration:

builder.UseSqlServer(ceConnection, x => x.UseRelationalNulls(true));
user_106463160
Related