LINQ to Entities Group By and then select the min value

Question

I'm using VS2012 and SQL Compact Edition and I have difficult time to create my database. I'm trying to use the designer from VS2012 but I still can't do simple things like defining Foreign keys and stuff like that, but I need to prepare a Demo fast so instead I decided to leave the learning for later and for now I work with only one table in my database (it's a small project) and I have less than 500 rows so it's not that big of a problem. However I need to do this :

I have insurance policies everyone of which has unique Policy number. But for one Policy there might be several dates when the customer need to make payments. So based on the information above in my table I have say four rows with different IDs but with the same Policy number and different date for each record.

I use MDI Windows Forms and I have a form where I show info for all clients and here comes the problem. If one policy has several dates or in other words several records in my table I want to show only the record with the upcoming date, and only if the user clicks "details" to show all date for this insurance policy.

So what I need is to query the table group the results by PolicyNumber and for each policy that has several dates to take the record with the most recent date.

For now I have this:

 using (RalBaseEntities ctx = new RalBaseEntities())
            {
                var mainGridInfo = from cs in ctx.MainInfo
                                   where cs.Id != null
                                   select cs;

                IList<MainInfo> mainGridInfoList = mainGridInfo.ToList<MainInfo>();
                dgvClients.DataSource = mainGridInfoList;
            }

So having this I try to use group by like this:

var mainGridInfo = from cs in ctx.MainInfo
                                   where cs.Id != null
                                   group cs by cs.PolicyNumber into test
                                   select test;

But then I get this marked as error : IList mainGridInfoList = mainGridInfo.ToList<MainInfo>();

And the error is :

Error   1   'System.Linq.IQueryable<System.Linq.IGrouping<string,SHAdmin.MainInfo>>' does not contain a definition for 'ToList' and the best extension method overload 'System.Linq.ParallelEnumerable.ToList<TSource>(System.Linq.ParallelQuery<TSource>)' has some invalid arguments  C:\Users\X-Man\Desktop\SHAdmin\SHAdmin\Forms\Clients.cs 44  52  SHAdmin

And even if this worker I still don't know how to extract only the record with the most recent date.

I don't think this question should have been such lengthy. Just post what you want and a piece of code you have tried. — King King, Aug 11 '13 at 16:04
if you didn't specify the type ``, just `ToList()`, you would run it successfully, however that is a list of `>` which might not be what you actually want. — King King, Aug 11 '13 at 16:12
Well I was secretly hoping someone to give hint about good places where I can find tutorials on how to design my database using VS2012 and SQL CE. I have several projects but have always used MS SQL 2008 + ADO.NET EF and Code First and now I see that I lack lots of basic knowledge. — Leron, Aug 11 '13 at 16:15
Related: http://stackoverflow.com/questions/16273485/entity-framework-select-one-of-each-group-by-date — Danny Varod, Jan 13 '15 at 15:42

score 4 · Accepted Answer · answered Aug 11 '13 at 16:01

Your query:

var mainGridInfo = from cs in ctx.MainInfo
                   where cs.Id != null
                   group cs by cs.PolicyNumber into test
                   select test;

returns IQueryable<IGrouping<string, MainInfo>>, so you can't call ToList<MainInfo> on it!

According to your requirements:

So what I need is to query the table group the results by PolicyNumber and for each policy that has several dates to take the record with the most recent date.

I think you're looking for:

var mainGridInfo = from cs in ctx.MainInfo
                   where cs.Id != null
                   group cs by cs.PolicyNumber into test
                   select test.OrderByDescending(cs => cs.Date).First();

And it will return IQueryable<MainInfo>, so you should be able to call ToList<MainInfo> on it.

It does exactly what I needed. Just - to fetch the nearest date it should be `test.OrderBy(cs => cs.Date)` when `OrderByDescending` I get the latest date. Thanks. — Leron, Aug 11 '13 at 16:12

score 0 · Answer 2 · answered Aug 11 '13 at 16:02

Try something like

var mainGridInfo = from cs in ctx.MainInfo
                                   where cs.Id != null
                                   group cs by cs.PolicyNumber into test
                                   select new { Key = test.Key, MainInfo = test};

LINQ to Entities Group By and then select the min value

2 Answers2