LINQ Tips - Join IQueryable with an IEnumerable

  • 结构关系图

IQueryable和IEnumerable的关系如下所示

class diagram

  • 测试环境

    • Windows Server 2016
    • SQL Server 2016
    • Entity Framework 6.0
class Program
{
    static void Main(string[] args)
    {
        using (var db = new NORTHWNDEntities()) {
            var ids = new string[] {
                "ALFKI",
                "BERGS",
                "CACTU"
            };
 
            var q = from c in db.Customers
                    join id in ids on c.CustomerID equals id
                    select c;
            var arr = q.ToList();
 
            foreach (var x in arr) {
                Console.WriteLine("{0} {1}", x.CustomerID, x.ContactName);
            }
        }
    }
}
  • 程序输出
ALFKI Maria Anders
BERGS Christina Berglund
CACTU Patricio Simpson
  • 实际的SQL
SELECT
    [Extent1].[CustomerID] AS [CustomerID],
    [Extent1].[CompanyName] AS [CompanyName],
    [Extent1].[ContactName] AS [ContactName],
    [Extent1].[ContactTitle] AS [ContactTitle],
    [Extent1].[Address] AS [Address],
    [Extent1].[City] AS [City],
    [Extent1].[Region] AS [Region],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Country] AS [Country],
    [Extent1].[Phone] AS [Phone],
    [Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]
    INNER JOIN (
        SELECT N'ALFKI' AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable1]
        UNION ALL
        SELECT N'BERGS' AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable2]
        UNION ALL
        SELECT N'CACTU' AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable3]
    ) AS [UnionAll2] ON [Extent1].[CustomerID] = [UnionAll2].[C1];