Seemingly equivalent queries in LINQ and SQL returns different results [duplicate]

Question

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)"

Answer 1

This is something that often happens when views are mapped in Entity Framework. Unlike regular database tables, views often don't have clearly unique key values. When EF encounters rows with identical primary key values, it just duplicates the row that belongs to the key it already knows. In joins, this may cause EF to produce more child records because this first-known row may have more children than the real database row.

The fix is to make sure that the view has a field (or combination of fields) that uniquely identifies a row. And, of course, in EF this should be mapped as a (possibly composite) primary key.

I don't know why EF displays this behavior. I think it should be possible for it to throw an exception that it materializes duplicate entities. This "feature" always causes a lot of confusion.