How to merge and update result

Question

User-1104215994 posted

Hello,

I have an asp.net Web API 2 and in one of my action method, I have to return a list of products. There are 2 separate lists, one from my database and the other is returned from a 3rd party rest API. What I would like to do is;

• There may be the same products within those 2 lists so I would like to find them and update the product stock quantity.
• And merge the result list and send one as a response

Here is my database sample response:

{
    "Products": [
        {
            "ProductID": 6446,
            "ProductName": "400 Riot Points - 400 RP",
            "SmallProductImage": "No Image",
            "SupplierID": 1,
            "GameID": 1,
            "SupplierName": "No Supplier",
            "GameName": "400 Riot Points - 400 RP",
            "Price": 10,
            "IsStockAvailable": true,
            "StockQuantity": 2
        },
        {
            "ProductID": 6447,
            "ProductName": "300",
            "SmallProductImage": "No Image",
            "SupplierID": 1,
            "GameID": 1,
            "SupplierName": "No Supplier",
            "GameName": "300",
            "Price": 5,
            "IsStockAvailable": true,
            "StockQuantity": 2
        },
        {
            "ProductID": 18000006446,
            "ProductName": "400 Riot Points - 400 RP",
            "SmallProductImage": "No Image",
            "SupplierID": 1,
            "GameID": 1,
            "SupplierName": "No Supplier",
            "GameName": "400 Riot Points - 400 RP",
            "Price": 12,
            "IsStockAvailable": true,
            "StockQuantity": 3
        }
    ]
}

Here is how I generate my response:

var productsList = await (context.GameBanks.Where(x => x.used == 0)
                .GroupBy(g => new
                {
                    g.productCode,
                    g.productDescription,
                    g.unitPrice,
                    g.used,
                })
                .Select(gcs => new
                    {
                        gcs.Key.productCode,
                        gcs.Key.productDescription,
                        gcs.Key.unitPrice,
                        gcs.Key.used,
                        StockQuantity = gcs.Count(),
                        IsStockAvailable = gcs.Count() > 0 ? "True" : "False",
                    }
                )).ToListAsync();

Here is 3rd party response:

{
    "products": [
        {
            "productID": 6440,
            "productCode": "6440",
            "productName": "4500 Kupon",
            "productDescription": "4500 Kupon",
            "smallProductImage": "",
            "supplierID": 0,
            "gameID": 0,
            "supplierName": "",
            "gameName": "",
            "isStockAvailable": false,
            "stockQuantity": 0,
            "commission": 8,
            "sellingPrice": 27.6,
            "payablePrice": "27.60",
            "price": 30,
            "unitPrice": "30.00",
            "currency": "TRY"
        },
        {
            "productID": 6449,
            "productCode": "6449",
            "productName": "3620 Riot Points - 3620 RP",
            "productDescription": "3620 Riot Points - 3620 RP",
            "smallProductImage": "",
            "supplierID": 0,
            "gameID": 0,
            "supplierName": "",
            "gameName": "",
            "isStockAvailable": true,
            "stockQuantity": 10,
            "commission": 6.5,
            "sellingPrice": 89.76,
            "payablePrice": "89.76",
            "price": 96,
            "unitPrice": "96.00",
            "currency": "TRY"
        },
        {
            "productID": 6446,
            "productCode": "6446",
            "productName": "400 Riot Points - 400 RP",
            "productDescription": "400 Riot Points - 400 RP",
            "smallProductImage": "",
            "supplierID": 0,
            "gameID": 0,
            "supplierName": "",
            "gameName": "",
            "isStockAvailable": true,
            "stockQuantity": 10,
            "commission": 6.5,
            "sellingPrice": 11.22,
            "payablePrice": "11.22",
            "price": 12,
            "unitPrice": "12.00",
            "currency": "TRY"
        },
        {
            "productID": 6478,
            "productCode": "6478",
            "productName": "5 TL'lik Steam Cüzdan Kodu",
            "productDescription": "5 TL'lik Steam Cüzdan Kodu",
            "smallProductImage": "",
            "supplierID": 0,
            "gameID": 0,
            "supplierName": "",
            "gameName": "",
            "isStockAvailable": true,
            "stockQuantity": 10,
            "commission": 8,
            "sellingPrice": 4.6,
            "payablePrice": "4.60",
            "price": 5,
            "unitPrice": "5.00",
            "currency": "TRY"
        }
    ],
    "responseStatusCode": "00",
    "version": "V1",
    "signature": "5e7efbcf0c9cb2211d6c783f501b5dc0",
    "applicationCode": "52e7cf966b724749a7c4efadc3727ed7"
}

For product 6446, the final response stock quantity should be 12 for this sample. And if there are unmatched products, they are merged into one list. <g class="gr_ gr_55 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="55" data-gr-id="55">May be</g> something like Union removes duplicate products but how can I sum up the stock quantity then.

productlistA.Union(productlistB).ToList();

Answer 1

User1724605321 posted

Hi cenk1536,

Use Concat, GroupBy and Sum: 

         public class Person {
            public long ProductID { get; set; }


            public int StockQuantity { get; set; }

            public Person() { }

            public Person(long productID, int stockQuantity) {
                this.ProductID = productID;
                this.StockQuantity = stockQuantity;
            }

        }
        public ActionResult Index()
        {
            var personlist1 = new List<Person>();
            personlist1.Add(new Person() { ProductID = 6446, StockQuantity = 2 });
            personlist1.Add(new Person() { ProductID = 6447, StockQuantity = 2 });
            personlist1.Add(new Person() { ProductID = 18000006446, StockQuantity = 3 
            });

            var personlist2 = new List<Person>();
            personlist2.Add(new Person() { ProductID = 6440, StockQuantity = 0 });
            personlist2.Add(new Person() { ProductID = 6449, StockQuantity = 10 });
            personlist2.Add(new Person() { ProductID = 6446, StockQuantity = 10 });
            personlist2.Add(new Person() { ProductID = 6448, StockQuantity = 10 });

            List<Person> merged = personlist1.Concat(personlist2)
      .GroupBy(so => so.ProductID)
      .Select(g => new Person( g.First().ProductID, g.Sum(so => so.StockQuantity)))
      .ToList();

           
        }

