Convert flat db row data to nested typed objects linq

Question

I'm getting the results of a sql outer join as flat results in an IEnumerable, and would like to convert them to nested typed objects in linq. From something like this:

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

to something like this:

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

I'm currently trying a solution with 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();
        }

but it doesn't feel great, especially with all the value.First() I'm using to get the values that only belong to each grouped company. Is there something more appropriate? Group join sounded more like what I wanted, but I'm having trouble understanding how to apply it to a single list. I'm open to using query syntax instead of the lambdas if that's easier.

I'm trying to go from this model (where company-related info will be duplicated for each outer joined industry result):

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

to this:

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

Answer 1

// FULL edit after providing your models

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);
        }
    }
}

output:

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)

edit:

alternatively you can do as below, however the "first" thing still occurs somewhere, I have tried also the GroupJoin but it doesn't really help in that case.

    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();

edit:

one more approach without using "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();