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.

join sm in db.AdviceLineCallSubjectMatters and from sm in grp.DefaultIfEmpty() looks weird. Try to change the second sm in to sm_leftand int the select reference it like AdviceLineCallSubjectMatterID = sm_left == null? 0 : sm_left.AdviceLineCallSubjectMatterID — Cleptus
– Cleptus, Commented Nov 27, 2017 at 16:55
@bradbury9 I made the changes you suggested. I still get the same count. However if I comment out: AdviceLineCallSubjectMatterDesc = sm.AdviceLineCallSubjectMatterDesc, I get the correct count. NOTE: I'm new to Stack Overflow and "think" I'm using backticks correctly before and after my code in this comment. Maybe it will display as a code block when I click "Add Comment?" — MikeNaka
– MikeNaka, Commented Nov 27, 2017 at 20:03
I'm pretty sure @bradbury9 is right. You shouldn't be using "sm" as the tag for both the join and the from. Change one to something different and only reference the tag you use in the from. "sm" and "smgrp", for example. Also, use the null property reference when assigning to your object (i.e. "AdviceLineCallSubjectMatterID = smgrp?.AdviceLineCallSubjectMatterID ?? string.Empty"). — Jacob Proffitt
– Jacob Proffitt, Commented Nov 28, 2017 at 1:58
@JacobProffitt AdviceLineCallSubjectMatter.AdviceLineCallSubjectMatterID is an int. So when I try something like your suggestion: AdviceLineCallSubjectMatterID = sm_left?.AdviceLineCallSubjectMatterID ?? null, , I get an error stating, "An expression tree lambda may not contain a null propagating operator," with red squiggles under: sm_left?.AdviceLineCallSubjectMatterID — MikeNaka
– MikeNaka, Commented Nov 28, 2017 at 14:18
sm_left?.AdviceLineCallSubjectMatterID ?? 0 would probably be what you want, then. The null coalescing operator (??) feeds it the default for when the expression evaluates to null. In this case, you probably want 0. — Jacob Proffitt
– Jacob Proffitt, Commented Nov 28, 2017 at 17:03

Cleptus · Accepted Answer · 2017-11-28 11:29:57Z

1

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)

answered Nov 28, 2017 at 11:29

Cleptus

3,5494 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MikeNaka Over a year ago

This works and doesn't work. When I try your suggestion, my Count = 5, which is the same as my original code. However if I comment out the next line:

AdviceLineCallSubjectMatterDesc = sm_left.AdviceLineCallSubjectMatterDesc == null ? String.Empty : sm_left.AdviceLineCallSubjectMatterDesc,

then my Count = 5,104, which is correct. I also tried:

AdviceLineCallSubjectMatterDesc = sm_left == null ? String.Empty : sm_left.AdviceLineCallSubjectMatterDesc,'. I could leave

AdviceLineCallSubjectMatterDesc` out of the select and then use another block of code, e.g. a foreach, to set it.

Cleptus Over a year ago

if you left any sm in the select you could be implicitly converting the left join into a regular inner join. Something similar to making select * from tableA left join tableB on a.Id = b.Id where b.Id = X and wondering why it behaves as a regular inne join.

Steve Py · Accepted Answer · 2017-11-28 01:23:25Z

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.

MikeNaka · Accepted Answer · 2017-12-01 14:40:46Z

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.

Collectives™ on Stack Overflow

Entity Framework not correctly translating C# to SQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related