实体框架中具有一对多关系的左联接 [英] Left join in Entity Framework with 1-to-many relation

问题描述

我有五个表:

Coupons
Coupon_Redemptions
User_Coupon
Wash
Account

这是 SQL查询，可在 SQL Server 和 Entity Raw SQL 中使用，但无法使其与一起使用LINQ 因为延迟加载的 1对多发行版无法获取，因为User_coupons，Wash ...是集合.

This is SQL Query that works in SQL Server and Entity Raw SQL, but can't get it to work with LINQ because 1-to-many releations, with lazy loading can't manage to get it because User_coupons, Wash... are collections.

SELECT 
    C.Id AS "CouponId", C.Coupon_code AS "CouponCode", 
    C.Discount_amount AS "DiscountAmount", 
    C.Valid_for_all AS "ValidForAll", C.Expiration_date AS "ExpirationDate", 
    R.Redemption_date AS "RedemptionDate",
    U.Date_added AS "DateAddedToUser", W.Id AS "WashId", A.Name  
FROM 
    Coupon C
    LEFT JOIN User_coupon U on U.CouponId = C.Id
    LEFT JOIN Coupon_redemption R on R.CouponId = C.Id
    LEFT JOIN Wash W on W.CouponId = C.Id
    LEFT JOIN Account A on U.AccountId = A.Id

这是关系图的摘录

对此查询尝试过变体，它返回一行.但是看起来很完美.

Tried variation on this query, it returns one row. But it looks perfect.

var results =
                from c in db.Coupons
                from u in c.User_coupon.DefaultIfEmpty()
                from r in c.Coupon_redemption.DefaultIfEmpty()
                from w in c.Washes.DefaultIfEmpty()
                select new {
                    CouponId = c.Id,
                    CouponCode = c.Coupon_code,
                    DiscountAmount = c.Discount_amount,
                    ValidForAll = c.Valid_for_all,
                    ExpirationDate = c.Expiration_date,
                    RedemptionDate = r.Redemption_date,
                    DateAddedToUser = u.Date_added,
                    WashId = w.Id
                };

结果转换为字符串:

SELECT[Extent1].[Id] AS [Id], [Extent1].[Coupon_code] AS [Coupon_code], [Extent1].[Discount_amount] AS [Discount_amount], [Extent1].[Valid_for_all] AS [Valid_for_all], [Extent1].[Expiration_date] AS [Expiration_date], [Extent3].[Redemption_date] AS [Redemption_date], [Extent2].[Date_added] AS [Date_added], [Extent4].[Id] AS [Id1] 
FROM [dbo].[Coupon] AS [Extent1] 
LEFT OUTER JOIN [dbo].[User_coupon] AS [Extent2] ON [Extent1].[Id] = [Extent2].[CouponId]
LEFT OUTER JOIN [dbo].[Coupon_redemption] AS [Extent3] ON [Extent1].[Id] = [Extent3].[CouponId]
LEFT OUTER JOIN [dbo].[Wash] AS [Extent4] ON [Extent1].[Id] = [Extent4].[CouponId]

推荐答案

好吧，当包含它们的对象被实现时，它们就是集合(带有或不带有延迟加载)(即，您通过对象实例进行工作)).在LINQ to Entities查询中使用时，它们是简单的表导航(联接).

Well, they are collections (with or without lazy loading) when the object containing them is materialized (i.e. you work through object instance). When used inside LINQ to Entities queries, they are simple table navigations (joins).

规则很简单.要获得相当于SQL内部联接的集合导航属性，请使用

The rules are simple. To get the equivalent of SQL inner join for collection navigation property, you use

from child in parent.Collection

和分别用于左外部联接:

and respectively for left outer join:

from child in parent.Collection.DefaultIfEmpty()

对于参考导航属性，您不能明确指定联接的类型-它取决于关系是必需关系还是可选关系(由该关系控制).而且，除了 from 之外，还可以使用 let 或直接使用Navigation属性来获取等效的SQL查询联接.

For reference navigation property you can't specify explicitly the type of the join - it depends on (is controlled by) whether the relationship is required or optional. And instead of from, you can use let or directly the navigation property to get the SQL query join equivalent.

话虽如此，等效的LINQ查询将是这样的:

With that being said, the equivalent LINQ query would be something like this:

var query = 
    from c in dbContext.Coupons
    from u in c.User_coupon.DefaultIfEmpty()
    from r in c.Coupon_redemptions.DefaultIfEmpty()
    from w in c.Washes.DefaultIfEmpty()
    let a = u.Account
    select new
    {
        CouponId = c.Id,
        CouponCode = c.Coupon_code,
        DiscountAmount = c.Discount_amount,
        ValidForAll = c.Valid_for_all,
        ExpirationDate = c.Expiration_date, 
        RedemptionDate = r.Redemption_date,
        DateAddedToUser = u.Date_added,
        WashId = w.Id,
        Name = a.Name,          
    };

这篇关于实体框架中具有一对多关系的左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！