Yöntem Tabanlı Sorgu Söz Dizimi Örnekleri: Toplama İşleçleri (LINQ to DataSet)
Bu konudaki örneklerde, yöntem sorgu söz dizimini Aggregatekullanarak verileri DataSet sorgulamak ve Sum toplamak için , CountAverageLongCount, , , Max, Minve işleçlerinin nasıl kullanılacağı gösterilmektedir.
FillDataSet
Bu örneklerde kullanılan yöntem, DataSet'e Veri Yükleme bölümünde belirtilmiştir.
Bu konudaki örneklerde AdventureWorks örnek veritabanındaki Contact, Address, Product, SalesOrderHeader ve SalesOrderDetail tabloları kullanılmıştır.
Bu konudaki örneklerde aşağıdaki using
/Imports
deyimler kullanılır:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
Option Explicit On
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization
Daha fazla bilgi için bkz . Nasıl yapılır: Visual Studio'da LINQ to DataSet Projesi Oluşturma.
Toplama
Örnek
Bu örnek, tablodan Aggregate ilk 5 kişiyi Contact
almak ve soyadlarının virgülle ayrılmış bir listesini oluşturmak için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
IEnumerable<DataRow> contacts = ds.Tables["Contact"].AsEnumerable();
string nameList =
contacts.Take(5).Select(contact => contact.Field<string>("LastName")).Aggregate((workingList, next) => workingList + "," + next);
Console.WriteLine(nameList);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As IEnumerable(Of DataRow) = _
ds.Tables("Contact").AsEnumerable()
Dim nameList As String = _
contacts.Take(5).Select(Function(contact) contact.Field(Of String)("LastName")). _
Aggregate(Function(workingList, next1) workingList + "," + next1)
Console.WriteLine(nameList)
Ortalama
Örnek
Bu örnek, Average ürünlerin ortalama liste fiyatını bulmak için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var products = ds.Tables["Product"].AsEnumerable();
Decimal averageListPrice =
products.Average(product => product.Field<Decimal>("ListPrice"));
Console.WriteLine("The average list price of all the products is ${0}",
averageListPrice);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As IEnumerable(Of DataRow) = _
ds.Tables("Product").AsEnumerable()
Dim averageListPrice As Decimal = _
products.Average(Function(product) product. _
Field(Of Decimal)("ListPrice"))
Console.WriteLine("The average list price of all the products is $" & _
averageListPrice)
Örnek
Bu örnek, her stilin Average ürünlerinin ortalama liste fiyatını bulmak için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var products = ds.Tables["Product"].AsEnumerable();
var query = from product in products
group product by product.Field<string>("Style") into g
select new
{
Style = g.Key,
AverageListPrice =
g.Average(product => product.Field<Decimal>("ListPrice"))
};
foreach (var product in query)
{
Console.WriteLine("Product style: {0} Average list price: {1}",
product.Style, product.AverageListPrice);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As IEnumerable(Of DataRow) = _
ds.Tables("Product").AsEnumerable()
Dim query = _
From product In products _
Group product By style = product.Field(Of String)("Style") Into g = Group _
Select New With _
{ _
.Style = style, _
.AverageListPrice = g.Average(Function(product) _
product.Field(Of Decimal)("ListPrice")) _
}
For Each product In query
Console.WriteLine("Product style: {0} Average list price: {1}", _
product.Style, product.AverageListPrice)
Next
Örnek
Bu örnekte ortalama vadesi geçmiş Average toplam değeri bulmak için yöntemi kullanılır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
Decimal averageTotalDue = orders.AsEnumerable().
Average(order => order.Field<decimal>("TotalDue"));
Console.WriteLine("The average TotalDue is {0}.",
averageTotalDue);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim averageTotalDue As Decimal = orders.AsEnumerable(). _
Average(Function(order) order.Field(Of Decimal)("TotalDue"))
Console.WriteLine("The average TotalDue is {0}.", _
averageTotalDue)
Örnek
Bu örnekte, her kişi kimliği için vadesi geçmiş ortalama toplamı almak için yöntemi kullanılır Average .
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
averageTotalDue =
g.Average(order => order.Field<decimal>("TotalDue"))
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
order.Category,
order.averageTotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.averageTotalDue = g.Average(Function(order) order. _
Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Average TotalDue = {1}", order.Category, _
order.averageTotalDue)
Next
Örnek
Bu örnekte, her ilgili kişinin ortalamasını TotalDue
içeren siparişleri almak için yöntemi kullanılırAverage.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
let averageTotalDue = g.Average(order => order.Field<decimal>("TotalDue"))
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.Field<decimal>("TotalDue") ==
averageTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
Console.WriteLine("");
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Let averageTotalDue = g.Average(Function(order) order.Field(Of Decimal)("TotalDue")) _
Select New With _
{ _
.Category = contactID, _
.CheapestProducts = g.Where(Function(order) order. _
Field(Of Decimal)("TotalDue") = averageTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: " & orderGroup.Category)
For Each order In orderGroup.CheapestProducts
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Console.WriteLine("")
Next
Sayı
Örnek
Bu örnek, tablodaki Count ürün Product
sayısını döndürmek için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var products = ds.Tables["Product"].AsEnumerable();
int numProducts = products.Count();
Console.WriteLine("There are {0} products.", numProducts);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim numProducts = products.AsEnumerable().Count()
Console.WriteLine("There are " & numProducts & " products.")
Örnek
Bu örnek, Count ilgili kişi kimliklerinin listesini ve her birinin kaç siparişi olduğunu döndürmek için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
var query = from contact in contacts.AsEnumerable()
select new
{
CustomerID = contact.Field<int>("ContactID"),
OrderCount =
contact.GetChildRows("SalesOrderContact").Count()
};
foreach (var contact in query)
{
Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
contact.CustomerID,
contact.OrderCount);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim query = _
From contact In contacts.AsEnumerable() _
Select New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.OrderCount = contact.GetChildRows("SalesOrderContact").Count() _
}
For Each contact In query
Console.Write("CustomerID = " & contact.ContactID)
Console.WriteLine(vbTab & "OrderCount = " & contact.OrderCount)
Next
Örnek
Bu örnek, ürünleri renge göre gruplandırarak her renk grubundaki ürün sayısını döndürmek için yöntemini kullanır Count .
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query =
from product in products.AsEnumerable()
group product by product.Field<string>("Color") into g
select new { Color = g.Key, ProductCount = g.Count() };
foreach (var product in query)
{
Console.WriteLine("Color = {0} \t ProductCount = {1}",
product.Color,
product.ProductCount);
}
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = _
From product In products.AsEnumerable() _
Group product By color = product.Field(Of String)("Color") Into g = Group _
Select New With {.Color = color, .ProductCount = g.Count()}
For Each product In query
Console.WriteLine("Color = {0} " & vbTab & "ProductCount = {1}", _
product.Color, _
product.ProductCount)
Next
Longcount
Örnek
Bu örnek, kişi sayısını uzun bir tamsayı olarak alır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
long numberOfContacts = contacts.AsEnumerable().LongCount();
Console.WriteLine("There are {0} Contacts", numberOfContacts);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim numberOfContacts = contacts.AsEnumerable().LongCount()
Console.WriteLine("There are {0} Contacts", numberOfContacts)
Maks
Örnek
Bu örnekte, Max en büyük ödenecek toplam değeri almak için yöntemi kullanılır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
Decimal maxTotalDue = orders.AsEnumerable().
Max(w => w.Field<decimal>("TotalDue"));
Console.WriteLine("The maximum TotalDue is {0}.",
maxTotalDue);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim maxTotalDue As Decimal = orders.AsEnumerable(). _
Max(Function(w) w.Field(Of Decimal)("TotalDue"))
Console.WriteLine("The maximum TotalDue is {0}.", maxTotalDue)
Örnek
Bu örnekte, her kişi kimliği için son tarihi en büyük toplamı almak için yöntemi kullanılır Max .
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
maxTotalDue =
g.Max(order => order.Field<decimal>("TotalDue"))
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",
order.Category, order.maxTotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.maxTotalDue = _
g.Max(Function(order) order.Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Maximum TotalDue = {1}", _
order.Category, order.maxTotalDue)
Next
Örnek
Bu örnekte, her ilgili kişi kimliği için en büyük TotalDue
olan siparişleri almak için yöntemi kullanılırMax.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
let maxTotalDue = g.Max(order => order.Field<decimal>("TotalDue"))
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.Field<decimal>("TotalDue") ==
maxTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Let maxTotalDue = g.Max(Function(order) order.Field(Of Decimal)("TotalDue")) _
Select New With _
{ _
.Category = contactID, _
.CheapestProducts = _
g.Where(Function(order) order. _
Field(Of Decimal)("TotalDue") = maxTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: " & orderGroup.Category)
For Each order In orderGroup.CheapestProducts
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1} ", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Next
Min
Örnek
Bu örnekte, en küçük vadesi Min geçmiş toplam değerini almak için yöntemi kullanılır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
Decimal smallestTotalDue = orders.AsEnumerable().
Min(totalDue => totalDue.Field<decimal>("TotalDue"));
Console.WriteLine("The smallest TotalDue is {0}.",
smallestTotalDue);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim smallestTotalDue As Decimal = orders.AsEnumerable(). _
Min(Function(totalDue) totalDue.Field(Of Decimal)("TotalDue"))
Console.WriteLine("The smallest TotalDue is {0}.", smallestTotalDue)
Örnek
Bu örnek, Min her kişi kimliği için son tarihi en küçük toplamı almak için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
smallestTotalDue =
g.Min(order => order.Field<decimal>("TotalDue"))
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",
order.Category, order.smallestTotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.smallestTotalDue = g.Min(Function(order) _
order.Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & _
"Minimum TotalDue = {1}", order.Category, order.smallestTotalDue)
Next
Örnek
Bu örnekte, her ilgili kişi için son tarihi en küçük olan siparişleri almak için yöntemi kullanılır Min .
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
let minTotalDue = g.Min(order => order.Field<decimal>("TotalDue"))
select new
{
Category = g.Key,
smallestTotalDue =
g.Where(order => order.Field<decimal>("TotalDue") ==
minTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.smallestTotalDue)
{
Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
Console.WriteLine("");
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Let minTotalDue = g.Min(Function(order) order.Field(Of Decimal)("TotalDue")) _
Select New With _
{ _
.Category = contactID, _
.smallestTotalDue = g.Where(Function(order) order. _
Field(Of Decimal)("TotalDue") = minTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: " & orderGroup.Category)
For Each order In orderGroup.smallestTotalDue
Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1} ", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Console.WriteLine("")
Next
Sum
Örnek
Bu örnek, tablodaki Sum toplam sipariş miktarı SalesOrderDetail
sayısını almak için yöntemini kullanır.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderDetail"];
double totalOrderQty = orders.AsEnumerable().
Sum(o => o.Field<Int16>("OrderQty"));
Console.WriteLine("There are a total of {0} OrderQty.",
totalOrderQty);
Örnek
Bu örnekte, her kişi kimliği için ödenecek toplam değeri almak için yöntemi kullanılır Sum .
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.Field<decimal>("TotalDue")),
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.TotalDue = g.Sum(Function(order) order. _
Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & "TotalDue sum = {1}", _
order.Category, order.TotalDue)
Next