ASP.NET MVC C# linq 查询将 2 个 IEnumerable<data> 结果集连接在一起

我对此进行了更深入的研究，我编写了一个基本的 c# 测试项目，并使用一些基本数据模拟了 db 上下文。

我注意到的第一件事是对 Distinct 的调用。因为查询结果是 CurrentAndAcceptedDetail，所以如果在对象具体化之后运行它，则不能保证良好的“不同”。distinct 将检查对对象的引用，而不一定是它的属性。这是一篇关于它的文章。关键是无论你在做什么，都需要实现 IEquatable 以便它可以与另一个进行比较以确定它是否不同。

下一点是它实际获取的数据。因为连接是双向的，如果 simData 中有 2 行“a”、“b”、“c”，而 simData2 中有 2 行“a”、“b”、“c”，它实际上会创建 4 行，因为 simData.row1 与 simData2.row1 和 simData2.Row2 匹配。simData.row2 和 simData2.row1 和 simData2.row2 也是如此。

结合在一起，您在 simData 上的独特之处在于返回的所有字段，而不仅仅是您加入的 3 个字段。所以假设我们有以下数据：

模拟数据

|"a"|"b"|"c"|1|2|3|
|"a"|"b"|"c"|4|5|6|

和

模拟数据2

|"a"|"b"|"c"|5|6|7|
|"a"|"b"|"c"|2|3|4|

这将产生 4 个结果。

|"a"|"b"|"c"|1|2|3|"a"|"b"|"c"|5|6|7|
|"a"|"b"|"c"|1|2|3|"a"|"b"|"c"|2|3|4|
|"a"|"b"|"c"|4|5|6|"a"|"b"|"c"|5|6|7|
|"a"|"b"|"c"|4|5|6|"a"|"b"|"c"|2|3|4|

如果没有您想要实现的实际数据，要进一步提供帮助有点困难，可能您需要一些 group by 语句才能获得最大值，或者在 simData 中查找最新记录。为了回答这个问题，我们需要更多关于您想要实现的目标的背景信息。

同时，这是我必须测试您的 linq 查询的代码。（先决条件：单元测试项目中用于 Moq 和 EntityFramework 的 Nuget 包）

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;

namespace StackOverflow.Tests
{
    [TestClass]
    public class Question44126393
    {
        public class PredictedObservedValue
        {
            public int ID { get; set; }
            public int PredictedObservedDetailsID { get; set; }
            public int SimulationsID { get; set; }
            public string ValueName { get; set; }
            public decimal PredictedValue { get; set; }
            public decimal ObservedValue { get; set; }
        }

        public class PredictedObservedDetail
        {
            public int ID { get; set; }
            public string TableName { get; set; }
        }
        public class Simulation
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }

        public class SimulationPredictedObserved
        {
            public string TableName { get; set; }
            public string SimulationName { get; set; }
            public string ValueName { get; set; }
            public double? PredictedValue { get; set; }
            public double? ObservedValue { get; set; }
            public double? Difference { get; set; }
        }

        public class CurrentAndAcceptedDetail : IEquatable<CurrentAndAcceptedDetail>
        {
            public string TableName { get; set; }
            public string SimulationName { get; set; }
            public string ValueName { get; set; }
            public double? CurrentPredictedValue { get; set; }
            public double? CurrentObservedValue { get; set; }
            public double? CurrentDifference { get; set; }
            public double? AcceptedPredictedValue { get; set; }
            public double? AcceptedObservedValue { get; set; }
            public double? AcceptedDifference { get; set; }

            public bool Equals(CurrentAndAcceptedDetail other)
            {
                return other != null &&
                    TableName == other.TableName &&
                    SimulationName == other.SimulationName &&
                    ValueName == other.ValueName &&
                    Nullable.Equals(CurrentPredictedValue, other.CurrentPredictedValue) &&
                    Nullable.Equals(CurrentObservedValue, other.CurrentObservedValue) &&
                    Nullable.Equals(CurrentDifference, other.CurrentDifference) &&
                    Nullable.Equals(AcceptedPredictedValue, other.AcceptedPredictedValue) &&
                    Nullable.Equals(AcceptedObservedValue, other.AcceptedObservedValue) &&
                    Nullable.Equals(AcceptedDifference, other.AcceptedDifference);
            }

            public override int GetHashCode()
            {
                var texts = TableName + SimulationName + ValueName;

                // Calculate the hash code for the object.
                return texts.GetHashCode();

            }
        }

        public class BaseDatacontext : DbContext
        {
            public virtual DbSet<PredictedObservedValue> PredictedObservedValues { get; set; }
            public virtual DbSet<PredictedObservedDetail> PredictedObservedDetails { get; set; }
            public virtual DbSet<Simulation> Simulations { get; set; }
        }

