locked
Pivot Linq To Sql RRS feed

  • 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

    Tuesday, May 5, 2015 5:47 PM

Answers

  • 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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 5, 2015 7:13 PM

All replies

  • 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!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 5, 2015 7:13 PM
  • User-309523270 posted

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

    Thanks for your help!

    Tuesday, May 5, 2015 7:28 PM