CODEWITHSUNDEEP
c#.netlinq
Aaron McKelvie
Aaron McKelvie

Reputation: 167

LINQ query on Datatable - Pulling multiple columns error

I have a datatable which holds 3 columns:

Product, Price, Manufacturer

I am trying to read the data into a list of objects, defining which rows are stored by the following:

Store all Products which have the cheapest price and take the manufacturer from that line.

EG-

Product, Price, Manufacturer
table, 15.00, ikea
table, 12.50, woodpty
chair, 11.00, ikea
chair, 9.00, woodpty

The expected output into the list is two objects with the following properties:

table, 12.50, woodpty
chair, 9.00, woodpty

I have the following code, but I am getting an error-

String does not contain a definition for 'Name' and no extension method 'Name' accepting a first argument of type 'string' could be found (are you missing an assembly reference?)

var result = (
    from row in dtProductListings.AsEnumerable()
    group row by row.Field<string>("Product") into g
    let x = new
    {
        Name = g.Key.Name, //THIS LINE IS CAUSING THE PROBLEM
        Price = g.Min(x => x.Field<float>("Price"))
    }
    where (row.Name == x.Name && row.Price == x.Price)
    select new Foo
    {
        Name = row.Name,
        Manufacturer = row.Manufacturer,
        Price = row.Price
    }
).ToList();

I am still quite new to LINQ and am wondering where I am going wrong? Any help would be greatly appreciated.

Upvotes: 1

Views: 478

Answers (2)

Adrian Sanguineti
Adrian Sanguineti

Reputation: 2495

Nice try but your attempt has a few issues. Firstly where you have used Name = g.Key.Name should be Name = g.Key, and secondly your Linq expression will fail to compile also because row is no longer in scope after the group by clause.

Linq can be a tricky thing to get your head around at the start, but what you're effectively trying to achieve is to group the rows by the product column and then select the row in each group which has the lowest price.

So to create the product groups:

var rowsGroups = from row in dtProductListings.AsEnumerable()
                 group row by row.Field<string>("Product") into g
                 select g.OrderBy(row => row.Price);

For your example, this will produce two groups of IOrderedEnumerable based on the product values, with the items in each group being order by lowest price to highest price.

Group 1: Key = "table"

  • Row 1: table, 12.50, woodpty
  • Row 2: table, 15.00, ikea

Group 2: Key = "chair"

  • Row 1: chair, 9.00, woodpty
  • Row 2: chair, 11.00, ikea

So now all your have to do to get your result is to select the first item in each group to get the minimum priced item:

var result = (from row in rowGroups
             select row.First())
             .ToList();

The same query using lambda expression and linq chain methods (which I find easier to write since it focuses you on the inputs and outputs of what your're doing):

var result = dtProductListings.AsEnumerable()
            .GroupBy(row => row.Field<string>("Product"))
            .Select(x => x.OrderBy(y => y.Price))
            .Select(x => x.First())
            .ToList();

Simplified further:

var result = dtProductListings.AsEnumerable()
            .GroupBy(row => row.Field<string>("Product"))
            .Select(x => x.OrderBy(y => y.Price).First())
            .ToList();

Upvotes: 1

Tony
Tony

Reputation: 7445

Try this:

var result = (
    from row in dtProductListings.AsEnumerable()
    group row by row.Field<string>("Product") into g
    select new
    {
        Name = g.Key,
        Price = g.Min(x => x.Field<float>("Price"))
        Manufacturer = g.First().Field<string>("Manufacturer")
    } 
).ToList();

Upvotes: 0

Related Questions