EF6 Linq Query .Include only returning first entry of child table

Question

I would like to retrieve a list of Encounters for a Given Pokemon, as a Pokemon can be encountered in many places, so I've been trying many variants of

var currentPokemon = _context.Pokemon
                                     .Where(mon => mon.Id == id)
                                     .Include(mon => mon.Encounters)
                                     .FirstOrDefault();

With the result being a Pokemon object with all of the relevant data, but only the FIRST encounter being retrieved and put into a collection resulting in this:

Looking at the database, there are about 20 encounters for caterpie, and I'd like access to all of them, but only ever just get the one.

What the Pokemon class looks like (irrelevant fields omitted):

[Table("pokemon")]
public partial class Pokemon {
    public Pokemon()
    {
        Encounters = new HashSet<Encounters>();
    }
    [Column("id")]
    public long Id { get; set; }
    [Required]
    [Column("identifier", TypeName = "VARCHAR(79)")]
    public string Identifier { get; set; }
    .
    .
    .

    [InverseProperty("Pokemon")]
    public virtual ICollection<Encounters> Encounters { get; set; }
 }

What Encounters looks like:

public partial class Encounters {
    .
    .
    .
    [ForeignKey("PokemonId")]
    [InverseProperty("Encounters")]
    public virtual Pokemon Pokemon { get; set; }
}

Db data :

What am I misunderstanding here?

what is the sql being generated? look online to see how to get the sql query — Fredou, Nov 30 '16 at 19:46
can you show the records of db ? and are your referring correct db ? — Sampath, Nov 30 '16 at 20:00
@sampath I only have one DB, and I am getting results from it, just not the entirety of the wanted results — Dessa, Nov 30 '16 at 20:43
I've been unable to reproduce this behavior with a brand new DbContext and minimal data. I get the pokemon and the encounters as expected. There has to be something else in the code you're not showing. The code you've posted is OK and the order in which you're doing the operation although is not optimal, does generate the same sql. — Karel Tamayo, Nov 30 '16 at 20:53
Which database brand is this? Any information on SQL provider middleware? — Gert Arnold, Nov 30 '16 at 21:06
@KarelTamayo Hmm, going thru this on Debug, the object is complete with a full list of encounters as it should be, so it looks like I've been asking the wrong question. The new question for me has to do with "what happens to the rest of these encounters between 'return currentPokemon' and receiving the JSONified object on my javascript frontend?" (which definitely does have only one encounter listed) Thank you for your help. — Dessa, Nov 30 '16 at 21:33
@Dessa yeap. That in fact is another question. Your'e welcome. — Karel Tamayo, Nov 30 '16 at 22:19

score 0 · Answer 1 · answered Nov 30 '16 at 19:44

0

I think its because you are calling .FirstOrDefault(), which is only getting the first item. Can you omit that and add .ToList()? Also, var currentPokemon doesn't seem like a good variable name. You said you want a list of Encounters, right? How about var pokemonEncounters?

answered Nov 30 '16 at 19:44

kimbaudi

13,655
9
62
74

first or default would be done on the pokemon result, not the encounter – Fredou Nov 30 '16 at 19:45
What Fredou said. Omitting `.FirstOrDefault()` just returns an array of length 1 of Pokemon, which is pointless, because I'm only ever looking to get 1 Pokemon. As for what I'm looking for, It's a single pokemon with a list of all associated encounters for that pokemon. In plain english, I want to ask "Who is pokemon #10" and get "Pokemon 10 is Caterpie, and you can find Caterpie in the following places: (list of places)." Adding `.ToList()` at the end of this results in a list of encounters, but no Pokemon. – Dessa Nov 30 '16 at 19:50
How about var currentPokemon = _context.Pokemon .Where(mon => mon.Id == id) .Include(mon => mon.Encounters) .FirstOrDefault().Select(p => p.Encounters); – kimbaudi Nov 30 '16 at 19:51
You cannot call `.Select()` on a Pokemon object which was returned by `.FirstOrDefault()` – Riwen Nov 30 '16 at 19:57
@Fredou and @Riwen - You're right. Now that I think about it, `var currentPokemon = _context.Pokemon.Include(mon => mon.Encounters).FirstOrDefault(p => p.Id = id);` should return the first Pokemon with a list of Encounters. you should be able to access the Encounters using `currentPokemon.Encounters`. Correct? – kimbaudi Nov 30 '16 at 20:02

score 0 · Answer 2 · answered Nov 30 '16 at 21:40

I think that the problem is the way that you set the relationship using ForeignKey and InverseProperty.

Try to rewrite to something like:

[Table("Pokemon")]
public class Pokemon
{
    public int Id { get; set; }
    public string Name { get; set; }

    [InverseProperty("Pokemon")]
    public ICollection<Encounter> Encounters { get; set; }

}

[Table("Enconters")]
public class Encounter
{
    public int Id { get; set; }

    public int PokemonId { get; set; }

    [ForeignKey("PokemonId")]
    public Pokemon Pokemon { get; set; }

}

score 0 · Answer 3 · answered Nov 30 '16 at 21:43

0

My apologies, I've been looking to the wrong places for my answer. After stepping through this in a debugger, I can see that my Pokemon query does indeed return the desired result: A pokemon with many encounters attached. My problem seems to be elsewhere (specifically: the JSONified object I'm seeing come through to my web front-end truncates the encounters array to contain only the first result).

I'll post a new question with the correct problem and link to it from here when it's figured out.

answered Nov 30 '16 at 21:43

Dessa

41
5

Yep, found a json serialization error in the output. – Dessa Nov 30 '16 at 21:57
Okay, so the issue was that the JSON serializer detected a self-referencing loop (because Pokemon referred to Encounters, and Encounters referred back to pokemon) and threw an error in the Debug output (which I missed repeatedly) to avoid going into an infinite loop. The solution to THIS issue is here: http://stackoverflow.com/questions/17313632/self-referencing-loop-detected-getting-back-data-from-webapi-to-the-browser – Dessa Dec 01 '16 at 16:18

EF6 Linq Query .Include only returning first entry of child table

3 Answers3