LINQ to SQL Joining Query returning 1 instead of 0

Question

Whats the difference between these two query?

I am getting 0 from codes1.Count() but 1 from codes2.Count() but it should be 0 for both.

 IQueryable<SecurityCode> codes1 = (from user in dataBase.SecurityUsers
                         from code in user.SecurityCodes
                         where user.UsrUserName.Equals(code.UsrUserName)
                               && user.UsrPhone.Equals(phone)
                         select code);


 IQueryable<SecurityCode> codes2 = (from user in dataBase.SecurityUsers
                               where user.UsrPhone.Equals(phone)
                         select user.SecurityCodes.FirstOrDefault());

The relationship is displayed below

Any explanation will be highly appreciated.

FirstOrDefault returns default(TSource) if source is empty; otherwise, the first element in source (from MSDN - http://msdn.microsoft.com/en-us/library/bb340482.aspx). — KV Prajapati, Dec 28 '11 at 07:11
also your first query is over complicated, there is no need for the where clause 'user.UsrUserName.Equals(code.UsrUserName)' as it is implied by 'from code in user.SecurityCodes' also == should be used instead of .Equals — undefined, Dec 28 '11 at 07:29
The second query has a less strict `where` clause, yet you're surprised it returns more results. Apparently there is a record that matches the second query, but not the first. — Prutswonder, Dec 28 '11 at 07:36
@Prutswonder my whole table is empty..where should it come from? — Foyzul Karim, Dec 28 '11 at 17:26
See your accepted answer. If you add `&& user.SecurityCodes.Any()` to your where statement, you will get correct results. — Prutswonder, Dec 29 '11 at 07:27

score 2 · Accepted Answer · answered Dec 28 '11 at 07:26

2

FirstOrDefault will always return exactly 1 element, regardless of if it exists. so your second query will always return elements. (one for each user)

answered Dec 28 '11 at 07:26

undefined

33,537
22
129
198

i changed the second query to only First() instead of FirstorDefault() but still i am getting different result – Foyzul Karim Dec 29 '11 at 04:43
1

First is no different only it will exception if there are no results. Like i said before you cant achieve what you want with the second query, you should simplify/modify the first to do what you want as its the correct one – undefined Dec 29 '11 at 04:53

score 0 · Answer 2 · answered Dec 28 '11 at 07:25

0

FirstOrDefault() on any collection means you will always get one result. That result will either be the first result encountered or a default value if not result was found.

If you change the second query to eliminate the FirstOrDefault(), the counts from both queries should produce the same result.

answered Dec 28 '11 at 07:25

Jeff Siver

7,434
30
32

The second query is actually just straight up wrong, removing the FirstOrDefault will just cause it to not match the return type. – undefined Dec 28 '11 at 07:28
i changed the second query to only First() instead of FirstorDefault() but still i am getting different result – Foyzul Karim Dec 29 '11 at 04:43

LINQ to SQL Joining Query returning 1 instead of 0

2 Answers2

Linked