Entity Framework 6 and navigational property issue

Question

Im hoping to get your help here. Im very new to EF and am having some troubles. I am using the Database First approach and have a database in Azure that I have to retreive data from.

[DataContract]
[Table("A")]
public class AgencyDC
{

    [DataMember]
    [Key]
    public string AID { get; set; }
    public string AName { get; set; }
    public string GeneralEmailAddress { get; set; }
    public string WebsiteURL { get; set; }
    [DataMember]
    [ForeignKey("AID")]
    [IgnoreDataMember]
    public virtual AExtensionDC AExtension { get; set; }
}

[DataContract]
[Table("AExtension")]
public class AExtensionDC
{
    [DataMember]
    [Key]
    public string AID { get; set; }
    [DataMember]
    public bool? IsActive { get; set; }
    public bool? IsOptedOut { get; set; }
    public DateTime? LastUpdated { get; set; }
}

I am trying to use EF6 to retreive my records using DBSets in my context..

public List<ADataCcontract> GetAllAs()
    {
        using (AContext _aCtx = new AContext())
        {                
            var mylist = _aCtx.A.Include("AExtension").ToList();
            return mylist;
        }            
    }

Now, I should be getting back 547 records back with only 1 of them having the AExtension navigational property having content within. The other 546 records should contain NULL. However, for some reason, I am only getting what appears to be a record that has a match in both tables. In SQL speak, I kind of just want a left join so that I return ALL rows from AE entity and OPTIONALLY matches in AE.

I hope this makes sense.

If possible, if you have a fix, could you please post an example I could referent? I am really stuck.

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

I think this SO Answer might get you most of the way? https://stackoverflow.com/a/4299667/78551

Basically Include does a left outer join or left join as 'outer' is actually optional in SQL.

edited May 23 '17 at 12:21

Community

1
1

answered Jun 19 '14 at 20:37

Tacoman667

1,391
9
16

Thank you. I read this article and it suggested that the .Include() should already be doing a left join. But the result set I'm getting back suggests otherwise. Im sure its my implementation but I was unable to use anything from the article and progress. Is there something wrong with the properties in one of my entity classes? – Solo812 Jun 19 '14 at 20:48
Have you removed the Include() to see if you get back the entire list you are looking for? – Tacoman667 Jun 19 '14 at 22:54
Ues I have. When I didn't include the Include() I didn't get the navigational property. I have just decided to go old school and use datasets :) I didn't really want to go this route but I just couldn't figure out how to make this work like I thought it should. Maybe next project. – Solo812 Jun 20 '14 at 22:33
If you use the Include(), only the objects with the related object will populate the navigation property. Otherwise they are left null. You will still get back all the parent objects regardless unless you add other constraints like Where(). – Tacoman667 Jun 20 '14 at 22:44

celerno · Answer 2 · 2014-06-19T21:12:50.150

0

A left join / inner join will be performed by ´.Include´ if your fields have/lack of nullability.

To review your query put a breakpoint and check this value:

  var myQuery = _aCtx.A.Include("AExtension").ToTraceString();

edited Jun 19 '14 at 21:12

answered Jun 19 '14 at 20:54

celerno

1,367
11
30

Im getting the following compilation error... The type 'AExtensionDS' must be a non-nullable value type in order to use it as a parameter 'T' in the generic type or mentod 'System.Nullable' – Solo812 Jun 19 '14 at 21:04
Sorry, didn't rememebered that. Just check the query for now. I'll update an alternative soon. I'm trying to remember. – celerno Jun 19 '14 at 21:12
Yes, it is doing an inner join.{SELECT 1 AS [C1], [Extent1].[AID] AS [AID], [Extent1].[AName] AS [AName], [Extent1].[GeneralEmailAddress] AS [GeneralEmailAddress], [Extent1].[WebsiteURL] AS [WebsiteURL], [Extent2].[AID] AS [AID1], [Extent2].[IsActive] AS [IsActive], [Extent2].[IsOptedOut] AS [IsOptedOut], [Extent2].[LastUpdated] AS [LastUpdated] FROM [dbo].[A] AS [Extent1] INNER JOIN [dbo].[AExtension] AS [Extent2] ON [Extent1].[AID] = [Extent2].[AID]} – Solo812 Jun 19 '14 at 21:19
If you want your C# query short, try not to add the ForeingKey relation. And add a lazyloading property. Otherwise, check this: http://www.progware.org/Blog/post/Left-Outer-Join-in-LINQ-to-Entities-(for-Entity-Framework-4).aspx – celerno Jun 19 '14 at 21:31
Thank you for the post but this link also suggests that the Include() method should result in a left outer join. And for some reason, my include() isn't giving me that. I have been all over the place trying to locate the cause and reading up on how it is suppossed to work but I keep coming up short. – Solo812 Jun 19 '14 at 21:55

Entity Framework 6 and navigational property issue

2 Answers2