如何在C#中使用Linq从多个嵌套列中选择特定字符串? [英] How to use Linq in C# to select a specific string from multiple nested columns?

问题描述

我在这里有一个小问题.我有下表及其关系:

I have a small problem here. I have the following tables with their relations :

Building
  Batteries
    Columns
      Elevators

建筑物可以有很多电池，电池可以有很多列，列可以有很多电梯.

A building can have many batteries, batteries can have many columns, columns can have many elevators.

一个电池有一个建筑物，一列有一个电池，一部电梯有一个列.

A battery has one building, a column has one battery, an elevator has one column.

如果要执行此操作var myintervention = _context.buildings.Where(b => b.batteries.Any(ba => ba.status == "Intervention")).ToList();，则在我的查询中可以很好地返回带有干预状态(状态为一列)的带有电池的建筑物的列表.

If I were to do this var myintervention = _context.buildings.Where(b => b.batteries.Any(ba => ba.status == "Intervention")).ToList(); it would work perfectly fine in my query to return a list of the buildings that have batteries with the intervention status (status is a column).

问题是我无法执行类似_context.buildings.Where(c => c.columns.Any...)的操作，因为建筑模型无法访问列类，但是电池可以访问...电梯也是如此，建筑物无法访问电梯，列就可以.

The problem is that I can't do something like _context.buildings.Where(c => c.columns.Any...) because the building model doesn't have access to the column class but the battery does... Same goes for elevators, buildings don't have access to elevators, columns do.

这是我定义modelBuilder的方式:

Here's how I defined my modelBuilder :

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Battery>()
            .HasOne(p => p.buildings)
            .WithMany(b => b.batteries)
            .HasForeignKey(p => p.building_id);

        modelBuilder.Entity<Column>()
            .HasOne(p => p.batteries)
            .WithMany(b => b.columns)
            .HasForeignKey(p => p.battery_id);

        modelBuilder.Entity<Elevator>()
            .HasOne(p => p.columns)
            .WithMany(b => b.elevators)
            .HasForeignKey(p => p.column_id);
    }

这是我的关系在我的模型中的样子:

Here's how my relations look in my models :

building.cs
  public List<Battery> batteries { get; set; }

battery.cs
  public long building_id { get; set; }
  public Building buildings { get; set; }
  public List<Column> columns { get; set; }

column.cs
  public long battery_id { get; set; }
  public Battery batteries { get; set; }
  public List<Elevator> elevators { get; set; }

elevator.cs
  public long column_id { get; set; }
  public Column columns { get; set; }

tl; dr;我想执行一次linq查询，以列出状态为"Intervention"的所有具有电池，列或电梯的建筑物.

tl;dr; I want to do a linq query to list all the buildings that have either a battery, column or elevator whose's status column is at "Intervention".

更新:显然，这在某种程度上可行，但是效率不高:

UPDATE: apparently this somehow works, but not efficiently :

var myintervention = _context.buildings.Where(a => a.batteries.SelectMany(b => b.columns.SelectMany(c => c.elevators)).Any(c => c.status == "Intervention")).ToList();

似乎不正确?有时会有电池介入，与这些电池关联的建筑物没有出现在建筑物列表中.电梯或专栏也一样..我有点迷路了！

It seems as though it's not accurate? Sometimes there are batteries in intervention and the building associated with those batteries doesn't appear in the building list. Same goes for elevators or columns.. I'm a bit lost!

更新2:这是我的请求:

UPDATE 2 : here's my request :

[HttpGet("intervention")]
        public List<Building> Getintervention(string status)
        {
            var myintervention = _context.buildings.Where(c => c.batteries.SelectMany(z => z.columns).Any(z => z.status == "Intervention")).ToList();
            return myintervention;
        }

推荐答案

var vara = _context.maintable.Where(a => a.tablerelatedtomain.SelectMany(b => b.tablerelatedtoprevious).SelectMany(c => c.tablerelatedtoprevious).Any(c => c.status == "mystring")).ToList();
var varb = _context.maintable.Where(a => a.tablerelatedtomain.SelectMany(b => b.tablerelatedtoprevious).Any(b => b.status == "mystring")).ToList();
var varc = _context.maintable.Where(a => a.tablerelatedtomain.Any(a => a.status == "mystring")).ToList();
var result = vara.Union(varb).Union(varc).OrderBy(z => z.id).ToList();

return result;

这篇关于如何在C#中使用Linq从多个嵌套列中选择特定字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！