LINQ to SQL Where 與 Select 操作詳解：條件查詢、匿名型別、巢狀結構與 First 單筆取得

Select 操作多樣型態

單一欄位

var q = from c in db.Customers select c.ContactName;

匿名型別：多欄位、重新命名、運算

// 保留原名
select new { c.ContactName, c.Phone };

// 重新命名屬性
select new { Name = e.FirstName + " " + e.LastName, Phone = e.HomePhone };

// 包含運算
select new { p.ProductID, HalfPrice = p.UnitPrice / 2 };

條件運算子（轉換為 SQL CASE WHEN）

select new { p.ProductName, 
    Availability = p.UnitsInStock - p.UnitsOnOrder < 0 ? "Out Of Stock" : "In Stock" };

指定具名型別

select new Name { FirstName = e.FirstName, LastName = e.LastName };

階層式結構

select new {
    c.CustomerID,
    CompanyInfo = new { c.CompanyName, c.City, c.Country },
    ContactInfo = new { c.ContactName, c.ContactTitle }
};

巢狀集合

from o in db.Orders
select new {
    o.OrderID,
    DiscountedProducts = from od in o.OrderDetails
                         where od.Discount > 0.0
                         select od,
    FreeShippingDiscount = o.Freight
};

回傳物件的 DiscountedProducts 屬性為子集合。

Distinct 去重

var q = (from c in db.Customers select c.City).Distinct();
// 對應 SQL: SELECT DISTINCT City FROM Customers

Where 條件查詢

基本比較

where c.City == "London"
where e.HireDate >= new DateTime(1994, 1, 1)

And / Or 邏輯

where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued
where p.UnitPrice > 10m || p.Discontinued

多個 Where 鏈式等同 And：

db.Products.Where(p => p.UnitPrice > 10m).Where(p => p.Discontinued)

First 單筆取得（對應 SQL TOP 1）

方法語法帶條件

Customer cust = db.Customers.First(c => c.CustomerID == "BONAP");
Order ord = db.Orders.First(o => o.Freight > 10.00M);

查詢運算式搭配 First

var q = (from c in db.Customers
         where c.City == "London"
         select c).First();

• 無條件 First() → TOP 1
• 有條件 First(predicate) → TOP 1 + WHERE
• 回傳單一物件而非集合

延遲載入提醒

查詢運算式只是宣告，需呼叫 ToList()、ToArray() 或遍歷時才真正執行 SQL。回傳的是物件集合而非資料集。