Entity SQL Language 三 Where/Exists/In/Like/参数及外键查询

上文写了关于Where及Select的基本操作，这一篇讲一下Where的进阶操作

先说一下查看所生成的SQL的方法，.ToTraceString()
例如：

string entitySQL = "select value a from orders as a;";var query = db.CreateQuery<Orders>(entitySQL);Console.WriteLine(query.ToTraceString());

参数

参数可以通过CreateQuery的第二个参数传递进去。

DateTime dt = new DateTime(1997, 1, 1);ObjectParameter[] ps = { new ObjectParameter("dt", dt) };ObjectQuery<Orders> query = db.CreateQuery<Orders>("SELECT VALUE Orders FROM Orders WHERE Orders.OrderDate < @dt;", ps);

这里通过@dt将参数传了进去，执行结果：

外键关系

Entity SQL与其它的查询一样，可以通过外键的关系直接取值或判断，如：

ObjectQuery<Orders> query = db.CreateQuery<Orders>(					"SELECT VALUE o FROM Orders AS o WHERE o.Customers.Country = 'Mexico';");

我们直接用o.Customers.Country这是一个一对多的外键关系。

多条件查询

与T-SQL相同，只要用相应的逻辑关键字即可。如：

string entitySQL = "SELECT VALUE p FROM Products AS p WHERE p.UnitsInStock < p.ReorderLevel and p.UnitsOnOrder = 0;";ObjectQuery<Products> query = db.CreateQuery<Products>(entitySQL);

Exists查询

做为Where的一个经常用的谓词，Exists的使用方法如下：

string entitySQL = @"SELECT VALUE s FROM Suppliers AS s WHERE EXISTS(SELECT p FROM s.Products AS p WHERE p.UnitsInStock = 0);";var query = db.CreateQuery<bool>(entitySQL);

运行后生成的SQL如下：

SELECT[Extent1].[Address] AS [Address],[Extent1].[City] AS [City],[Extent1].[CompanyName] AS [CompanyName],[Extent1].[ContactName] AS [ContactName],[Extent1].[ContactTitle] AS [ContactTitle],[Extent1].[Country] AS [Country],[Extent1].[Fax] AS [Fax],[Extent1].[HomePage] AS [HomePage],[Extent1].[Phone] AS [Phone],[Extent1].[PostalCode] AS [PostalCode],[Extent1].[Region] AS [Region],[Extent1].[SupplierID] AS [SupplierID]FROM [dbo].[Suppliers] AS [Extent1]WHERE  EXISTS (SELECTcast(1 as bit) AS [C1]FROM [dbo].[Products] AS [Extent2]WHERE ([Extent1].[SupplierID] = [Extent2].[SupplierID]) AND ([Extent2].[UnitsInStock] = 0))

IN查询

In查询当然也是不可少的。使用方法如下：

string entitySQL =@"SELECT VALUE c FROM Customers AS c WHERE c.City in {'Seattle','harbin'}";var query = db.CreateQuery<Orders>(entitySQL);

LIKE查询

like做为一个模糊查询的关键字是一定要支持的。

string entitySQL =@"SELECT VALUE c FROM Customers AS c WHERE c.City like '%at%';";var query = db.CreateQuery<Orders>(entitySQL);

Between、And查询

string entitySQL =@"SELECT VALUE c FROM Customers AS c WHERE c.ID between 1 and 100";var query = db.CreateQuery<Orders>(entitySQL);