我正在尝试获取变体列表，并且对于每个变体都获取所有 subvariants list 而不 pipe 变体适用于何处 特定的 Test say 100。这是示例数据:

Id      TestId    SourceSubVariantId   TargetSubVariantId   DiffPerc
114      100           66              67                   100.00
115      100           67              68                   100.00
116      100           70              71                   99.99

我有变体 1 的 3 个子变体:

Id=66,Name=Abc
Id=68,Name=Pqr
Id=69,Name=xyz

我有 变体 2 的 3 个子变体:

Id=70,Name=lmn
Id=71,Name=xxx
Id=72,Name=hhh

但请注意，在我的输出中，Variant1 CustomSubvariantList 中的 Variants 2 subvariants list 的所有 Id 都为 0 >:

数据模型:

public class Variants
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Type { get; set; }
        public virtual ICollection<SubVariants> SubVariants { get; set; }
    }

    public class SubVariants
    {
        public int Id { get; set; }
        public int VariantId { get; set; }
        public string Name { get; set; }
        public virtual Variants Variants { get; set; }
        public virtual ICollection<TestOperation> TestOperation { get; set; }
        public virtual ICollection<TestOperation> TestOperation1 { get; set; }
    }

        public class TestOperation
    {
        public int Id { get; set; }
        public Nullable<int> TestId { get; set; }
        public int SourceSubVariantId { get; set; }
        public int TargetSubVariantId { get; set; }
        public decimal DiffPerc { get; set; }
        public virtual SubVariants SubVariants { get; set; }
        public virtual SubVariants SubVariants1 { get; set; }
                public virtual Test Test { get; set; }
    }

查询:

int testId=100;
var query =
      from v in context.Variants 
      where v.Type  == "Add"
      select new
      {
           ParentVariant = v.Name,
           Type = v.Method,
           CustomSubvariantList =
           (
                from svName in context.SubVariants.Select(sv => sv.Name).Distinct()
                join x in
                (
                     from sv in v.SubVariants 
                     from to in sv.TestOperation 
                     where to.TestId == testId
                     orderby sv.Id
                     select new
                     {
                          sv.Name,
                          to.DiffPerc,
                          SourceId = (int?)to.SubVariants.Id,
                          TargetID=(int?)to.SubVariants1.Id
                     }
                 )
                 on svName equals x.Name into g  
                 from x in g.DefaultIfEmpty()
                 orderby x.SourceId
                 select new
                 {
                      SourceId=x.SourceId ?? 0,
                      TargetId=x.TargetID ?? 0,
                      Name = svName,
                      DiffPerc = x.DiffPerc
                 }
             ).ToList()
         };

更新: 根据评论，这是示例输入和预期输出:

情况 1:当所有父变体中的子变体名称都不同时

变体:

Id      Name       Type   CategoryId
11      Variant1    Add     1
12      Variant2    Add     1
13      Variant3    Add     1
14      Variant4    Add     1

子变体:

Id     VariantId     Name
66      11           Abc
67      11           PQR
68      11           Xyz

70      12           lmn
71      12           xxx
72      12           hhh

测试操作:

Id      TestId    SourceSubVariantId   TargetSubVariantId   DiffPerc
114      100           66               67                  10.00
115      100           67               68                  20.00

114      100           70               71                  40.00
115      100           71               72                  50.00

预期输出:

情况 2:当所有父变体中的子变体名称都相同时:

子变体:

Id     VariantId     Name
66      11           Abc
67      11           PQR
68      11           Xyz

70      12           Abc
71      12           PQR
72      12           Xyz

预期输出:

最佳答案

我们在聊天中进行了一些讨论，直到我了解您的需求。

这是代码:

    class Program
{
    static void Main(string[] args)
    {
        VariantsEntities db=new VariantsEntities();

        var queryResult = db.Variants.AsEnumerable().Select(x => new PageViewModel
        {
            ParentVariant = x.Name,
            Type = x.Type,
            CustomSubvariantList = GetCustomSubVariants(x.Id,db).ToList()
        }).ToList();


        var jsonObj = JsonConvert.SerializeObject(queryResult);
        Console.WriteLine(jsonObj);
        Console.ReadKey();
    }

    private static IEnumerable<Customsubvariantlist> GetCustomSubVariants(int variantId, VariantsEntities db)
    {
        var subVariants = db.SubVariants.ToList();

        foreach (var subVariant in subVariants)
        {
            var obj=new Customsubvariantlist();
            obj.Name = subVariant.Name;

            var testOpTarget = db.TestOperations
                .FirstOrDefault(x => x.TargetSubVariantId == subVariant.Id);
            var testOpSource = db.TestOperations
                .FirstOrDefault(x => x.SourceSubVariantId == subVariant.Id);

            if (subVariant.VariantId == variantId)
            {
                obj.Value = testOpTarget == null ? 
                    testOpSource?.SourceValue : testOpTarget?.TargetValue;
                obj.DiffPerc = testOpTarget?.DiffPerc;
            }
            else
            {
                obj.Value = null;
                obj.DiffPerc = null;
            }
            yield return obj;
        }

    }
}

