CODEWITHSUNDEEP
linqc#-4.0
user1202606
user1202606

Reputation: 1160

linq query, search by max date if previous date exists

I'm trying to get three columns back from the database, DecalExpireDate, DecalExpireMonth and DecalExpireYear. Basically, I'm trying to check if it has a previous expiration date and if it does, I need to get the latest expiration date, as it may have multiple previous expiration dates.

var previousExpirationDate = (from d in db.CT_Decals
      where d.TankID == decal.TankID
      && d.DecalStatus == "Approved"
      && d.DecalExpireDate == ((from dn in db.CT_Decals
                               where dn.TankID == decal.TankID
                               && dn.DecalStatus == "Approved"
                               select dn.DecalExpireDate).Max())
      select new 
      {
        d.DecalExpireDate,
        d.DecalExpireMonth,
        d.DecalExpireYear

      });

This query isn't working, can anybody see the problem? I'm searching by TankID, Status = approved and then I try to use the max expirationDate.

Upvotes: 1

Views: 286

Answers (3)

Risky Martin
Risky Martin

Reputation: 2521

Although Max may be the problem in your example, it would be nice to find a way to use it properly because it is faster than sorting. How about:

Edit: this answer only returns the DecalExpireDate 

var previousExpirationDate = (from d in db.CT_Decals
      where d.TankID == decal.TankID
      && d.DecalStatus == "Approved"
      select new 
      {
        d.DecalExpireDate,
        d.DecalExpireMonth,
        d.DecalExpireYear
      }).Max(d => d.DecalExpireDate);

For this version you will have to test to see if it's worth making an extra call to the database to avoid sorting:

var latestDate = (from d in db.CT_Decals
      where d.TankID == decal.TankID
      && d.DecalStatus == "Approved"
      select d.DecalExpireDate).Max();

var previousExpirationDate = (from d in db.CT_Decals
      where d.TankID == decal.TankID
      && d.DecalStatus == "Approved"
      && d.DecalExpireDate == latestDate
      select new 
      {
        d.DecalExpireDate,
        d.DecalExpireMonth,
        d.DecalExpireYear
      }).First();

Upvotes: 0

Satish
Satish

Reputation: 3100

How about this 

 var result = db.CT_Decals.Where(o => o.TankId ==  decal.TankId && o.DecalStatus == "Approved")
                  .OrderByDescending(o => o.DecalExpireDate)
                  .Select(o => new { o.DecalExpireDate,
                                     o.DecalExireMonth,
                                     o.DecalExireYear }).First()

or 

var result = (from d in db.CT_Decals
                      where d.TankID == 1 && d.Status == "Approved"
                      orderby d.ExpireDate descending
                      select new { d.DecalExpireDate, 
                                   d.DecalExpireMonth, 
                                   d.ExpireDate }).First();

Upvotes: 1

Jorge
Jorge

Reputation: 18237

The problem it's with the that max function couldn't be translated to Sql in the scope that you need so you could try like this

 var previousExpirationDate = (from d in db.CT_Decals
  where d.TankID == decal.TankID
  && d.DecalStatus == "Approved"
  && d.DecalExpireDate == ((from dn in db.CT_Decals
                           where dn.TankID == decal.TankID
                           && dn.DecalStatus == "Approved"
                           order by dn.DecalExpireDate
                           select dn.DecalExpireDate).First())
  select new 
  {
    d.DecalExpireDate,
    d.DecalExpireMonth,
    d.DecalExpireYear

  });

Upvotes: 0

Related Questions