根据项目喜好、已连接项目和项目评级来查找最佳项目

Question

我有一张桌子：

PL_ProjectLikes

​

PC_ProjectConnect

​

​

PR_ProjectRating

​

​

P_Project

​

​

PL_PageLayout

​

​

这是我的链接查询：

List<PProject> p = ctx.PProject.Where(x => x.PCountryCode == cC && x.PParentalGuidence == r).ToList();
List<PlPageLayout> pppp = ctx.PlPageLayout.Where(x => p.Select(n => n.PIdG).Contains(x.PlPId)).ToList();

现在PL_PageLayout有一个名为PL_P_Id或PlPId的字段，这是一个guid。

我想要的是利用这些表格计算出一个评级或总和或计数，以将最好的项目筛选在列表的顶部。

我所做的是通过使用PIdG对这些表进行分组来提取每个表，guid是项目中每个表的PL_PageLayout，每个项目都是guid。

从表中提取值：

PL_ProjectLIke：

var plike = ctx.PlProjectLike.Where(x => x.PlValue == "Like").Select(c => c).GroupBy(g => new { g.PlPIdG }, (key, group) => new { sumR = group.Count(), pidG = key.PlPIdG });
                List<string> p0p = plike.Select(t => t.pidG).ToList();

PR_ProjectRating：

var prating = ctx.PrProjectRating.Where(x => x.PrIsDeleted == false).Select(k => k).GroupBy(g => new { g.PrPIdG }, (key, group) => new { sumR = group.Sum(k => k.PrValue), pidG = key.PrPIdG });
                List<string> p0 = prating.Select(t => t.pidG).ToList();

PC_ProjectConnect：

var pconnect = ctx.PcProjectConnect.Where(x => x.PcStatus == "Connected").Select(c => c).GroupBy(g => new { g.PcPIdG }, (key, group) => new { sumR = group.Count(), pidG = key.PcPIdG });
                List<string> p0pp = pconnect.Select(t => t.pidG).ToList();

如何使用linq组合上面的过滤器来找到最好的项目或页面布局？

我试过这个：

                pppp = pppp.OrderBy(c => p0.Contains(c.PlPId) ? p0.IndexOf(c.PlPId) : int.MaxValue).ToList();

哪一个是有效的，并通过每个项目的评分总和获得最佳项目，但我如何组合其他两个查询来找到最佳项目？

这是答案，还是只得到最后一个集合的查询：

                List<PlPageLayout> pppp = ctx.PlPageLayout.Where(x => p.Select(n => n.PIdG).Contains(x.PlPId)).ToList();
                pppp = pppp.OrderBy(c => p0.Contains(c.PlPId) ? p0.IndexOf(c.PlPId) : int.MaxValue).ToList();
                pppp = pppp.OrderBy(c => p0p.Contains(c.PlPId) ? p0p.IndexOf(c.PlPId) : int.MaxValue).ToList();
                pppp = pppp.OrderBy(c => p0pp.Contains(c.PlPId) ? p0p.IndexOf(c.PlPId) : int.MaxValue).ToList();

每次我喜欢一个项目的时候，我都在测试它把项目往下推，这样上面的代码就不起作用了，但还是取得了一些进展。

                List<PlPageLayout> pppp = ctx.PlPageLayout.Where(x => p.Select(n => n.PIdG).Contains(x.PlPId)).ToList();
                pppp = pppp.OrderBy(c => p0.Contains(c.PlPId) ? p0.IndexOf(c.PlPId) : int.MaxValue).ToList();
                pppp = pppp.OrderBy(c => p0p.Contains(c.PlPId) ? p0p.IndexOf(c.PlPId) : int.MaxValue).ToList();
                pppp = pppp.OrderBy(c => p0pp.Contains(c.PlPId) ? **p0pp**.IndexOf(c.PlPId) : int.MaxValue).ToList();

Answer 1

在这个答案的帮助下：

https://stackoverflow.com/questions/65014531/summing-a-value-inside-of-a-anonymous-type

我添加了以下代码来获得最好的项目：

                  var ratings =
                from r1 in ctx.PrProjectRating
                where r1.PrIsDeleted == false
                group r1.PrValue by r1.PrPIdG into g
                select new
                {
                Id = g.Key,
                Sum = g.Sum(),
                };
                var likes =
                    from l in ctx.PlProjectLike
                    where l.PlValue == "Like"
                    group 1 by l.PlPIdG into g
                    select new
                    {
                        Id = g.Key,
                        Count = g.Count(),
                    };
                var connects =
                    from c1 in ctx.PcProjectConnect
                    where c1.PcStatus == "Connected"
                    group 1 by c1.PcPIdG into g
                    select new
                    {
                        Id = g.Key,
                        Count = g.Count(),
                    };

                var ids = ratings.Select(r => r.Id)
                    .Union(likes.Select(l => l.Id))
                    .Union(connects.Select(c => c.Id))
                    .ToHashSet();
                var query =
                    from i in ids
                    join ra in ratings on i equals ra.Id into rs
                    from ra in rs.DefaultIfEmpty()
                    join l in likes on i equals l.Id into ls
                    from l in ls.DefaultIfEmpty()
                    join co in connects on i equals co.Id into cs
                    from co in cs.DefaultIfEmpty()
                    select new
                    {
                        Id = i,
                        Ratings = ra?.Sum ?? 0,
                        Likes = l?.Count ?? 0,
                        Connects = co?.Count ?? 0,
                    };
                List<PlPageLayout> pppp = ctx.PlPageLayout.Where(x => p.Select(n => n.PIdG).Contains(x.PlPId)).ToList();
                pppp = query.OrderByDescending(x => x.Ratings + x.Likes + x.Connects).SelectMany(j => pppp.Where(s => s.PlPId == j.Id)).ToList();

