掌握左连接不当输出 [英] Getting inappropriate output with left join
问题描述
我想获得变种的列表，并为每个这种变体得到所有 subvariants列表不论在哪里subvariants下跌了
特别测试说100 。这是样本数据：
标识TestId SourceSubVariantId TargetSubVariantId DiffPerc
114 100 66 67 100.00
115 100 67 68 100.00
116 100 70 71 99.99
我有第3 subvariants的变种1
n = 66，名称= ABC
n = 68，名称= PQR
n = 69，名称= XYZ
我有第3 subvariants的变种2
n = 70，名称= LMN
标识= 71，名称= XXX
n = 72，名称= HHH
但通知我在上午的输出让所有标识为0 为变种2 subvariants列表在 VARIANT1 CustomSubvariantList ：
的你可以下载示例项目。该项目是用你送我示例数据库完成的，因此是数据库第一，有可能是一些道具或表有不同的名称，请在您的项目移动代码前检查。
基本上我所做的：
-
创建了一个新的控制台应用程序项目
-
我从你的JSON对象模型（我复制了JSON对象，并使用（VS菜单填写Flash在一个班，）编辑 - >过去又特别的 - >过去的JSON作为类
-
由于您希望所有的
SubVariants每个瓦里安，我创建了一个单独的方法来处理，你必须创建规则的CustomSubVariantList。在这种方法我重复抛出的所有SubVariants和我。在你的条件创建的对象的基础
更新：您在一个查询的解决方案：
静态无效的主要（字串[] args）
{
VariantsEntities DB =新VariantsEntities（）;
VAR的结果=从X在db.Variants
选择新PageViewModel
{
ParentVariant = x.Name，
类型= x.Type，$ b在db.SubVariants
$ b CustomSubvariantList =（借助z让testOpTarget = z.TestOperations1
.FirstOrDefault（q => q.TargetSubVariantId == z.Id）
让testOpSource = Z。 TestOperations
.FirstOrDefault（q => q.SourceSubVariantId == z.Id）
选择新Customsubvariantlist
{
名称= z.Name，
值= X .ID == z.VariantId？
testOpTarget.TargetValue？
testOpSource.SourceValue：空，
DiffPerc = x.Id == z.VariantId？
testOpTarget.DiffPerc：空
}）了ToList（）
}。
VAR JSON = JsonConvert.SerializeObject（result.ToList（））;
Console.WriteLine（JSON）;
Console.ReadKey（）;
}
这个查询产生像上述方法相同的结果。
请让我知道，如果是你需要的东西！
更新：这是第2种情况查询
VAR的结果=从X在db.Variants
选择新PageViewModel
{
ParentVariant = x.Name ，
类型= x.Type，
CustomSubvariantList =（从db.SubVariants.GroupBy Z（G => g.Name）
。选择（G => g.FirstOrDefault（ D => d.VariantId == x.Id）?? g.FirstOrDefault（））
让testOpTarget = z.TestOperations1
.FirstOrDefault（q => q.TargetSubVariantId == z.Id ）
让testOpSource = z.TestOperations
.FirstOrDefault（q => q.SourceSubVariantId == z.Id）
选择新Customsubvariantlist
{
名称= Z .name和
SubVariantId = z.Id，
CombineName =（z.TestOperations.Any（）|| z.TestOperations1.Any（））？
testOpTarget.TargetValue.HasValue？
testOpTarget.SubVariant.Name +到+ testOpTarget.SubVariant1.Name：空：未定义，
值= x.Id == z.VariantId
？ testOpTarget.TargetValue？
testOpSource.SourceValue
：空，
DiffPerc = x.Id == z.VariantId
？ 。testOpTarget.DiffPerc
：空
}）排序依据（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屋！
