When Including child elements LINQ query returns less results

Question

I would like to do a LINQ query that returns all of the records for the Parent table, and includes the Child, if applicable. I know I can do a LINQ join with DefaultIfEmpty(), but then I must output a ViewModel. I'd like to get an IQuerable<Parent> of the actual Parent Class.

So, if I have these classes:

public class Parent
{
    [Key]
    public int ParentId {get; set;}
    public string ParentName {get; set;}

    public int? MyChildId {get; set;}

    [ForeignKey("MyChildId")]
    public virtual Child MyChild {get; set;}

    public bool IsActive {get;set;}
}

public class Child
{
    public int ChildId {get;set;}
    public string ChildName {get;set;}
}

In LINQPad, if I do this:

var results = db.Parent.Where(ra => ra.IsActive);
results.Dump();

I get 111 records.

If I do this:

var results = db.Parent.Where(ra => ra.IsActive);

var results2 = (from r in results
                select new
                {
                    ParentId = r.ParentId,
                    ParentName = r.ParentName,
                    MyChildId = r.MyChildId
                });
results2.Dump();

I also receive 111 records.

But if I do this:

var results = db.Parent.Where(ra => ra.IsActive);

var results2 = (from r in results
                select new
                {
                    ParentId = r.ParentId,
                    ParentName = r.ParentName,
                    MyChildId = r.MyChildId,
                    IsActive = r.IsActive,
                    MyChildName = r.MyChild == null ? null : r.MyChild.ChildName
                });
results2.Dump();

I only get 50 records. These are the 50 Parent records that have a child. If they do not have a child, they don't come back.

The SQL generated looks like this:

SELECT 
    [Extent1].[ParentId] AS [ParentId], 
    [Extent1].[ParentName] AS [ParentName], 
    [Extent1].[IsActive] AS [IsActive],
    [Extent2].[ChildName] AS [ChildName]
    FROM  [dbo].[Parent] AS [Extent1]
    INNER JOIN [dbo].[Child] AS [Extent2] ON [Extent1].[MyChildId] = [Extent2].[ChildId]
    WHERE [Extent1].[IsActive] = 1

How can I get a resultset that includes all 111 Parent records, even if they have no child, but does include the Child elements, if they are there?

UPDATE So, I may have lied a bit. I posted the above for simplicity sake, but just in case it helps, here is a closer sample of what the code does:

public class Parent
{
    [Key]
    public int ParentId {get; set;}
    public string ParentName {get; set;}

    public int? MyChildId {get; set;}

    [ForeignKey("MyChildId")]
    public virtual Child MyChild {get; set;}

    [ForeignKey("MyChildId")]
    public virtual StepChild MyStepChild {get; set;}

    public bool IsActive {get;set;}
}

public class Child
{
    public int ChildId {get;set;}
    public string ChildName {get;set;}
}

public class StepChild
{
    public int StepChildId {get;set;}
    public string StepChildName {get;set;}
}

