LINQ系列:LINQ to SQL Exists/In/Any/All/Contains

1. Any

  返回没有Product的Category

var expr = from c in context.Categories
            where !c.Products.Any()
            select c;
SELECT 
    [Extent1].[CategoryID] AS [CategoryID], 
    [Extent1].[CategoryName] AS [CategoryName]
    FROM [dbo].[Category] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Product] AS [Extent2]
        WHERE [Extent1].[CategoryID] = [Extent2].[CategoryID]
    )
var expr = from c in context.Categories
            where !c.Products.Any(p => p.UnitPrice > 10m)
            select c;
SELECT 
    [Extent1].[CategoryID] AS [CategoryID], 
    [Extent1].[CategoryName] AS [CategoryName]
    FROM [dbo].[Category] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Product] AS [Extent2]
        WHERE ([Extent1].[CategoryID] = [Extent2].[CategoryID]) AND ([Extent2].[UnitPrice] > cast(10 as decimal(18)))
    )

2. All

var expr = from c in context.Categories
           where c.Products.All(p => p.Discontinued)
           select c;
SELECT 
    [Extent1].[CategoryID] AS [CategoryID], 
    [Extent1].[CategoryName] AS [CategoryName]
    FROM [dbo].[Category] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Product] AS [Extent2]
        WHERE ([Extent1].[CategoryID] = [Extent2].[CategoryID]) AND ([Extent2].[Discontinued] <> cast(1 as bit))
    )

3. Contains

var expr = from p in context.Products
            where new string[] 
            { 
                "LINQ to Object",
                "LINQ to ADO.NET", 
                "LINQ to XML" 
            }
            .Contains(p.ProductName)
            select p;
SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[CategoryID] AS [CategoryID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent1].[UnitPrice] AS [UnitPrice], 
    [Extent1].[UnitsInStock] AS [UnitsInStock], 
    [Extent1].[Discontinued] AS [Discontinued]
    FROM [dbo].[Product] AS [Extent1]
    WHERE [Extent1].[ProductName] IN (NLINQ to Object, NLINQ to ADO.NET, NLINQ to XML)
var expr = from p in context.Products
            where !(new string[] 
            { 
                "LINQ to Object",
                "LINQ to ADO.NET", 
                "LINQ to XML" 
            })
            .Contains(p.ProductName)
            select p;
SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[CategoryID] AS [CategoryID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent1].[UnitPrice] AS [UnitPrice], 
    [Extent1].[UnitsInStock] AS [UnitsInStock], 
    [Extent1].[Discontinued] AS [Discontinued]
    FROM [dbo].[Product] AS [Extent1]
    WHERE  NOT ([Extent1].[ProductName] IN (NLINQ to Object, NLINQ to ADO.NET, NLINQ to XML))

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。