sql—如何使用distinct选项筛选与不同外键相关的列名值c#.net lambda linq网络

fkvaft9z 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(351)

我有一个名为campaign的表，它有一个名为snapshot的表中的外键。
我想从表campaigns和每个外键（snapshotid）中获取记录，以按列campaigname对它们进行区分。因此，在外键snapshotid中，我想使用datasetid，然后通过campaigname进行区分。
如果我使用distinctby而不是distinct，如果一个campaigname属于不同的外键，它将在所有情况下对它们进行区分。但是，我想区分对应于相同datasetid的campaigname的值
最后，作为一个结果，我正在尝试：我有一个名为upstream的campaigname，它属于外键（snapshotid）1、2和5。snapshotid 1和2对应于计划，5对应于生产。所以，我想带上所有记录，并过滤每个datasetid的活动名称。所以上游应该来两次。与生产和计划有关的一个。然而，在规划的上游，它将被区分，因为它存在两次。
快照表

市场活动表

作为例子：我试过了

var campaigns = db.Campaigns.Include(c => c.Snapshot)
                                        .OrderBy(i => i.Snapshot.DatasetID)
                                        .ThenBy(i => i.CampaignName.Distinct());

上面的一个抛出了一个exception=>dbdistinctexpression需要一个collection参数。参数名称：参数

var campaigns = db.Campaigns.Include(c => c.Snapshot)
                                    .GroupBy(i => i.Snapshot.DatasetID)
                                    .Select(i => i.CampaignName.Distinct());

上面没有这么编译，我试过很多组合但也没用。
如果可能的话，我想帮助使查询主要写在lambda中，然后同样的查询写在linq中

sql Lambda c#.net linq

来源：https://stackoverflow.com/questions/62931687/how-to-use-distinct-option-to-filter-the-value-of-a-column-name-that-is-related

2条答案

按热度按时间

q7solyqu1#

我用类来模拟你的数据库以显示正确的语法。必须检查创建日期是否介于开始日期和结束日期之间。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication11
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.txt";
        static void Main(string[] args)
        {
            DataBase db = new DataBase()
            {
                Snapshot = new List<Snapshot>() {
                    new Snapshot() { Id = 1, CreationDate = new DateTime(2019, 10, 1, 8,0,0),  DatasetID = "Planning"},
                    new Snapshot() { Id = 2, CreationDate = new DateTime(2019, 10, 2, 8,0,0),  DatasetID = "Planning"},
                    new Snapshot() { Id = 3, CreationDate = new DateTime(2019, 10, 15, 13,0,0),  DatasetID = "Production"},
                    new Snapshot() { Id = 4, CreationDate = new DateTime(2019, 10, 16, 14,0,0),  DatasetID = "Production"},
                    new Snapshot() { Id = 5, CreationDate = new DateTime(2019, 10, 16, 17,0,0),  DatasetID = "Production"}
                },
                Campaign = new List<Campaign>() {
                    new Campaign() { Id = 1, CampaignName = "Upstream",  StartDate = new DateTime(2019, 11, 1, 8,0,0), EndDate = new DateTime(2019, 11, 2, 17,0,0), SnapshotID = 1},
                    new Campaign() { Id = 2, CampaignName = "Downstream",  StartDate = new DateTime(2019, 11, 3, 8,0,0), EndDate = new DateTime(2019, 11, 6, 15,0,0), SnapshotID = 2},
                    new Campaign() { Id = 3, CampaignName = "Upstream",  StartDate = new DateTime(2019, 11, 1, 10,0,0), EndDate = new DateTime(2019, 11, 2, 18,0,0), SnapshotID = 2},
                    new Campaign() { Id = 4, CampaignName = "BufferPrep",  StartDate = new DateTime(2019, 12, 1, 6,0,0), EndDate = new DateTime(2019, 12, 5, 15,0,0), SnapshotID = 3},
                    new Campaign() { Id = 5, CampaignName = "Product1",  StartDate = new DateTime(2019, 12, 6, 8,0,0), EndDate = new DateTime(2019, 12, 7, 19,0,0), SnapshotID = 4},
                    new Campaign() { Id = 6, CampaignName = "Product2",  StartDate = new DateTime(2019, 12, 8, 8,0,0), EndDate = new DateTime(2019, 12, 9, 20,0,0), SnapshotID = 5},
                    new Campaign() { Id = 7, CampaignName = "BufferPrep",  StartDate = new DateTime(2019, 12, 1, 12,0,0), EndDate = new DateTime(2019, 12, 6, 10,0,0), SnapshotID = 5},
                    new Campaign() { Id = 9, CampaignName = "Upstream",  StartDate = new DateTime(2019, 11, 5, 0,0,0), EndDate = new DateTime(2019, 11, 9, 0,0,0), SnapshotID = 5}
                }
            };

            var groups = (from s in db.Snapshot
                          join c in db.Campaign on s.Id equals c.SnapshotID
                          select new { snapshot = s, campaign = c }
                           )
                           .GroupBy(x => x.snapshot.Id)
                           .ToList();

            var results = groups.Select(x => new
            {
                snapshot = x.First().snapshot,
                campaign = x.GroupBy(y => y.campaign.CampaignName).Select(y => y.First().campaign).ToList()
            }).ToList();
        }
    }
    public class DataBase
    {
        public List<Snapshot> Snapshot { get; set; }
        public List<Campaign> Campaign { get; set; }
    }
    public class Snapshot
    {
        public int Id { get; set; }
        public DateTime CreationDate { get; set; }
        public string DatasetID { get; set; }
    }
    public class Campaign
    {
        public int Id { get; set; }
        public string CampaignName { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
        public int SnapshotID { get; set; }
    }

}

赞(0）回复(0）举报 2021-07-26

qpgpyjmq2#

初始结果无明显差异
我配置了查询： var campaigns = db.Campaigns.Include(c => c.Snapshot) .GroupBy(i => i.Snapshot.DatasetID).AsEnumerable() .SelectMany(i =>i.DistinctBy(z=>z.CampaignName)); 这是我想要的，但这次不显示datasetid新结果的值
这次，如何显示datasetid值？
这是view.cshtml

赞(0）回复(0）举报 2021-07-26

我来回答

sql—如何使用distinct选项筛选与不同外键相关的列名值c#.net lambda linq网络

2条答案

相关问题

热门标签

最新问答