仅从 EF 关联中获取键值

发布于 2024-11-09 08:09:10 字数 692 浏览 3 评论 0原文

我已经基于仅包含一对键值的第三个表在两个表之间建立了多对多关联。现在我想做一个查询，将右表键值按左分组，而不需要其他数据。

LeftTable { LeftID, LeftField1, LeftField2 } 
JoinTable { LeftID, RightID} 
RightTable { RightID, RightField1, RightField2 }

有没有什么方法可以本质上只查询 JoinTable 并获取按“LeftID”分组的所有“RightID”，而无需 SQL 尝试从任一侧获取字段？ JoinTable 在模型中本身并不是一个实体，而是映射到关联。

我已经使用 ObjectQuery 和 EntityCommand (ESQL) 进行了一些实验，两者似乎仍然通过加入我不需要的 RightTable 来加载到其他字段中。

我的 ESQL 看起来类似于：

SELECT lt.LeftID, (SELECT rt.RightID
FROM NAVIGATE(lt, MyModel.LeftToRightAssoc, RightTable) as rt)
FROM MyEntities.LeftTable as lt;

但生成的 SQL 仍在 RightField1 和 RightField2 中获取。

当然必须有一种更简单的方法来做到这一点？

原文

I've set up a many-to-many association between two tables based on a third table that just holds a pair of key values. Now I'd like to do a query that groups the right tables key values by the lefts without needing other data.

LeftTable { LeftID, LeftField1, LeftField2 } 
JoinTable { LeftID, RightID} 
RightTable { RightID, RightField1, RightField2 }

Is there any way to essentially just query the JoinTable and get all the 'RightIDs' grouped by the 'LeftIDs' without the SQL trying to fetch the fields from either side?
The JoinTable is not an entity in its own right in the model, but is mapped to the association.

I've experimented a bit with both using ObjectQuery and EntityCommand (ESQL) and both seem to still load in the other fields by joining to RightTable which I don't need.

My ESQL looks something like:

SELECT lt.LeftID, (SELECT rt.RightID
FROM NAVIGATE(lt, MyModel.LeftToRightAssoc, RightTable) as rt)
FROM MyEntities.LeftTable as lt;

but the generated SQL is still fetching in RightField1 and RightField2.

Surely there must be a simpler way to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不必在意 2024-11-16 08:09:10

假设您的类 Left 有一个导航属性 Rights （Right 实体的集合），您可以尝试以下操作：

var list = context.Lefts.Select(l => new
{
    LeftId = l.LeftId,
    RightIds = l.Rights.Select(r => r.RightId)
});

foreach (var item in list)
{
    Console.WriteLine("LeftId = {0}", item.LeftId);
    foreach (var rightId in item.RightIds)
    {
        Console.WriteLine("RightId = {0}", rightId);
    }
}

您将获得匿名的集合类型对象，其中每个元素都有 LeftId 和相应 RightIds 的集合。此查询不应触及其他字段，例如 RightField1 等。您还可以创建自己的自定义类型，然后在上面的查询中投影到此类型，而不是匿名类型。

Assuming that your class Left has a navigation property Rights (a collection of Right entities) you could try this:

var list = context.Lefts.Select(l => new
{
    LeftId = l.LeftId,
    RightIds = l.Rights.Select(r => r.RightId)
});

foreach (var item in list)
{
    Console.WriteLine("LeftId = {0}", item.LeftId);
    foreach (var rightId in item.RightIds)
    {
        Console.WriteLine("RightId = {0}", rightId);
    }
}

You would get a collection of anonymous type objects where each element has the LeftId and a collection of corresponding RightIds. This query should not touch the other fields like RightField1, etc. Instead of an anonymous type you could also create your own custom type and then project into this type in the query above.

回复收藏 0 原文

~没有更多了~