你只需要替换 DbContext 名称，然后测试它。

这是结果:

    [
  {
    "ParentVariant": "Variant1",
    "Type": "Add",
    "CustomSubvariantList": [
      {
        "Name": "Abc",
        "Value": 200,
        "DiffPerc": null
      },
      {
        "Name": "Pqr",
        "Value": 300,
        "DiffPerc": 100.0
      },
      {
        "Name": "xyz",
        "Value": 500,
        "DiffPerc": 200.0
      },
      {
        "Name": "lmn",
        "Value": null,
        "DiffPerc": null
      },
      {
        "Name": "xxx",
        "Value": null,
        "DiffPerc": null
      },
      {
        "Name": "hhh",
        "Value": null,
        "DiffPerc": null
      }
    ]
  },
  {
    "ParentVariant": "Variant2",
    "Type": "Add",
    "CustomSubvariantList": [
      {
        "Name": "Abc",
        "Value": null,
        "DiffPerc": null
      },
      {
        "Name": "Pqr",
        "Value": null,
        "DiffPerc": null
      },
      {
        "Name": "xyz",
        "Value": null,
        "DiffPerc": null
      },
      {
        "Name": "lmn",
        "Value": 1000,
        "DiffPerc": null
      },
      {
        "Name": "xxx",
        "Value": 2000,
        "DiffPerc": 1000.0
      },
      {
        "Name": "hhh",
        "Value": 4000,
        "DiffPerc": 2000.0
      }
    ]
  }
]

来自 here您可以下载示例项目。这个项目是用你发给我的示例数据库完成的，数据库优先也是如此，一些 Prop 或表格可能有不同的名称，请在你的项目中移动代码之前检查这一点。

基本上我做了什么:

1. 创建了一个新的控制台应用项目

2. 我从您的 json 对象中获得了模型(我复制了 json 对象并将其粘贴到一个类中，使用(vs 菜单)编辑 -> 过去特殊 -> 过去 JSON 作为类。

3. 由于您需要每个 Varian 的所有 SubVariants，我创建了一个单独的方法来处理您创建 CustomSubVariantList。在这种方法中，我迭代抛出所有 SubVariants 并根据您的条件创建对象。

更新:您在一次查询中的解决方案:

static void Main(string[] args)
    {
        VariantsEntities db = new VariantsEntities();

        var result = from x in db.Variants                        
                     select new PageViewModel
                     {
                         ParentVariant = x.Name,
                         Type = x.Type,
                         CustomSubvariantList = (from z in db.SubVariants
                                                 let testOpTarget=z.TestOperations1
                                                        .FirstOrDefault(q=>q.TargetSubVariantId==z.Id)
                                                 let testOpSource=z.TestOperations
                                                        .FirstOrDefault(q=>q.SourceSubVariantId==z.Id)
                                                 select new Customsubvariantlist
                                                 {
                                                     Name = z.Name,
                                                     Value = x.Id==z.VariantId? 
                                                             testOpTarget.TargetValue??
                                                             testOpSource.SourceValue:null,
                                                     DiffPerc = x.Id==z.VariantId? 
                                                                testOpTarget.DiffPerc:null
                                                 }).ToList()
                     };

        var json = JsonConvert.SerializeObject(result.ToList());
        Console.WriteLine(json);
        Console.ReadKey();
    }

此查询产生与上述方法相同的结果。 请让我知道您是否需要!

更新:这是案例 2 的查询

 var result = from x in db.Variants
            select new PageViewModel
            {
                ParentVariant = x.Name,
                Type = x.Type,
                CustomSubvariantList = (from z in db.SubVariants.GroupBy(g => g.Name)
                    .Select(g => g.FirstOrDefault(d => d.VariantId == x.Id) ?? g.FirstOrDefault())
                    let testOpTarget = z.TestOperations1
                        .FirstOrDefault(q => q.TargetSubVariantId == z.Id)
                    let testOpSource = z.TestOperations
                        .FirstOrDefault(q => q.SourceSubVariantId == z.Id)
                    select new Customsubvariantlist
                    {
                        Name = z.Name,
                        SubVariantId = z.Id,
                        CombineName =(z.TestOperations.Any() || z.TestOperations1.Any())? 
                                      testOpTarget.TargetValue.HasValue? 
                                      testOpTarget.SubVariant.Name+" to "+testOpTarget.SubVariant1.Name : null: "Undefined",
                        Value = x.Id == z.VariantId
                            ? testOpTarget.TargetValue ??
                              testOpSource.SourceValue
                            : null,
                        DiffPerc = x.Id == z.VariantId
                            ? testOpTarget.DiffPerc
                            : null
                    }).OrderBy(k => k.SubVariantId).ToList()
            };

关于c# - 使用左连接获取不适当的输出，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38562624/