Ծ֧ǵĹҵ

10Ԫ 50Ԫ





֤룺  ֤,?ˢ֤



  Ҫ Ŀ Lib Ƶ Code iProcess γ ֤ ѯ   ɳ֮·  
Ա   
 
   
 
  
ÿ15ƪ
ı
׷
 
 
     
   
 
 
Ϊʲô˵LINQҪʤSQL
 
Դ51CTO  2017-4-14
Ҫ   356     ۣ      
 

е˵ LINQ ͬ C#( VB)һ𣬹ʶ˱Ժݿ֮ϵĺ蹵ͬʱΪԴṩ˵һIJѯӿڡȻЩʵǹµһ֡ҪǣҪݿвѯʱLINQ ڴ¶ SQL Ч

㻹ûг LINQͻɶСֵġSQL ûлΪʲôҪ޲? ΪʲôǻҪһֲѯ?

е˵ LINQ ͬ C#( VB)һ𣬹ʶ˱Ժݿ֮ϵĺ蹵ͬʱΪԴṩ˵һIJѯӿڡȻЩʵǹµһ֡ҪǣҪݿвѯʱLINQ ڴ¶ SQL Ч

ͬ SQL ȣ LINQ 򵥡Ҹ߼Ӹ C# ͬ C++ Ƚϡģʱʹ C++ Ȼõѡ(ʹ SQL ij)ڴУʹִԶΪײϸڲһʤ

SQL һŷdzϵԡ 1974 ꡣȻչûбƹʹеˡ VB6 Visual FoxProҲѾϰڴκδ©ĵط!

һӡҪдһ򵥵IJѯȡͻݣ£

SELECT UPPER(Name) 

FROM Customer

WHERE Name LIKE 'A%'

ORDER BY Name

ڼҪЩṩһҳȡ 21 30 ݡҪһӲѯ

SELECT UPPER(Name) FROM 

(

SELECT *, RN = row_number()

OVER (ORDER BY Name)

FROM Customer

WHERE Name LIKE 'A%'

) A

WHERE RN BETWEEN 21 AND 30

ORDER BY Name

Ҫְ֧汾( SQL Server 2005 ֮ǰ)ϵݿ⣬:

SELECT TOP 10 UPPER (c1.Name) 

FROM Customer c1

WHERE

c1.Name LIKE 'A%'

AND c1.ID NOT IN

(

SELECT TOP 20 c2.ID

FROM Customer c2

WHERE c2.Name LIKE 'A%'

ORDER BY c2.Name

)

ORDER BY c1.Name

ӶңҲΥ DRY ԭʹ LINQ ʵͬIJѯܡȻڼϸʤһ

var query = 

from c in db.Customers

where c.Name.StartsWith ("A")

orderby c.Name

select c.Name.ToUpper();
var thirdPage = query.Skip(20).Take(10);

ֻеöٵ thirdPage ʱѯŻʵִСڴ LINQ SQL Entity Framework ijУὫ(϶ɵ)ѯתһ SQL 䣬ӵݿŻġ

Ѿע⵽ LINQ һ΢(΢ش)ĺôѡеѯ裺

IQueryable Paginate (this IQueryable query, int skip, int take) 

{

return query.Skip(skip).Take(take);

}

ǿ

var query = ... 

var thirdPage = query.Paginate (20, 10);

ҪǣǿԽķҳѯ֮ͨ LINQ ԰Ѳѯֽһ֣ȻӦóá

LINQ һôԲ JOIN ܽйϵѯ磬Ҫгй $1000 ϣҾסڻʢٵĹ˿͡ǻٶùĿ(ҲǾIJɹ/Ŀɹ)Ұ(ûй˿ͼ¼)ֽҲҪĸ(Purchase, Customer, Address Լ PurchaseItem)֮вѯʹ LINQIJѯѴ֮

from p in db.Purchases 

where p.Customer.Address.State == "WA" || p.Customer == null

where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000

select p

ͬȹܵ SQL Ƚϣ

SELECT p.* 

FROM Purchase p

LEFT OUTER JOIN

Customer c INNER JOIN Address a ON c.AddressID = a.ID

ON p.CustomerID = c.ID

WHERE

(a.State = 'WA' || p.CustomerID IS NULL)

AND p.ID in

(

SELECT PurchaseID FROM PurchaseItem

GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000

)

ԴһչҪ۸УյͶӰʾԱԼĿǿȻظرЩIJѯ

from p in db.Purchases 

where p.Customer.Address.State == "WA" || p.Customer == null

let purchaseValue = p.PurchaseItems.Sum (pi => pi.SaleAmount)

where purchaseValue > 1000

orderby purchaseValue descending

select new

{

p.Description,

p.Customer.SalesPerson.Name,

PurchaseItemCount = p.PurchaseItems.Count()

}

ʹ SQL ʵͬIJѯ

SELECT  

p.Description,

s.Name,

(SELECT COUNT(*) FROM PurchaseItem pi WHERE p.ID = pi.PurchaseID) PurchaseItemCount

FROM Purchase p

LEFT OUTER JOIN

Customer c

INNER JOIN Address a ON c.AddressID = a.ID

LEFT OUTER JOIN SalesPerson s ON c.SalesPersonID = s.ID

ON p.CustomerID = c.ID

WHERE

(a.State = 'WA' OR p.CustomerID IS NULL)

AND p.ID in

(

SELECT PurchaseID FROM PurchaseItem

GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000

)

ORDER BY

(SELECT SUM (SaleAmount) FROM PurchaseItem pi WHERE p.ID = pi.PurchaseID) DESC

˼ǿԽ SQL ѯתص LINQɵIJѯÿһ鶼ɵʽظ̳ﳣIJѯ(ͨǷǹİ汾) SQL ˼ LINQ ˼Ľǽ Fortran ת C# 6 ʱᱧԹ GOTO ı׾﷨һ

