如何制定一个IQueryable查询递归的数据库表？ [英] How to formulate an IQueryable to query a recursive database table?
问题描述
我有这样一个数据库表：
实体
---------- -----------
ID INT PK
PARENTID INT FK
码VARCHAR
文字文本
的
PARENTID 字段是同一个表（递归）的外键与另一个纪录。所以结构代表了树。
我试图写来查询该表并获得基于路径1特定实体的方法。路径是代表实体和家长实体的代码属性的字符串。因此，一个示例路径是富/酒吧/巴兹这意味着一个特定的实体，其中的代码==巴兹，父母的代码==栏和家长的代码的父==富
我尝试：
公共实体单（字符串路径）
{
的String [] = pathParts path.Split（'/'）;
串码= pathParts [pathParts.Length -1];
如果（pathParts.Length == 1）
返回dataContext.Entities.Single（E => e.Code ==代码和放大器;&安培; e.ParentID == 0）;
&IQueryable的LT;实体GT;实体= dataContext.Entities.Where（E => e.Code ==代码）;
的for（int i = pathParts.Length - 2; I> = 0;我 - ）
{
串parentCode = pathParts [I]
实体= entities.Where（E => e.Entity1.Code == parentCode）; //不正确
}
返回entities.Single（）;
}
我知道这是不正确的，因为在哪里中的为循环只是增加了更多的条件的当前实体而不是父实体，但我怎么纠正呢？在的话我想for循环说和家长的代码必须是x和那父母的代码父母必须为Y，那父母的代码，父母的父母必须通过z ....等等。除此之外，由于性能原因我想它为一个IQueryable的所以会有仅有1查询打算到数据库中。
如何制定一个IQueryable查询递归的数据库表？
我想它为一个IQueryable的所以会有1只去查询
到数据库中。
我不认为穿越使用单一翻译查询一个分层表是目前可能的实体框架。原因是你需要实现一个循环或递归和我最好的知识既不可以被翻译成EF对象存储查询。
< STRONG>更新
@Bazzz和@Steven让我思考，我不得不承认我是完全错误的：它是可能的，很容易构造一个< 。code>的IQueryable 动态这些要求
下面的函数可以被递归调用建立查询：
公共静态的IQueryable< TestTree>遍历（这IQueryable的< TestTree>源的IQueryable< TestTree>表，链表<串GT;部分）
{
变种代码= parts.First.Value;
变种查询= source.SelectMany（R1 => table.Where（R2 => r2.Code ==代码&放大器;&放大器; r2.ParentID == r1.ID），（R1，R2）=> ; R2）;
如果（parts.Count == 1）
{
返回查询;
}
parts.RemoveFirst（）;
返回query.Traverse（表中，份）;
}
根查询是一种特殊情况;这里是调用导线的工作示例：使用（VAR背景
=新TestDBEntities（））
{
VAR路径=富/酒吧/巴兹
VAR部分=新的LinkedList<串GT;（path.Split（'/'））;
无功表= context.TestTrees;
变种代码= parts.First.Value;
无功根= table.Where（R1 => r1.Code ==代码和放大器;&安培;！r1.ParentID.HasValue）;
parts.RemoveFirst（）;
的foreach（在root.Traverse变种Q（表，零部件））
Console.WriteLine（{0} {1} {2}，q.ID，q.ParentID， q.Code）;
}
该数据库查询只此一次，生成的代码：
EXEC sp_executesql的N'SELECT
[Extent3]。[ID] AS [ID]，
[Extent3]。[PARENTID ] AS [PARENTID]，
[Extent3]。[编号] AS [代码]
从[DBO]。[TestTree] AS [Extent1]
INNER JOIN [DBO]。[TestTree] AS [Extent2] ON（[Extent2]。[代码] = @ p__linq__1）AND（[Extent2]。[PARENTID] = [Extent1]。[ID]）
INNER JOIN [DBO]。[TestTree] AS [ Extent3] ON（[Extent3]。[代码] = @ p__linq__2）AND（[Extent3]。[PARENTID] = [Extent2]。[ID]）
WHERE（[Extent1]。[代码] = @ p__linq__0） AND（[Extent1]。[PARENTID] IS NULL）'，N'@ p__linq__1为nvarchar（4000），@ p__linq__2为nvarchar（4000），@ p__linq__0为nvarchar（4000），@ p__linq__1 = N'bar'，@ p__linq__2 = N 巴兹'，@ p__linq__0 = N'foo'
虽然我喜欢原始的执行计划查询（见下文）好一点，这种方法是有效的，也许是有益的。
更新结束
使用的IEnumerable
我们的想法是抢一气呵成从表中的相关数据，然后做穿越在使用LINQ to对象的应用程序。
下面是一个递归函数，将得到一个序列的节点：
静态TestTree GetNode（这IEnumerable的< TestTree>表，字符串[]部分，INT指数，诠释的parentID）
{
变种q =表
。凡（R =>
r.Code ==部分[指数]&功放;&安培;
（r.ParentID.HasValue r.ParentID ==的parentID：？的parentID == NULL））
。单（）;
回报指数< parts.Length - 1？ table.GetNode（零件，指数+ 1，q.ID）：Q;
}
您可以使用这样的：
使用（VAR上下文=新TestDBEntities（））
{
VAR路径=富/酒吧/巴兹
变种Q = context.TestTrees.GetNode（path.Split（'/'），0，NULL）;
Console.WriteLine（{0} {1} {2}，q.ID，q.ParentID，q.Code）;
}
这将执行一个数据库查询每条路径的一部分，所以如果你想要的DB只能查询一次，用这个来代替：使用
（VAR背景=新TestDBEntities（））
{
VAR路径=富/酒吧/巴兹
变种Q = context.TestTrees
.ToList（）
.GetNode（path.Split（'/'），0，NULL）;
Console.WriteLine（{0} {1} {2}，q.ID，q.ParentID，q.Code）;
}
这是明显的优化是移动前排除在我们的道路不存在的代码：
使用（VAR上下文=新TestDBEntities（））
{
VAR路径=富/酒吧/巴兹;
VAR部分= path.Split（'/'）;
变种Q =上下文
.TestTrees
。凡（R => parts.Any（p值=指p == r.Code））
.ToList（）
.GetNode（份，0，NULL）;
Console.WriteLine（{0} {1} {2}，q.ID，q.ParentID，q.Code）;
}
此查询应该是足够快，除非大部分的实体也有类似的代码。但是，如果你绝对需要顶级的性能，你可以使用原始查询。
SQL Server的原始查询
对于SQL Server基于CTE的查询可能会是最好的：使用
（VAR背景=新TestDBEntities （））
{
VAR路径=富/酒吧/巴兹
变种Q = context.Database.SqlQuery< TestTree>（@与树（ID，PARENTID，代码的TreePath
）作为
（
选择ID，PARENTID，代码， CAST（代码为nvarchar（512））AS的TreePath
FROM dbo.TestTree
，其中PARENTID IS NULL
UNION ALL
选择TestTree.ID， TestTree.ParentID，TestTree.Code，CAST（TreePath的+'/'+ TestTree.Code AS为nvarchar（512））
FROM dbo.TestTree
INNER JOIN树Tree.ID = TestTree.ParentID
）
SELECT * FROM树枝上的TreePath = @path，新的SqlParameter（路径，路径））单（）。
Console.WriteLine（{0} {1} {2}，q.ID，q.ParentID，q.Code）;
}
由根节点限制数据是容易的，可能是非常有用的性能代价
使用（VAR上下文=新TestDBEntities（））
{
VAR路径=富/酒吧/巴兹
变种Q = context.Database.SqlQuery< TestTree>（@与树（ID，PARENTID，代码的TreePath
）作为
（
选择ID，PARENTID，代码， CAST（代码为nvarchar（512））AS的TreePath
FROM dbo.TestTree
，其中PARENTID IS NULL和代码= @parentCode
UNION ALL
选择TestTree.ID，TestTree.ParentID，TestTree.Code，CAST（TreePath的+'/'+ TestTree.Code AS为nvarchar（512））
FROM dbo.TestTree
INNER JOIN树Tree.ID = TestTree.ParentID
）
SELECT * FROM树枝上的TreePath = @path，
新的SqlParameter（路径，路径），
新的SqlParameter（parentCode路径。斯普利特（'/'）[0]））
。单（）;
Console.WriteLine（{0} {1} {2}，q.ID，q.ParentID，q.Code）;
}
脚注
<$ C $：
所有这一切都与.NET 4.5，EF 5，SQL Server 2012的数据建立测试脚本C> CREATE TABLE dbo.TestTree
（
ID int不空IDENTITY PRIMARY KEY，
PARENTID INT空引用dbo.TestTree（ID），
码为nvarchar（100）
）
GO
将dbo.TestTree（PARENTID，代码）VALUES（NULL，'富'）
将dbo.TestTree（PARENTID，代码）VALUES（1， 巴）
将dbo.TestTree（PARENTID，代码）VALUES（2，'巴兹'）
将dbo.TestTree（PARENTID，代码）VALUES（NULL，喇嘛）
INSERT dbo.TestTree（PARENTID，代码）VALUES（1，'蓝光'）
将dbo.TestTree（PARENTID，代码）VALUES（2，'一个BLO'）
将dbo.TestTree（PARENTID，代码）VALUES（NULL，巴兹）
将dbo.TestTree（PARENTID，代码）VALUES（1，'富'）
将dbo.TestTree（PARENTID，代码）VALUES（2，'巴' ）
在我的测试中的所有示例返回ID为3，巴兹实体它假定实体确实存在。错误处理超出了这个帖子的范围。
更新
要地址@ Bazzz的评论，以路径的数据如下所示。代码是由一个级别独特的，而不是全局。
ID PARENTID代码的TreePath
---- ----- ------ --------- -------------------
1 NULL富富
4 NULL喇嘛喇嘛
7 NULL巴兹巴兹
2 1巴富/酒吧
5 1蓝光富/蓝光
8 1富富/ foo的
3 2巴兹富/酒吧/巴兹
6 2一个BLO富/酒吧/ BL0
9 2巴富/酒吧/酒吧
I have a database table like this:
Entity
---------------------
ID int PK
ParentID int FK
Code varchar
Text text
The ParentID field is a foreign key with another record in the same table (recursive). So the structure represents a Tree.
I'm trying to write a method to query this table and get 1 specific Entity based on a path. A path would be a string representing the Code properties of the Entity and the parent Entities. So an example path would be "foo/bar/baz" which means the one specific Entity of which the Code == "baz", the parent's Code == "bar" and the parent of the parent's Code == "foo".
My attempt:
public Entity Single(string path)
{
string[] pathParts = path.Split('/');
string code = pathParts[pathParts.Length -1];
if (pathParts.Length == 1)
return dataContext.Entities.Single(e => e.Code == code && e.ParentID == 0);
IQueryable<Entity> entities = dataContext.Entities.Where(e => e.Code == code);
for (int i = pathParts.Length - 2; i >= 0; i--)
{
string parentCode = pathParts[i];
entities = entities.Where(e => e.Entity1.Code == parentCode); // incorrect
}
return entities.Single();
}
I know this isn't correct because the Where inside the forloop just adds more conditions to the current Entity instead of the parent Entity, but how do I correct this? In words I would like the for-loop to say "and the parent's code must be x and the parent of that parent's code must be y, and the parent of that parent of that parent's code must be z .... etc". Besides that, for performance reasons I'd like it to be one IQueryable so there will be just 1 query going to the database.
How to formulate an IQueryable to query a recursive database table? I'd like it to be one IQueryable so there will be just 1 query going to the database.
I don't think traversing an hierarchical table using a single translated query is currently possible with Entity Framework. The reason is you'll need to implement either a loop or recursion and to my best knowledge neither can be translated into an EF object store query.
UPDATE
@Bazzz and @Steven got me thinking and I have to admit I was completely wrong: it is possible and quite easy to construct an IQueryable for these requirements dynamically.
The following function can be called recursively to build up the query:
public static IQueryable<TestTree> Traverse(this IQueryable<TestTree> source, IQueryable<TestTree> table, LinkedList<string> parts)
{
var code = parts.First.Value;
var query = source.SelectMany(r1 => table.Where(r2 => r2.Code == code && r2.ParentID == r1.ID), (r1, r2) => r2);
if (parts.Count == 1)
{
return query;
}
parts.RemoveFirst();
return query.Traverse(table, parts);
}
The root query is a special case; here's a working example of calling Traverse:
using (var context = new TestDBEntities())
{
var path = "foo/bar/baz";
var parts = new LinkedList<string>(path.Split('/'));
var table = context.TestTrees;
var code = parts.First.Value;
var root = table.Where(r1 => r1.Code == code && !r1.ParentID.HasValue);
parts.RemoveFirst();
foreach (var q in root.Traverse(table, parts))
Console.WriteLine("{0} {1} {2}", q.ID, q.ParentID, q.Code);
}
The DB is queried only once with this generated code:
exec sp_executesql N'SELECT
[Extent3].[ID] AS [ID],
[Extent3].[ParentID] AS [ParentID],
[Extent3].[Code] AS [Code]
FROM [dbo].[TestTree] AS [Extent1]
INNER JOIN [dbo].[TestTree] AS [Extent2] ON ([Extent2].[Code] = @p__linq__1) AND ([Extent2].[ParentID] = [Extent1].[ID])
INNER JOIN [dbo].[TestTree] AS [Extent3] ON ([Extent3].[Code] = @p__linq__2) AND ([Extent3].[ParentID] = [Extent2].[ID])
WHERE ([Extent1].[Code] = @p__linq__0) AND ([Extent1].[ParentID] IS NULL)',N'@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000),@p__linq__0 nvarchar(4000)',@p__linq__1=N'bar',@p__linq__2=N'baz',@p__linq__0=N'foo'
And while I like the execution plan of the raw query (see below) a bit better, the approach is valid and perhaps useful.
End of UPDATE
Using IEnumerable
The idea is to grab the relevant data from the table in one go and then do the traversing in the application using LINQ to Objects.
Here's a recursive function that will get a node from a sequence:
static TestTree GetNode(this IEnumerable<TestTree> table, string[] parts, int index, int? parentID)
{
var q = table
.Where(r =>
r.Code == parts[index] &&
(r.ParentID.HasValue ? r.ParentID == parentID : parentID == null))
.Single();
return index < parts.Length - 1 ? table.GetNode(parts, index + 1, q.ID) : q;
}
You can use like this:
using (var context = new TestDBEntities())
{
var path = "foo/bar/baz";
var q = context.TestTrees.GetNode(path.Split('/'), 0, null);
Console.WriteLine("{0} {1} {2}", q.ID, q.ParentID, q.Code);
}
This will execute one DB query for each path part, so if you want the DB to only be queried once, use this instead:
using (var context = new TestDBEntities())
{
var path = "foo/bar/baz";
var q = context.TestTrees
.ToList()
.GetNode(path.Split('/'), 0, null);
Console.WriteLine("{0} {1} {2}", q.ID, q.ParentID, q.Code);
}
An obvious optimization is to exclude the codes not present in our path before traversing:
using (var context = new TestDBEntities())
{
var path = "foo/bar/baz";
var parts = path.Split('/');
var q = context
.TestTrees
.Where(r => parts.Any(p => p == r.Code))
.ToList()
.GetNode(parts, 0, null);
Console.WriteLine("{0} {1} {2}", q.ID, q.ParentID, q.Code);
}
This query should be fast enough unless most of your entities have similar codes. However, if you absolutely need top performance, you could use raw queries.
SQL Server Raw Query
For SQL Server a CTE-based query would probably be best:
using (var context = new TestDBEntities())
{
var path = "foo/bar/baz";
var q = context.Database.SqlQuery<TestTree>(@"
WITH Tree(ID, ParentID, Code, TreePath) AS
(
SELECT ID, ParentID, Code, CAST(Code AS nvarchar(512)) AS TreePath
FROM dbo.TestTree
WHERE ParentID IS NULL
UNION ALL
SELECT TestTree.ID, TestTree.ParentID, TestTree.Code, CAST(TreePath + '/' + TestTree.Code AS nvarchar(512))
FROM dbo.TestTree
INNER JOIN Tree ON Tree.ID = TestTree.ParentID
)
SELECT * FROM Tree WHERE TreePath = @path", new SqlParameter("path", path)).Single();
Console.WriteLine("{0} {1} {2}", q.ID, q.ParentID, q.Code);
}
Limiting data by the root node is easy and might be quite useful performance-wise:
using (var context = new TestDBEntities())
{
var path = "foo/bar/baz";
var q = context.Database.SqlQuery<TestTree>(@"
WITH Tree(ID, ParentID, Code, TreePath) AS
(
SELECT ID, ParentID, Code, CAST(Code AS nvarchar(512)) AS TreePath
FROM dbo.TestTree
WHERE ParentID IS NULL AND Code = @parentCode
UNION ALL
SELECT TestTree.ID, TestTree.ParentID, TestTree.Code, CAST(TreePath + '/' + TestTree.Code AS nvarchar(512))
FROM dbo.TestTree
INNER JOIN Tree ON Tree.ID = TestTree.ParentID
)
SELECT * FROM Tree WHERE TreePath = @path",
new SqlParameter("path", path),
new SqlParameter("parentCode", path.Split('/')[0]))
.Single();
Console.WriteLine("{0} {1} {2}", q.ID, q.ParentID, q.Code);
}
Footnotes
All of this was tested with .NET 4.5, EF 5, SQL Server 2012. Data setup script:
CREATE TABLE dbo.TestTree
(
ID int not null IDENTITY PRIMARY KEY,
ParentID int null REFERENCES dbo.TestTree (ID),
Code nvarchar(100)
)
GO
INSERT dbo.TestTree (ParentID, Code) VALUES (null, 'foo')
INSERT dbo.TestTree (ParentID, Code) VALUES (1, 'bar')
INSERT dbo.TestTree (ParentID, Code) VALUES (2, 'baz')
INSERT dbo.TestTree (ParentID, Code) VALUES (null, 'bla')
INSERT dbo.TestTree (ParentID, Code) VALUES (1, 'blu')
INSERT dbo.TestTree (ParentID, Code) VALUES (2, 'blo')
INSERT dbo.TestTree (ParentID, Code) VALUES (null, 'baz')
INSERT dbo.TestTree (ParentID, Code) VALUES (1, 'foo')
INSERT dbo.TestTree (ParentID, Code) VALUES (2, 'bar')
All examples in my test returned the 'baz' entity with ID 3. It's assumed that the entity actually exists. Error handling is out of scope of this post.
UPDATE
To address @Bazzz's comment, the data with paths is shown below. Code is unique by level, not globally.
ID ParentID Code TreePath
---- ----------- --------- -------------------
1 NULL foo foo
4 NULL bla bla
7 NULL baz baz
2 1 bar foo/bar
5 1 blu foo/blu
8 1 foo foo/foo
3 2 baz foo/bar/baz
6 2 blo foo/bar/blo
9 2 bar foo/bar/bar
这篇关于如何制定一个IQueryable查询递归的数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
