首页 新闻 会员 周边

如何屏蔽LinqTo EF的内联

0
悬赏园豆：10 [已关闭问题] 关闭于 2012-04-19 12:40

我的方法如下：

public IList<FittingInfoDTO> GetFittingList(IList<Expression<Func<FittingInfoDTO, bool>>> condition, int start, int limit, out int totalNum, string sort, string dir)
        {
            using (AutoDMSEntities ctx = new AutoDMSEntities())
            {
                var query = from c in ctx.T_SCM_4s_Fitting
                            join e in ctx.T_SCM_Fitting on c.F_FitID equals e.F_ID into e2
                            from e in e2.DefaultIfEmpty()
                            join d in ctx.T_SCM_4s_FittingStore on c.F_FitID equals d.F_FitID into d2
                            from d in d2.DefaultIfEmpty()
                            select new FittingInfoDTO
                            {
                                F_FitForBrand = e.F_FitForBrand,
                                F_FittingCode_In4s = c.F_FittingCode_In4s,
                                F_FitModel = e.F_FitModel,
                                F_FittingName = e.F_FittingName,
                                F_FitUnit = e.F_FitUnit,
                                F_ID = c.F_FitID.Value,
                                F_IsDel = c.F_IsDel,
                                F_IsStop = c.F_IsStop,
                                F_RetailPrice = c.F_SellPrice,
                                F_StoreAmount = d.F_StoreAmount,
                                F_OrgID=c.F_OrgID                                
                            };
                foreach (var obj in condition)//拼接查询条件                 {
                    query = query.Where(obj);
                }
                if (!string.IsNullOrEmpty(sort))//排序处理                 {
                    query = OrderByFiledString<FittingInfoDTO>(query, sort, dir);
                }
                else//排序条件为空的话，默认按照进厂日期降序排序                 {
                    query = query.OrderByDescending(c => c.F_ID);
                }
                totalNum = query.Count();
                query = query.Skip(start).Take(limit);
                return query.ToList();
            }
        }

condition是个判断条件数组，如果判断条件不来自于主表T_SCM_4s_Fitting，那么外联就会变成内联，求高手指点啊，拜托了!

魂殇的主页 魂殇 | 初学一级 | 园豆：154
提问于：2012-04-11 11:21
< >
分享
所有回答(2)
0

你可以考虑用导航属性，而不是join试试

alexstrasza | 园豆：115 (初学一级) | 2012-04-17 10:07

属性导航翻译成sql，是一种内联，我现在做的，是要左联，即便是右边没数据，也能查出右边的数据来

支持(0) 反对(0) 魂殇 | 园豆：154 (初学一级) | 2012-04-19 12:36
0 
from c in T_SCM_4s_Fitting
                            join e in T_SCM_Fitting on c.F_FitID equals e.F_ID into e2
                            from e in e2.DefaultIfEmpty()
                            from d in
                                (
                                    from f in T_SCM_4s_FittingStore
                                    where f.F_FitID == c.F_FitID
                                    group f by f.F_FitID into f2
                                    select new
                                    {
                                        f2.Key,
                                        F_StoreAmount = f2.Sum(g => g.F_StoreAmount)
                                    }
                                ).DefaultIfEmpty()
                            select new 
                            {
                                F_FitForBrand = e.F_FitForBrand,
                                F_FittingCode_In4s = c.F_FittingCode_In4s,
                                F_FitModel = e.F_FitModel,
                                F_FittingName = e.F_FittingName,
                                F_FitUnit = e.F_FitUnit,
                                F_ID = c.F_FitID.Value,
                                F_IsDel = c.F_IsDel,
                                F_IsStop = c.F_IsStop,
                                F_RetailPrice = c.F_SellPrice,
                                F_StoreAmount = d.F_StoreAmount,
                                F_OrgID = c.F_OrgID
                            }

这种方式能解决上述问题了，试了很多方法，终于解决了，生成的sql是这样的

SELECT 
1 AS [C1], 
[Extent2].[F_FitForBrand] AS [F_FitForBrand], 
[Extent1].[F_FittingCode_In4s] AS [F_FittingCode_In4s], 
[Extent2].[F_FitModel] AS [F_FitModel], 
[Extent2].[F_FittingName] AS [F_FittingName], 
[Extent2].[F_FitUnit] AS [F_FitUnit], 
[Extent1].[F_FitID] AS [F_FitID], 
[Extent1].[F_IsDel] AS [F_IsDel], 
[Extent1].[F_IsStop] AS [F_IsStop], 
[Extent1].[F_SellPrice] AS [F_SellPrice], 
[GroupBy1].[A1] AS [C2], 
[Extent1].[F_OrgID] AS [F_OrgID]
FROM   [dbo].[T_SCM_4s_Fitting] AS [Extent1]
LEFT OUTER JOIN [dbo].[T_SCM_Fitting] AS [Extent2] ON [Extent1].[F_FitID] = [Extent2].[F_ID]
OUTER APPLY  (SELECT 
    [Extent3].[F_FitID] AS [K1], 
    SUM([Extent3].[F_StoreAmount]) AS [A1]
    FROM [dbo].[T_SCM_4s_FittingStore] AS [Extent3]
    WHERE [Extent3].[F_FitID] = [Extent1].[F_FitID]
    GROUP BY [Extent3].[F_FitID] ) AS [GroupBy1]
魂殇 | 园豆：154 (初学一级) | 2012-04-19 12:39
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。