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.
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;
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With