SQL Function to LINQ to SQL
本文关键字:to SQL LINQ Function | 更新日期: 2023-09-27 17:55:57
我想将以下sql过程更改为LINQ到SQL,任何人都可以帮助我。
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
CREATE PROC dbo.ShowHierarchy (@Root int) AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)
SET @EmpName = (
SELECT EmpName
FROM dbo.Emp
WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName
SET @EmpID = (
SELECT MIN(EmpID)
FROM dbo.Emp
WHERE MgrID = @Root)
WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @EmpID
SET @EmpID = (
SELECT MIN(EmpID)
FROM dbo.Emp
WHERE MgrID = @Root AND EmpID > @EmpID)
END
END
在 SQL Server 中执行查询的最有效方法是使用公用表表达式(短 CTE)。编写一个存储过程并从 Linq2Sql 调用它。在纯 Linq 中没有真正有效的方法可以做到这一点,除了 Steven 概述的方式(拉出雇主,然后递归调用函数),这将导致相当数量的数据库查询。
试试这个:
public string[] ShowHierarchy(int employeeId)
{
using (var db = new YourDataContext("yourConStr"))
{
return ShowHierarchyRecursive(db, employeeId, 1)
.ToArray();
}
}
private IEnumerable<string> ShowHierarchyRecursive(
YourDataContext db, int employeeId, int level)
{
// get the name of employeeId from db
// yield return that name
// get the list of people managed by that employeeId
// foreach employee in that list,
// call the ShowHierarchyRecursive, and
// foreach item in the returned list yield return
// that item.
}