        [TestMethod]
        public void TestMethodLinq()
        {
            var fakePredictedObservedValues = new List<PredictedObservedValue>
            {
                new PredictedObservedValue
                {
                    ID = 1,
                    SimulationsID = 2,
                    PredictedObservedDetailsID = 3,
                    ValueName = "Value A",
                    PredictedValue = 5,
                    ObservedValue = 1
                },
                new PredictedObservedValue
                {
                    ID = 2,
                    SimulationsID = 2,
                    PredictedObservedDetailsID = 6,
                    ValueName = "Value A",
                    PredictedValue = 3,
                    ObservedValue = 20
                },
                new PredictedObservedValue
                {
                    ID = 3,
                    SimulationsID = 2,
                    PredictedObservedDetailsID = 3,
                    ValueName = "Value A",
                    PredictedValue = 5,
                    ObservedValue = 2
                },
                new PredictedObservedValue
                {
                    ID = 4,
                    SimulationsID = 2,
                    PredictedObservedDetailsID = 6,
                    ValueName = "Value A",
                    PredictedValue = 3,
                    ObservedValue = 19
                },
            };

            var fakePredictedObservedDetails = new List<PredictedObservedDetail>
            {
                new PredictedObservedDetail
                {
                    ID = 3,
                    TableName = "Table Z",
                },
                new PredictedObservedDetail
                {
                    ID = 6,
                    TableName = "Table Z",
                },
            };

            var fakeSimulations = new List<Simulation>
            {
                new Simulation
                {
                    ID = 2,
                    Name = "Simulation 2",
                },
                new Simulation
                {
                    ID = 7,
                    Name = "Simulation 7",
                },
            };

            var fakePredictedObservedValueSet = GetMockDbSet(fakePredictedObservedValues.AsQueryable());
            var fakePredictedObservedDetailSet = GetMockDbSet(fakePredictedObservedDetails.AsQueryable());
            var fakeSimluationsSet = GetMockDbSet(fakeSimulations.AsQueryable());

            var fakeDbContext = new Mock<BaseDatacontext>();
            fakeDbContext.Setup(db => db.PredictedObservedValues).Returns(fakePredictedObservedValueSet.Object);
            fakeDbContext.Setup(db => db.PredictedObservedDetails).Returns(fakePredictedObservedDetailSet.Object);
            fakeDbContext.Setup(db => db.Simulations).Returns(fakeSimluationsSet.Object);

            var simData = GetValueSummary(fakeDbContext.Object, 3, "Value A");
            var simData2 = GetValueSummary(fakeDbContext.Object, 6, "Value A");
            var combinedData = (from PO1 in simData
                                join PO2 in simData2
                                on new { PO1.TableName, PO1.SimulationName, PO1.ValueName }
                                equals new { PO2.TableName, PO2.SimulationName, PO2.ValueName }
                                select new CurrentAndAcceptedDetail
                                {
                                    TableName = PO1.TableName,
                                    SimulationName = PO1.SimulationName,
                                    ValueName = PO1.ValueName,
                                    CurrentPredictedValue = (double?)Math.Round((double)PO1.PredictedValue, 3),
                                    CurrentObservedValue = ((PO1.ObservedValue.HasValue) ? (double?)Math.Round((double)PO1.ObservedValue, 3) : 0),
                                    CurrentDifference = ((PO1.PredictedValue.HasValue && PO1.ObservedValue.HasValue) ? (double?)Math.Round((double)PO1.PredictedValue - (double)PO1.ObservedValue, 3) : 0),
                                    AcceptedPredictedValue = (double?)Math.Round((double)PO2.PredictedValue, 3),
                                    AcceptedObservedValue = ((PO2.ObservedValue.HasValue) ? (double?)Math.Round((double)PO2.ObservedValue, 3) : 0),
                                    AcceptedDifference = ((PO2.PredictedValue.HasValue && PO2.ObservedValue.HasValue) ? (double?)Math.Round((double)PO2.PredictedValue - (double)PO2.ObservedValue, 3) : 0)
                                })
                                .Distinct();

            Assert.AreEqual(2, combinedData.Count());
        }

        public Mock<DbSet<T>> GetMockDbSet<T>(IQueryable<T> queryable) where T : class
        {
            var mockSet = new Mock<DbSet<T>>();
            mockSet.As<IQueryable<T>>().Setup(m => m.Provider).Returns(queryable.Provider);
            mockSet.As<IQueryable<T>>().Setup(m => m.Expression).Returns(queryable.Expression);
            mockSet.As<IQueryable<T>>().Setup(m => m.ElementType).Returns(queryable.ElementType);
            mockSet.As<IQueryable<T>>().Setup(m => m.GetEnumerator()).Returns(() => queryable.GetEnumerator());

            return mockSet;
        }

        public IEnumerable<SimulationPredictedObserved> GetValueSummary(BaseDatacontext db, int predictedObservedId, string valueName)
        {
            return (from pov in db.PredictedObservedValues
                    join pod in db.PredictedObservedDetails on pov.PredictedObservedDetailsID equals pod.ID
                    join s in db.Simulations on pov.SimulationsID equals s.ID
                    where pov.PredictedObservedDetailsID == predictedObservedId && pov.ValueName == valueName
                    select new SimulationPredictedObserved()
                    {
                        TableName = pod.TableName,
                        SimulationName = s.Name,
                        ValueName = pov.ValueName,
                        PredictedValue = Math.Round((double)pov.PredictedValue, 3),
                        ObservedValue = Math.Round((double)pov.ObservedValue, 3),
                        Difference = (double?)Math.Round((double)pov.PredictedValue - (double)pov.ObservedValue, 3)
                    })
                    .Distinct();
        }
    }
}