I have a online dataset that I am looping through to save it to a sqlite database. It works well, however the 'count' in which it is sequentially looping through is based on the ID column, however there are instances where the IDs (for the category that I am filtering on) are not sequential. I'd rather loop through row number or similar if possible - but I can't seem to find a way to do it. Below is my code:

Note the count starts at 793 because this is the ID of the first 'surface' categorised record

private async void updateAssetRegisterLocal()
        {
            try
            {
                var current = Connectivity.NetworkAccess;

                if (current != NetworkAccess.Internet)
                {
                    await DisplayAlert("No Internet Connectivity", "Move to a location with mobile coverage and try again", "OK");
                }

                else
                {

                    UserDialogs.Instance.ShowLoading();

                    HttpClient client = new HttpClient();

                    var response = await client.GetStringAsync("https://XXXX.azurewebsites.net/api/assets");

                    var onlineAssets = JsonConvert.DeserializeObject<List<AssetRegister>>(response);

                    var assetsPrelim = onlineAssets
                    .Where(w => w.Asset_Category == "Surface")
                    .OrderBy(d => d.ID)
                    .ToList();
                    ;

                    using (SQLiteConnection conn = new SQLiteConnection(App.FilePath))
                    {
                        try
                        {
                            conn.DropTable<AssetRegister>();
                        }
                        catch
                        {
                            await DisplayAlert("Unsuccessful", "Can't Access", "OK");
                        }
                    }

                    for (int count = 793; count <= (assetsPrelim.Count   793); count  )
                    {
                        var assets = assetsPrelim.Where(x => x.ID == count);
                        var id = assets.Select(l => l.ID).FirstOrDefault();
                        var asset_ID = assets.Select(l => l.Asset_ID).FirstOrDefault();
                        var road_ID = assets.Select(l => l.Road_ID).FirstOrDefault();
                        var segmentString = assets.Select(l => l.AssetSegmentString).FirstOrDefault();
                        var hierarchy = assets.Select(l => l.Road_Hierarchy).FirstOrDefault();
                        var assetCategory = assets.Select(l => l.Asset_Category).FirstOrDefault();
                        var lat = assets.Select(l => l.Lat).FirstOrDefault();
                        var inspectionDue = assets.Select(l => l.Inspection_Due).FirstOrDefault();
                        var lon = assets.Select(l => l.Lon).FirstOrDefault();
                        var stdRoadId = assets.Select(l => l.StdRoadID).FirstOrDefault();
                        var assetType = assets.Select(l => l.Asset_Type).FirstOrDefault();
                        var inspectionDate = assets.Select(l => l.Last_Inspection_Date).FirstOrDefault();

                        using (SQLiteConnection con = new SQLiteConnection(App.FilePath))
                        {
                            con.CreateTable<AssetRegister>();
                            var assetsLocal = con.Table<AssetRegister>().ToList();

                            int arlID = assetsLocal.Count   1;

                            AssetRegister arl = new AssetRegister()
                            {
                                ID = id,
                                Asset_ID = asset_ID,
                                Asset_Type = assetType,
                                Asset_Category = assetCategory,
                                Road_ID = road_ID,
                                Road_Hierarchy = hierarchy,
                                AssetSegmentString = segmentString,
                                Inspection_Due = inspectionDue,
                                StdRoadID = stdRoadId,
                                Lat = lat,
                                Lon = lon,
                                Last_Inspection_Date = inspectionDate,

                            };

                            using (SQLiteConnection conn = new SQLiteConnection(App.FilePath))
                            {
                                conn.CreateTable<AssetRegister>();
                                int rowsAdded = conn.Insert(arl);
                                conn.Close();
                            }
                        }
                    }

                    await DisplayAlert("Success", "Asset Register Updated on Device", "OK");
                    UserDialogs.Instance.HideLoading();
                }
            }

            catch
            {
                await DisplayAlert("Error", "Could not get data - Report to System Adminstrator", "OK");
                UserDialogs.Instance.HideLoading();
            }
        }

CodePudding user response：

It looks like you can loop simply iterating using foreach Instead of

for (int count = 793; count <= (assetsPrelim.Count   793); count  )
{
  var assets = assetsPrelim.Where(x => x.ID == count);
  var id = assets.Select(l => l.ID).FirstOrDefault();
  var asset_ID = assets.Select(l => l.Asset_ID).FirstOrDefault();
  var road_ID = assets.Select(l => l.Road_ID).FirstOrDefault();
  var segmentString = assets.Select(l => l.AssetSegmentString).FirstOrDefault();
  var hierarchy = assets.Select(l => l.Road_Hierarchy).FirstOrDefault();
  var assetCategory = assets.Select(l => l.Asset_Category).FirstOrDefault();
  var lat = assets.Select(l => l.Lat).FirstOrDefault();
  var inspectionDue = assets.Select(l => l.Inspection_Due).FirstOrDefault();
  var lon = assets.Select(l => l.Lon).FirstOrDefault();
  var stdRoadId = assets.Select(l => l.StdRoadID).FirstOrDefault();
  var assetType = assets.Select(l => l.Asset_Type).FirstOrDefault();
  var inspectionDate = assets.Select(l => l.Last_Inspection_Date).FirstOrDefault();
  ...

you can have

foreach (var asset in assetsPrelim)
{
  var id = asset.ID;
  var asset_ID = asset.Asset_ID;
  var road_ID = assets.Road_ID;
  var segmentString = asset.AssetSegmentString;
  var hierarchy = asset.Road_Hierarchy;
  var assetCategory = asset.Asset_Category;
  var lat = asset.Lat;
  var inspectionDue = asset.Inspection_Due;
  var lon = asset.Lon;
  var stdRoadId = asset.StdRoadID;
  var assetType = asset.Asset_Type;
  var inspectionDate = asset.Last_Inspection_Date;
  ...

CodePudding user response：

Simply use foreach() instead of for() loop,

for (var asset in assetsPrelim)
{
  var id = asset.Id;
  var asset_ID = asset.Asset_ID;
  var road_ID = assets.Road_ID;

  //...
   
}