How to use Join and Where to return an IList?

Question

How can I achieve a join and a where in C# MVC using something like Linq or EF Join?

This is the equivalent SQL I am trying to achieve.

select * from promotion P
JOIN PromotionsClaimed PC
on PC.PromotionId = P.objectid
where PC.userId = @USERID

This method should return a list of the promotions for the user. First, I get a list of all of the promotions, then I get the composite list of claimed promotions for the user. This is what I have so far.

    public IList<Promotion> GetRewardsForUser(string userId)
    {
        //a list of all available promotions
        IList<Promotion> promos =  _promotionLogic.Retrieve();

        //contains a list of Promotion.objectIds for that user
        IList<PromotionsClaimed> promosClaimed = _promotionsClaimedLogic.RetrieveByCriteria(t => t.userId == userId);

        //should return a list of the Promotion name and code for the rewards claimed by user, but a complete list of Promotion entities would be fine
        var selectedPromos =
            from promo in promos
            join promoClaimed in promosClaimed on promo.objectId equals promoClaimed.PromotionId
            select new { PromoName = promo.Name, PromoCode = promo.Code };

        return selectedPromos;
    }

I realize there are a lot of problems here. I'm trying to learn Linq and Entity Framework, but I don't know how to add the where clause to an IList or if there is an easier way to accomplish this.

It seems to me like there would be a way to filter the promotion list where it contains the Promotion.objectId in the promosClaimed list, but I don't know the syntax.

Answer 1

 public IList<Promotion> GetRewardsForUser(string userId)
{
    //a list of all available promotions
    IList<Promotion> promos =  _promotionLogic.Retrieve();

    //contains a list of Promotion.objectIds for that user
    IList<PromotionsClaimed> promosClaimed = _promotionsClaimedLogic.RetrieveByCriteria(t => t.userId == userId);

    //should return a list of the Promotion name and code for the rewards claimed by user, but a complete list of Promotion entities would be fine
    var selectedPromos =
        (from promo in promos
        join promoClaimed in promosClaimed on promo.objectId equals promoClaimed.PromotionId
        select new { PromoName = promo.Name, PromoCode = promo.Code }).ToList();

    return selectedPromos;
}

Answer 2

If I understood your question correctly, you could do something like this:

public IList<Promotion> GetRewardsForUser(string userId)
{
    //contains a list of Promotion.objectIds for that user
    IList<PromotionsClaimed> promosClaimed = _promotionsClaimedLogic
        .RetrieveByCriteria(t => t.userId == userId);

    var promotionIds = promosClaimed.Select(p => p.PromotionId).ToList();

    IList<Promotion> promos = _promotionLogic.Retrieve()
        .Where(p => promotionIds.Contains(p.objectId))
        .Select(p => new { PromoName = p.Name, PromoCode = p.Code });

    return selectedPromos;
}

The claimed promotions should be already filtered by a user so this should possibly work.