private static void CheckCanada()
{
try
{
var notFound = 0;
var total = 0;
var missed = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
using (var textReader = File.OpenText(@"C:\temp\CanadianZipCodes201805.csv"))
{
using (var csv = new CsvReader(textReader))
{
while (csv.Read())
{
var postalCode = csv.GetField <string>(0).Replace(" ", "").Trim();
var city = csv.GetField <string>(1);
var prov = csv.GetField <string>(2);
if (!context.CanadaPostalCode.Any(x => x.PostalCode == postalCode))
{
notFound++;
Console.WriteLine($"Total={total}, NotFound={notFound}, PostalCode={postalCode}");
//Get same city
var template = context.CanadaPostalCode.FirstOrDefault(x => x.City == city);
if (template != null)
{
//Add new
context.AddItem(new CanadaPostalCode {
PostalCode = postalCode, City = template.City, Latitude = template.Latitude, Longitude = template.Longitude
});
context.SaveChanges();
}
else
{
missed++;
}
}
total++;
}
}
}
}
Console.WriteLine($"Total={total}, NotFound={notFound}, Missed={missed}");
}
catch (Exception ex)
{
throw;
}
}
private static void ResetPopulations()
{
try
{
var index = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
var allZips = context.City.ToList();
var states = context.State.ToList();
var input = @"C:\temp\cities.csv";
using (var sr = new StreamReader(input))
{
using (var csv = new CsvReader(sr))
{
while (csv.Read())
{
var record = csv.GetRecord <CityItem>();
var population = record.Population.ToInt();
if (population > 0)
{
var state = states.Where(x => x.Name.Match(record.State)).Select(x => x.Abbr).FirstOrDefault();
var dbZip = allZips.FirstOrDefault(x => x.Name.Match(record.City) && x.State == state);
if (dbZip == null)
{
context.AddItem(new Gravitybox.GeoLocation.EFDAL.Entity.City {
Name = record.City, Population = population, State = state
});
Console.WriteLine($"Index={index}, Action=Add, City={record.City}, State={state}, Population={record.Population}");
}
else if (dbZip != null && dbZip.Population != population)
{
Console.WriteLine($"Index={index}, Action=Update, City={record.City}, State={state}, Population={record.Population}, Population2={dbZip.Population}");
dbZip.Population = population;
}
}
index++;
}
}
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw;
}
}
private static void CleanCity()
{
try
{
var index = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
var allZips = context.City.ToList();
//Set to largest population
Dictionary <string, int> _cache = new Dictionary <string, int>();
foreach (var item in allZips)
{
var key = (item.Name + "|" + item.State).ToLower();
if (!_cache.ContainsKey(key))
{
_cache.Add(key, item.Population ?? 0);
}
var newPopulation = item.Population ?? 0;
if (_cache[key] < newPopulation)
{
_cache[key] = newPopulation;
}
index++;
Console.WriteLine($"Loop 1: Index={index}, Count={allZips.Count}");
}
index = 0;
//Reset the populations
foreach (var item in allZips)
{
var key = (item.Name + "|" + item.State).ToLower();
item.Population = _cache[key];
index++;
Console.WriteLine($"Loop 2: Index={index}, Count={allZips.Count}");
}
context.SaveChanges();
allZips = context.City.ToList();
//Delete duplicates
index = 0;
var dups = new HashSet <int>();
_cache = new Dictionary <string, int>();
foreach (var item in allZips)
{
var key = (item.Name + "|" + item.State).ToLower();
if (_cache.ContainsKey(key))
{
dups.Add(item.CityId);
}
else
{
_cache.Add(key, 0);
}
index++;
Console.WriteLine($"Loop 3: Index={index}, Count={allZips.Count}");
}
index = 0;
foreach (var key in dups)
{
context.City.Where(x => x.CityId == key).Delete();
context.SaveChanges();
Console.WriteLine($"Loop 4: Index={index}, Count={dups.Count}");
}
Console.WriteLine("Done");
}
}
catch (Exception ex)
{
throw;
}
}