LINQ系列：LINQ to SQL Concat/Union

libingql發表於2014-10-25
SQL

1. Concat

　　單列Concat

var expr = (from p in context.Products
            select p.ProductName)
            .Concat(
            from c in context.Categories
            select c.CategoryName
            );
foreach (var item in expr)
{
    Console.WriteLine(item);
}
var expr = context.Products
    .Select(p => p.ProductName)
    .Concat(
    context.Categories
    .Select(c => c.CategoryName)
    );
SELECT 
    [UnionAll1].[ProductName] AS [C1]
    FROM  (SELECT 
        [Extent1].[ProductName] AS [ProductName]
        FROM [dbo].[Product] AS [Extent1]
    UNION ALL
        SELECT 
        [Extent2].[CategoryName] AS [CategoryName]
        FROM [dbo].[Category] AS [Extent2]) AS [UnionAll1]

　　多列Concat

var expr = context.Products
    .Select(p => new 
    {
        p.ProductID, 
        p.ProductName 
    })
    .Concat(
    context.Categories
    .Select(c => new 
    { 
        ProductID = c.CategoryID, 
        ProductName = c.CategoryName 
    })
    );
SELECT 
    [UnionAll1].[ProductID] AS [C1], 
    [UnionAll1].[ProductID1] AS [C2], 
    [UnionAll1].[ProductName] AS [C3]
    FROM  (SELECT 
        [Extent1].[ProductID] AS [ProductID], 
        [Extent1].[ProductID] AS [ProductID1], 
        [Extent1].[ProductName] AS [ProductName]
        FROM [dbo].[Product] AS [Extent1]
    UNION ALL
        SELECT 
        [Extent2].[CategoryID] AS [CategoryID], 
        [Extent2].[CategoryID] AS [CategoryID1], 
        [Extent2].[CategoryName] AS [CategoryName]
        FROM [dbo].[Category] AS [Extent2]) AS [UnionAll1]

2. Union

var expr = context.Products
    .Select(p => p.ProductName)
    .Union(
    context.Categories
    .Select(c => c.CategoryName)
    );
SELECT 
    [Distinct1].[C1] AS [C1]
    FROM ( SELECT DISTINCT 
        [UnionAll1].[ProductName] AS [C1]
        FROM  (SELECT 
            [Extent1].[ProductName] AS [ProductName]
            FROM [dbo].[Product] AS [Extent1]
        UNION ALL
            SELECT 
            [Extent2].[CategoryName] AS [CategoryName]
            FROM [dbo].[Category] AS [Extent2]) AS [UnionAll1]
    )  AS [Distinct1]

相關文章