Is possible return data from two tables in ICollection using LINQ?

Question

This is my first question, I hope to do it correctly.

I am starting to use an ASP.NET Core Web API and I have a question.

I have the following models:

public class Pokemon
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }

    public ICollection<Review> Reviews { get; set; }
    public ICollection<PokemonOwner> PokemonOwners { get; set; }
    public ICollection<PokemonCategory> PokemonCategories { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<PokemonCategory> PokemonCategories { get; set; }
}

public class PokemonCategory
{
    public int PokemonId { get; set; }
    public int CategoryId { get; set; }

    public Pokemon Pokemon { get; set; }
    public Category Category { get; set; }
}

I'm trying to display in an endpoint of my controller where it shows me together the pokemons and their corresponding category.

I have tried to make a join between the two tables but it is impossible to get the expected result (return the pokemons and its category).

public List<Category> GetPokemonAndCategory(int pokemonid, int categoryid)
{
     return _context.Categories
                    .Include(a => a.PokemonCategories)
                    .Where(c => c.Id == categoryid).ToList();
}

With this code, I get this data returned:

[
  {
    "id": 2,
    "name": "Water",
    "pokemonCategories": [
      {
        "pokemonId": 2,
        "categoryId": 2,
        "pokemon": null,
        "category": null
      }
    ]
  }
]

Can you help me? Thanks!

Return pokemons and categories in the same query

EDIT

This works with cemahseri answer, if i change the DTO for something like this;

 public class PokemonCategoryDto
    {

        public Pokemon Pokemon { get; set; }
       // public Category Category { get; set; }
    }

but i get this result;

{
  "pokemon": {
    "id": 2,
    "name": "Squirtle",
    "birthDate": "1903-01-01T00:00:00",
    "reviews": null,
    "pokemonOwners": null,
    "pokemonCategories": [
      {
        "pokemonId": 2,
        "categoryId": 2,
        "pokemon": null,
        "category": {
          "id": 2,
          "name": "Water",
          "pokemonCategories": [
            null
          ]
        }
      }
    ]
  }
}

i think is because mi pokemon class have the other classes, how i can not show it like this?

 {
  "pokemon": {
    "id": 2,
    "name": "Squirtle",
    "birthDate": "1903-01-01T00:00:00",
    "pokemonCategories": [
      {
        "pokemonId": 2,
        "categoryId": 2,
         "category": {
          "id": 2,
          "name": "Water",
          
        }
      }
    ]
  }
}

Does this answer your question? [Entity Framework - Include Multiple Levels of Properties](https://stackoverflow.com/questions/10822656/entity-framework-include-multiple-levels-of-properties) — 41686d6564 stands w. Palestine, Nov 01 '22 at 17:17
one case one question is better, you should give mark to the reply which really helped you, rather than Q1 solved then pls answer Q2. And your new question seems to be un-achievable. — Tiny Wang, Nov 02 '22 at 01:51

score 1 · Answer 1 · answered Nov 01 '22 at 17:18

You can create DTO and return any data you want from that action. It's also recommended to use DTOs. Because sometimes you might not want to expose all properties in those classes. For example, let's say you have a DTO like this;

public class PokemonDto
{
    public Pokemon Pokemon { get; set; }
    public Category Category { get; set; }
}

Then you can get Pokémon and its category then return it from the action like this;

public async Task<IActionResult> GetPokemons(int pokemonId, int categoryId)
{
    var pokemon = await _databaseContext.Pokemons.FirstOrDefaultAsync(p => p.Id == pokemonId);
    var category = await _databaseContext.Categories.FirstOrDefaultAsync(c => c.Id == categoryId);

    return Ok(new PokemonDto
    {
        Pokemon = pokemon,
        Category = category
    });
}

Can you read mi update? THX!!! – mancha1 Nov 01 '22 at 18:10 — mancha1, Nov 01 '22 at 18:10

Is possible return data from two tables in ICollection using LINQ?

1 Answers1