CODEWITHSUNDEEP
c#
Ryan
Ryan

Reputation: 682

IOrderedEnumerable looping throws error about casting to IEnumerable

I have a method to get some data using Dapper and then group and average 2 columns in that list.

public IEnumerable<PriceSheetCompare> GetTopXFlatPriceSheets(int topValue, int? departmentId = null, int? locationID = null, int? priceCode = null)
        {
            IEnumerable<PriceSheetCompare> rows = null;
            using (connection)
            {
                string sqlFilter = "";
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT TOP(@topValue) SKU, Price as AvgPrice FROM PriceSheetHistoryFlatView WHERE ");

                if(departmentId != null)
                {
                    sqlFilter = "DepartmentID = @departmentId ";       
                }
                if(locationID != null)
                {
                    if(!String.IsNullOrEmpty(sqlFilter))
                    {
                        sqlFilter += "AND LocationID = @locationID ";
                    }
                    else
                    {
                        sqlFilter = "LocationID = @locationID ";
                    }
                }
                if(priceCode != null)
                {
                    if (!String.IsNullOrEmpty(sqlFilter))
                    {
                        sqlFilter += "AND PriceCode = @priceCode ";
                    }
                    else
                    {
                        sqlFilter = "PriceCode = @priceCode ";
                    }
                }
                sql.Append(sqlFilter);
                sql.Append("ORDER BY PriceSheetDate DESC");
                var sheet = connection.Query<PriceSheetCompare>(sql.ToString(), 
                            new { topValue, departmentId, locationID, priceCode });

                var groupedSheet = sheet.GroupBy(x => x.SKU).Select(p => new { SKU = p.Key, AvgPrice = (decimal)p.Average(a => a.AvgPrice) }).OrderBy(o => o.SKU);
                rows = (IEnumerable<PriceSheetCompare>)groupedSheet;
            }
            return rows;
        }

The code sheet.GroupBy returns an IOrderedIEnumerable object, and when I try to cast it to an IEnumerable object like this

rows = (IEnumerable<PriceSheetCompare>)groupedSheet;

to return the rows I get

System.InvalidCastException: 'Unable to cast object of type 'System.Linq.OrderedEnumerable2[<>f__AnonymousType32[System.String,System.Decimal],System.String]' to type 'System.Collections.Generic.IEnumerable`1

I need to return this object so I can loop and perform some other calculations but IOrderedIEnumerable does not seem to allow this the same way an IEnumerable does.

If I do return the IOrderedEnumerable and try to use it I get errors regarding casting anonymous types to other data types.

I don't understand how to use the IOrderedEnumerable that GroupBy returns.

All I need to do is use the grouped and averaged properties in some calculations.

Ryan

Upvotes: 0

Views: 228

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460228

The problem is that you create instances of an anonymous type but want to return instances of PriceSheetCompare. This should work since the properties match:

return sheet
    .GroupBy(x => x.SKU)
    .Select(p => new PriceSheetCompare{ SKU = p.Key, AvgPrice = (decimal)p.Average(a => a.AvgPrice) })
    .OrderBy(o => o.SKU);

Upvotes: 1

Related Questions