Best Regards,

Nan Yu

Answer 2

User-1104215994 posted

Hi Nan Yu,

I will try and let you know.

Thank you.

Answer 3

User-1104215994 posted

How can I convert 3.party JSON response to a list in order to use your suggestion? Here is how I get response from 3. party.

var httpClient = new HttpClient();

            var keyValues = products.ToKeyValue();
            var content = new FormUrlEncodedContent(keyValues);

            var response =
                await httpClient.PostAsync("https://test.com/Product/", content);


            var htmlResponse = await response.Content.ReadAsStringAsync();
            var model = JsonConvert.DeserializeObject<ProductsResponse>(htmlResponse);

Here is ProductResponse entity;

public class ProductsResponse
    {
        public List<Product> Products { get; set; } = new List<Product>();
    }

I am getting this;

{0}Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.List`1[EPINMiddleWareAPI.Models.Product]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.
To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List<T>) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object.
Path 'Products', line 1, position 12.

Answer 4

User-1104215994 posted

This doesn't work because productID is NOT the first one in the list. It was just an example to demonstrate.

 List<Person> merged = personlist1.Concat(personlist2)
      .GroupBy(so => so.ProductID)
      .Select(g => new Person( g.First().ProductID, g.Sum(so => so.StockQuantity)))
      .ToList();

Answer 5

User-1104215994 posted

Here is my merged list as you suggested but it does NOT sum the same product IDs.

var merged = list1.Concat(list2)
                .GroupBy(g => new
                {
                    g.ProductID,
                    g.ProductName,
                    g.Price,
                    g.StockQuantity,
                    g.IsStockAvailable
                })
                .Select(gcs => new
                {
                    gcs.First().ProductID,
                    gcs.Key.ProductName,
                    gcs.Key.Price,
                    StockQuantity = gcs.Sum(so => so.StockQuantity),
                    gcs.Key.IsStockAvailable
                }).ToList();

Here is the output which is not what I want.

[
    {
        "ProductID": 6446,
        "ProductName": "400 Riot Points - 400 RP",
        "Price": 10,
        "StockQuantity": 2,
        "IsStockAvailable": true
    },
    {
        "ProductID": 6447,
        "ProductName": "300",
        "Price": 5,
        "StockQuantity": 2,
        "IsStockAvailable": true
    },
    {
        "ProductID": 18000006446,
        "ProductName": "400 Riot Points - 400 RP",
        "Price": 12,
        "StockQuantity": 3,
        "IsStockAvailable": true
    },   
    {
        "ProductID": 6449,
        "ProductName": "3620 Riot Points - 3620 RP",
        "Price": 96,
        "StockQuantity": 10,
        "IsStockAvailable": true
    },
    {
        "ProductID": 6446,
        "ProductName": "400 Riot Points - 400 RP",
        "Price": 12,
        "StockQuantity": 10,
        "IsStockAvailable": true
    },
    {
        "ProductID": 6478,
        "ProductName": "5 TL'lik Steam Cüzdan Kodu",
        "Price": 5,
        "StockQuantity": 10,
        "IsStockAvailable": true
    }
]

Answer 6

User-1104215994 posted

Is there a way to just group by ProductID but I need to select the ProductName, Price StockQuantity and IsStockAvailable as well?

Answer 7

User1724605321 posted

Hi cenk1536,

The same as code sample provided above :

  public class Person {
            public long ProductID { get; set; }

            public string ProductName { get; set; }

            public string Price { get; set; }

            public int StockQuantity { get; set; }

            public Person() { }

            public Person(long productID, int stockQuantity ,string productName,string price) {
                this.ProductID = productID;
                this.StockQuantity = stockQuantity;
                this.ProductName = productName;
                this.Price = price;
            }

        }
        public ActionResult Index()
        {
            var personlist1 = new List<Person>();
            personlist1.Add(new Person() { ProductID = 6446, StockQuantity = 2 ,ProductName= "400 Riot Points - 400 RP",Price="10" });
            personlist1.Add(new Person() { ProductID = 6447, StockQuantity = 2, ProductName = "No Image", Price = "5" });
            personlist1.Add(new Person() { ProductID = 18000006446, StockQuantity = 3, ProductName = "400 Riot Points - 400 RP", Price = "12" });

            var personlist2 = new List<Person>();
            personlist2.Add(new Person() { ProductID = 6440, StockQuantity = 0 , ProductName = "4500 Kupon", Price = "30" });
            personlist2.Add(new Person() { ProductID = 6449, StockQuantity = 10 , ProductName = "3620 Riot Points - 3620 RP", Price = "96" });
            personlist2.Add(new Person() { ProductID = 6446, StockQuantity = 10 , ProductName = "400 Riot Points - 400 RP", Price = "12" });
            personlist2.Add(new Person() { ProductID = 6478, StockQuantity = 10 , ProductName = "5 TL'lik Steam Cüzdan Kodu", Price = "5" });

            List<Person> merged = personlist1.Concat(personlist2)
      .GroupBy(so => so.ProductID)
      .Select(g => new Person(g.First().ProductID, g.Sum(so => so.StockQuantity),g.First().ProductName,g.First().Price))
      .ToList();

            return View();
        }

Best Regards,

Nan Yu