LINQ query to populate a List<T>

This LINQ query is populating the List, but I need to group by Code and then only display the record for each Code that has the highest integer value for the Version column.

Current query - returns all versions for each Code.

                from i in db.I
                join ai in db.AI on i.ID equals ai.ID into temp
                from fgi in temp.Where(f => f.Code == Code).DefaultIfEmpty()
                where i.Site == Site

My current attempt to group by Code and then order by descending Version and then only display the highest Version for each Code.  Each Code can have more than on version, but only the highest version is the current version I want to display.  I tried this query, but I now get not records returned.

                from i in db.I.GroupBy(x => x.Code).Select(x => x.OrderByDescending(y => y.Version).FirstOrDefault()).OrderByDescending(x => x.Version)
                join ai in db.AI on i.ID equals ai.ID into temp
                from fgi in temp.Where(f => f.Code == Code).DefaultIfEmpty()
                where(i.Site == Site)


I appreciate some feedback on why my attempt is not working.

Thanks
JBM2015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi JBM2015;

There is something missing in the first query, it should not compile because it is missing a select clause for one. Also the local variable fgi looks like it is not being used. Please copy and paste from your code in the project to here.

In your second, also not complete, you should always do any sorting at the end because method calls in the query will most likely reorder the results returning an unordered list.
JBM2015Author Commented:
This is the original query that works, but it returns all version rows for the main table (db.Ingr) in the query.

I need to modify the query to group by 'db.Ingr.Code' column and then only return the highest version number row from 'db.Ingr' table.


Thanks

public List<Ingr> GetIngr(string fCode, string pCode)
{
      Data db = new Data(Properties.Settings.Default.DatabaseConnectionString);

      //Orig LINQ query working, but returns all version rows for Ing:            
        IQueryable<Ingr> ingr =                
              from i in db.Ingr
                join ai in db.AIngr on i.ID equals ai.ID into temp
                from fgi in temp.Where(f => f.Code == fCode).DefaultIfEmpty()
                where i.PCode == pCode

            select new Ingr
                {
                      ID = i.ID,
                          Code = i.Code,
                          Descr = i.Descr,
                          Version = i.Version,
                          PCode = i.pCode,
                };


          return ingr.ToList();
}
Fernando SotoRetiredCommented:
The query is a little confusing to me because it seems like this following line is not even affecting the results.

from fgi in temp.Where(f => f.Code == fCode).DefaultIfEmpty()

Can you run the query and then comment out the above line from the query and compare the two results to see if they are the same?
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

JBM2015Author Commented:
Sorry about that, I had omitted one column from my new section, here it is, I do need the fgi alias table.

                select new Ingr
                 {
                       ID = i.ID,
                           Code = i.Code,
                           Descr = i.Descr,
                           Version = i.Version,
                           PCode = i.pCode,
                           Approve = fgifCode != null ? true : false
                 };
JBM2015Author Commented:
Approve = fgi.fCode != null ? true : false
Fernando SotoRetiredCommented:
Do you have a test database that you can upload to the web site that I can download to work with?
JBM2015Author Commented:
Thanks, but I can't share the database.
Fernando SotoRetiredCommented:
OK, not a problem, may be someone else can help with this.
Andrew BeersTechnology LeadCommented:
I'm not much of a fan of LINQ query syntax, but I can help you with the chaining syntax :-)

db.I
   .Where(x => x.Site == Site)
   .GroupBy(x => x.Code)
   .Select(x => new { Code = x.Key, Version = x.Values.Max(y => y.Version)) })
   .ToList();

Open in new window


Now I'm not entirely certain what your joining on db.AI but if you need that included you could always do a .Join(...) which would join the two IQueryable collections.
JBM2015Author Commented:
Here's what I ended up with, it seems to work fine.  Thanks for the assistance.

from i in db.Ingr
where i.PCode == pCode
group i by i.ICode into grp
join ai in db.AIngr on new { grp.FirstOrDefault().IID, grp.FirstOrDefault().ICode } equals new { ai.IID, ai.ICode }
into temp
from fgi in temp.Where(f => f.FCode == fCode).DefaultIfEmpty()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JBM2015Author Commented:
I ended up researching and finding the solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.