CODEWITHSUNDEEP
c#sql
Frey_ja
Frey_ja

Reputation: 51

Populate object from dataset when having list in list C#

I have this data available from the database:

|----------------|--------------|----------------|----------------|----------------|----------------|
|  entity_name   |  provider_id | provider_name  |  product_id    | product_name   | country_name   |    
|----------------|--------------|----------------|----------------|----------------|----------------|
|     test       |     123      |  Provider1     |         1      |  Product1      |    Russia      | 
|----------------|--------------|----------------|----------------|----------------|----------------| 
|     test       |     123      |  Provider1     |         2      |  Product2      |    Spain       | 
|----------------|--------------|----------------|----------------|----------------|----------------|
|     test       |     123      |  Provider1     |         3      |  Product3      |    France      | 
|----------------|--------------|----------------|----------------|----------------|----------------|
|     test       |     456      |  Provider2     |         3      |  Product3      |    France      | 
|----------------|--------------|----------------|----------------|----------------|----------------|
|     test       |     123      |  Provider1     |         4      |  Product4      |    France      |

And I have to map it to this model in C#:

public class EntityModel 
{
   public string EntityName { get; set; }
   public List<ProviderModel> Providers { get; set; }
}

public class ProviderModel
{
   public int ProviderID { get; set; }
   public string ProviderName { get; set; }
   public List<ProductModel> Products { get; set; }
   public ProviderModel() { }
}

public class ProductModel
{
  public int ProductID { get; set; }
  public string ProductName { get; set; }
  public string CountryName { get; set; }
  public ProductModel() { }
}

So basically I have to group by providers, for every provider I have to show the products:

{
   "entityName": "string",
   "providers": [
    {
     "ProviderID": 0,
     "ProviderName": "string",
     "products": [
       {
        "productId": 0,
        "productName": "string",
        "countryName": "string"
       }
     ]
    }
  ]
}

The sp that returns the data is like this:

SELECT DISTINCT 
e.entity_name 
,pro.provider_id
,pro.provider_name
,p.product_id
,p.product_name
,pc.country_name
FROM provider_product_table ppt
INNER JOIN product p ON ppt.product_id = p.product_id
INNER JOIN product_parent pp on pp.product_parent_id=p.product_parent_id
INNER JOIN provider pro ON pro.provider_id = ppt.provider_id
INNER JOIN product_country pc on pc.product_id=p.product_id
INNER JOIN entity e on e.product_parent_id=pp.product_parent_id
WHERE p.product_parent_id = @product_parent_id
ORDER BY p.product_id ASC

I tried a lot of groupBy versions but I get stuck at mapping the second list, the products one. How can I achieve this? Thank you !

Upvotes: 0

Views: 90

Answers (1)

MoxGeek
MoxGeek

Reputation: 458

let's start with saying that your database is not well-structured or not created for your need, it will be more than good if you try to seprate your data into three table (entity,provider,product) with relationship between entity and provider , and relationship between your provider and the product table.

however, maybe you are developing new feature that it was not thinking at first to make your code or even your database more extensible. in this case i need to see your group query request (the groupby ) that you created already.

meanwhile i can imagine your need, so you have several solution.

the first one is what are you trying to do , create several query that fill your classes ,i can also imagine with Redundancy, that because there are some id who are unfortunately duplicated due to your database structure, in this case you need to use DISTINCT or INTERSECT (even if its not your need here ) .

seconds one you can deal with temporary table using the keyword INTO#tmpTable if you are using sqlserver, so basically is to crate a new tables in memory to perform your query ( those tables is what i propose above ).

try to edit your question with those query you describe.

Upvotes: 1

Related Questions