如何翻译SQL"WHERE expr IN(查询)"?进入LINQ? [英] How to Translate SQL "WHERE expr IN (query)" into LINQ?

问题描述

基本上我想使用linq进行此SQL查询:

Basically I want to make this SQL query with linq:

SELECT * 
FROM Orders 
WHERE Identifier IN (SELECT DISTINCT [Order] FROM OrderRows WHERE Quantity = '1')

这是我想出的:

var q = from o in db.Orders 
     where o.Identifier in (from r in db.OrderRows 
                           where r.Quantity == 1 select r.Order).Distinct());

但是 o.Identifier 之后的 in 无效.

关键字IN的正确语法是什么?

What is the correct syntax for the keyword IN?

推荐答案

我有点迟了，但是我做了一个演示！

I'm a little late, but I made a demo!

正如其他人所说，我总是使用Contains:

As other people have stated, I always use Contains:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ContainsExample
{
    class Program
    {
        static void Main(string[] args)
        {
            var foos = new List<Foo>
            {
                new Foo { ID = 1, FooName = "Light Side" },
                new Foo { ID = 2, FooName = "Dark Side" }
            };

            var bars = new List<Bar>
            {
                new Bar { ID = 1, BarName = "Luke", FooID = 1 },
                new Bar { ID = 2, BarName = "Han", FooID = 1 },
                new Bar { ID = 3, BarName = "Obi-Wan", FooID = 1 },
                new Bar { ID = 4, BarName = "Vader", FooID = 2 },
                new Bar { ID = 5, BarName = "Palpatine", FooID = 2 },
                new Bar { ID = 6, BarName = "Fett", FooID = 2 },
                new Bar { ID = 7, BarName = "JarJar", FooID = 3 }
            };

            var criteria = from f in foos
                           select f.ID;

            var query = from b in bars
                        where criteria.Contains(b.FooID)
                        select b;

            foreach (Bar b in query)
            {
                Console.WriteLine(b.BarName);
            }

            Console.WriteLine();
            Console.WriteLine("There should be no JarJar...");

            Console.ReadLine();
        }
    }

    public class Foo
    {
        public int ID { get; set; }
        public string FooName { get; set; }
    }

    public class Bar
    {
        public int ID { get; set; }
        public string BarName { get; set; }
        public int FooID { get; set; }
    }   
}

这篇关于如何翻译SQL"WHERE expr IN(查询)"?进入LINQ?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！