I have 3 tables: Documents
, Products
and Items
which is the join table with Quantity
and Price
.
It looks something like this (simplified):
public class Document
{
public List<Items> Items { get; set; } = new();
public List<Product> Products { get; set; } = new();
}
public class Product
{
public string Name { get; set; }
public List<Items> Items { get; set; } = new();
public List<Document> Documents { get; set; } = new();
}
public class Items
{
public Document Document { get; set; }
public Product Product { get; set; }
public decimal Quantity { get; set; }
public decimal Price { get; set; }
}
It is joined with this:
modelBuilder
.Entity<Document>()
.HasMany(p => p.Products)
.WithMany(p => p.Documents)
.UsingEntity<Items>();
And it works, but I have the following problem: result is object with separated properties
doc.Products[0].Name
doc.Items[0].Quantity
doc.Items[0].Price
Or:
doc.Items[0].Product.Name
doc.Items[0].Quantity
doc.Items[0].Price
How can I get:
doc.Products[0].Name
doc.Products[0].Quantity
doc.Products[0].Price
Many hours later:
After a some research I found out that properties in join table is called
payload
.Also EF has a so-called thing
skip navigations
so you can skip join table in model use (which is applied to the example above). For example, instead of usingdocument.items.products
, you can use justdocument.products
.But, problem is when join have payload. Then the data is in two places. For example, if you need product name and price, that is
document.product.name
anddocument.item.price
.Yes, I know, it's the same in the database, however, I was hoping that if the EF already skipped the join table when using it, why isn't there some way to handle the payload (with Fluent Api or Data Annotations attributes). It can be called
skip payload
orchildernise payload
;)Good article for this is: https://learn.microsoft.com/en-us/ef/core/change-tracking/relationship-changes
You need to use SelectMany which flatten a multidimensional array by one level.
Document doc = new Document();
var results = doc.Products.SelectMany(x => x.Items.Select(y => new { name = x.Name, quant = y.Quantity, price = y.Price })).ToList();
If you want only the first then use this :
var results = doc.Products.Select(x => x.Items.Select(y => new { name = x.Name, quant = y.Quantity, price = y.Price }).FirstOrDefault()).ToList();