ڲѯдӶѡ C յĽһƽΪλԪ顣ʹ˶ SQLΪ²ᷢϡظӶʹý޷ڿͻ˺ܺõʹáԵʱԽܡ෴LINQ Իȡķֲ㼶ݡͱظý״ڴҲϲıҪ磬Ҫȡһ˿ͣÿһ¼ǵĸ߼ֵסʹ LINQ

from c in db.Customers 

where c.Address.State == "WA"

select new

{

c.Name,

c.CustomerNumber,

HighValuePurchases = c.Purchases.Where (p => p.Price > 1000)

}

HighValuePurchasesһϡDzѯһԣͲҪˡһϻϵϸͱܺõijˡڴУ SQLܾһϣLINQ ΪӼϷصԪؾųСҪһԷһϵĶ:

from c in db.Customers 

where c.Address.State == "WA"

let HighValuePurchases = c.Purchases.Where (p => p.Price > 1000)
where HighValuePurchases.Any()select new

{

c.Name,

c.CustomerNumber,

HighValuePurchases

}

LINQ ͨһḻIJƽϡϡѯԼֲͬͲѯ֧֡

Ҫ֮ǰӲأˡWA״̬DzǾҪһ? ʵֻҪͿ:

string state = "WA"; 

var query =

from c in db.Customers

where c.Address.State == state

DbCommand IJߵ SQL ע빥 LINQ IJͰȫҸ߶ȿɶġ⡪ҽúܲ

Ϊ LINQ ѯʱԽϣǿνʡ磬дһ£

IQueryable GetCustomers (string state, decimal? minPurchase) 

{

var query = Customers.AsQueryable();

if (state != null)

query = query.Where (c => c.Address.State == state);

if (minPurchase != null)

query = query.Where (c => c.Purchases.Any (p => p.Price > minPurchase.Value));

return query;

}

ʹÿյ state Լ minPurchase ֵôöٽʱ SQL ͻᱻɳ

SELECT [t0].[ID], [t0].[Name], [t0].[AddressID] 

FROM [Customer] AS [t0]

ָ state minPurchase ֵLINQ SQL ͲֻѯνʣбҪ䣺

SELECT [t0].[ID], [t0].[Name], [t0].[AddressID] 
 
FROM [Customer] AS [t0] 
 
LEFT OUTER JOIN [Address] AS [t1] ON [t1].[ID] = [t0].[AddressID] 
 
WHERE (EXISTS( 
 
    SELECT NULL AS [EMPTY] 
 
    FROM [Purchase] AS [t2] 
 
    WHERE ([t2].[Price] > @p0) AND ([t2].[CustomerID] = [t0].[ID]) 
 
    )) AND ([t1].[State] = @p1) 

Ϊǵķһ IQueryableѯöٵ֮ǰᱻʵʵת SQL ִС͸˵ýһνʡҳԶͶӰȵȵĻᡣ

̬Ͱȫ

֮ǰIJѯУǽ state һһַôѯڱʱͻᱨõȵʱҲͬڰѱŪعʱһʵڵĺôûͷĹʾ

ͻ˴

LINQ ɵؽѯһЩתƵͻϽдڸظϴݿʵܡֻҪȡûг(֮㻹Ҫڷ)ͿԾԵͨѶԽתԼѹתƵؽٵӦ÷ȥʹ LINQҪľ AsEnumerable() תƵѯ֮УǸ֮鶼ڱִС

ʲôʱ LINQ ȥѯݿ

LINQ Ĺǿ󣬵ȡ SQL 95% ϵ󣬲ʱȻҪSQL:

ҪֶIJѯ (ҪŻͽʾʱ);

Щ漰Ҫ select ʱȻҪЩвѯIJѯ;

Ԥ֪ĸԼ

оõʱ㻹Ҫ SQL (ʹ LINQ ʱĶdzҪҪʹô洢̺ͺʱSQL Dzɻȱ)ͨ SQL бдֵ SQL LINQ һ, ȻڸӸӵ LINQ ѯЩ

˽ŲѯԲ⣬Ϊ㶼Ҫȥѧϰ LINQ LINQ ڲѯؼԼ XML DOM ʱdzʵáʹõȻϾɵĻ XmlDocument DOMLINQ to XML DOM һ־ϷЧĽ

о SQL LINQ գдIJѯʹ LINQ SQL ôɡ

LINQ ʵս

Ҽֻ LINQ ݿѯΪЧʡ

Ӧóıдԣҵĸ˾һʹ LINQ ݷʲ(ʹһ LINQ SQL Entity Framework API)ԽݷʵĿʱ俳һ룬ҿάӵɡ

   
 
 
ĵ ؿγ



Ǹݿ
ݿƾ̸
ݿƹ
ݿܽ
ݿܵż
ݿܵ
ݿŻ
ݿϵͳܵϵ
ݿŻ
߼ݿܹʦ
ݲֿھ
Hadoopԭܵ
 

MySQLݽṹ
MySQLܹܵ
SQL Serverݿⱸָ
ݿ 10DB2Ż
oracleʱռд
ݿĿƽ̨
...   

ݿ
߼ݿܹʦ
Hadoopԭʵ
Oracle ݲֿ
ݲֿھ
Oracleݿ⿪
γ...   

IT˾ androidƽ̨ʵ
Android
ijԴҵ Android
ij칫˾ AndroidIOSӦ
Linuxں
Ĭ Ƕʽܹ
Ƕʽܹ
...   
 
 
 
 
 
ÿ2ĵ/Ƶ
ɨ΢Ŷά붩
ļ¿
ÿ300Դ
 
 

| ϵ | ICP10020922 110108001071