Entity Framework performing different on multiple computers

Question

I am at a loss, So I came here to see what everyone thinks about this.

Background: Textbox using jquery autocomplete w/ EF 5

Problem: Entity is performing a inner join, but the syntax shows a left join is being performing. (Both PC's are running Win 7 and latest framework)

Anomaly: It works the right way (left outer join) on my PC. But on my buddys PC, it shows an inner join. Both files are binary equals. (Actually being pulled from git)

Here is the code:

public JsonResult AutoCompleteName(string term)
        {
            using (var db = new PersonnelContext())
            {
                return this.Json((from r in db.Personnel
                                  join per in db.PersonnelEmployee on r.Id equals per.Personnel_Id
                                  join dep in db.RefDepartment on per.Department equals dep.Department into rfdp
                                  from g in rfdp.DefaultIfEmpty()
                                  where r.First_Name.ToLower().Contains(term.ToLower()) | r.Last_Name.ToLower().Contains(term.ToLower()) | (r.First_Name.ToLower() + " " + r.Last_Name.ToLower()).Contains(term.ToLower())
                                  select new { firstname = r.First_Name, lastname = r.Last_Name, department = g.DeptDesc ?? "None", per.Personnel_Id, per.Pernr }).OrderBy(a => a.firstname).ToArray(), JsonRequestBehavior.AllowGet);
        }
    }

Here is the intellitrace from the PC where its working fine: (Note I took out some due to confidentiality)

FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[First_Name] AS [First_Name], 
    [Extent1].[Last_Name] AS [Last_Name], 
    [Extent2].[Personnel_Id] AS [Personnel_Id], 
    [Extent2].[Pernr] AS [Pernr], 
    CASE WHEN ([Extent3].[Dept_Desc] IS NULL) THEN N'None' ELSE [Extent3].[Dept_Desc] END AS [C1]
    FROM   [Core].[Personnel] AS [Extent1]
    INNER JOIN [Core].[Personnel_Employee] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Personnel_Id]
    LEFT OUTER JOIN [Core].[Ref_Department] AS [Extent3] ON [Extent2].[Department] = [Extent3].[Department]
    WHERE (( CAST(CHARINDEX(LOWER(@p__linq__0), LOWER([Extent1].[First_Name])) AS int)) > 0) OR (( CAST(CHARINDEX(LOWER(@p__linq__1), LOWER([Extent1].[Last_Name])) AS int)) > 0) OR (( CAST(CHARINDEX(LOWER(@p__linq__2), LOWER([Extent1].[First_Name]) + N' ' + LOWER([Extent1].[Last_Name])) AS int)) > 0)
)  AS [Project1]
ORDER BY [Project1].[First_Name] ASC"

As you can see, its performing a Left Outer Join, just as the syntax suggests.

Here is the Intellitrace from PC 2: (Exact same code!)

FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[First_Name] AS [First_Name], 
    [Extent1].[Last_Name] AS [Last_Name], 
    [Extent2].[Personnel_Id] AS [Personnel_Id], 
    [Extent2].[Pernr] AS [Pernr], 
    CASE WHEN ([Extent3].[Dept_Desc] IS NULL) THEN N'None' ELSE [Extent3].[Dept_Desc] END AS [C1]
    FROM   [Core].[Personnel] AS [Extent1]
    INNER JOIN [Core].[Personnel_Employee] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Personnel_Id]
    INNER JOIN [Core].[Ref_Department] AS [Extent3] ON [Extent2].[Department] = [Extent3].[Department]
    WHERE (( CAST(CHARINDEX(LOWER(@p__linq__0), LOWER([Extent1].[First_Name])) AS int)) > 0) OR (( CAST(CHARINDEX(LOWER(@p__linq__1), LOWER([Extent1].[Last_Name])) AS int)) > 0) OR (( CAST(CHARINDEX(LOWER(@p__linq__2), LOWER([Extent1].[First_Name]) + N' ' + LOWER([Extent1].[Last_Name])) AS int)) > 0)
)  AS [Project1]
ORDER BY [Project1].[First_Name] ASC"

Here is another strange anomaly: When deployed to QA, it works fine on both PC's!! There is no consistency here for us to know whats going on.

Any ideas here? I have no idea what going on. We have completely wiped away our working versions, and pulled down again from git. Same thing happens. If no one has any ideas, we may need to use a proc. But I would at least like to know whats going on.

** Edit ** Answer: I really have no idea why this works, but i got it working. Here is the query now:

return this.Json((from r in db.Personnel
                                  join per in db.PersonnelEmployee on r.Id equals per.Personnel_Id
                                  where r.First_Name.ToLower().Contains(term.ToLower()) | r.Last_Name.ToLower().Contains(term.ToLower()) | (r.First_Name.ToLower() + " " + r.Last_Name.ToLower()).Contains(term.ToLower())
                                  join dep in db.RefDepartment
                                  .Where(x => x.DeptDesc != null || x.DeptDesc == null) on per.Department equals dep.Department into rfdp
                                  from g in rfdp.DefaultIfEmpty()
                                  select new { 
                                      firstname = r.First_Name,
                                      lastname = r.Last_Name, 
                                      department = (g == null) ? "None" : g.DeptDesc,
                                      per.Personnel_Id, 
                                      per.Pernr }).ToArray(), JsonRequestBehavior.AllowGet);

I think this one works because it explicitly looks for DeptDesc being null and not null.... I am just glad its working. Thanks for everyone who looked at this.

Does the program target the same database engine on both machines? And if so, is each the same version of the database? — Chris Dunaway, Jun 27 '13 at 14:58
They both target the same remote database with the exact same connection string. — Botonomous, Jun 27 '13 at 15:10

score 1 · Accepted Answer · answered Jun 27 '13 at 15:17

I was able to get this fixed with the following modified query:

return this.Json((from r in db.Personnel
                                  join per in db.PersonnelEmployee on r.Id equals per.Personnel_Id
                                  where r.First_Name.ToLower().Contains(term.ToLower()) | r.Last_Name.ToLower().Contains(term.ToLower()) | (r.First_Name.ToLower() + " " + r.Last_Name.ToLower()).Contains(term.ToLower())
                                  join dep in db.RefDepartment
                                  .Where(x => x.DeptDesc != null || x.DeptDesc == null) on per.Department equals dep.Department into rfdp
                                  from g in rfdp.DefaultIfEmpty()
                                  select new { 
                                      firstname = r.First_Name,
                                      lastname = r.Last_Name, 
                                      department = (g == null) ? "None" : g.DeptDesc,
                                      per.Personnel_Id, 
                                      per.Pernr }).ToArray(), JsonRequestBehavior.AllowGet);

As I said in the edits: I'm not 100% sure why this works instead of the first, but im glad it does!

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

This sounds suspiciously like an already 'fixed' issue with EF... have a look at this duplicated join issue

They note that removing the orderby removes the duplicate... worth a test eh

edited May 23 '17 at 11:50

Community

1
1

answered Jun 27 '13 at 14:32

Paul Zahra

9,522
8
54
76

Thanks for the reply. I removed the orderby and its still doing the same thing :( – Botonomous Jun 27 '13 at 14:37

Entity Framework performing different on multiple computers

2 Answers2