EF联合查询的新用法

用EF很多年了,做联合查询时,只知道linq和lambda两种语法,今天朋友发了一个链接,打开看后发现是EF内置的新的关于联合查询的方法,赶紧抄录下来,以备后用。

现在先把这几种方法,各写一个例子,便于大家参考:

linq:

                var re = from o in db.Order
                         join d in db.OrderDetail
                         on o.Code equals d.OrderCode
                         join p in db.Product
                         on d.ProductID equals p.id
                         where o.Code == "20150326000096"
                         select new {
                            OrderCode = o.Code,
                            ProductName = p.Name,
                            SalePrice = p.SalePrice,
                            ProductQuantity = d.Quantity,
                            Amount = d.Amount
                         };

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[OrderCode] AS [OrderCode], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[SalePrice] AS [SalePrice], 
    [Extent1].[Quantity] AS [Quantity], 
    [Extent1].[Amount] AS [Amount]
    FROM  [dbo].[OrderDetails] AS [Extent1]
    INNER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ProductID] = [Extent2].[id]
    WHERE ([Extent1].[OrderCode] IS NOT NULL) AND (N'20150326000096' = [Extent1].[OrderCode])


lambda:

re = db.OrderDetail.Where(d => d.OrderCode == "20150326000096").Select(d => new {
                    OrderCode = d.Order.Code,
                    ProductName = d.Product.Name,
                    SalePrice = d.Product.SalePrice,
                    ProductQuantity = d.Quantity,
                    Amount = d.Amount               
                });

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[OrderCode] AS [OrderCode], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[SalePrice] AS [SalePrice], 
    [Extent1].[Quantity] AS [Quantity], 
    [Extent1].[Amount] AS [Amount]
    FROM  [dbo].[OrderDetails] AS [Extent1]
    INNER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ProductID] = [Extent2].[id]
    WHERE N'20150326000096' = [Extent1].[OrderCode]


内置join方法:

var re = db.Employees.Join(db.Orders, e => e.EmployeeID, o => o.EmployeeID, (e, o) => new { EmployeeID = e.EmployeeID, OrderID = o.OrderID, Address = o.ShipAddress, EmployeeAddress = e.Address });
SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent2].[OrderID] AS [OrderID], 
    [Extent2].[ShipAddress] AS [ShipAddress], 
    [Extent1].[Address] AS [Address]
from  [dbo].[Employees] AS [Extent1]
INNER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[EmployeeID]= [Extent2].[EmployeeID]


内置Join用法2:

var re = db.Employees.Join(db.Orders, e => e.EmployeeID, o => o.EmployeeID, (e, o) => new { EmployeeID = e.EmployeeID, OrderID = o.OrderID });
SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[EmployeeID] IS NOT NULL


内置的GroupJoin方法:

var re = db.Categories.GroupJoin(db.Products, c => c.CategoryID, p => p.CategoryID, (c, p) => new { CategoryID = c.CategoryID, ProductList = p });
SELECT 
[Project1].[CategoryID] AS [CategoryID], 
[Project1].[C1] AS [C1], 
[Project1].[ProductID] AS [ProductID], 
[Project1].[ProductName] AS [ProductName], 
[Project1].[SupplierID] AS [SupplierID], 
[Project1].[CategoryID1] AS [CategoryID1], 
[Project1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Project1].[UnitPrice] AS [UnitPrice], 
[Project1].[UnitsInStock] AS [UnitsInStock], 
[Project1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Project1].[ReorderLevel] AS [ReorderLevel], 
[Project1].[Discontinued] AS [Discontinued]
from ( SELECT 
    [Extent1].[CategoryID] AS [CategoryID], 
    [Extent2].[ProductID] AS [ProductID], 
    [Extent2].[ProductName] AS [ProductName], 
    [Extent2].[SupplierID] AS [SupplierID], 
    [Extent2].[CategoryID] AS [CategoryID1], 
    [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Extent2].[UnitPrice] AS [UnitPrice], 
    [Extent2].[UnitsInStock] AS [UnitsInStock], 
    [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Extent2].[ReorderLevel] AS [ReorderLevel], 
    [Extent2].[Discontinued] AS [Discontinued], 
    CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL ASint) ELSE 1 END AS [C1]
    FROM  [dbo].[Categories] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]
)  AS [Project1]
ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC


Join和GroupJoin方法还没用过,代码是照搬的,以后应用中发现了什么再来修改吧,先贴出来备用

参考文章:EntityFramework查询--联合查询(Join,GroupJoin)


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值