CODEWITHSUNDEEP
c#entity-frameworklinq
Impostor
Impostor

Reputation: 2050

Get current and previous Item with Linq

I have a Offer class like

public class Offer
{
    public int OfferID { get; set; }
    public DateTime OfferDate { get; set; }
    public int CustomerID { get; set; }
}

Now I have a lot of offers like this

List<Offer> oList = new List<Offer>();
oList.Add(new Offer() { OfferID = 1, OfferDate = new DateTime(2018, 01, 01), CustomerID = 1 });
oList.Add(new Offer() { OfferID = 2, OfferDate = new DateTime(2018, 01, 03), CustomerID = 1 });
oList.Add(new Offer() { OfferID = 3, OfferDate = new DateTime(2018, 01, 01), CustomerID = 2 });
oList.Add(new Offer() { OfferID = 4, OfferDate = new DateTime(2018, 01, 05), CustomerID = 2 });
oList.Add(new Offer() { OfferID = 5, OfferDate = new DateTime(2018, 01, 02), CustomerID = 1 });

And I want to get a Offer by ID but also the previous Offer of this customer by Date.

Currently I'm using Linq2SQL and I make two selects. First I select the required Offer by ID and then I select the previous one by OfferDate.

Example: In case of OfferID==5 the previous Offer of this Customer is OfferID==1.

public List<Offer> GetCurrentAndPrevious(int OfferID)
{
    using (DataContext cx = new DataContext())
    {
        Offer oCurrent = cx.Offer.Single(x => x.OfferID = OfferID);
        Offer oPrevious = cx.Offer.OrderBy(x => x.OfferDate)
                                  .Last(x => x.CustomerID = oCurrent.CustomerID && x.OfferDate < oCurrent .OfferDate);
        return new List<Offer>() { oCurrent , oPrevious };
    }
}

Question: Is there a way to solve this with one query instead of querying the database twice?

Upvotes: 2

Views: 2442

Answers (5)

Slava Utesinov
Slava Utesinov

Reputation: 13498

//query syntax
var data = (from curr in cx.Offer.Where(x => x.OfferID == OfferID)
            from prev in cx.Offer
            where curr.CustomerID == prev.CustomerID && curr.OfferDate >= prev.OfferDate
            orderby prev.OfferDate descending 
            select prev).Take(2).ToList();

//fluent syntax
data = cx.Offer.Where(x => x.OfferID == OfferID)
.Join(cx.Offer, curr => 0, prev => 0, (curr, prev) => new { curr, prev })
.Where(x => x.curr.CustomerID == x.prev.CustomerID && x.curr.OfferDate >= x.prev.OfferDate)
.OrderByDescending(x => x.prev.OfferDate)
.Select(x => x.prev).Take(2).ToList();

 return new List<Offer>() { data.FirstOrDefault(), data.Count == 2 ? data.Last() : null };

Upvotes: 2

Dishant
Dishant

Reputation: 1595

You can try something like this:

 var offers  = from u in oList
               join c in (
               from crt in oList
               where crt.OfferID == OfferID
               orderby crt.OfferDate
               select crt)
               on u.CustomerID equals c.CustomerID
               where u.OfferID == OfferID || u.OfferDate < c.OfferDate
               select u;

Upvotes: 0

SᴇM
SᴇM

Reputation: 7213

var q = (from c in cx.Offer where c.OfferID == OfferID
         from p in cx.Offer
         where p.OfferDate <= c.OfferDate &&
               p.CustomerID == c.CustomerID
         orderby p.OfferDate descending
         select p).Take(2);

Upvotes: 2

V.Leon
V.Leon

Reputation: 586

This can be done with a sub-query and by projecting to an anonymous type, for example:

var currentAndPrevious = dataContext.Offer
                                    .Where(x => x.OfferID == OfferID)
                                    .Select(x =>
                                    new
                                    {
                                        Current = x,
                                        Previous = dataContext.Offer.OrderByDescending(y => y.OfferDate).Where(y => y.OfferDate < x.OfferDate && y.CustomerID == x.CustomerID).FirstOrDefault()
                                    })
                                    .FirstOrDefault();

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

I'm not a linq expert so perhaps the other answers are better, but here is a lamba syntax option:

var currentAndPreviousOffers = oList.Where(offer => 
    {
        var current = oList.Single(o => o.OfferID == offerID);
        var previous = oList
            .Where(o => o.CustomerID == current.CustomerID && o.OfferDate < current.OfferDate)
            .OrderByDescending(o => o.OfferDate).First();
        return offer.OfferID == current.OfferID || offer.OfferID == previous.OfferID;
    });

Upvotes: 0

Related Questions