将扁平的数据库行数据转换为嵌套的类型对象 LINQ

Question

将扁平的数据库行数据转换为嵌套的类型对象 LINQ

3

我正在以IEnumerable的形式获取SQL外连接的结果，希望在LINQ中将它们转换为嵌套的类型化对象。从类似这样的结果开始：

[{id: 1, industryId: 1}, {id:1, industryId: 2}, {id:2, industryId: 1} etc..]

转换为如下格式：

list of Company [{id: 1, list of Industry{industryId: 1, 2}, {id: 2, list of Industry{industryId: 1}}]

我目前正在尝试使用GroupBy来解决问题：

Companies = flatDbRows
                .GroupBy(
                row => row.CompanyId,
                (key, value) => new CompanyModel
                {
                    CompanyId = value.First().CompanyId,
                    CompanyName = value.First().CompanyName,
                    Industries = value
                        .GroupBy(
                            row => new { row.IndustryId, row.Industry },
                            (k, v) => new IndustryModel() { IndustryId = k.IndustryId, Name = k.Industry }
                        )
                        .Where(x => x.IndustryId != 0)
                        .ToList(),
                }).ToList();
        }

但是，使用First()获取每个分组公司所拥有的值并不感觉很好，尤其是考虑到所有价值。是否有更合适的方式？Group join听起来更符合我的需求，但我不知道如何将其应用于单个列表。如果这样更容易，我可以使用查询语法而不是lambda。

我试图从以下模型转换（其中与公司相关的信息将为每个外部连接的行业结果重复）：

public class CompanyFlatDbRowsModel
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public int IndustryId{ get; set; }
    public string Industry { get; set; }
}

转化为：

public class CompanyModel
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public IEnumerable<IndustryModel> Industries { get; set; }
}

- J EC

2

在问题中添加您的代码模型会很有帮助。 - Falco Alexander

谢谢，已完成。@FalcoAlexander - J EC

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- M G · Accepted Answer

// 提供您的模型后进行完整编辑

public class TestClass
{
    public class CompanyModel
    {
        public int CompanyId { get; set; }
        public string CompanyName { get; set; }
        public List<IndustryModel> Industires { get; set; }
    }

    public class IndustryModel
    {
        public int IndustryId { get; set; }
        public string IndustryName { get; set; }
    }

    public class CompanyFlatDbRowsModel
    {
        public CompanyFlatDbRowsModel()
        {
        }

        public int CompanyId { get; set; }
        public string CompanyName { get; set; }
        public int IndustryId { get; set; }
        public string Industry { get; set; }
    }

    [Fact]
    public void Test()
    {
        var data = new List<CompanyFlatDbRowsModel>
        {
            new CompanyFlatDbRowsModel
            {
                CompanyId = 1,
                CompanyName = "Company 1",
                IndustryId = 1,
                Industry = "Industry 1"
            },
            new CompanyFlatDbRowsModel
            {
                CompanyId = 1,
                CompanyName = "Company 1",
                IndustryId = 2,
                Industry = "Industry 2"
            },
            new CompanyFlatDbRowsModel
            {
                CompanyId = 2,
                CompanyName = "Company 2",
                IndustryId = 3,
                Industry = "Industry 3"
            },
            new CompanyFlatDbRowsModel
            {
                CompanyId = 2,
                CompanyName = "Company 2",
                IndustryId = 4,
                Industry = "Industry 4"
            },
        };

        var result = data.GroupBy(x => x.CompanyId)
            .Select(x => new CompanyModel()
            {
                CompanyId = x.Key,
                CompanyName = x.First().CompanyName,
                Industires = x.Select(y=> new IndustryModel
                {
                    IndustryName = y.Industry,
                    IndustryId = y.IndustryId
                }).ToList()
            }).ToList();

        foreach (var item in result)
        {
            var text = $"Company id : {item.CompanyId}, industries : {string.Join(',',item.Industires.Select(x=>$"(name: {x.IndustryName}, id: {x.IndustryId})"))}";
            Debug.WriteLine(text);
        }
    }
}

输出：

Company id : 1, industries : (name: Industry 1, id: 1),(name: Industry 2, id: 2)
Company id : 2, industries : (name: Industry 3, id: 3),(name: Industry 4, id: 4)

编辑：

或者你可以按照以下方式操作，但是“第一个”问题仍然会在某个地方出现，我也尝试过GroupJoin，但在这种情况下并没有真正帮助。

    var otherResult = data.Select(x =>
        new CompanyModel
        {
            CompanyId = x.CompanyId,
            CompanyName = x.CompanyName,
            Industires = data
                .Where(y => y.CompanyId == x.CompanyId)
                .Select(y => new IndustryModel
                {
                    IndustryId = y.IndustryId,
                    IndustryName = y.Industry
                }).ToList()
        })
        .GroupBy(y => y.CompanyId)
        .Select(x => x.First())
        .ToList();

编辑：

还有一种方法可以不使用“first”来实现。

    var anotherResult = data.GroupBy(x => x.CompanyId)
        .Select(x =>
        {
            var companyModel = new CompanyModel()
            {
                CompanyId = x.Key
            };

            companyModel.Industires = x.Select(y =>
            {
                companyModel.CompanyName = y.CompanyName; // assignign here occurs multiple times however with the same value
                return new IndustryModel
                {
                    IndustryId = y.IndustryId,
                    IndustryName = y.Industry
                };
            }).ToList();

            return companyModel;
        }).ToList();