How to use Join and Where to return an IList?

Question

How can I achieve a connection and where in C # MVC is something like Linq or EF Join used?

This is the SQL equivalent I'm 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 promotions for the user. Firstly, I get a list of all promotions, then I get a composite list of advertised promotions for the user. This is what I still have.

  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 understand that there are a lot of problems. I am trying to learn Linq and Entity Framework, but I don’t know how to add where clause to IList or if there is an easier way to accomplish this.

It seems to me that there is a way to filter the promotion list, where it contains 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; } 

Advertised promotions must already be filtered by the user for this to work.

Answer 3

First of all, do you use the framework entity? Or are you just trying to combine the two collections?

Because if you use EF, you are mistaken. The object must use the correct method, for example:

 public DbSet<promotion > promotion { get; set; } public DbSet<PromotionsClaimed> PromotionsClaimed{ get; set; } Context.promotion.Include(o => o.PromotionsClaimed).FirstOrDefault(s => s.Id == USERID); 

If you only need to join the two collections using linq, you can do it.

 var userId = 1; var test = ( from p in promos join pc in promosClaimed on p.objectid equals pc.PromotionId where pc.userId == userId select p ).ToList(); 

Answer 4

Did you try to just add your condition to your code? How:

 var selectedPromos = from promo in promos join promoClaimed in promosClaimed on promo.objectId equals promoClaimed.PromotionId where promosClaimed.UserId == userId select new { PromoName = promo.Name, PromoCode = promo.Code }; 

This should work, or I just didn't understand you.