Entity Framework not correctly translating C# to SQL

Question

I've been using Linq to SQL and have started using Entity Framework for a new project. I use LinqPad to test my code before incorporating it into Visual Studio. While debugging in VS, I noticed my counts differed. When I inspected the SQL created by my code in VS, I noticed that it didn't translate correctly.

My code in VS:

var adviceLineCallTotalViewModelList = 
    from a in db.AdviceLineCalls
    .Include(a => a.Agency)     
    .Include(a => a.Staff)
    .Include(a => a.StatusOfAdviceLineCaller)
    .Include(a => a.AdviceLineCallSubjectMatter)

   join ag in db.Agencies on a.AgencyNumber equals ag.AgencyNumber
   join st in db.StatusOfAdviceLineCallers on a.StatusOfAdviceLineCallerID 
                                           equals st.StatusOfAdviceLineCallerID
   join s in db.Staffs on a.StaffID equals s.StaffID
   join sm in db.AdviceLineCallSubjectMatters on a.AdviceLineCallSubjectMatterID 
                                              equals sm.AdviceLineCallSubjectMatterID into grp
   from sm in grp.DefaultIfEmpty()
   where s.Employed == true
   select new AdviceLineCallTotalViewModel()
   {
       AdviceLineCallID = a.AdviceLineCallID,
       AdviceLineCallSubjectMatterID = sm.AdviceLineCallSubjectMatterID,
       AdviceLineCallSubjectMatterDesc = sm.AdviceLineCallSubjectMatterDesc,
       StatusOfAdviceLineCallerID = st.StatusOfAdviceLineCallerID,
       StatusOfAdviceLineCallerDesc = st.StatusOfAdviceLineCallerDesc,
       AgencyNumber = a.AgencyNumber,
       AgencyNumberNameFacility = ag.AgencyNumberNameFacility,
       CallDate = a.CallDate,
       CallLength = a.CallLength,
       Comments = a.Comments,
       StaffID = a.StaffID,
       LastName = s.LastName
   };

When I debug and look at the SQL generated, I see:

SELECT 
[Extent1].[AdviceLineCallID] AS [AdviceLineCallID], 
[Extent5].[AdviceLineCallSubjectMatterID] AS [AdviceLineCallSubjectMatterID], 
[Extent5].[AdviceLineCallSubjectMatterDesc] AS [AdviceLineCallSubjectMatterDesc], 
[Extent3].[StatusOfAdviceLineCallerID] AS [StatusOfAdviceLineCallerID], 
[Extent3].[StatusOfAdviceLineCallerDesc] AS [StatusOfAdviceLineCallerDesc], 
[Extent1].[AgencyNumber] AS [AgencyNumber], 
[Extent2].[AgencyNumberNameFacility] AS [AgencyNumberNameFacility], 
[Extent1].[CallDate] AS [CallDate], 
[Extent1].[CallLength] AS [CallLength], 
[Extent1].[Comments] AS [Comments], 
[Extent1].[StaffID] AS [StaffID], 
[Extent4].[LastName] AS [LastName]
FROM     [dbo].[AdviceLineCall] AS [Extent1]
INNER JOIN [dbo].[Agency] AS [Extent2] ON [Extent1].[AgencyNumber] = [Extent2].[AgencyNumber]
INNER JOIN [dbo].[StatusOfAdviceLineCaller] AS [Extent3] ON [Extent1].[StatusOfAdviceLineCallerID] = [Extent3].[StatusOfAdviceLineCallerID]
INNER JOIN [dbo].[Staff] AS [Extent4] ON [Extent1].[StaffID] = [Extent4].[StaffID]
INNER JOIN [dbo].[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1].[AdviceLineCallSubjectMatterID] = [Extent5].[AdviceLineCallSubjectMatterID]
WHERE 1 = [Extent4].[Employed]

The last "INNER JOIN" should be a "LEFT OUTER JOIN" because of the lines:

join sm in db.AdviceLineCallSubjectMatters on a.AdviceLineCallSubjectMatterID equals sm.AdviceLineCallSubjectMatterID into grp
from sm in grp.DefaultIfEmpty()

Right???

NOTE: I included the "Include" statements after reading another post about why the "LEFT OUTER JOIN" is not being included. I get the same results with or without the "Includes."

I've used DefaultIfEmpty() in other, simpler queries before and have not run into this problem.

As an EF newbie, I'm not sure if I'm doing something wrong or if EF in my project got corrupted some how. I'm using EF 6.2.

EDIT:

I created a new Visual Studio Project and used the following code:

        var adviceLineCallTotalViewModelList = from a in db.AdviceLineCalls
                                               join ag in db.Agencies on a.AgencyNumber equals ag.AgencyNumber
                                               join st in db.StatusOfAdviceLineCallers on a.StatusOfAdviceLineCallerID equals st.StatusOfAdviceLineCallerID
                                               join s in db.Staffs on a.StaffID equals s.StaffID
                                               join sm in db.AdviceLineCallSubjectMatters on a.AdviceLineCallSubjectMatterID equals sm.AdviceLineCallSubjectMatterID into grp
                                               from sm_left in grp.DefaultIfEmpty()
                                               where s.Employed == true
                                               select new AdviceLineCallTotalViewModel()
                                               {
                                                   AdviceLineCallID = a.AdviceLineCallID,
                                                   AdviceLineCallSubjectMatterID = sm_left == null ? 0 : sm_left.AdviceLineCallSubjectMatterID,
                                                   AdviceLineCallSubjectMatterDesc = sm_left == null ? String.Empty : sm_left.AdviceLineCallSubjectMatterDesc,
                                                   StatusOfAdviceLineCallerID = st.StatusOfAdviceLineCallerID,
                                                   StatusOfAdviceLineCallerDesc = st.StatusOfAdviceLineCallerDesc,
                                                   AgencyNumber = a.AgencyNumber,
                                                   AgencyNumberNameFacility = ag.AgencyNumberNameFacility,
                                                   CallDate = a.CallDate,
                                                   CallLength = a.CallLength,
                                                   Comments = a.Comments,
                                                   StaffID = a.StaffID,
                                                   LastName = s.LastName
                                               };

