I wrote a program which consumes external API and it works great. I went through SO and other resources but can't solve my problem. I'm quite new to c#. I created model GetEquipment where I store data returned from API and EquipmentDb which is a SQL table where I want to insert data. I tried to save it this way:

Consume API:

    public async Task<IEnumerable<GetEquipment>> GetDataFromAPI()
    {
         try
         {
             var token = await _accountService.GetToken(
                  _configuration["CredentialsXY:client_id"],
                  _configuration["CredentialsXY:client_secret"]);

             var equipment = await _callAPIService.GetData(token);

             return equipment;

         }
         catch (Exception e)
         {
             Debug.Write(e);
             return Enumerable.Empty<GetEquipment>();
         }
     }

Model - SQL table:

    public partial class EquipmentDb
    {
        public string SerialNo { get; set; }
        public double? Latitude { get; set; }
        public double? Longitude { get; set; }
    }

Model - data returned from API:

        public class GetEquipment
        {
            public Item[] items { get; set; }
        }

        public class Item
        {
            public string serial_number { get; set; }
            public Location location { get; set; }
        }
        public class Location
        {
            public float longitude { get; set; }
            public float latitude { get; set; }
        }

Save data to db:

        public async Task Index()
        {
            var result = await GetEquipment();  // result from API

            using (var ctx = new HUDAContext())
            {
                var equipment = new EquipmentDb(); // SQL table

                    foreach(var item in result)
                    {
                        equipment.SerialNo = item.items.serial_number,  // here I iterate through 'result' where I store data from API but it does not work, 'items' is highlighted
                        equipment.Latitude = item.items.location.latitude,
                        equipment.Latitude = item.items.location.longitude
                    };

                ctx.EquipmentDbs.Add(equipment);
                await ctx.SaveChangesAsync();
            }                                            
        }   

Inserting data is not working, I try to assign data from result to each field in my SQL table but it doesn't work out.

CodePudding user response：

It's not clear from the question exactly how the data is formed. The important thing to understand is you have two collection wrappers: the IEnumerable in the API result, which includes the Item[] inside it. You have to look inside both of those.

However, I suspect you only ever expect one result for one or the other (again: it's not clear which from the question). With that in mind, you might want either this:

public async Task Index()
{
    var result = await GetEquipment();  // result from API
    var transformed = result.Select(e =>
          new EquipmentDB {
              SerialNo = e.items[0].serial_number,  // here I iterate through 'result' where I store data from API but it does not work, 'items' is highlighted
              Latitude = e.items[0].location.latitude,
              Latitude = e.items[0].location.longitude
          }
    );

    using var ctx = new HUDAContext();
    foreach(var item in transformed)
    {
        ctx.EquipmentDbs.Add(item);
        await ctx.SaveChangesAsync();
    }                                            
}

Or this:

public async Task Index()
{
    var result = await GetEquipment();  // result from API
    var transformed = result.First().Select(e =>
          new EquipmentDB {
              SerialNo = e.serial_number,  // here I iterate through 'result' where I store data from API but it does not work, 'items' is highlighted
              Latitude = e.location.latitude,
              Latitude = e.location.longitude
          }
    );

    using var ctx = new HUDAContext();
    foreach(var item in transformed)
    {
        ctx.EquipmentDbs.Add(item);
        await ctx.SaveChangesAsync();
    }                                            
}

And again, you might really need to do both (look at SelectMany() for this), and visit at each element inside the enumerable, and for each of those elements visit each item inside the array.

CodePudding user response：

Assuming you're using EF, and EquipmentDB is your context class you'd want to create a new equipmentDB object within the foreach e.g:

foreach(var item in result)
{
   ctx.EquipmentDbs.Add(new EquipmentDb
   {
      SerialNo = item.items.serial_number, 
      Latitude = item.items.location.latitude,
      Latitude = item.items.location.longitude
   });
};

otherwise your just overriding the same object and will only save the last result.

Then Call the save after the loop is done with your new populated list :)!

-Also assuming you meant GetDataFromAPI instead of GetEquipment()

CodePudding user response：

Try saving it to a list first, and then call AddRange on the context

        public async Task Index(){
            var result = await GetEquipment();  // result from API

            using (HUDAContext ctx = new HUDAContext())
            {
                
                List<EquipmentDb> equipmentlist = new List<EquipmentDb>()

                    foreach(var item in result)
                    {
                        equipmentlist.add(new EquipmentDb{
                            SerialNo = item.items.serial_number, 
                            Latitude = item.items.location.latitude, 
                            Longitude = item.items.location.longitude
                        })
                    };

                ctx.EquipmentDbs.AddRange(equipmentlist);
                await ctx.SaveChangesAsync();
            }                                            
        }