LINQ: Convert DataTable to User Defined Class containing array

Question

I have this object structure

public class Product
{
public int Id {get;set;}
public string DisplayName{get;set;}
public Ingredient[] Ingredents{get;set;}
}

public class Ingredient
{
 public int id{get;set;}
 public string Name{get;set;}
}

I have the datatable with the below result set

    Id | DisplayName  |  IngredientId | IngName
-------------+-------------------------+------------------------
    1  | Prod1         |   1           |  Ing1
    1  | Prod1         |   2           |  Ing2
    1  | Prod1         |   3           |  Ing3
    2  | Prod2         |   1           |  Ing1
    2  | Prod2         |   2           |  Ing2
    3  | Prod3         |   1           |  Ing1
    3  | Prod3         |   2           |  Ing2

I need to have LINQ query which would give me list of Products as below: // pseudo code which describes structure:

resultProductList =
{
  new Product() { Id = 1
                , DisplayName = "Prod1"
                , Ingredients = {
                                 new Ingredient{ Id = 1, Name = "Ing1" } 
                                , new Ingredient{ Id = 2, Name = "Ing2" } 
                                , new Ingredient{ Id = 3, Name = "Ing3" } 
                                }
 , new Product() { Id = 2
                , DisplayName = "Prod2"
                , Ingredients = {
                                 new Ingredient{ Id = 1, Name = "Ing1" } 
                                , new Ingredient{ Id = 2, Name = "Ing2" } 
                                }
 , new Product() { Id = 3
                , DisplayName = "Prod3"
                , Ingredients = {
                                 new Ingredient{ Id = 1, Name = "Ing1" } 
                                , new Ingredient{ Id = 2, Name = "Ing2" } 
                                }

}

I refered this but I am unable to get the solution.

List<Product> lstContracttype = dsGetProducts.Tables[0]
                                      .AsEnumerable()
                                      .Select(x => new Product
                                      {
                                          Id= Convert.ToInt32(x[0]),
                                          DisplayName= Convert.ToString(x[1])
                                      })
                                      .GroupBy(y => y.id).ToList();

Could anyone please suggest how could I write LINQ query for get this result?

UPDATE: I added one more sub-class and added it to main Product class. See below:

public class Product
    {
    public int Id {get;set;}
    public string DisplayName{get;set;}
    public Ingredient[] Ingredents{get;set;}
    public Price MyPrice{get;set;}
    }
public class Price
{
    public int min {get;set;}
    public int max {get;set;}
    public int defaultPrice {get;set;}
}

I modified the LINQ query as follows and am getting the results. My question is in below LINQ query, should I write .First<Price> or .FirstOrDefault<Price>? Is this proper way to handle this condition or is there any other way?

var lstProducts = dsGetProducts.Tables[0].AsEnumerable().Cast<DataRow>()
                  .GroupBy(r => new { Id = r["Id"], 
                                      DisplayName = r["DisplayName"], 
                                      priceMax = r["priceMax"],
                                      priceMin = r["priceMin"],
                                      priceDefault = r["priceDefault"]
                                    })
                  .Select(g => new Product()
                  {
                     Id = (int)g.Key.Id,
                     DisplayName = (string)g.Key.DisplayName,
                     Ingredients = g.Select(r => new Ingredient()
                     {
                        id = (int)r["IngredientId"],
                        Name = (string)r["IngName"]
                     }).ToArray(),
                     MyPrice =  g.Select(r => new Price()
                     {
                        min= (int)r["minPrice"],
                        max= (int)r["maxPrice"],
                        defaultPrice= (int)r["priceDefault"],
                     }).FirstOrDefault<Price>()

Matthias Meid · Accepted Answer · 2014-03-27 15:47:41Z

3

You need to group and then project:

dataTable.Rows.Cast<DataRow>()
    .GroupBy(r => new { Id = r["Id"], DisplayName = r["DisplayName"] })
    .Select(g => new Product()
    {
        Id = (int)g.Key.Id,
        DisplayName = (string)g.Key.DisplayName,
        Ingredients = g.Select(r => new Ingredient()
        {
            id = (int)r["IngredientId"],
            Name = (string)r["IngName"]
        }).ToArray()
    });

You need the Cast<DataRow>. DataTable has been around since .NET 1.1, where generics did not exist. The enumerator therefore yields object instances.

Taking comments into account, there is another option you can use, provided you've got .NET Framework 3.5 installed, using the Field<T> extension method in System.Data.DataSetExtensions. Be sure to add a reference to the assembly:

dataTable.Rows.Cast<DataRow>()
    .GroupBy(r => new { Id = r.Field<int>("Id"), DisplayName = r.Field<string>("DisplayName") })
    .Select(g => new Product()
    {
        Id = g.Key.Id,
        DisplayName = g.Key.DisplayName,
        Ingredients = g.Select(r => new Ingredient()
        {
            id = r.Field<int>("IngredientId"),
            Name = r.Field<string>("IngName")
        }).ToArray()
    });

edited Mar 27, 2014 at 15:47

answered Mar 21, 2014 at 15:21

Matthias Meid

12.5k7 gold badges48 silver badges79 bronze badges

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

10 Comments

Mrudang Vora Over a year ago

I am unable to find dsGetProducts.Tables[0].AsEnumerable().Rows where dsGetProducts is DataSet

sloth Over a year ago

@MrudangVora Just use dsGetProducts.Tables[0].AsEnumerable() instead of dsGetProducts.Tables[0].AsEnumerable().Rows

Matthias Meid Over a year ago

dsGetProducts.Tables[0].AsEnumerable() is already a collection of your typed rows. You can go straight to Cast<YourTypedRow>. Rows is the collection of untyped rows.

sloth Over a year ago

Since .NET 4.0 there's a new assembly (System.Data.DataSetExtensions.dll) which provides among others the AsEnumerable() extension method for DataTable so you don't need Rows.OfType<DataRow> or Rows.Cast<DataRow>. Also, the new Field<T> extension method allows you to access fields in a typed, safe manner (without manually casting/DBNull checks). You should use them in your code because as it is now, it will not compile (the values accessed via the indexer are all of type object).

Matthias Meid Over a year ago

@DominicKexel OK, thank you for the remark. I added some casts, but I agree that it's still a little fragile. It'd be great if you could post an example with Field<T>, as I haven't used it yet.

|

Collectives™ on Stack Overflow

LINQ: Convert DataTable to User Defined Class containing array

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related