It seems that equivalent queries in LINQ and SQL return different results

Question

Not sure why this is connected as a hoax. The question is different. The answer is different. Not sure what to change. If someone sees what I am missing, please let me know ...

I get different results using these two queries. After analyzing it for several hours, I need to throw a towel, admitting that I can not see the difference in significance. As my arsenal of approaches is empty, I ask 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, if the above two statements are really equivalent or I am missing something. Question number two is what I am missing (if any) or how it can be removed (if not).

• Calls are made on the same database.
• The numbers of the results are strongly separated from each other (142 and 1437).
• The same result sets are selected for internal join.
• Both Uno and Duo are views, not tables.

What else can I check?

Edit

After some tremendous community support, we found that LINQ can be processed by SQL with 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 a row that in SQL Manager yields 142 results (just like the query in the example above, the SQL version) and differs slightly from it. However, executing the LINQ query itself yields 1,437 results. I'm too embarrassed to even start to cry ...

"SELECT \ r \ n [Extent1]. [CommonId] AS [CommonId], \ r \ n [Extent1]. [X] AS [X] \ r \ n FROM (SELECT \ n [Uno]. [[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 often happens when views are displayed in the Entity Framework. Unlike regular database tables, views often do not have uniquely unique key values. When EF encounters strings with the same primary key value, it simply duplicates the row that belongs to the key that it already knows. In joins, this can cause EF to create more child records because this first known row can have more children than the actual database row.

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

I do not know why EF displays this behavior. I think it should be possible for him to make an exception that materializes repeating entities. This "function" always causes a lot of confusion.

Answer 2

Try looking at the query that you run after executing LINQ in the IntelliTrace window while debugging this place. And then compare with your SQL, I think they are different. If you do not have IntelliTrace, try Express Profiler . Just set the name of SQL Server, click "Trace" and look into it. Hope this helps!

Answer 3

This is a long shot, but will this LINQ expression change anything?

 (from uno in context.Unos join duo in context.Duos on uno.CommonId equals duo.CommonId where uno.Amount > 0 && duo.Type == 2 select new {Uno = uno, Duo = duo} ).ToList() 

I like this form better because it looks like a real SQL query. Anyway, the Join expression in your LINQ bothers me ...