CODEWITHSUNDEEP
c#sqllinq
Florian
Florian

Reputation: 1481

Linq query : select content of an ID when max(date)

Happy new Year everyone ! :-)

I've searched for several hours but I can't figure out how to build my query properly using Linq.

I have 2 tables : A and B

In table A : I have A_ID, B_ID, VALUE_DATE.

In table B : I have B_ID, STATUS.

Example :

A_ID = 547 VALUE_DATE = 01/05/10 B_ID = 14750

A_ID = 547 VALUE_DATE = 01/10/10 B_ID = 14750

I want to join A and B using B_ID where VALUE_DATE is max. At the moment, this is what I have :

context is OracleDataContextCommon object evalId and listAffId are parameters of my webservice method.

var query = (from tabA in (from a in context.A
                          where listAffId.Contains(a.A_ID)
                          group a by a.A_IDinto grp
                          select new
                          {
                              pId = (int)grp.Key,
                              valDate = grp.Max(x => x.ValueDate),
                              bId = grp.Select(x => B_ID).FirstOrDefault()
                          })
                         join tabB in context.B on tabA.bIdequals equals tabB.B_ID
                         select new
                         {
                             affId = tabA.pId,
                             status = tabB.Status
                         });

Where am I wrong ? Thank you for investigate...

Upvotes: 1

Views: 876

Answers (3)

jdweng
jdweng

Reputation: 34421

I simulated code with classes. I think this should work

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Context context = new Context();
            List<int> listAffId = new List<int>() { 547 };

            var query = (from a in context.A where listAffId.Contains(a.A_ID)
                         join b in context.B on a.B_ID equals b.B_ID
                         select new { a = a, b = b})
                         .GroupBy(x => x.a.A_ID)
                         .Select(x => new { x = x, max = x.Max(y => y.a.VALUE_DATE)})
                         .Select(x => new {
                             affId = x.x.Key,
                             status = x.x.Where(y => y.a.VALUE_DATE == x.max).Select(y => y.b.STATUS).FirstOrDefault()
                         }).ToList();



        }
    }
    public class Context
    {
        public List<TableA> A { get; set; }
        public List<TableB> B { get; set; }
    }
    public class TableA
    {
        public int A_ID { get; set; }
        public int B_ID { get; set; }
        public DateTime VALUE_DATE { get; set; }
    }
    public class TableB
    {
        public int B_ID { get; set; }
        public string STATUS { get; set; }
    }
}

Upvotes: 1

NetMage
NetMage

Reputation: 26917

You need to select the B_ID that is for the max date, so find the max date first:

var query = (from tabA in (from a in context.A
                          where listAffId.Contains(a.A_ID)
                          group a by a.A_IDinto grp
                          let maxdate = grp.Max(a => a.ValueDate)
                          select new
                          {
                              pId = (int)grp.Key,
                              valDate = maxdate,
                              bId = grp.Where(a => a.ValueDate == maxdate).Select(x => B_ID).FirstOrDefault()
                          })
                         join tabB in context.B on tabA.bId equals equals tabB.B_ID
                         select new
                         {
                             affId = tabA.pId,
                             status = tabB.Status
                         });

Upvotes: 1

StuartLC
StuartLC

Reputation: 107247

The problem is that you're taking the max Date of the Group, but you are selecting the FirstOrDefault B_ID in the group, which doesn't necessarily correlate with the A row which had the max date. Easiest is probably to just order the data by date descending before grouping, and then take the first item in each group for both date and b_id.

Note that I've switched your context for with some dummy data below.

var theAs = new[]
{
   new {A_ID = 547, ValueDate = new DateTime(2010, 05, 01), B_ID = 14750}, 
   new {A_ID = 547, ValueDate = new DateTime(2010, 10, 01), B_ID = 14751}
};
var theBs = new[]
{
  new {B_ID = 14750, Status = "Foo"}, 
  new {B_ID = 14751, Status = "Bar"}
};
var listAffId = new[]{547};

var query = (from tabA in (from a in theAs
             where listAffId.Contains(a.A_ID)
             // Order the data - the first row per group is thus max date
             orderby a.ValueDate descending
             group a by a.A_ID into grp
             select new
             {
                 pId = (int)grp.Key,
                 // Now pull the date and B_ID from the same row
                 valDate = grp.First().ValueDate,
                 bId = grp.First().B_ID
             })
             join tabB in theBs on tabA.bId equals tabB.B_ID
             select new
             {
                 affId = tabA.pId,
                 status = tabB.Status
             });

Upvotes: 1

Related Questions