I'm currently working on net core 2 application.
I created a "Goods" abstract class.
public abstract class Good : BaseEntity
{
public int Id { get; set; }
public string Title { get; set; }
public string Details { get; set; }
public double Price { get; set; }
public string IndexId { get; set; }
public string RefPintel { get; set; }
public bool IsDiscountable { get; set; }
public int ProductTypeId { get; set; }
public bool IsEnabled { get; set; }
public int DepartmentId { get; set; }
public string Season { get; set; }
}
Two inherited classes Product and Batch.
public class Product : Good
{
public string File { get; set; }
public bool? IsDisplayedOnJCE { get; set; }
public bool? IsBasicProduct { get; set; }
public int? PintelSheetId { get; set; }
public class Batch : Good
{
public ICollection<Product> Products { get; set; }
As you can see, a batch includes a collection of Products.
Everything works fine, EF creates a single table "Goods" using TPH inheritance model. A product within a batch has a BatchId (column created by EF), discriminator column works well (even though I do not have access to it in my code) etc...
My problem is that, when I perform a GetAll request, I get all batch products twice.
{
"products": [
{
**"id": 85,**
"title": "500 Pièces LEGO",
"details": "Boîte de 500 pièces LEGO pour construire des trucs mega top",
"refPintel": "0002",
"file": "lego-500pcs.pdf",
"providerId": 2,
"productType": "Jouet"
},
{
"id": 86,
"title": "1000 Pièces LEGO",
"details": "Boîte de 1000 pièces LEGO pour construire des trucs mega top",
"refPintel": "0001",
"file": "lego-1000pcs.pdf",
"providerId": 3,
"productType": "Jouet"
}
],
"id": 78,
"details": "Lot super LEGO 1500 pièces ! ",
"title": "Lot LEGO TOP WOWOW",
"price": 139.99,
"indexId": "J",
"refPintel": "0004",
"isDiscountable": true,
"productTypeId": 1,
"isEnabled": true,
"departmentId": 1,
"season": "2018",
"isBatch": true,
"createdBy": "mathieu",
"updatedBy": "mathieu"
},
{
"file": "lego-500pcs.pdf",
"isDisplayedOnJCE": true,
"isBasicProduct": true,
"pintelSheetId": 0,
"providerId": 2,
"id": 85,
"details": "Boîte de 500 pièces LEGO pour construire des trucs mega top",
"title": "500 Pièces LEGO",
"price": 50,
"indexId": "G",
"refPintel": "0002",
"isDiscountable": false,
"productTypeId": 1,
"isEnabled": true,
"departmentId": 1,
"season": null,
"isBatch": false,
"createdBy": "mathieu",
"updatedBy": "mathieu"
},
{
"file": "lego-1000pcs.pdf",
"isDisplayedOnJCE": true,
"isBasicProduct": true,
"pintelSheetId": 0,
"providerId": 3,
"id": 86,
"details": "Boîte de 1000 pièces LEGO pour construire des trucs mega top",
"title": "1000 Pièces LEGO",
"price": 100,
"indexId": "I",
"refPintel": "0001",
"isDiscountable": false,
"productTypeId": 1,
"isEnabled": true,
"departmentId": 1,
"season": null,
"isBatch": false,
"createdBy": "mathieu",
"updatedBy": "mathieu"
},
As you can see, I have two products (id 85 and id 86) that are included in my batch AND included in my "Goods" query. They're the same, they should'nt exist outside a batch.
I managed to not remove them from my Goods query with a foreach
foreach (var item in query)
{
if (item is Batch)
{
var batch = item as Batch;
query = query.Except(query.Where(p => batch.Products.Any(b => b.Id == p.Id)));
}
}
And the result
{
"products": [
{
"id": 85,
"title": "500 Pièces LEGO",
"details": "Boîte de 500 pièces LEGO pour construire des trucs mega top",
"refPintel": "0002",
"file": "lego-500pcs.pdf",
"providerId": 2,
"productType": "Jouet"
},
{
"id": 86,
"title": "1000 Pièces LEGO",
"details": "Boîte de 1000 pièces LEGO pour construire des trucs mega top",
"refPintel": "0001",
"file": "lego-1000pcs.pdf",
"providerId": 3,
"productType": "Jouet"
}
],
"id": 78,
"details": "Lot super LEGO 1500 pièces ! ",
"title": "Lot LEGO TOP WOWOW",
"price": 139.99,
"indexId": "J",
"refPintel": "0004",
"isDiscountable": true,
"productTypeId": 1,
"isEnabled": true,
"departmentId": 1,
"season": "2018",
"isBatch": true,
"createdBy": "mathieu",
"updatedBy": "mathieu"
},
{
"products": [
{
"id": 88,
"title": "Puzzle 3d 850 pieces - poudlard la grande salle",
"details": "Édifiez la célèbre École de Magie et de Sorcellerie avec Poudlard - Grande Salle, un puzzle 3D en mousse de 850 pièces. Vous y retrouverez plusieurs lieux connus du merveilleux monde d'Harry Potter tel que la grande salle, le bureau du directeur ainsi que plusieurs autres. Vous pouvez combiner ce puzzle avec Poudlard - Tour d'astronomie (référence 04031480 vendue séparément) et obtenir un puzzle 3D de 1725 pièces. Livret d'instructions inclus. Assemblage à tenons et mortaises, produisant des structures et assemblages 3D des plus solides. Conception et reproductions d'illustrations de qualité supérieure. Pour des heures de divertissement et de plaisir en famille. Dim. : L.50 x l.38 x H.47 cm.",
"refPintel": "0003",
"file": "puzzle-Poudlards.pdf",
"providerId": 2,
"productType": "Jouet"
}
],
"id": 87,
"details": "lot de produits harry potter",
"title": "Lot Harry Potter",
"price": 199.9,
"indexId": "J",
"refPintel": "0005",
"isDiscountable": true,
"productTypeId": 1,
"isEnabled": true,
"departmentId": 3,
"season": "2018",
"isBatch": true,
"createdBy": "mathieu",
"updatedBy": "mathieu"
},
This works as intended, my Products 85 and 86 are not being displayed. But as you may know, performances are suffering from the foreach. With the foreach, it takes about 10 seconds to get the result on a 1200 rows table. Without, 0.2 seconds.
My Question is how can I send to my user all my Goods, meaning Products and Batches, without having the products who belong to batches sent twice (only inside a batch, not like a product outside a batch), without "filtering" my query with a foreach.
I've tried some other things like playing around my modelbuilder etc... without succeeding...
Maybe I'm missing something obvious there, since I'm new to this, but I'm currently stuck with that performance issue.
If you need any clarification, details or anything else, just let me know.
Thanks for reading me.
edit: added a DB screenshot to show how it looks like if it can help undertanding the problem.
edit 2: added the output of the foreach.
edit 3 : edited the question... a bit.
