如何使用Join和Where返回IList

Question

如何使用Linq或EF join之类的东西在C#MVC中实现联接和where？

这就是我试图实现的等效SQL。

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

此方法应返回用户的促销列表。首先，我得到了所有促销活动的列表，然后我得到了用户声称的促销活动的合成列表。这就是我到目前为止所拥有的。

    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;
    }

我意识到这里有很多问题。我正在尝试学习Linq和实体框架，但我不知道如何将where子句添加到IList中，也不知道是否有更简单的方法可以实现这一点。

在我看来，有一种方法可以过滤促销列表，其中包含promosClaimed列表中的Promotion.objectId，但我不知道语法。

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

如果我正确理解你的问题，你可以这样做：

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;
}

用户应该已经过滤了声称的促销活动，因此应该可能有效。

Answer 3

首先，您使用的是实体框架吗？还是你们只是想把两个集合连接起来？

因为如果你在使用EF，你的想法就错了。在实体中，正确的方法是使用include，例如：

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

如果您只需要使用linq连接两个集合，那么您可以这样做。

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

您是否尝试过将条件添加到代码中？类似：

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 };

这应该有效，否则我就是不理解你的