Convert Datatable to nested list/object using C#

Question

I have a datatable as this. and data model as

public class Details
    {

        public String Id { get; set; }
        public String Type { get; set; }
        public String Name { get; set; }
        public String Terms { get; set; }
        public List<FAQ> Faqs { get; set; }
    }

    public class FAQ
    {
        public string Question { get; set; }
        public string Answer { get; set; }
    }

I want to convert it to a list type of details like List<Details> detatilsList; I tried like this but the rows are repeating. If i remove Details than I get distinct result but cant convert it to Details type due to anonymous cast exception

 var details = dt.AsEnumerable().GroupBy(x =>
                new Details //removing this detail gives distinct record as expected. But can not cast
                {
                    ID= x.Field<decimal>("ID"),
                    NAME = x.Field<string>("NAME"),
                    TYPE = x.Field<string>("TYPE"),
                    TERMS = x.Field<string>("TERMS")
                })
            .Select(x =>
                new
                {
                    x.Key.ID,
                    x.Key.NAME,
                    x.Key.TYPE,
                    x.Key.TERMS,
                    Faqs =
                    x.Select(
                        s => new Faq {Question = s.Field<string>("QUESTION"), Answer = s.Field<string>("ANSWER")})

                }).ToList();

How can i fix it and convert it to List of Details

You need to Select your Details, and GroupBy the correct grouping elements. — Sam Axe
– Sam Axe, Commented Oct 16, 2019 at 17:26

user1672994 · Accepted Answer · 2019-10-16 17:45:36Z

1

You can group By by anonymous object for multiple keys then select the required columns from DataTable

The below code first groups the data using ID , Name, Type and Terms and later in Select clause, creates new Details object.

 var details = dt.AsEnumerable().GroupBy(x =>
            new
            {
                ID= x.Field<decimal>("ID"),
                NAME = x.Field<string>("NAME"),
                TYPE = x.Field<string>("TYPE"),
                TERMS = x.Field<string>("TERMS")
            })
        .Select(x =>
            new Details
            {
                x.Key.ID,
                x.Key.NAME,
                x.Key.TYPE,
                x.Key.TERMS,
                Faqs =
                x.Select(
                    s => new Faq {Question = s.Field<string>("QUESTION"), Answer = s.Field<string>("ANSWER")}).ToList()
            }).ToList();

Another point, if Id itself is unique then you don't need to group by multiple columns, you can group by ID only.

edited Oct 16, 2019 at 17:45

answered Oct 16, 2019 at 17:31

user1672994

10.9k1 gold badge25 silver badges35 bronze badges

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

2 Comments

kartoos khan Over a year ago

I also tried like this but it says Cannot implicitly convert type system.collection.generic to system.generic.list<FAQ> on this line x.Select( s => new Faq {Question = s.Field<string>("QUESTION"), Answer = s.Field<string>("ANSWER")})

user1672994 Over a year ago

Added ToList to select FAQs. With the update the details with FAQ will be populated

Collectives™ on Stack Overflow

Convert Datatable to nested list/object using C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related