Sorgu İfadesi Söz Dizimi Örnekleri: Filtreleme
Bu konudaki örnekler, sorgu ifadesi söz dizimini Where
kullanarak AdventureWorks Satış Modeli'ni sorgulamak için ve Where…Contains
yöntemlerinin nasıl kullanılacağını göstermektedir. Not, Nerede...Contains
derlenmiş sorgunun parçası olarak kullanılamaz.
Bu örneklerde kullanılan AdventureWorks Sales modeli, AdventureWorks örnek veritabanındaki Contact, Address, Product, SalesOrderHeader ve SalesOrderDetail tablolarından oluşturulur.
Bu konudaki örneklerde aşağıdaki using
/Imports
deyimler kullanılır:
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
Konumu
Örnek
Aşağıdaki örnek tüm çevrimiçi siparişleri döndürür.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var onlineOrders =
from order in context.SalesOrderHeaders
where order.OnlineOrderFlag == true
select new
{
SalesOrderID = order.SalesOrderID,
OrderDate = order.OrderDate,
SalesOrderNumber = order.SalesOrderNumber
};
foreach (var onlineOrder in onlineOrders)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim onlineOrders = _
From order In orders _
Where order.OnlineOrderFlag = True _
Select New With { _
.SalesOrderID = order.SalesOrderID, _
.OrderDate = order.OrderDate, _
.SalesOrderNumber = order.SalesOrderNumber _
}
For Each onlineOrder In onlineOrders
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}", _
onlineOrder.SalesOrderID, _
onlineOrder.OrderDate, _
onlineOrder.SalesOrderNumber)
Next
End Using
Örnek
Aşağıdaki örnek, sipariş miktarının 2'den büyük ve 6'dan küçük olduğu siparişleri döndürür.
int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from order in context.SalesOrderDetails
where order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax
select new
{
SalesOrderID = order.SalesOrderID,
OrderQty = order.OrderQty
};
foreach (var order in query)
{
Console.WriteLine("Order ID: {0} Order quantity: {1}",
order.SalesOrderID, order.OrderQty);
}
}
Dim orderQtyMin = 2
Dim orderQtyMax = 6
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails
Dim query = _
From order In orders _
Where order.OrderQty > orderQtyMin And order.OrderQty < orderQtyMax _
Select New With { _
.SalesOrderID = order.SalesOrderID, _
.OrderQty = order.OrderQty _
}
For Each order In query
Console.WriteLine("Order ID: {0} Order quantity: {1}", _
order.SalesOrderID, order.OrderQty)
Next
End Using
Örnek
Aşağıdaki örnek tüm kırmızı renkli ürünleri döndürür.
String color = "Red";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from product in context.Products
where product.Color == color
select new
{
Name = product.Name,
ProductNumber = product.ProductNumber,
ListPrice = product.ListPrice
};
foreach (var product in query)
{
Console.WriteLine("Name: {0}", product.Name);
Console.WriteLine("Product number: {0}", product.ProductNumber);
Console.WriteLine("List price: ${0}", product.ListPrice);
Console.WriteLine("");
}
}
Dim color = "Red"
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim query = _
From product In products _
Where product.Color = color _
Select New With { _
.Name = product.Name, _
.ProductNumber = product.ProductNumber, _
.ListPrice = product.ListPrice _
}
For Each product In query
Console.WriteLine("Name: {0}", product.Name)
Console.WriteLine("Product number: {0}", product.ProductNumber)
Console.WriteLine("List price: ${0}", product.ListPrice)
Console.WriteLine("")
Next
End Using
Örnek
Aşağıdaki örnek, 1 Aralık 2003'tan sonra yapılan siparişleri bulmak için yöntemini kullanır Where
ve ardından her siparişin order.SalesOrderDetail
ayrıntılarını almak için gezinti özelliğini kullanır.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> query =
from order in context.SalesOrderHeaders
where order.OrderDate >= new DateTime(2003, 12, 1)
select order;
Console.WriteLine("Orders that were made after December 1, 2003:");
foreach (SalesOrderHeader order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.SalesOrderID, order.OrderDate);
foreach (SalesOrderDetail orderDetail in order.SalesOrderDetails)
{
Console.WriteLine(" Product ID: {0} Unit Price {1}",
orderDetail.ProductID, orderDetail.UnitPrice);
}
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From order In orders _
Where order.OrderDate >= New DateTime(2003, 12, 1) _
Select order
Console.WriteLine("Orders that were made after December 1, 2003:")
For Each order In query
Console.WriteLine("OrderID {0} Order date: {1:d} ", _
order.SalesOrderID, order.OrderDate)
For Each orderDetail In order.SalesOrderDetails
Console.WriteLine(" Product ID: {0} Unit Price {1}", _
orderDetail.ProductID, orderDetail.UnitPrice)
Next
Next
End Using
Nerede... Içerir
Örnek
Aşağıdaki örnek, dizideki bir Where…Contains
değerle eşleşen tüm ürünleri bulmak için yan ProductModelID
tümcesinin parçası olarak bir dizi kullanır.
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
int?[] productModelIds = {19, 26, 118};
var products = from p in AWEntities.Products
where productModelIds.Contains(p.ProductModelID)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
}
}
Using AWEntities As New AdventureWorksEntities()
Dim productModelIds As System.Nullable(Of Integer)() = {19, 26, 118}
Dim products = From p In AWEntities.Products _
Where productModelIds.Contains(p.ProductModelID) _
Select p
For Each product In products
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID)
Next
End Using
Not
Yan tümcesindeki koşulun bir Where…Contains
Arrayparçası olarak, arabirimini uygulayan IEnumerable<T> herhangi bir türde bir , List<T>veya koleksiyonu kullanabilirsiniz. Ayrıca LINQ to Entities sorgusu içinde bir koleksiyon bildirebilir ve başlatabilirsiniz. Daha fazla bilgi için sonraki örne bakın.
Örnek
Aşağıdaki örnek, dizilerdeki değerlerle eşleşen veya Size
değerine sahip tüm ürünleri bulmak için yan Where…Contains
ProductModelID
tümcesindeki dizileri bildirir ve başlatır.
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var products = from p in AWEntities.Products
where (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID,
product.ProductModelID,
product.Size);
}
}
Using AWEntities As New AdventureWorksEntities()
Dim products = From p In AWEntities.Products _
Where (New System.Nullable(Of Integer)() {19, 26, 18}).Contains(p.ProductModelID) _
OrElse (New String() {"L", "XL"}).Contains(p.Size) _
Select p
For Each product In products
Console.WriteLine("{0}: {1}, {2}", product.ProductID, _
product.ProductModelID, _
product.Size)
Next
End Using