使用Adventureworks数据库测试了linq2sql,发现其对表达式列支持不好,测试代码如下:
AdventureWorksDataContext db
=
new AdventureWorksDataContext();
Customer c = new Customer();
c.ModifiedDate = DateTime.Now;
c.rowguid = Guid.NewGuid();
c.TerritoryID = 1 ;
c.CustomerType = ' S ' ;
// c.AccountNumber = " AW00000199 " ;
db.Customers.Add(c);
db.SubmitChanges();
Customer c = new Customer();
c.ModifiedDate = DateTime.Now;
c.rowguid = Guid.NewGuid();
c.TerritoryID = 1 ;
c.CustomerType = ' S ' ;
// c.AccountNumber = " AW00000199 " ;
db.Customers.Add(c);
db.SubmitChanges();
AccountNumber为表达式列,结果运行的时候抛出异常。
同时,发现其分页算法效率有点低,代码如下:
AdventureWorksDataContext db = new AdventureWorksDataContext();
var c = db.Customers.Skip( 100 ).Take( 100 );
/*SELECT TOP 100 [t1].[CustomerID], [t1].[TerritoryID], [t1].[AccountNumber], [t1].[CustomerType], [t1].[rowguid], [t1].[ModifiedDate]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[TerritoryID], [t0].[AccountNumber], [t0].[CustomerType], [t0].[rowguid], [t0].[ModifiedDate]) AS [ROW_NUMBER], [t0].[CustomerID], [t0].[TerritoryID], [t0].[AccountNumber], [t0].[CustomerType], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Sales].[Customer] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] > 100*/
使用Sql Server Query Visualizer可以看到上面的sql代码。其Row_Number() over了全部的列, 我在Management Studio中运行的时候上面的sql代码大概花费了40毫秒(前后加上Select getdate()),而如果Row_Number 只over主键CustomerID的话,则没看到时间差距。
var c = db.Customers.Skip( 100 ).Take( 100 );
/*SELECT TOP 100 [t1].[CustomerID], [t1].[TerritoryID], [t1].[AccountNumber], [t1].[CustomerType], [t1].[rowguid], [t1].[ModifiedDate]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[TerritoryID], [t0].[AccountNumber], [t0].[CustomerType], [t0].[rowguid], [t0].[ModifiedDate]) AS [ROW_NUMBER], [t0].[CustomerID], [t0].[TerritoryID], [t0].[AccountNumber], [t0].[CustomerType], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Sales].[Customer] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] > 100*/