Answer 2

我在RexTester上放了一些测试代码，但我不确定你的问题。我认为您可以在创建结果列表时对它们进行排序，或者我只是误解了这个问题

public class PlProjectLike
{
    public int PlId { get; set; }
    public Guid PlPIdG { get; set; }
    public int PlUId { get; set; }
    public string PlValue { get; set; }
    public DateTime PlCreatedDate { get; set; }
}

public class PcProjectConnect
{
    public int PcId { get; set; }
    public Guid PcPIdG { get; set; }
    public int PcUId { get; set; }
    public DateTime PcCreatedDate { get; set; }
    public string PcStatus{ get; set; }
}

public class PrProjectRating
{
    public int PrId { get; set; }
    public int PrUId { get; set; }
    public string PrText { get; set; }
    public int PrValue { get; set; }
    public Guid PrPIdG { get; set; }
    public DateTime PrCreatedDate { get; set; }
    public bool PrIsDeleted{ get; set; }
}

public class PProject
{
    public int PId { get; set; }
    public Guid PIdG { get; set; }
    public string PName { get; set; }
    public DateTime PDateCreated { get; set; }
    public bool PDeleted { get; set; }
    public int PUId { get; set; }
    public int PTtId { get; set; }
    public string PCountry { get; set; }
    public string PCountryCode { get; set; }
    public string PParentalGuidence { get; set; }
    public string PConnectionType { get; set; }
}

public class PlPageLayout
{
    public int PLId { get; set; }
    public Guid PlPId { get; set; }
    public string PLName { get; set; }
}

public class CTX
{
    public List<PProject> PProject { get; set; }
    public List<PlPageLayout> PlPageLayout { get; set; }
    public List<PlProjectLike> PlProjectLike { get; set; }
    public List<PrProjectRating> PrProjectRating { get; set; }
    public List<PcProjectConnect> PcProjectConnect { get; set; }

    public CTX()
    {
        PProject = new List<PProject>();
        PlPageLayout = new List<PlPageLayout>();
        PlProjectLike = new List<PlProjectLike>();
        PrProjectRating = new List<PrProjectRating>();
        PcProjectConnect = new List<PcProjectConnect>();
    }
}

public class LikeGroup
{
    public int sumR { get; set; }
    public Guid pidG { get; set; }
}
public class Program
{
    public static void Main(string[] args)
    {
        CTX ctx = new CTX();
        String r = "R";
        string cC = "us";

        // Select project for country and rating
        List<PProject> p = ctx.PProject.Where(x => x.PCountryCode == cC && x.PParentalGuidence == r).ToList();
        // List of PlPageLayouts where the PlPId is in the selected PProject list
        List<PlPageLayout> pppp = ctx.PlPageLayout.Where(x => p.Select(n => n.PIdG).Contains(x.PlPId)).ToList();
        // List of Count/PlPIdG from PlProjectLike where the PlValue is 'Like' Ordered by the count descending
        List<LikeGroup> plike = ctx.PlProjectLike.Where(x => x.PlValue == "Like").Select(c => c).GroupBy(g => new { g.PlPIdG }, (key, group) => new LikeGroup() { sumR = group.Count(), pidG = key.PlPIdG }).OrderByDescending(dat => dat.sumR).ToList();
        // List of Sum(PrValue)/PlPIdG from PrProjectRating where PrIsDeleted is false Ordered by the Sum(PrValue) descending
        List<LikeGroup> prating = ctx.PrProjectRating.Where(x => x.PrIsDeleted == false).Select(k => k).GroupBy(g => new { g.PrPIdG }, (key, group) => new LikeGroup(){ sumR = group.Sum(k => k.PrValue), pidG = key.PrPIdG }).OrderByDescending(dat => dat.sumR).ToList();
        // List of Count/PlPIdG from PcProjectConnect where PcStatus is Connected Ordered by the count descending
        List<LikeGroup> pconnect = ctx.PcProjectConnect.Where(x => x.PcStatus == "Connected").Select(c => c).GroupBy(g => new { g.PcPIdG }, (key, group) => new LikeGroup() { sumR = group.Count(), pidG = key.PcPIdG }).OrderByDescending(dat => dat.sumR).ToList();

        List<PlProjectLike> OrderedProjectLikeList = 
            (from pl in ctx.PlProjectLike 
             join ord in plike on pl.PlPIdG equals ord.pidG 
             orderby ord.sumR descending 
             select pl).ToList();

        List<PrProjectRating> OrderedPrProjectRatingList = 
            (from pr in ctx.PrProjectRating
             join ord in prating on pr.PrPIdG equals ord.pidG
             orderby ord.sumR descending
             select pr).ToList();

        List<PcProjectConnect> OrderedPcProjectConnectList =
            (from pc in ctx.PcProjectConnect
             join ord in prating on pc.PcPIdG equals ord.pidG
             orderby ord.sumR descending
             select pc).ToList();

    }
}