使用左连接获取不合适的输出 [英] Getting inappropriate output with left join
本文介绍了使用左连接获取不合适的输出的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试获取变体列表，并且对于每个变体都可以获得所有子变量列表，而不管子变量在哪里
特定测试说100 。这是示例数据：
Id TestId SourceSubVariantId TargetSubVariantId DiffPerc
114 100 66 67 100.00
115 100 67 68 100.00
116 100 70 71 99.99
我有 3个变体1的子变量：
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
但请注意我输出我得到 Id为0 为变体2 子 Variant1 CustomSubvariantList ：
数据模型：
public class Variants
{
public int Id {get;组; }
public string Name {get;组; }
public string Type {get;组; }
public virtual ICollection< SubVariants> SubVariants {get;组; }
}
public class SubVariants
{
public int Id {get;组; }
public int VariantId {get;组; }
public string Name {get;组; }
public virtual Variants Variants {get;组; }
public virtual ICollection< TestOperation> TestOperation {get;组; }
public virtual ICollection< TestOperation> TestOperation1 {get;组;
}
public class TestOperation
{
public int Id {get;组; }
public Nullable< int> TestId {get;组; }
public int SourceSubVariantId {get;组; }
public int TargetSubVariantId {get;组; }
public decimal DiffPerc {get;组; }
public virtual SubVariants SubVariants {get;组; }
public virtual SubVariants SubVariants1 {get;组; }
public virtual Test Test {get;组; }
}
查询：
int testId = 100;
var query =
from v in context.Variants
其中v.Type ==Add
选择新
{
ParentVariant = v.Name ，
Type = v.Method，
CustomSubvariantList =
（
from svName in context.SubVariants.Select（sv => sv.Name）.Distinct（）
加入x
（
从sv在v.SubVariants
从sv.TestOperation
其中to.TestId == testId
orderby sv.Id
选择新
{
sv.Name，
to.DiffPerc，
SourceId =（int？）to.SubVariants.Id，
TargetID =（int？ ）to.SubVariants1.Id
}
）
on svName将x.Name转换为g
从x in g.DefaultIfEmpty（）
orderby x.SourceId
选择新
{
SourceId = x.SourceId？ 0，
TargetId = x.TargetID？ 0，
Name = svName，
DiffPerc = x.DiffPerc
}
）.ToList（）
};
更新： 根据评论，这是示例输入和预期输出：
案例1 ：当所有父变体中的子变量名称不同时 p>
变体：
Id名称类型CategoryId
11 Variant1添加1
12 Variant2添加1
13 Variant3添加1
14 Variant4添加1
SubVariants：
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 ：当所有父类变体中的子变量名称相同时：
SubVariants ：
Id变量名称
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（）。选择（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
}，
{
名称：xyz，
值：500，
DiffPerc ：200.0
}，
{
名称：lmn，
值：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
}，
{
名称：xxx，
值：2000，
DiffPerc：1000.0
} ，
{
Name：hhh，
Value：4000，
DiffPerc：2000.0
}
]
}
]
从这里，您可以下载示例项目。该项目是使用您发送给我的示例数据库完成的，因此Database First是可能的，一些道具或表可能具有不同的名称，请在移动项目中的代码之前检查此项目。
基本上我做了什么：
-
创建了一个新的控制台应用程序项目
-
我从你的json对象中获取了模型（我复制了json对象并将其放在一个类中，使用（vs菜单）Edit - > Past Special - >过去的JSON作为类。
-
由于您想为每个
CustomSubVariantList的规则，在这个方法中，我迭代抛出所有的SubVariants和根据您的条件创建对象。
更新： / p>
static void Main（string [] args）
{
VariantsEntities db = new VariantsEntities（）;
var result = from x in db.Variants
select new PageViewModel
{
ParentVarian t = 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）
选择新的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（）;
}
此查询产生与上述方法相同的结果。
请让我知道你需要什么!!
更新：这是Case 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 => 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）
选择新的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（）
};
I am trying to get list of variants and for each of this variants get all subvariants list irrespective of where subvariants fall for
particular Test say 100.This is sample data:
Id TestId SourceSubVariantId TargetSubVariantId DiffPerc
114 100 66 67 100.00
115 100 67 68 100.00
116 100 70 71 99.99
I have 3 subvariants for Variants 1 :
Id=66,Name=Abc
Id=68,Name=Pqr
Id=69,Name=xyz
I have 3 subvariants for Variants 2 :
Id=70,Name=lmn
Id=71,Name=xxx
Id=72,Name=hhh
But notice in my output in am getting all Id as 0 for Variants 2 subvariants list in Variant1 CustomSubvariantList:
Data Model:
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; }
}
Query:
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()
};
Update: Based on comments this is the sample inputs and expected output:
Case 1:When subvariants name are different in all parent variants
Variants:
Id Name Type CategoryId
11 Variant1 Add 1
12 Variant2 Add 1
13 Variant3 Add 1
14 Variant4 Add 1
SubVariants:
Id VariantId Name
66 11 Abc
67 11 PQR
68 11 Xyz
70 12 lmn
71 12 xxx
72 12 hhh
Test Operation:
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
Expected output:
Case 2:When Subvariants name are same in all parents variants:
SubVariants:
Id VariantId Name
66 11 Abc
67 11 PQR
68 11 Xyz
70 12 Abc
71 12 PQR
72 12 Xyz
Expected Output:
解决方案
We had some discussions on chat till I understood what you want.
this is the code:
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;
}
}
}
you just need to replace the DbContext name, and test it.
this is the result:
[
{
"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
}
]
}
]
From here you can download the sample project. This project was done with the sample database you sent me, so is Database First and is possible that some props or tables to have a different name, please check this before moving the code in your project.
Basically what I did:
created a new console app project
I got the model from your json object (I copied the json object and pase it in a class, using (vs menu) Edit -> Past Special -> Past JSON as Classes.
Since you want all
SubVariantsfor eachVarian, I created a separate method to handle the rules which you had for creating theCustomSubVariantList. In this method I iterate throw all SubVariants and I created objects base on your conditions.
Update: your solution in one query:
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();
}
this query produce the same result like above method. Please let me know if is what you need!!
Update: this is the query for Case 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()
};
这篇关于使用左连接获取不合适的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
