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

it would be helpful to add you model in code to the question. — Falco Alexander
– Falco Alexander, Commented Dec 7, 2018 at 18:54

M G · Accepted Answer · 2018-12-08 08:29:31Z

5

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

edited Dec 8, 2018 at 8:29

answered Dec 7, 2018 at 19:04

M G

1541 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

J EC Over a year ago

Thanks, but I'm wondering is there a way get the CompanyName field using Select as well, or something other than just grabbing the first value as I did? I don't think I can use it as a composite key, since the name is allowed to be null in this case.

M G Over a year ago

You can grup by multiple properties (in that case anonymous object), please check my edited code

M G Over a year ago

Sorry, i have missed the fact you can't use composite key. Please check my editted answer. While in my group by still "first" occurs I think it is a little bit simpler as it does not contain nested group by. Alternatively the second linq with where and then group by. Hope that helps

J EC Over a year ago

Thanks, I agree yours looks a lot nicer, but I'm getting duplicate industries without the nested groupby :( I'm surprised there isn't something more abstract baked in to C#/linq.

M G Over a year ago

I don't realy know its features but you may be interested in MoreLinq morelinq.github.io, check their docs, maybe there is something worth trying

|

Collectives™ on Stack Overflow

Convert flat db row data to nested typed objects linq

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related