Categories
c# entity-framework join linq sql

Seemingly equivalent queries in LINQ and SQL returns different results

Not sure why it got linked as a dupe. The question is way different. The answer is different. Not sure what to change. If someone sees what I’m missing, please let me know…

I’m getting different number of results using those two queries. After having analyzed it for a few hours I need to throw in the towel, admitting that I can’t spot the difference of significance. Since my arsenal of approaches is emptied, I’m asking for help.

LINQ

List<Uno> result = context.Unos
.Join(context.Duos, uno => uno.CommonId, duo => duo.CommonId,
(uno, duo) => new { Uno = uno, Duo = duo })
.Where(item => item.Uno.Amount > 0
&& item.Duo.Type == 2)
.Select(item => item.Uno)
.ToList();

SQL

select * from Uno as u
join Duo as d on d.CommonId = u.CommonId
where u.Amount > 0
and d.Type = 2

Question number one is if the above two statements are indeed equivalent or if I’m missing something. Question number two is what I’m missing (if anything) or how to trouble-shoot it (if I’m not).

  1. Calls are made against the same database.
  2. The numbers of results are vastly apart (142 and 1437).
  3. The same result sets are fetched for inner join.
  4. Both Uno and Duo are views, not tables.

What more can I verified?

Edit

After some awesomely great support from the community, we established that the LINQ query can be SQL’ized by the following command.

var wtd = context.Unos
.Join(context.Duos, uno => uno.CommonId, duo => duo.CommonId,
(uno, duo) => new { Uno = uno, Duo = duo })
.Where(item => item.Uno.Amount > 0
&& item.Duo.Type == 2)
.Select(item => item.Uno)
.ToString();

The crazy thing is that executing that string in SQL Manager produces 142 results (just as the query in the example above, the SQL version) and it differs only insignificantly from it. However, executing the LINQ query itself produces 1437 results. I’m too confused to even start crying…

“SELECT \r\n [Extent1].[CommonId] AS [CommonId], \r\n [Extent1].[X] AS [X]\r\n FROM (SELECT \n [Uno].[CommonId] AS [CommonId], \n [Uno].[X] AS [X]\n FROM [Finance].[Uno] AS [Uno]) AS [Extent1]\r\n INNER JOIN (SELECT \n [Duo].[CommonId] AS [CommonId], \n [Duo].[Y] AS [Y], \n [Duo].[Z] AS [Z], \n [Duo].[Type] AS [Type], \n [Duo].[U] AS [U], \n [Duo].[V] AS [V]\n FROM [Finance].[Duo] AS [Duo]) AS [Extent2] ON [Extent1].[CommonId] = [Extent2].[CommonId]\r\n WHERE ([Extent1].[X] > cast(0 as decimal(18))) AND ([Extent2].[Type] = @p__linq__0)”