如何在 Linq 中执行子查询

发布于 2024-10-28 04:32:43 字数 1082 浏览 5 评论 0原文

我有类 Foo 和 Bar：

public class Foo
{
  public int Id {get;set;}
  public IEnumerable<Bar> Bars {get;private set;}
}

public class Bar
{
  public int Id {get;set;}
  public string Type {get;set;}
  public string Description {get;set;}
}

这两个类以可预测的方式映射到数据库中的表“Bar 具有 的外键” Foo' 之类的方式。（如果需要，我可以提供 FluentNHibernate 映射）。

我想编写一个 linq 查询，其功能相当于：

SELECT FOOS.*, (SELECT Description FROM BARS WHERE BAR.FOOID = FOO.ID AND BAR.TYPE = 'Irish')
FROM FOOS
WHERE FOO.ID = 1

我将如何做到这一点？

是的，我知道如果 Foo 有多个爱尔兰 Bar 查询将会中断（尽管我可以使用 TOP 1 类型语法来保护它）。是的，我的意思是使用子查询而不是联接。

编辑：

这是我到目前为止正在尝试的：

var foosWithIrishBarDescription = FooRepository.All.Where(x => x.Id == 1).Select(x=> new {FooId = x.Id, IrishBarDescription = x.Bars.Where(y => y.Type == "Irish").Select(y => y.Description)});

但我收到错误“未实现“选择”方法。”这在 Linq to NHibernate 中不能实现吗？（我使用的是2.1）。

原文

I have the classes Foo and Bar:

public class Foo
{
  public int Id {get;set;}
  public IEnumerable<Bar> Bars {get;private set;}
}

public class Bar
{
  public int Id {get;set;}
  public string Type {get;set;}
  public string Description {get;set;}
}

These two classes map to tables in the database in a predictable 'Bar has foreign key to Foo' kind of way. (I can provide the FluentNHibernate mappings if required).

I would like to write a linq query that does the equivalent of this:

SELECT FOOS.*, (SELECT Description FROM BARS WHERE BAR.FOOID = FOO.ID AND BAR.TYPE = 'Irish')
FROM FOOS
WHERE FOO.ID = 1

How would I do this?

And yes, I'm aware that the query will break if the Foo has more than one Irish Bar (although I could use TOP 1 type syntax to protect it). And yes, I mean to use a subquery rather than a join.

EDIT:

This is what I'm trying so far:

var foosWithIrishBarDescription = FooRepository.All.Where(x => x.Id == 1).Select(x=> new {FooId = x.Id, IrishBarDescription = x.Bars.Where(y => y.Type == "Irish").Select(y => y.Description)});

But I get the error 'The method 'Select' is not implemented.' Can this not be achieved in Linq to NHibernate? (I'm using 2.1).

分享到QQ

分享到微博

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

发布评论

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

妞丶爷亲个 2024-11-04 04:32:43

我假设您的 IEnumerable 实际上是一个名为“Bars”的属性：

var foos = dbContext.FOOS.Where(f => f.ID == 1);
var foobar = foos.Select(f => new {f, f.Bars.FirstOrDefault(b => b.TYPE == "Irish")});

I assume your IEnumerable is actually a property called "Bars":

var foos = dbContext.FOOS.Where(f => f.ID == 1);
var foobar = foos.Select(f => new {f, f.Bars.FirstOrDefault(b => b.TYPE == "Irish")});

回复收藏 0 原文

萤火眠眠 2024-11-04 04:32:43

from f in dbContext.FOOSs
where f.ID = 1
select new
{
    FOOS = f,
    Description = (from b in dbContext.BARSs 
                   where b.FOOID == f.ID && b.TYPE == "Irish" 
                   select b.Description).FirstOrDefault(),
}

如果每个 FOO 只有一个 BAR，您也可以将查询表示为联接。

如果您在 FOO 和 BAR 之间存在 FK 关系，您可以使用该信息来获得更具可读性的查询

from f in dbContext.FOOSs
where f.ID = 1
select new
{
    FOOS = f,
    Description = (from b in f.BARSs 
                   where b.TYPE == "Irish" 
                   select b.Description).FirstOrDefault(),
}

from f in dbContext.FOOSs
where f.ID = 1
select new
{
    FOOS = f,
    Description = (from b in dbContext.BARSs 
                   where b.FOOID == f.ID && b.TYPE == "Irish" 
                   select b.Description).FirstOrDefault(),
}

If you only have one BAR for each FOO you can also express the query as a join.

If you have a FK relation between FOO and BAR you can use that information to get a more readable query

from f in dbContext.FOOSs
where f.ID = 1
select new
{
    FOOS = f,
    Description = (from b in f.BARSs 
                   where b.TYPE == "Irish" 
                   select b.Description).FirstOrDefault(),
}

回复收藏 0 原文

~没有更多了~