private void Confirm()
{
if (string.IsNullOrEmpty(HighTrainYesFolderPath) ||
string.IsNullOrEmpty(HighTrainNoFolderPath) ||
string.IsNullOrEmpty(SelectedIndex))
{
Messenger.Default.Send(new GenericMessage <string>("选择相关参数"), "Message");
return;
}
var folderPath = DialogHelper.OpenFolderDialog(true);
if (string.IsNullOrEmpty(folderPath))
{
return;
}
if (Cities.Count(item => item.IsSelected) == 0)
{
return;
}
if (!FileExist())
{
Messenger.Default.Send(new GenericMessage <string>("文件缺失"), "Message");
return;
}
Init();
InitDic();
Messenger.Default.Send(
Run(folderPath) ? new GenericMessage <string>("指标计算成功") : new GenericMessage <string>("指标计算失败"), "Message");
}
public CityDataContext(DbContextOptions <CityDataContext> options)
: base(options)
{
//Database.EnsureDeleted();
Database.EnsureCreated();
if (Cities.Count() == 0)
{
Cities.Add(new City {
Name = "Odense", Description = "Smukkeste By", Attractions = new List <Attraction> {
new Attraction {
Name = "Døckerslundbageren", Description = "Best Bager i verden"
}, new Attraction {
Name = "Larz' crib", Description = "Det fedeste hus i verden"
}
}
});
Cities.Add(new City {
Name = "Kerteminde", Description = "En anden by i Danmark", Attractions = new List <Attraction> {
new Attraction {
Name = "Amanda", Description = "En statue af en kælling"
}, new Attraction {
Name = "Vaffelhuset", Description = "Verdens Bedste Ishus"
}
}
});
SaveChanges();
}
}
private bool ValidateFields()
{
bool result = true;
if (certificateCode.Equals("5"))
{
if (CountrySelected == null)
{
ErrorCountry = true;
result = false;
}
if (Cities.Count(x => x.IsSelected == true) < 1)
{
ErrorCity = true;
result = false;
}
}
if (CertificateCode.Equals("3"))
{
if (string.IsNullOrWhiteSpace(DateSelected))
{
result = false;
ErrorDate = true;
}
}
return(result);
}
static void Main(string[] args)
{
using (var db = new NorthwindEntities())
{
Console.WriteLine("\n\nAll Customers\n\n");
var output1 = from customer in db.Customers
select customer;
PrintCustomers(output1.ToList());
Console.WriteLine("\n\nAll London Customers\n\n");
var output2 = from c in db.Customers where c.City == "London"
select c;
PrintCustomers(output2.ToList());
Console.WriteLine("\n\nAll London Customers Descending\n\n");
var output3 = from c in db.Customers where c.City == "London"
orderby c.ContactName descending
select c;
PrintCustomers(output3.ToList());
Console.WriteLine("\n\nCreating a New Output Object\n\n");
var output4 = from c in db.Customers
orderby c.Country
select new
{
Name = c.ContactName,
Address = c.City + " " + c.Country
};
foreach (var c in output4)
{
Console.WriteLine($"{c.Name, -20}, {c.Address}");
}
Console.WriteLine("\n\nGroup By City\n\n");
var output5 = from customer in db.Customers
group customer by customer.City into Cities
orderby Cities.Key
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in output5)
{
Console.WriteLine($"{c.City, -20}, {c.Count}");
}
Console.WriteLine("\n\nSelect Customers and their Orders\n\n");
var output6 = from customer in db.Customers
join order in db.Orders
on customer.CustomerID equals order.CustomerID
select new
{
Name = customer.ContactName,
OrderID = order.OrderID,
OrderDate = order.OrderDate
};
output6.ToList().ForEach(c => Console.WriteLine($"{c.Name,-10}, {c.OrderID,10}, {c.OrderDate}"));
}
}
private void ProgressHandler(int count)
{
lblProgress.Text = $"{count}/{Cities.Count()}";
progressBar1.PerformStep();
if (DGVCities.SelectedRows.Count > 0)
{
DGVCities.FirstDisplayedScrollingRowIndex = DGVCities.SelectedRows[0].Index;
}
}
private void Run(object p)
{
Hashtable para = p as Hashtable;
var wait = para["wait"] as ProgressWait;
var folderPath = para["folderPath"].ToString();
int totalCount = Cities.Count(item => item.IsSelected);
int count = 0;
wait.SetWaitCaption("计算高铁未通车");
RasterOp.Reset();
try
{
float sumM = SumM();
foreach (var calculatorCity in Cities)
{
if (calculatorCity.IsSelected)
{
wait.SetProgress((double)count++ / totalCount);
Overlay(RasterOp, HighTrainNoFolderPath, calculatorCity.Name + "_高铁未通车", calculatorCity.Name);
}
}
RasterOp.Overlay(item => item / sumM);
Wirte(RasterOp, folderPath, "区位可达性_未通车");
GC.Collect();
//*********************************************
count = 0;
wait.SetWaitCaption("计算高铁通车后");
RasterOp.Reset();
foreach (var calculatorCity in Cities)
{
if (calculatorCity.IsSelected)
{
wait.SetProgress((double)count++ / totalCount);
Overlay(RasterOp, HighTrainYesFolderPath, calculatorCity.Name + "_高铁通车后", calculatorCity.Name);
}
}
RasterOp.Overlay(item => item / sumM);
Wirte(RasterOp, folderPath, "区位可达性_通车后");
GC.Collect();
para["ret"] = true;
}
catch (Exception e)
{
_log.Error(e.Message + e.StackTrace);
para["ret"] = false;
}
finally
{
wait.CloseWait();
}
}
protected async Task RestaurantSelected(GoogleTextSearchDTO restaurant)
{
this.Cities[Cities.Count() - 1].SelectedRestaurant = restaurant;
this.RestaurantSearches.Clear();
if (this.MuseumSearches.Count == 0 && this.RestaurantSearches.Count == 0)
{
var serializedCities = JsonConvert.SerializeObject(this.Cities);
await LocalStorageManagerService.DeletePermanentItemAsync("Cities");
await LocalStorageManagerService.SavePermanentItemAsync("Cities", serializedCities);
}
await JSRuntime.InvokeVoidAsync("hideLocation");
ShowSuccessAlert("Restaurant selected");
StateHasChanged();
}
public int Testing_9()
{
using (var db = new NorthwindDbContext())
{
var cust =
from c in db.Customers
group c by c.City into Cities
where Cities.Count() > 2
select new
{
Cities.Key,
Count = Cities.Count()
};
return(cust.Count());
}
}
private async void btnStart_Click(object sender, EventArgs e)
{
switchButtons(true);
progressBar1.Maximum = Cities.Count();
CancellationTokenSource = new CancellationTokenSource();
int i = 0;
await Task.Run(() =>
{
try
{
ParallelOptions parallelOptions = new ParallelOptions()
{
MaxDegreeOfParallelism = 2000,
CancellationToken = CancellationTokenSource.Token
};
Parallel.ForEach(Cities, parallelOptions, (city, loop) =>
{
DGVCities.Rows[i++].Selected = true;
CityWeathers.Add(Factory.newApiHandler().GetCityWeather(city.id));
this.Invoke(progress, i);
if (parallelOptions.CancellationToken.IsCancellationRequested)
{
loop.Break();
}
DGVCities.Rows[i++].Selected = false;
});
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
CancellationTokenSource.Dispose();
Save();
}
});
switchButtons(false);
}
private void Run(object p)
{
Hashtable para = p as Hashtable;
var wait = para["wait"] as ProgressWait;
string folderPath = para["folderPath"].ToString();
string rasterFolderPath = Path.GetDirectoryName(_rasterFilePath);
try
{
RasterReader reader = new RasterReader(rasterFolderPath, RasterFileName + ".tif");
int totalCount = Cities.Count(item => item.IsSelected);
int count = 0;
foreach (var calculatorCity in Cities)
{
if (calculatorCity.IsSelected)
{
wait.SetProgress((double)count++ / totalCount);
Run(reader, calculatorCity, folderPath);
GC.Collect();
}
}
para["ret"] = true;
}
catch (ArgumentOutOfRangeException e)
{
Messenger.Default.Send(new GenericMessage <string>("存在城市不在计算范围内"), "Exception");
para["ret"] = false;
}
catch (Exception e)
{
_log.Error(e.Message + e.StackTrace);
para["ret"] = false;
}
finally
{
wait.CloseWait();
}
}
static void Main(string[] args)
{
#region Explaination
// Read Northwind using Entity core 2.1.1
// Basic LINQ
// More advanced LINQ with lambda
// Nuget : entityframeworkcore 2.1.1/ entityframeworkcore.sqlserver 2.1.1/ entityframeworkcore.design 2.1.1
// Command - install-package microsoft.entityframeworkcore - v 2.1.1
#endregion
List <Customer> customers = new List <Customer>();
List <Customer> selectedDatabaseCustomers;
List <Product> productsList = new List <Product>();
List <Category> categoriesList = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
// Simple Linq from local collection
// Whole dataset is returned (more data)
// IENUMERABLE ARRAY
var selectedLocalCustomers =
from customer in customers
select customer;
// Simple query over database directly
// Only return actual data we need
// Lazy loading - query not actually executed
// Data not actually arrived yet
// Force data by pushing ToList
selectedDatabaseCustomers =
(from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.CompanyName
select customer).ToList();
printCustomers(selectedDatabaseCustomers);
// Create custom object output
var selectedCustomCustomers =
(from customer in db.Customers
select new {
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Take(10).ToList();
foreach (var c in selectedCustomCustomers)
{
Console.WriteLine($"{c.Name,-20}{c.Location}");
}
// Grouping
// Group and list all customers by city
// City... count(Customer)
var selectedGroupCustomers =
(from customer in db.Customers
group customer by customer.City into Cities
orderby Cities.Key
select new
{
City = Cities.Key,
Count = Cities.Count()
});
foreach (var c in selectedGroupCustomers.ToList())
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
// Join
// Product with categoryID => category
var products =
(from product in db.Products
join category in db.Categories
on product.CategoryID equals category.CategoryID
select new
{
ID = product.ProductID,
Name = product.ProductName,
Category = category.CategoryName
}).ToList();
products.ForEach(p => Console.WriteLine($"{p.ID,-10}" + $"{p.Name,-30}" + $"{p.Category}"));
// Smarter way to join
Console.WriteLine("\n\nDisplay List using smarter \n" + "'dot' Notation to join tables\n");
// Read in products and categories
productsList = db.Products.ToList();
categoriesList = db.Categories.ToList();
productsList.ForEach(p => Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"));
// List categories with count of products and sub-list of product names
Console.WriteLine("\n\nList categories with count of products and sub-list of product names\n");
categoriesList.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-5}{c.CategoryName,-15} has {c.Products.Count} products");
// Loop within loop
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
});
// Linq lambda notation
Console.WriteLine("\n\nLINQ Lambda notation");
customers = db.Customers.ToList();
Console.WriteLine($"Local count is {customers.Count}");
Console.WriteLine($"Database count is {db.Customers.Count()}");
// Select....Distinct
Console.WriteLine("\n\nList of Cities : Select ... Distinct ... OrderBy\n");
Console.WriteLine("Using SELECT to select one column\n");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
// Contaims i.e. same like SQL Like
Console.WriteLine("\n\nContaims i.e. same like SQL Like\n");
var cityListFiltered =
db.Customers
.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
}
static void Main(string[] args)
{
#region Explanation
/*
* 1) Read NORTHWIND using Entity Core 2.1.1
* 2) Basic LINQ
* 3) More advanced LINQ with LAMBDA
*
*/
// Nuget: Downloaded MicrosoftEntityFrameworkCore / .sqlserver / .design
#endregion
List <Customer> customers = new List <Customer>();
List <ModifiedCustomer> modifiedCustomers = new List <ModifiedCustomer>();
List <Product> products = new List <Product>();
List <Category> categories = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
// SIMPLE LINQ
// WHOLE DATASET IS RETURNED (MORE DATA)
//IENUMERABLE ARRAY
var selectedCustomers =
from customer in customers
select customer;
// SAME QUERY OVER THE DB DIRECTLY
// ONLY RETURN DATA WE NEED
// LAZY LOADING: QUERY ISNT EXECUTED
// DATA HAS NOT ACTUALLY ARRIVED YET
var selectedCustomers2 =
(from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.CompanyName
select customer).ToList();
// FORCED DATA BY PUSHING THE LIST IE .ToList() or by taking aggregate eg sum, count
printCustomers(selectedCustomers2);
var selectedCustomers3 =
(from customer in db.Customers
select new
{
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Take(10).Skip(10).ToList();
foreach (var c in selectedCustomers3)
{
Console.WriteLine($"{c.Name,-30}{c.Location,-30}");
}
// OR
var selectedCustomers4 =
(from c in db.Customers
select new ModifiedCustomer(c.ContactName, c.City + " " + c.Country)).ToList();
// Grouping
// group and list all customers by CITY
// CITY ... Count(CUSTOMER)
var selectedCustomers5 =
from cust in db.Customers
group cust by cust.City into Cities
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectedCustomers5)
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
// JOIN
// products with CategoryID => category
Console.WriteLine("\n\nList of Products Inner Join Category showing name0");
var listofproducts =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
listofproducts.ForEach(p => Console.WriteLine($"{p.ID,-15}{p.Name,-15}{p.Category,15}"));
Console.WriteLine("\n\nNow print off the same list but using smarter 'Dot' notationto join tables");
// Read in products and categories
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p => Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-15}{p.Category.CategoryName,-15}"));
Console.WriteLine("\n\nList Categories with count of products and sub list of product names\n");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID} {c.CategoryName} has {c.Products.Count} products");
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
}
);
Console.WriteLine("\n\nLINQ Lambda notation");
customers = db.Customers.ToList();
Console.WriteLine($"Count is {customers.Count}");
Console.WriteLine($"Count is {db.Customers.Count()}");
// Select Distinct
Console.WriteLine("\n\nList of Distinct Cities\n");
Console.WriteLine("Using select to select one column \n");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine("\n\nContains (same as SQL LIKE)\n");
var cityListFiltered =
db.Customers.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
}
static void Main(string[] args)
{
#region Explanation
/*
* 1) Read NORTHWIND using ENTITY CORE 2.1.1
* 2) Basic LINQ
* 3) More advanced LINQ with LAMBDA
*
*
* // Nuget : microsoft.entityframeworkcore / .sqlserver / .design v 2.1.1
* // install-package microsoft.entityframeworkcore -v 2.1.1
*/
#endregion
using (var db = new Northwind())
{
customers = db.Customers.ToList();
// SIMPLE LINQ FROM LOCAL COLLCTION
// WHOLE DATASET IS RETURNED (MORE DATA)
// IENUMERABLE ARRAY
var selectedCustomers =
from customer in customers
select customer;
// SAME QUERY OVER DATABASE DIRECTLY
// ONLY RETURN ACTUAL DATA WE NEED
// LAZY LOADING : QUERY IS NOT ACTUALLY EXECUTED!!!
// DATA HAS NOT ACTUALLY ARRIVED YET!
var selectedCustomers2 =
(from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
where customer.ContactName != "Phil"
where customer.ContactName != "New Name"
orderby customer.ContactName
select customer).ToList();
// FORCE DATA BY PUSHING TO A LIST IE .ToList() or by taking aggregate eg Sum,Count
printCustomers(selectedCustomers2);
Console.WriteLine($"There are {selectedCustomers2.Count} records returned");
// CREATE CUSTOM OBJECT OUTPUT
var selectedCustomers3 =
(from customer in db.Customers
select new {
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Take(10).Skip(10).ToList();
foreach (var c in selectedCustomers3)
{
Console.WriteLine($"{c.Name,-20}{c.Location}");
}
//or
var selectedCustomers4 =
(from c in db.Customers
select new
ModifiedCustomer(c.ContactName, c.City + " " + c.Country))
.ToList();
// Grouping
// Group and list all customers by CITY
// CITY ... COUNT(CUSTOMER)
var selectedCustomers5 =
from cust in db.Customers
group cust by cust.City into Cities
where Cities.Count() > 1
orderby Cities.Count() descending
select new {
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectedCustomers5.ToList())
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
Console.WriteLine("\n\nList of Products Inner Join Category Showing Name\n" +
"======================================================\n");
var listofproducts =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
listofproducts.ForEach(p =>
Console.WriteLine($"{p.ID,-15}{p.Name,-30}{p.Category}"));
Console.WriteLine("\n\nNow Print Off The Same List But Using Much Smarter\n" +
"'dot' Notation To Join Tables\n");
// read in products and categories
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p =>
Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"));
Console.WriteLine("\n\nList Categories With Count of Products And Sub-List Of Product Names\n");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-5}{c.CategoryName,-15} has {c.Products.Count} products");
// loop within loop
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
}
);
Console.WriteLine($"\n\nLINQ Lambda Notation\n");
customers = db.Customers.ToList();
Console.WriteLine($"Count is {customers.Count}");
Console.WriteLine($"Count is {db.Customers.Count()}");
// Select ... Distinct
Console.WriteLine("\n\nList Of Cities : Select ... Distinct...OrderBy\n");
Console.WriteLine("Using SELECT to SELECT ONE COLUMN\n");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine("\n\nContains (same as SQL LIKE)\n");
var cityListFiltered =
db.Customers
.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
}
}
static void Main(string[] args)
{
using (var db = new NorthwindDbContext())
{
customers = db.Customers.ToList();
}
PrintCustomers(customers);
//Process.Start("EXCEL", "customers.csv");
using (var db = new NorthwindDbContext())
{
//LINQ basic (query) syntax
//Would have to cast these objects to list or array to use them
var customers01 = from customer in db.Customers select customer;
var customer02 =
from c in db.Customers
where c.City == "London" || c.City == "Berlin"
orderby c.ContactName descending
select c;
PrintCustomers(customer02.ToList());
var customList =
from c in db.Customers
select new
{
Name = c.ContactName,
City = c.City,
Country = c.Country
};
customList.ToList().ForEach(item => Console.WriteLine($"{item.Name,-25} {item.City,-15} {item.Country}"));
var newList =
from c in db.Customers
select new CustomObject()
{
Name = c.ContactName,
City = c.City,
Country = c.Country
};
newList.ToList().ForEach(item => Console.WriteLine($"{item.Name,-25} {item.City,-15} {item.Country}"));
//Grouping items
//SQL has aggregation ie sum, count, average, max and min
//Query by city ==> count per city
var customersCountByCity =
from c in db.Customers
group c by c.City into Cities
orderby Cities.Count() descending
where Cities.Count() > 1
select new
{
City = Cities.Key,
Count = Cities.Count()
};
Console.WriteLine($"\n\nLIST OF CUSTOMER COUNT BY CITY\n\n");
foreach (var item in customersCountByCity.ToList())
{
Console.WriteLine($"{item.City, -20}{item.Count}");
}
//Joining Tables
//Products will have a category and link via catergory id
var products01 =
(from p in db.Products
select p).ToList();
//in order to add category name to output, first have to pull in categories into loacl database store (cache)
//In linq by default we have 'lazy loading' which means query not actually run and bought into
//memory until we actually need the data
//var categories = db.categories; ==> lazy loading
// .ToList(); ==> forcing loading
var categories =
(from c in db.Categories
select c).ToList();
PrintProducts(products01.ToList());
}
}
//static List<Customer> customers = new List<Customer>();
static void Main(string[] args)
{
using (var db = new NorthwindEntities())
{
var output1 =
(from customer in db.Customers
select customer).ToList();
PrintCustomers(output1);
var output2 =
from c in db.Customers
where c.City == "London"
select c;
PrintCustomers(output2.ToList());
var output3 =
from c in db.Customers
orderby c.City descending
select c;
PrintCustomers(output3.ToList());
var output4 =
from c in db.Customers
select new
{
Name = c.ContactName,
Address = c.City + " " + c.Country
};
foreach (var c in output4)
{
Console.WriteLine($"{c.Name,-20} {c.Address}");
}
Console.WriteLine("\n\nGroup By - City\n\n");
var output5 =
from c in db.Customers
group c by c.City into Cities
orderby Cities.Key
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in output5.ToList())
{
Console.WriteLine($"{c.City, -20} {c.Count}");
}
using (var db2 = new NorthwindEntities())
{
var output6 =
from customer in db2.Customers
join order in db2.Orders
on customer.CustomerID equals order.CustomerID
select new
{
Name = customer.ContactName,
order.OrderID,
order.OrderDate
};
output6.ToList().ForEach(x => Console.WriteLine($"{x.Name, -25} {x.OrderID,-15} {x.OrderDate}"));
}
Console.ReadLine();
}
}
private void Run(object p)
{
Hashtable para = p as Hashtable;
var wait = para["wait"] as ProgressWait;
string folderPath = para["folderPath"].ToString();
int totalCount = Cities.Count(item => item.IsSelected);
int count = 0;
try
{
wait.SetWaitCaption("计算高铁城市空间可达性");
foreach (string city in _dijkstra.GetCityEnumerator())
{
wait.SetProgress((double)count++ / _dijkstra.Count);
_highTrainStation.Add(city, CalculationCity(city));
}
Dictionary <string, RasterOp> backup = new Dictionary <string, RasterOp>(_highTrainStation.Count);
//backup
foreach (var keyValue in _highTrainStation)
{
backup.Add(keyValue.Key, new RasterOp(keyValue.Value));
}
//***********************************
wait.SetProgress(0);
wait.SetWaitCaption("计算高铁城市叠加效应");
count = 0;
foreach (string city in _dijkstra.GetCityEnumerator())
{
wait.SetProgress((double)count++ / _dijkstra.Count);
float[] times = _dijkstra.Dijkstra(city);
foreach (var otherCity in _dijkstra.GetCityEnumerator())
{
if (city != otherCity)
{
int cityIndex = _dijkstra.GetCityIndex(otherCity);
backup[otherCity].Overlay(item => item + times[cityIndex]);
_highTrainStation[city].Overlay(backup[otherCity], Math.Min);
backup[otherCity].Overlay(item => item - times[cityIndex]);
}
}
}
//****************************************
backup.Clear();
//foreach (var keyValue in _highTrainStation)
//{
// backup.Add(keyValue.Key, new RasterOp(keyValue.Value));
//}
wait.SetWaitCaption("计算所有城市空间可达性");
wait.SetProgress(0);
count = 0;
foreach (var calculatorCity in Cities)
{
if (calculatorCity.IsSelected)
{
wait.SetProgress((double)count++ / totalCount);
RasterOp res;
if (_highTrainStation.ContainsKey(calculatorCity.Name))
{
res = _highTrainStation[calculatorCity.Name];
}
else
{
res = CalculationCity(calculatorCity.Name);
RasterOp back = res.Clone();
foreach (var station in _highTrainStation)
{
//RasterOp op = back.Clone();
City city = _allCities.First(item => item.Name == station.Key);
Postion pos = _rasterReader.Coordinate(city.XCoord, city.YCoord);
float timecost = (float)back.Read(pos.XIndex, pos.YIndex);
_highTrainStation[station.Key].Overlay(item => item + timecost);
res.Overlay(_highTrainStation[station.Key], Math.Min);
_highTrainStation[station.Key].Overlay(item => item - timecost);
}
}
RasterWriter writer
= new RasterWriter(folderPath, calculatorCity.Name + "_高铁通车后", _rasterReader.RasterInfo);
res.WriteRaster(writer, "TIFF");
}
}
para["ret"] = true;
}
catch (Exception e)
{
_log.Error(e.Message + e.StackTrace);
para["ret"] = false;
}
finally
{
wait.CloseWait();
}
}
static void Main(string[] args)
{
#region explanation
/* 1) read northwind core using entity core 2.1.1
* 2) basic LINQ
* 3) More advance LINQ with Lambda
*/
#endregion
List <Customer> customers = new List <Customer>();
List <Customer> selectedCustomersDB;
List <ModifiedCustomer> modifiedcustomers = new List <ModifiedCustomer>();
List <Product> products = new List <Product>();
List <Category> categories = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
//simple LINQ from local query
// whole dataset is returned (more data)
//Ienumerable array
var selectedCustomers = from customer in customers
select customer;
//same query over database directly
//only return actual data we need
//lazy loading - query is not actually executed!!
//data has not actually arrived yet!
selectedCustomersDB =
(from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.ContactName
select customer).ToList();
//printCustomers(selectedCustomersDB);
var SelectedCustomers3 =
(from customer in db.Customers
select new
{
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Take(10).ToList();
foreach (var c in SelectedCustomers3)
{
Console.WriteLine($"{c.Name,-20}{c.Location}");
}
var selectedCustomer4 =
(from c in db.Customers
select new ModifiedCustomer(c.ContactName, c.City + " " + c.Country)).ToList();
var selectedCustomers5 =
from cust in db.Customers
group cust by cust.City into Cities
where Cities.Count() > 1
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectedCustomers5.ToList())
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
// Join
// products with categoryid where we can then get the product name
Console.WriteLine("\n\nList of Products Inner Join Category Showing Name\n" +
"===================================================================\n");
var listOfproducts =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
listOfproducts.ForEach(p => Console.WriteLine($"{p.ID,-15}{p.Name,-30}{p.Category}"));
Console.WriteLine("\n\nNow print of the same list but much smarter\n" +
"'dot' Notation to join tables\n");
// read from db
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p =>
Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"));
Console.WriteLine("\n\nList Categories with Count of Products and Sub-List of Product Names\n");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-5}{c.CategoryName,-15} has {c.Products.Count}products");
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
}
);
Console.WriteLine($"\n\nLINQ Lambda Notation\n");
customers = db.Customers.ToList();
Console.WriteLine($"Count is {db.Customers.Count()}");
//Distinct
Console.WriteLine("\n\nList of Cities Distinct\n");
Console.WriteLine("Using Select to select one column\n");
var cityList = db.Customers.Select(c => c.City).Distinct()
.OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine("\n\nContains(same as SQL Like)\n");
var cityListFiltered =
db.Customers.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
}
// force data by pushing to a list ie .ToList() or by taking aggregate e.g. Sum, Count
// Grouping
// Group and list all customers by CITY
//CITY... COUNT(CUSTOMER)
}
/*
* 1. Read Northwind using Entity Core 2.1.1.
* 2. Basic LINQ
* 3. Advanced LINQ (Lambda)
*/
#endregion Explanation
private static void Main(string[] args)
{
List <Customer> customers = new List <Customer>();
List <ModifiedCustomer> modifiedcustomers = new List <ModifiedCustomer>();
List <Product> products = new List <Product>();
List <Category> categories = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
// Simple linq from local collection
// Whole dataset is returned, more data
// IEnumerable Array
var selectedCustomers =
from customer in customers
select customer;
// Same query over database directly
// Only return data we need
// Lazy loading - Query is not actually executed
// Data has not actualy arrived yet
var selectedCustomers2 =
(from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.ContactName
select customer).ToList();
printCustomer(selectedCustomers2);
// force data by pushing to a list, i.e ToList() or by taking aggregate e.g. Sum, Count
var selectedCustomers3 =
(from customer in db.Customers
select new
{
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).ToList();
foreach (var c in selectedCustomers3)
{
Console.WriteLine("SelectedCustomers3");
Console.WriteLine($"{c.Name,-20}{c.Location}");
Console.WriteLine("");
}
var selectedCustomers4 =
(from c in db.Customers
select new
ModifiedCustomer(c.ContactName, c.City + " " + c.Country)).ToList();
var selectedCustomers5 =
from cust in db.Customers
group cust by cust.City into Cities
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectedCustomers5.ToList())
{
Console.WriteLine("SelectedCustomers5");
Console.WriteLine($"{c.City,-15}{c.Count}");
Console.WriteLine("");
}
var listofproducts =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductID,
Category = c.CategoryName
}).ToList();
listofproducts.ForEach(p =>
Console.WriteLine($"{p.ID,15}{p.Name,-30}{p.Category}"));
Console.WriteLine("\n\nNow print same list but using smarter join tables with . Notation");
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p =>
Console.WriteLine($"{p.ProductID,-15}{p.ProductName}{p.Category.CategoryName}"));
//secet distinct
Console.WriteLine(("\n\n List of cities distinct \n"));
var cityList = db.Customers.Select(c => c.City).ToList();
}
}
//static string CustomersPath = "customers.csv";
static void Main(string[] args)
{
using (var db = new NorthwindDbContext()) {
customers = db.Customers.ToList();
}
//PrintCustomers(customers);
// Process.Start("EXCEL", CustomersPath);
// LINQ BASIC SYNTAX
using (var db = new NorthwindDbContext())
{
// LINQ BASIC (QUERY) SYNTAX
// NOTE : WOULD HAVE TO CAST THESE OBJECTS TO LIST OR ARRAY TO USE THEM
var customers01 =
from customer in db.Customers
select customer;
foreach (var customer in customers01.ToList())
{
// ... iterate
}
var customers02 =
from c in db.Customers
where c.City == "LONDON" || c.City == "BERLIN"
orderby c.ContactName descending
select c;
// print
//PrintCustomers(customers02.ToList());
// creating custom output object - LITERAL
var customList =
from c in db.Customers
select new
{
Name = c.ContactName,
City = c.City,
Country = c.Country
};
// custom print
// Console.WriteLine($"Customer Detail\n");
// Console.WriteLine($"{"Name",-25}{"City",-20}{"Country"}");
// customList.ToList().ForEach(item => Console.WriteLine($"{item.Name,-25}" +
// $"{item.City,-20}{item.Country}"));
var customList2 =
from c in db.Customers
select new customObject()
{
Name = c.ContactName, City = c.City, Country = c.Country
};
//Console.WriteLine($"\nCustomer Detail\n");
//Console.WriteLine($"{"Name",-25}{"City",-20}{"Country"}");
//customList2.ToList().ForEach(item => Console.WriteLine($"{item.Name,-25}" +
// $"{item.City,-20}{item.Country}"));
// SQL has AGGREGATION ie SUM, COUNT, AVERAGE, MAX AND MIN
// QUERY BY CITY ==> COUNT PER CITY
var customerCountByCity =
from cust in db.Customers
group cust by cust.City into Cities
where Cities.Count() > 1
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
};
//Console.WriteLine($"\n\nLIST OF CUSTOMER COUNT BY CITY\n");
foreach (var item in customerCountByCity.ToList())
{
//Console.WriteLine($"{item.City,-20}{item.Count}");
}
var customerByCity = db.Customers.ToList().OrderBy(c => c.Country).ThenBy(c => c.City);
var customersGroupedByCity = db.Customers.ToList()
.GroupBy(c => c.City)
.Where(item => item.Count() > 0)
.OrderByDescending(item => item.Count())
.ThenByDescending(item => item.Key);
//Console.WriteLine($"\n\nLIST OF CUSTOMER GROUPED BY CITY\n");
foreach (var item in customersGroupedByCity.ToList())
{
//Console.WriteLine($"{item.Key,-20}{item.Count()}");
}
// JOINING TABLES
// PRODUCTS WILL HAVE A CATEGORY AND LINK VIA CATEGORY ID
products =
(from p in db.Products
select p).ToList();
// In order to add CATEGORY NAME to output, first have to pull
// in Categories into local database store (cache)
// NOTE : In LINQ by default we have 'LAZY LOADING' which means QUERY NOT ACTUALLY RUN
// AND BROUGHT INTO MEMORY UNTIL WE ACTUALLY NEED THE DATA.
// var categories = db.Categories; ==> LAZY LOADING (NOT RUN!)
// .ToList(); ==> FORCED LOADING (QU
categories =
(from c in db.Categories
select c).ToList();
// print
PrintProducts(products.ToList());
}
}
static void Main(string[] args)
{
List <Customer> customers = new List <Customer>();
List <ModifiedCustomer> modifiedCustomers = new List <ModifiedCustomer>();
List <Product> products = new List <Product>();
List <Category> categories = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
#region List_Customers_Based_on_specificCities_Skip_Some
// Simple LINQ from Local Collection
//Whole dataset is returned(More Data)
var selectedCustomers = from customer in customers select customer;
//printCustomers(selectedCustomers.ToList());
//Same query over database directly
//Only return actual data we need
//Lazy loading: Query is not actuaslly executed
Console.WriteLine("\n\nCreate custom object output\n" +
"===================================================n");
var selectedCustomers2 = (from customer in db.Customers
where
customer.City == "London" || customer.City == "Berlin"
orderby customer.ContactName
select customer).ToList();
//Force Data by pushing to a list . ToList() or by taking aggregate eg. Sum, Count
printCustomers(selectedCustomers2);
Console.WriteLine($"There are {selectedCustomers2.Count} records returned");
#endregion
#region List_Customers_Concatenate_CityAndCountry_into_Location
Console.WriteLine("\n\nCreate custom object output\n" +
"=====================================================n");
var selectedCustomers3 = (from customer in db.Customers
select new
{
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Skip(10).ToList(); // Take(10) or Skip(10)
foreach (var c in selectedCustomers3)
{
Console.WriteLine($"{c.Name,-20}{c.Location}");
}
//or
var selectedCustomer4 =
(from c in db.Customers
select new
ModifiedCustomer(c.ContactName, c.City + " " + c.Country))
.ToList();
#endregion
#region Group_and_List_Customers_ByCity
Console.WriteLine("\n\nGROUP and list all customers by CITY\n" +
"=========================================================n");
var selectCustomer5 =
from cust in db.Customers
group cust by cust.City into Cities
orderby Cities.Count() descending
where Cities.Count() > 1
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectCustomer5.ToList())
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
#endregion
#region List_Products_Inner_Join_Category_Showing_Name
Console.WriteLine("\n\nList of Products Inner Join Category Showing Name\n" +
"==================================================================n");
var listofproducts =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
listofproducts.ForEach(p => Console.WriteLine($"{p.Category,-15}{p.Name,-30 }{p.Category}"));
Console.WriteLine("\n\nNow print off the same List But using much smarter 'dot' Notation to Join Tables\n" +
"===========================================================================");
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p => Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"
));
#endregion
#region List_Categories_with_Count_of_Products_and_subList_of_ProductName
Console.WriteLine("\n\nList Categories with Count of Products and sub List of Product Name\n" +
"==========================================================================");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-10}{c.CategoryName,-15} has {c.Products.Count} products");
//loop within loop
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
});
#endregion
#region other_LINQ_Notations:_Distinct_OrderBy_Contains(SQL_Like)
Console.WriteLine($"\n\nLINQ Lambda Notation\n");
customers = db.Customers.ToList();
Console.WriteLine($"Count is {customers.Count}");
Console.WriteLine($"Count is {db.Customers.Count()}");
//Distinct
Console.WriteLine($"\n\nList of Cities: Select.. Distinct..OrderBy\n");
Console.WriteLine("Using SELECT to select one Column\n");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine("\n\n\nContains(same as SQL like)\n" +
"============================================================");
var cityListFiltered =
db.Customers.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
#endregion
}
}
static void Main(string[] args)
{
List <Customer> customers = new List <Customer>();
List <ModifiedCustomer> modCustomers = new List <ModifiedCustomer>();
List <Product> products = new List <Product>();
List <Category> categories = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
#region Simple LINQ
// IEnumerable: returend when we query a local array/collection
var selectedCustomers = from customer in customers select customer;
// IQueryable: returned when we query a remote array/collection (i.e. database table)
// Data is not returned yet by this query below, called Lazy Loading
var selectedCustomers2 = from customer in db.Customers select customer;
// Force data to be returned as a List collection
selectedCustomers2.ToList();
// Everything combined in one statement
var selectedCustomers3 = (from customer in db.Customers select customer).ToList();
// A simple sql-style query
var selectedCustomers4 = (from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.ContactName select customer).ToList();
// Print customers selected above
PrintCustomers(selectedCustomers4);
// Create customer object output
Console.WriteLine("------------------------------------------------------------------------------------------------------------");
var selectedCustomer5 = (from customer
in db.Customers
select new
{
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Take(10).ToList();
selectedCustomer5.ForEach(c => Console.WriteLine($"{c.Name,-25}{c.Location,-20}"));
// Using a custom class for customer
var modifiedCustomer = (from c
in db.Customers
select new ModifiedCustomer(c.ContactName, c.City + " " + c.Country)).ToList();
modCustomers = modifiedCustomer;
Console.WriteLine("------------------------------------------------------------------------------------------------------------");
// Grouping
// Group and list all customer by city
// city...count(customer)
var groupCustomers = (from c in db.Customers
group c by c.City into Cities
where Cities.Count() > 1
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
}).ToList();
groupCustomers.ForEach(c => Console.WriteLine($"{c.City,-15}{c.Count}"));
Console.WriteLine("------------------------------------------------------------------------------------------------------------");
// Join
// products with categoryid => category name
Console.WriteLine("\n\nList of Products Inner Join Category Showing Name\n");
var join = (from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
Id = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
join.ForEach(p => Console.WriteLine($"{p.Id, -10}{p.Name, -40}{p.Category}"));
Console.WriteLine($"\n\nPrint off the same list with much smarter \"dot\" join notation to join tables.\n");
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p => Console.WriteLine($"{p.ProductID, -15}{p.ProductName, -40}{p.Category.CategoryName}"));
Console.WriteLine($"\n\nList Categories with Count of Products And Sub-List of Product Names\n");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-5}" +
$"{c.CategoryName,-15} has {c.Products.Count} products");
// c.Products is a join: Category with Product
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID, -5}{p.ProductName, -40}");
}
});
Console.WriteLine($"\n\nLINQ LAmbda Notation:\n");
customers = db.Customers.ToList();
Console.WriteLine($"Count is {customers.Count}");
Console.WriteLine($"Count is {db.Customers.Count()}");
// Distinct
Console.WriteLine($"\n\nList of distinct cities.\n");
Console.WriteLine($"Using IQueryable.Select to \"SELECT\" one column.");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine($"\n\nContains (same as SQL LIKE)\n");
var cityListFiltered = db.Customers.Select(c => c.City)
.Where(c => c.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(i => Console.WriteLine(i));
#endregion
}
static void Main(string[] args)
{
/*
*
* Read northwind using entity core 2.1.1
*
* Basic LINQ
* `
* More advanced LINQ with lambda
*
*/
//Nuget - microsoft.entityframeworkcore/.sqlserver/.design -v 2.1.1
List <Customer> customers = new List <Customer>();
List <ModifiedCustomer> modifiedCustomers = new List <ModifiedCustomer>();
List <Product> products = new List <Product>();
List <Category> categories = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
//simple linq from local collection
// whole dataset is returned (more data)
// IENUMERABLE ARRAY
var selectedCustomer = from customer in customers
select customer;
//Same query over database directly
//ONLY RETURN ACTUAL DATABASE WE NEED
// LAZY LOADING : QUERY ID NOT ACTUALLY EXESUTED
//DATA HAS NOT ACTUALLY ARRIVED YET!!
var selectedCustomer2 = (from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.ContactName
select customer).ToList();
//CREATE CUSTOM OBJECT OUTPUT
var selectCustomer3 = (from customer in db.Customers
select new
{
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
});
foreach (var c in selectCustomer3)
{
Console.WriteLine($"{c.Name,-20}{c.Location}");
}
printCustomers(selectedCustomer2);
// Grouping
// group and list all customers by CITY
// CITY ... Count(CUSTOMER)
var selectedCustomers5 =
from cust in db.Customers
group cust by cust.City into Cities
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectedCustomers5)
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
// JOIN
// products with CategoryID => category
var listofproducts =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
listofproducts.ForEach(p => Console.WriteLine($"{p.ID,-15}{p.Name,-15}{p.Category,15}"));
Console.WriteLine("\n\n Now Print off the same list but using mush smarter\n" + "'dot' Notation To Join Tables\n");
//read in products and categories
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p => Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"));
Console.WriteLine("\n\nList categoties with count of products and sub--list of product names\n");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-15}{c.CategoryName,-15} has {c.Products.Count} products");
// loop within loop
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
}
);
Console.WriteLine($"\n\nLinq lamda notation\n");
customers = db.Customers.ToList();
Console.WriteLine($"Count is {customers.Count}");
Console.WriteLine($"Count is {db.Customers.Count()}");
//select ... distinct
Console.WriteLine($"\n\nList of cities distinctn\n");
Console.WriteLine("Using SLECT TO SELECT ONE COLUMN");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine("\n\nContain(same as SQL LIKE)\n");
var cityListFilterd = db.Customers.Select(c => c.City).Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFilterd.ForEach(city => Console.WriteLine(city));
}
//FORCE DATA BY PUSHING TO A LIST IE. ToList() OR BY TAKING AGGREGATE eg SUM, Count
}
static void Main(string[] args)
{
#region Explanation
/*
* 1) Reading NORTHWIND USING ENTITY CORE
* 2) BASIC LINQ
* 3) MORE ADVANCED Linq with LAMDA
*/
// NUtget: microsoft.entityframeworkcore/ .sqlserver/ .design
// install-package microsoft entityframeworkcore -v 2.1.1 very stable no issues.
#endregion
using (var db = new Northwind())
{
customers = db.Customers.ToList();
// SIMPLE LINQ FROM LOCAL COLLECTION
// WHOEL DATASET IS RETURNED (MORE DATA)
// IENUMERABLE ARRAY
var selectedCustomers = from customer in customers
select customer;
// SAME QUERY OVER DATATBASE DIRECTLY
// ONLY RETURN ACTUAL DATAT WE NEED
// LAZEY LOADING : QUERY IS NOT ACTUALLY EXECUTED!!!
// DATA HAS NOT ACTUALLY ARRIVED YET
var selectedCustomers2 = (from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.ContactName
select customer).ToList();
// FORCE THE DATA BY PUSHING TO A LIST IE ToList() or by talking aggragate eg sum, count
printCustomers(selectedCustomers2);
Console.WriteLine("-------------------------------------------------------------------------------");
Console.WriteLine($"there are: {selectedCustomers2.Count} records returned");
Console.WriteLine("-------------------------------------------------------------------------------");
// create custom object output
var selectedCustomer3 =
(from customer in db.Customers
select new {
Name = customer.ContactName,
Location = customer.City + "" + customer.Country
}).Take(30).Skip(10).ToList();
foreach (var c in selectedCustomer3)
{
Console.WriteLine($"{c.Name, -20}{c.Location}");
}
Console.WriteLine("-------------------------------------------------------------------------------");
Console.WriteLine($"there are: {selectedCustomer3.Count} records returned");
Console.WriteLine("-------------------------------------------------------------------------------");
var selectedCustomers4 = (from c in db.Customers
select new ModifiedCustomer
(c.ContactName, c.City + " " + c.Country)).ToList();
// grouping
//group and list all customers by city
// city ... count(customer)
var selectedCustomers5 = from cust in db.Customers
group cust by cust.City into Cities
where Cities.Count() > 1
orderby Cities.Count() descending
select new
{
City = Cities.Key,
Count = Cities.Count()
};
foreach (var c in selectedCustomers5.ToList())
{
Console.WriteLine($"{c.City, -15}{c.Count}");
}
// JOIN
// products with categoryID => category
Console.WriteLine("\n\nList of Products Joined with Categories Showing Name\n" +
"====================================================================");
var ListOfproducts = (
from p in db.Products
join c in db.Categories
on p.CategoryID equals c.CategoryID
select new
{
ID = p.ProductID,
Name = p.ProductName,
Category = c.CategoryName
}).ToList();
ListOfproducts.ForEach(p => Console.WriteLine($"{p.ID,-10}{p.Name,-30}{p.Category}"));
Console.WriteLine("\n\nNow print off the same list but using much smarter\n" + "'dot' notation to join tables\n");
// read in products and categories
products = db.Products.ToList();
categories = db.Categories.ToList();
products.ForEach(p => Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"));
Console.WriteLine("\n\n List Categories with Count of Products And Sub-List Of Product Names\n");
categories.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-5}{c.CategoryName,-15} has {c.Products.Count} products");
// loop within a loop
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t{p.ProductID, -5}{p.ProductName}");
}
}
);
Console.WriteLine($"\n\nLINQ Lambda Notation \n");
customers = db.Customers.ToList();
Console.WriteLine($"Count is: {customers.Count}");
Console.WriteLine($"Count is: {db.Customers.Count()}");
// distinct
Console.WriteLine("\n\nList of Cities Disitinct\n");
Console.WriteLine("Using SELECT to SELECT one column\n");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList(); // toList(); has to be at the end
cityList.ForEach(city => Console.WriteLine(city));
Console.WriteLine("\n\n Contains (Same as SQL LIKE)\n");
var cityListFiltered =
db.Customers.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
}
}