加载孩子而不是无望的加入 [英] Load child instead of hopeless join

问题描述

我有一点问题，我正在尝试加载子实体而不使用连接。采取以下场景，1条带有3条子记录的记录，我想将这些数据加载到我的小1..n实体中。通常这将创建一个具有所有典型开销的大连接，然后相应地填充它们，而不是非常LINQuish。

我创建了一个小型演示：

Hi there

I have a little issue here, I'm trying to load child entities without using a join. Take the following scenario, 1 record with 3 child records, I want to load that data into my small 1..n entity. Normally this will create one big join with all the typical overhead and then populate them accordingly , not very LINQuish.

I've created a small demo:

class Program
    {
        static void Main(string[] args)
        {

            using(var context = new DataClasses1DataContext() )
            {
                var c = from p in context.ProductCategories
                        select new ProductCat
                        {
                            ProductCatID = p.ProductCategoryID,
                            CatName = p.Name,
                            SubCatalogs = (from sub in p.ProductSubcategories
                                          select new ProductSubCat
                                              {
                                              ProductSubCatID = sub.ProductSubcategoryID,
                                              SubCatName = sub.Name
                                              }).ToList()
                                                                                   
                        };

                var result = c.ToList();

            };
        }
    }

    public class ProductCat
    {
        public int ProductCatID { get; set; }
        public string CatName { get; set; }
        public IList<ProductSubCat> SubCatalogs { get; set; }
    }

    public class ProductSubCat
    {
        public int ProductSubCatID { get; set; }
        public string SubCatName { get; set; }

    }

您可以看到我正在尝试获取父项，然后为每个父项执行查找。在一个更现实的场景中，我只想获取单个ProductCatagory以及子类 - 但这只是一个演示:)

这是一种延迟加载，但加载了

从分析器：
$

You can see I'm trying to get the parent and then for each parent do a lookup. In a more realistic scenario I just want to fetch a single ProductCatagory along with the subcats - but this is only a demo :)

It is a sort of a deferred load, but loaded


From the profiler:

SELECT [t0].[ProductCategoryID] AS [ProductCatID], [t0].[Name] AS [CatName], [t1].[ProductSubcategoryID] AS [ProductSubCatID], [t1].[Name] AS [SubCatName], (
    SELECT COUNT(*)
    FROM [Production].[ProductSubcategory] AS [t2]
    WHERE [t2].[ProductCategoryID] = [t0].[ProductCategoryID]
    ) AS [value]
FROM [Production].[ProductCategory] AS [t0]
LEFT OUTER JOIN [Production].[ProductSubcategory] AS [t1] ON [t1].[ProductCategoryID] = [t0].[ProductCategoryID]
ORDER BY [t0].[ProductCategoryID], [t1].[ProductSubcategoryID]

如果我这样做：

 
If I just do like this:

                var c = (from p in context.ProductCategories
                         select p).ToList();

然后调试，鼠标点击'c' - >扩展到subcat我可以模拟我想要的东西:)

and then debug, mouseclick 'c' -> expand until subcat I can simulate what I want :)

推荐答案

哼...我玩了一点，想出了以下：

hum... I played a bit more and came up with the following:

var c = (from p in context.ProductCategories.Where(x => x.ProductCategoryID == 1).ToList()
                         let sub = p.ProductSubcategories
                         select new ProductCat
                         {
                             ProductCatID = p.ProductCategoryID,
                             CatName = p.Name,
                             SubCatalogs = (from s in sub
                                           select new ProductSubCat
                                               {
                                                   ProductSubCatID = s.ProductSubcategoryID
                                               }).ToList()
                         }).ToList();

有趣的是ough，如果我删除第一个x.ProductCategoryID == 1）.ToList（），那么它会再次创建其中一个愚蠢的连接。

请随意评论或建议更好的方法:)

The funny thing is though, if I remove the first x.ProductCategoryID == 1).ToList(), then it creates one of those stupid joins again.

Please feel free to comment or suggest a better approach still :)

这篇关于加载孩子而不是无望的加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！