重视Linq技术_4

//LINQ to SQL

//1、Baisc query

Customers.Single (c => c.ID == 2)  .Dump ("Customer With ID Of 2");

//2、Associations

Customer cust1 = Customers.OrderBy (c => c.Name).First();
foreach (Purchase p In cust1.Purchases)
Console.WriteLine (p.Price);
//3、 Retrieve the customer who made the lowest value purchase:
Purchase cheapest = Purchases.OrderBy (p => p.Price).First();
cheapest.Customer.Dump();

//4、Single Round Trip

from c in Customers
select 
	from p in c.Purchases
	select new { c.Name, p.Price }

sql:

SELECT [t0].[Name], [t2].[Price], (
    SELECT COUNT(*)
    FROM [Purchase] AS [t3]
    WHERE [t3].[CustomerID] = [t0].[ID]
    ) AS [value]
FROM [Customer] AS [t0]
OUTER APPLY (
    SELECT [t1].[Price], [t1].[ID]
    FROM [Purchase] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[ID]
    ) AS [t2]
ORDER BY [t0].[ID], [t2].[ID]

//5、Projecting EntitySets

from c in Customers
select new { c.Name, c.Purchases }

sql:

SELECT [t0].[Name], [t1].[ID], [t1].[CustomerID], [t1].[Date], [t1].[Description], [t1].[Price], (
    SELECT COUNT(*)
    FROM [Purchase] AS [t2]
    WHERE [t2].[CustomerID] = [t0].[ID]
    ) AS [value]
FROM [Customer] AS [t0]
LEFT OUTER JOIN [Purchase] AS [t1] ON [t1].[CustomerID] = [t0].[ID]
ORDER BY [t0].[ID], [t1].[ID]

//6、Multiple Round Trips
foreach (Customer c in Customers)
{
	foreach (Purchase p in c.Purchases) // Another SQL round-trip
	{
		Console.WriteLine (c.Name + " spent " + p.Price);
	}
}

sql:

SELECT [t0].[ID], [t0].[Name]
FROM [Customer] AS [t0]
GO

-- Region Parameters
DECLARE @p0 Int SET @p0 = 1
-- EndRegion
SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE [t0].[CustomerID] = @p0
GO

-- Region Parameters
DECLARE @p0 Int SET @p0 = 2
-- EndRegion
SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE [t0].[CustomerID] = @p0
GO

-- Region Parameters
DECLARE @p0 Int SET @p0 = 3
-- EndRegion
SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE [t0].[CustomerID] = @p0
GO

//7、DataLoadOptions用于立即加载对象，避免往返访问数据库，提高查询性能。
var options = new DataLoadOptions();
options.AssociateWith<Customer> (c => c.Purchases.Where (p => p.Price > 1000));
LoadOptions = options;

//8、Updates-Insert Update Delete

Customer cust = new Customer { ID=1000, Name="Bloggs" };
Customers.InsertOnSubmit (cust);
SubmitChanges();
Customers.Dump ("After inserting");

//9、Customer cust = Customers.Where (c => c.ID==1000).First();

//Customer cust = Customers.Single (c => c.ID ==1000);
cust.Name = "Bloggs2";
SubmitChanges();                 // Updates the customer
Customers.Dump ("After updating");

ustomers.DeleteOnSubmit (cust);
SubmitChanges();                 // Deletes the customer
Customers.Dump ("After deleting");

//10、Updates-Associations

Purchase p1 = new Purchase { ID=100, Description="Bike",  Price=500, Date = DateTime.Now };
Purchase p2 = new Purchase { ID=101, Description="Tools", Price=100, Date = DateTime.Now };

Customer cust = Customers.Single (c => c.ID == 1);
cust.Purchases.Add (p1);
cust.Purchases.Add (p2);
SubmitChanges();           // Inserts the two purchases and links them to cust

Refresh (RefreshMode.OverwriteCurrentValues, cust);
cust.Purchases.Dump ("cust's purchases, including the two new ones we just inserted");

cust.Purchases.Remove (p1);
cust.Purchases.Remove (p2);

SubmitChanges();           // Unlinks the two purchases

Refresh (RefreshMode.OverwriteCurrentValues, cust);
cust.Purchases.Dump ("After calling EntitySet<>.Remove, notice how the purchases have gone from the customer's collection");

Purchases.Where (p => p.ID == 100 || p.ID == 101)
.Dump ("The purchases have not been deleted from the table, however: they're still there with a null CustomerID");

Purchases.DeleteOnSubmit (Purchases.Single (p => p.ID == 100));
Purchases.DeleteOnSubmit (Purchases.Single (p => p.ID == 101));
SubmitChanges();

Purchases.Dump ("To delete them completely, we must remove them from Table<Purchase> and call SubmitChanges");

//11、Extra - Dealing with SQL NULL semantics

// The following query returns all purchases with a null CustomerID:
Purchases.Where (p => p.CustomerID == null)
.Dump ("Customers with null CustomerID, using a null literal");
sql:

SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE [t0].[CustomerID] IS NULL
GO

// Look at the SQL translatation-the predicate reads"WHERE [t0].[CustomerID] IS NULL".
// The "IS" keyword in SQL is essential here, to avoid running afoul of SQL's three-value null logic.
// If it translated instead to "WHERE [t0].[CustomerID] = NULL" we would get no rows back.

// But suppose that you want to use a variable instead of a literal, in other words:

int? customerID = null;
Purchases.Where (p => p.CustomerID == customerID)
.Dump ("Customers with a null CustomerID, using a variable");
sql:

-- Region Parameters
DECLARE @p0 Int SET @p0 = null
-- EndRegion
SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE [t0].[CustomerID] = @p0
GO

// Notice, now, that no results are returned. We can see why by looking at the SQL: the
// the predicate translates to: "WHERE [t0].[CustomerID] = @p0".

// Fortunately, there's an easy workaround. If you use the static object.Equals method to
// perform the equality comparison, LINQ to SQL emits an "IS NULL" expression if the value
// is null, otherwise it emits an "= @p" expression:

Purchases.Where (p => object.Equals (p.CustomerID, customerID))
.Dump ("Using the static object.Equals method, with a null variable");
sql:

SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE [t0].[CustomerID] IS NULL
GO

customerID = 1;
Purchases.Where (p => object.Equals (p.CustomerID, customerID))
.Dump ("Using the static object.Equals method, with a non-null variable");

-- Region Parameters
DECLARE @p0 Int SET @p0 = 1
-- EndRegion
SELECT [t0].[ID], [t0].[CustomerID], [t0].[Date], [t0].[Description], [t0].[Price]
FROM [Purchase] AS [t0]
WHERE ([t0].[CustomerID] IS NOT NULL) AND ([t0].[CustomerID] = @p0)

//12、Extra - Compiled Queries

// LINQ to SQL lets you precompile queries so that you pay the cost of translating
// the query from LINQ into SQL only once. In LINQPad the typed DataContext is
// called TypeDataContext, so we proceed as follows:

 

var cc = CompiledQuery.Compile ((TypedDataContext dc, decimal minPrice) =>    
	from c in Customers
	where c.Purchases.Any (p => p.Price > minPrice)
	select c
);
cc (this, 100).Dump ("Customers who spend more than $100");
cc (this, 1000).Dump ("Customers who spend more than $1000");