none
Entity Framework not correctly translating C# to SQL RRS feed

  • Question

  • I've been using Linq to SQL and have started using Entity Framework for a new project. I use Linq Pad 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
                                                       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.AdviceLineCallSubjectMatterID,
                                                           AdviceLineCallSubjectMatterDesc = 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
                                                       }; 
    


    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_left in grp.DefaultIfEmpty()

    I created another Visual Studio project, added the ADO.NET Data Provider, Code First from the DB, and copied and pasted the same code, and it works correctly. My C# code is correctly translated into SQL. The last Join is correctly translated into a LEFT OUTER JOIN:

    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]
        LEFT OUTER JOIN [dbo].[AdviceLineCallSubjectMatter] AS [Extent5] ON [Extent1].[AdviceLineCallSubjectMatterID] = [Extent5].[AdviceLineCallSubjectMatterID]
        WHERE 1 = [Extent4].[Employed]

    I am not sure what is causing the differences in translation. Both are .NET 4.6 using EF 6.2. Any advice would be greatly appreciated!



    Thursday, November 30, 2017 3:33 PM

All replies

  • 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 have the Required data annotations. 

    Thursday, November 30, 2017 8:19 PM
  • Hi MikeNaka,

    According to your description and related code, I create a simple console application with entity framework 6.1.3 and .Net framework 4.6, without adding Require attribute, it works as expect, and I could reproduce the issue as you mentioned.

    Could you please provide what steps can I could reproduce the issue. or share a simple demo via OneDrive.

    Note: Please remove related privacy information before you share the sample.

    Related Code:

    using (var db = new EFDemoContext())
                {
                    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.AdviceLineCallSubjectMatterID,
                                                               AdviceLineCallSubjectMatterDesc = 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
                                                           };
    
                    Console.WriteLine(adviceLineCallTotalViewModelList.ToString());
    
                    Console.ReadKey();
                }

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, December 1, 2017 8:21 AM
    Moderator
  • Hey Zhanglong-

    Thanks for taking the time to read and reply to my question.

    To reproduce the error, add the [Required] Data Annotation to the AdviceLineCallSubjectMatterID property in the AdviceLineCall class. This should change the join from a LEFT OUTER JOIN to an INNER JOIN.

    I'm not sure why the [Required] Data Annotation causes this???

    Friday, December 1, 2017 1:48 PM
  • Hi MikeNaka,

    >>I'm not sure why the [Required] Data Annotation causes this???

    Thanks for your explaining, I have reproduced the issue on my side.

    Yes, It does INNER JOIN because you have configured the FK property as [Required]. So from EF point of view the related AdviceLineCallSubjectMatter record should always exist.

    EF relies heavily to the model metadata provided through conventions, data annotations and fluent configuration. All the decisions are made based on that information. For example, when you configure a persistent primitive property as required, all the null checks against that property in the query will be removed and resolved at query translation time to either constant true or false. The same applies to relationships. Inside queries they are used by EF to determine the cardinality of the joins and the type of the joins. When querying the one side of the relationship, the Required/Optional property of the FK controls whether a INNER or LEFT OUTER join is generated.

    Best regards,

    Zhanglong Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 4, 2017 7:33 AM
    Moderator