C# linq IQueryable multiple filtering

Question

I cant find a way how to do a multiple filtering with LINQ.

what i want to do:

1.Filter the Version(Achieved)

2.Filter down the CompanyName(filtering/removing duplicates)

3.Get the Latest Timestamp

4.Then add all to a List.

Here is so far the code that i have written(which is not working).

public List<ReleaseStatistics> GetReleaseStatistics(IQueryable<InstallationInformation> input, IQueryable<DBContext.Version> mapping)
        {
            List<ReleaseStatistics> Releasestats = new List<ReleaseStatistics>();            
            foreach (var version in mapping)
            {
                IQueryable<InstallationInformation> QueryResult1 = input.Where(x => x.ProductVersion == version.VersionNumber);
 
                IQueryable<InstallationInformation> QueryResult2 = QueryResult1.GroupBy(x => x.CompanyName).SelectMany(y => y);

                List<InstallationInformation> ListofInstallationInformation = QueryResult2.ToList<InstallationInformation>();


                if (ListofInstallationInformation.Count >= 1)
                {
                    Releasestats.Add(new ReleaseStatistics
                    {
                        ReleaseVersion = version.ReleaseName,
                        CustomerCount = QueryResult1.Where(x => x.ProductVersion == version.VersionNumber).Count()
                    });
                }
            }  

            return Releasestats;
        }

Addition information:

One of the problem is that there are duplicate and i want to Filter/remove them, but i want to get the latest timestamp of each CompanyName and then add it to the list.

So the `QueryResult2 ` got duplicated? You might use DistinctBy in [MoreLinq](https://stackoverflow.com/a/11811129/3789481) to remove these duplicated. — Tấn Nguyên, Jul 09 '20 at 10:17
but the problem is the duplicate CompanyNames have there own unique Timestamps. So i need for each Company only 1 Duplicate which from all Duplicate the Latest Timestamp. There could also be Multiple Companys: for example Comanyname = C1 duplicate count is 5 but each duplicate has a unique Timestamp — johhny, Jul 09 '20 at 10:31
@johnny you might give more details your input/desired output/unwanted output to make sure that we are thinking rightway. — Tấn Nguyên, Jul 09 '20 at 14:19

ddfra · Answer 1 · 2020-07-10T06:57:26.240

the problem is that the line

IQueryable<InstallationInformation> QueryResult2 = QueryResult1.GroupBy(x => x.CompanyName).SelectMany(y => y);

actually does nothing.

Suppose QueryResult1 is

CompanyName | F1 | F2 |

CN1 | f1a | f2a |

CN2 | f1b | f2b |

then QueryResult1.GroupBy(x => x.CompanyName) is

Group | Data

CN1 | CompanyName | F1 | F2 |

     CN1          |  f1a | f2a |

     CN1          |  f1a | f2a |

CN2 | CompanyName | F1 | F2 |

     CN2          |  f1b | f2b |

then QueryResult1.GroupBy(x => x.CompanyName).SelectMany(y => y); is again

CompanyName | F1 | F2 |

CN1 | f1a | f2a |

CN2 | f1b | f2b |

what you want to do is probably

var QueryResult2 = QueryResult1.GroupBy(x => x.CompanyName).Select(y => new {CompanyName = y.Key, MaxTimestamp = y.Max(z => z.TimeStamp)});

System.Linq.IQueryable<<>" cannot be implicitly converted to "". There is already an explicit conversion (possibly a conversion is missing). — johhny, Jul 10 '20 at 06:25
yes.. beacause it returns an IEnumerable of dynamics. I edited the code: just use var QueryResult2 instead of IQueryable QueryResult2 — ddfra, Jul 10 '20 at 06:58

jdweng · Answer 2 · 2020-07-09T10:59:03.463

I did it with classes to simulate your query

           DBContext context = new DBContext();
            List<InstallationInformation> input = new List<InstallationInformation>();


            var query = (from m in context.mapping
                         join  i in input on  m.VersionNumber equals i.ProductVersion
                         select new { version = m.VersionNumber, companyName = i.CompanyName}
                        ).ToList();

            List<ReleaseStatistics> results = query.GroupBy(x => x.version).Select(x =>  new ReleaseStatistics() { ReleaseVersion = x.Key, CustomerCount = x.Distinct().Count() }).ToList();        

        }
    }
    public class DBContext
    {
        public List<Version> mapping { get; set; }
    }
    public class InstallationInformation
    {
        public int ProductVersion { get; set; }
        public string CompanyName { get; set; }
    }

    public class Version
    {
       public int VersionNumber { get; set; }
    }
    public class ReleaseStatistics
    {
        public int ReleaseVersion { get; set; }
        public int CustomerCount { get; set; }
    }

I need more info. What do you get in the query? I suspect the VersionNumber and ProductVersion are different so the join is failing. — jdweng, Jul 10 '20 at 09:33

score 0 · Answer 3 · answered Jul 10 '20 at 09:33

HERE WAS MY END RESULT:

public List<ReleaseStatistics> GetReleaseStatistics(IQueryable<InstallationInformation> input, IQueryable<DBContext.Version> mapping)
        {
            List<ReleaseStatistics> Releasestats = new List<ReleaseStatistics>();            
            foreach (var version in mapping)
            {
                IQueryable<InstallationInformation> QueryResult1 = input.Where(x => x.ProductVersion == version.VersionNumber);

                IQueryable<string> companynamecollection = QueryResult1.Select(x => x.CompanyName).Distinct();

                List<InstallationInformation> listofAggregatedInstallationInformation = new List<InstallationInformation>();

                foreach (var item in companynamecollection)
                {
                    var maxdatetime = QueryResult1.Where(x => x.CompanyName == item).Select(x => x.Timestamp).Max();
                    IQueryable<InstallationInformation> listofresult = QueryResult1.Where(y => y.CompanyName == item && y.Timestamp == maxdatetime);

                    foreach (var item2 in listofresult)
                    {
                        listofAggregatedInstallationInformation.Add(new InstallationInformation
                        {
                            InstallationInformationID = item2.InstallationInformationID,
                            LicenceKey = item2.LicenceKey,
                            ProductName = item2.ProductName,
                            ProductVersion = item2.ProductVersion,
                            CompanyName = item2.CompanyName,
                            Timestamp = item2.Timestamp,
                            ImportRunID = item2.ImportRunID
                        });
                    }
                }

                if (listofAggregatedInstallationInformation.Count >= 1)
                {
                    Releasestats.Add(new ReleaseStatistics
                    {
                        ReleaseVersion = version.ReleaseName,
                        CustomerCount = listofAggregatedInstallationInformation.Where(x => x.ProductVersion == version.VersionNumber).Count()
                    });
                }
            }  

            return Releasestats;
        }

C# linq IQueryable multiple filtering

3 Answers3