`var results2 = (from r in results2`, you declare results2 and without assigning anything to it you perform a query on it. How can that even work? — Alexander Derck, Mar 25 '16 at 19:44
Did you try to make your MyChild property nullable: public virtual Child? MyChild {get; set;} or just user .DefaultIfEmpty() in your linq: MyChildName = r.MyChild.DefaultIfEmpty() == null ? null : r.MyChild.ChildName — Ivan Yuriev, Mar 25 '16 at 19:57
@IvanYuriev, I get this: ''MyNamespace.Model.Child' does not contain a definition for 'DefaultIfEmpty' and no extension method 'DefaultIfEmpty' accepting a first argument of type 'MyNamespace.Model.Child' could be found (press F4 to add a using directive or assembly reference)' — M Kenyon II, Mar 25 '16 at 20:02
this post should help you: http://stackoverflow.com/questions/4290802/entity-framework-include-with-left-join-is-this-possible — Ivan Yuriev, Mar 25 '16 at 20:14
For some reason, I can't use `DefaultIfEmpty()` on Child doing it the way recommended in that post. I think it's just late on a Friday, and time to go home. — M Kenyon II, Mar 25 '16 at 20:18
Did you try making the Child property nullable before doing DefaultIfEmpty? Not clear from your response above. — stephen.vakil, Mar 25 '16 at 20:26
How can I make the child property nullable? I can't do this: `public virtual Child? MyChild {get; set;}`, I get a design time error. — M Kenyon II, Mar 25 '16 at 20:28
PS, I also tried `from p in Parent join testChild in db.Child on p.MyChildId equals testChild.ChildId into tc from c in tc.DefaultIfEmpty() where ra.IsActive select new {ParentId = p.ParentId, MyChild = c}` and still get only 50 records. — M Kenyon II, Mar 25 '16 at 20:33
Wait, so child and stepChild both point to the same FK? (MyChildId) Also, Child is already nullable because the exposed FK is nullable (MyChildId). You definitely don't need to use public virtual Child? MyChild My child is a reference type it will be null in the absence of a value. — univ, Mar 25 '16 at 20:38
Well before even going there, may I ask why do you need to do that? :P — univ, Mar 25 '16 at 20:41
I don't know if it solves your problem but you don't need your ternary because in LINQ to Entity r.MyChild.Name will return null if MyChild is null without throwing an exceptoin — Casey, Mar 25 '16 at 20:43
Parent usually has a dependent. Sometimes it's a Child, sometimes it's a StepChild. In our actual code the two dependent types are different enough that we couldn't do inheritance. Sometimes it's an individual, sometimes it's an organization. — M Kenyon II, Mar 25 '16 at 20:43
I would definitely try using separate FK properties for each. Because they are different entities. — univ, Mar 25 '16 at 20:45
But that does not explain why you are trying to use the same Id field to represent the foreign key relationship. Why not `MyChildId` and `MyStepChildId`? I think you are just asking for EF confusion with this data model — stephen.vakil, Mar 25 '16 at 20:45
I think that EF is going haywire because it can't understand why you would reuse the same foreign key property for two completely different entities. — univ, Mar 25 '16 at 20:47
The actual table is a RoleAssignment table. We assign a person or organization a particular Role. So the columns are more like RoleAssignmentId, RoleId, and AssigneeId. The Assignee could be Id of the Org (who has rights to do something), or an Id of a Person. We're using Guids for the Ids, and ensure no duplicates by getting all Ids from a central ResourceId table. — M Kenyon II, Mar 25 '16 at 21:03
Your design simply not gonna work with EF. Consider two mutually exclusive nullable FK Ids. — Ivan Stoev, Mar 25 '16 at 21:09
I updated my answer to reflect the new information from your question. Hope it helps. — univ, Mar 25 '16 at 21:14

score 0 · Answer 1 · answered Mar 25 '16 at 19:55

0

Sometimes it's hard to tell what's going on behind the scenes of EF, and you can encounter some non-obvious behaviors, what I usually do in such cases - is check the actually generated SQL query, and tweak the LINQ query until it is logically equivalent to the query I expect. This is not the best approach as it is dependent on implementation details, that can change, but sometimes it is the only way to overcome a EF bug. You can use ObjectQuery or EF logging and interception of DB calls to get to actual SQL query

answered Mar 25 '16 at 19:55

ironstone13

3,325
18
24

It looks like it's just doing an `inner join`. I can't figure out how to force it to do a `left join`. – M Kenyon II Mar 25 '16 at 20:07
Most probably yes, but it could also be an outer join with a predicate in where, although unlikely – ironstone13 Mar 25 '16 at 21:23

univ · Answer 2 · 2016-03-25T21:18:44.533

0

You need two separate FK properties one int? personAssigneeId and another int? organizationAssigneeId. These FKs are pointing to two completely different entities. EF is not able to work properly if you reuse the same FK for two separate entities, it needs a FK per entity.

edited Mar 25 '16 at 21:18

answered Mar 25 '16 at 20:28

univ

717
4
12

Nope, still only 50 records. Sql says it's still doing an Inner Join. – M Kenyon II Mar 25 '16 at 20:30

score 0 · Answer 3 · answered Mar 25 '16 at 20:51

Your foreign key is non nullable by default (or required), so you need to tell the EF that it should be optional. To make it you need to override the following modelBuilder method:

public class MyContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
        }   
    }

and configure your Entity foreign key:

modelBuilder.Entity<Parent>().HasOptional(e => e.MyChild).WithMany();

Details you can look here: http://blog.staticvoid.co.nz/2012/7/17/entity_framework-navigation_property_basics_with_code_first

His exposed foreign key property MyChildId is already nullable which makes MyChild a non required field. He is using data annotations instead of the fluent API (which is what you are using). — univ, Mar 25 '16 at 20:53

When Including child elements LINQ query returns less results

3 Answers3