Pivot Linq To Sql

Question

User-309523270 posted

Hi,

I'm trying to create a crosstab like :

ProfileId       Population 1      Population 2       Population 3....Population x      Min
-----------    ---------------    ---------------      ---------------  ---------------    ---------------
1                        8000                10000                   5000                                         5000
2                        2000                1000                     7000                                         1000
3                        5000                6000                     7000                                         5000

where Population is dynamic and can range from 1 to xxx.

Referencing this post https://social.msdn.microsoft.com/Forums/en-US/fd619109-ed42-446c-8808-c5c8791f84bb/dynamic-linq-pivot?forum=csharplanguage

Code is as follows:

public static object PivotResults(List<ProfilePopulationResult> oResult)        
{
               var set = (from f in oResult
                               group f by new { f.ProfileId }
                               into myGroup
                               where myGroup.Count() > 0
                               select new
                               {        myGroup.Key.ProfileId,                                   
                                        LR = myGroup.GroupBy(f => f.PopulationId).Select(m => new { PopulationId = m.Key, Result = m.Sum(c => c.result) }),
                                        Min = myGroup.Min()                               
                               });

                return set;

        }

public class ProfilePopulationResult : ProfilePopulationResultComparer    
{         public int ProfileId { get; set; }        
          public int PopulationId { get; set; }
          public decimal result { get; set; }

          public static IComparer<ProfilePopulationResult> ProfilePopulationResultComparer()
          {            
               return (IComparer<ProfilePopulationResult>)new ProfilePopulationResultComparer();        
          }    
}

public class ProfilePopulationResultComparer : IComparer<ProfilePopulationResult>    
{        
        public int Compare(ProfilePopulationResult x, ProfilePopulationResult y)
        {            
             if (x.ProfileId < y.ProfileId)
            {                 return -1;             }

            if (x.ProfileId > y.ProfileId)
            {                 return 1;             }

            if (x.PopulationId < y.PopulationId)
            {                 return -1;             }

            if (x.PopulationId > y.PopulationId)
             {                 return 1;             }                        

              return 0;                    
}

However, I'm running into runtime error:

At least one object must implement IComparable.              Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.            
Exception Details: System.ArgumentException: At least one object must implement IComparable.
Source Error:

Line 419: into myGroup Line 420: where myGroup.Count() > 0 Line 421: select new Line 422: { Line 423: myGroup.Key.ProfileId,

 

Thanks for your help!

Best,

tinac99

Answer 1

User-309523270 posted

I was able to get rid of the error by removing Min = myGroup.Min().  Can anyone tell me how I can get the minimum Population for this Profile?

Thanks!

Answer 2

User-309523270 posted

MinNum = myGroup.Min(f => f.result)

Thanks for your help!