Top Rat Top Rat - 3 months ago 12
ASP.NET (C#) Question

Including properties of a one to many or many to many in LINQ .NET CORE

Having created a many to many relation using code first using similar classes to:

class Product
{
public int Id { get; set; }
public ICollection<Categorization> Categorizations { get; set; }
}

class Categorization
{
public int ProductId { get; set; }
public Product Product { get; set; }

public int CategoryId { get; set; }
public Category Category { get; set; }
}

class Category
{
public int Id { get; set; }
public ICollection<Categorization> Categorizations { get; set; }
}


And the following for a composite key in the dbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Categorization>().HasKey(x => new { x.ProductId , x.CategoryId });
}


I have tried to return JSON of a Product containing all its Categories, or vice-versa.
Using this method:

var prod = _context.Products
.Include(p => p.Categorization)
.FirstOrDefault(p => p.Id == 1));


I get

{
"product": {
"id": 1,
"categorizations": [
{
"categoryId": 1
}
]
}
}


This also cuts off anything which would have come after, e.g Another field.

Using this method:

var prod= _context.Products
.Select(p => new
{
p.Id,
p.Categorization
})
.FirstOrDefault(p => p.Id == 1);


I get

{
"product": {
"id": 1,
"categorizations": [
{
"categoryId": 1,
"category": null,
"productId": 1,
"product": null
},
{
"categoryId": 2,
"category": null,
"productId": 1,
"product": null
},
]
}
}


How can I:


  1. Get the properties of the referenced object "category" using include.

  2. Get the properties of the referenced object "category" using select.

  3. Also, slightly off topic, can I query on a field which is not being "selected". Example being I wanted to search for a product by Id, but return only the other data.


Answer

I would suggest you reading the Loading Related Data section from the documentation.

Here are the answers to (some of) your concrete questions:

(1) Get the properties of the referenced object "category" using include.

You should use ThenInclude:

var prod = _context.Products
            .Include(p => p.Categorization)
                .ThenInclude(c => c.Category)
            .FirstOrDefault(p => p.Id == 1));

(2) Get the properties of the referenced object "category" using select.

You can use nested projection (select):

var prod = db.Products
            .Where(p => p.Id == 1)
            .Select(p => new
            {
                p.Id,
                Categories = p.Categorizations.Select(c => c.Category).ToList()
            })
            .FirstOrDefault();

What about question (3), you need to provide a concrete example in order to get a concrete answer.

Comments