This retrieves the correct number of rows (5104). It also correctly creates the last join as a LEFT OUTER JOIN:

LEFT OUTER JOIN [dbo].[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1].[AdviceLineCallSubjectMatterID] = [Extent5].[AdviceLineCallSubjectMatterID]

However, this same line of code in my current project only returns 5 records, and the last join is incorrectly translated into an INNER JOIN.

Does this mean EF or something got corrupted in my current project? As a newbie to MVC and EF, I'm not sure what to do.

Answer 1

I found the cause of my problem and why the same C# code was being translated into SQL differently, and it all had to do with the Required Data Annotation.

The table, AdviceLineCallSubjectMatter is a new addition to the DB. So only new AdviceLineCall records will have a AdviceLineCallSubjectMatterID so I made it a nullable int.

There are certain AdviceLineCall fields that are "Required," and the new int? AdviceLineCallSubjectMatterID was added to my AdviceLineCall Model class with the Required data annotation.

    public partial class AdviceLineCall
{
  .
  .
  .
    [Required(ErrorMessage = "Subject Matter is required")]
    [DisplayName("Subject Matter")]
    public int? AdviceLineCallSubjectMatterID { get; set; }
  .
  .
  .
}


public partial class AdviceLineCallSubjectMatter
{
    public AdviceLineCallSubjectMatter()
    {
        AdviceLineCalls = new HashSet<AdviceLineCall>();
    }

    [DisplayName("Subject Matter")]
    public int AdviceLineCallSubjectMatterID { get; set; }

    [StringLength(3)]
    [DisplayName("Subject Matter")]
    public string AdviceLineCallSubjectMatterDesc { get; set; }

    public virtual ICollection<AdviceLineCall> AdviceLineCalls { get; set; }
}

}

When I comment out the Required data annotation in the AdviceLineCall Model class, my C# is translated to the expected SQL with a LEFT OUTER JOIN on AdviceLineCallSubjectMatter.

I am not sure why the Required data annotation has this effect???

NOTE: In the temporary project I created to test the same query, I created the DB Context and Model classes via Code First From DB and didn't add the Required data annotations.

Answer 2

Change

join sm in db.AdviceLineCallSubjectMatters ...
from sm in grp.DefaultIfEmpty() ...
.... 
select 
    AdviceLineCallSubjectMatterID = sm.AdviceLineCallSubjectMatterID

into

join sm in db.AdviceLineCallSubjectMatters ...
from sm_left in grp.DefaultIfEmpty() ...
....
select
    AdviceLineCallSubjectMatterID = sm_left == null? 0 : sm_left.AdviceLineCallSubjectMatterID

Depending on the .net framework version you could alter the select null checking to a more clean way (check @Jacob Proffitt comment)

Answer 3

If your AdviceLineCall entity already has references declared for the related entities (I.e. a.AdviceLineCallSubjectMatter) then you do not need to declare DbSets in your context and manually join these tables in a query. EF handles all of this for you via the mapped references.

var adviceLineCallTotalViewModelList = db.AdviceLineCalls
  .Where( a=> a.Staff.Employed)
  .Select(a => new {
    a.AdviceLineCallId,
    a.SubjectMatter.AdviceLineCallSubjectMatterID,
    a.SubjectMatter.AdviceLineCallSubjectMatterDesc,
    a.StatusOfAdviceLineCaller.StatusOfAdviceLineCallerID,
    a.StatusOfAdviceLineCaller.StatusOfAdviceLineCallerDesc,
    a.Agency.AgencyNumber,
    a.Agency.AgencyNumberNameFacility,
    a.CallDate,
    a.CallLength,
    a.Comments,
    a.Staff.StaffID,
    a.Staff.LastName})
  .ToList() // I'd recommend using .Take() to limit the # of rows returned.
  .Select(x => new AdviceLineCallTotalViewModel()
   {
       AdviceLineCallID = x.AdviceLineCallID,
       AdviceLineCallSubjectMatterID = x.AdviceLineCallSubjectMatterID,
       AdviceLineCallSubjectMatterDesc = x.AdviceLineCallSubjectMatterDesc,
       StatusOfAdviceLineCallerID = x.StatusOfAdviceLineCallerID,
       StatusOfAdviceLineCallerDesc = x.StatusOfAdviceLineCallerDesc,
       AgencyNumber = x.AgencyNumber,
       AgencyNumberNameFacility = x.AgencyNumberNameFacility,
       CallDate = x.CallDate,
       CallLength = x.CallLength,
       Comments = x.Comments,
       StaffID = x.StaffID,
       LastName = x.LastName
   });

The first .Select() extracts just the fields you want from the object model. Provided your entities are mapped as Optional (null-able FK) then it will compose the suitable joins automatically. In cases where something like the SubjectMatter is #null, those two fields requested from that referenced entity will be null/default. When you .Select() that data into your ViewModel you are dealing with POCO objects post the .ToList() or .Take() so you should handle any logic around what to do with missing optional dependencies here. If you want to filter data out, do it in the Where() clause to ensure only relevant data is returned from SQL.