How to group by on 2 child entities and get total of both this child entities?

Question

I want to get total variants run for my Test Version 0 i.e Test Id=100

This is my table and records:

Test:

Id      Version
100        0

Variants:

Id      Name       Type   CategoryId
11      Variant1   Diff     2
12      Variant1   Add      2
13      Variant2   Add      3
14      Variant2   Diff     2
15      Variant3   Add      6

SubVariants:

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

69      12           Abc
70      12           PQR
71      12           Xyz

72      13           Abc
73      13           PQR

74      14           Abc
75      14           PQR
76      14           Xyz

77      15           ABC
78      15           PQR

TestOperation:

Id   TestId    SourceSubVariantId   TargetSubVariantId   variation
1     100       69                    70                   0
1     100       70                    71                   20
1     100       72                    73                   90

TestOperationDifference:

Id   TestId    SourceSubVariantId   TargetSubVariantId   Unmatch
1     100       66                    67                   0
1     100       67                    68                   2
1     100       74                    75                   7
1     100       75                    76                   0
1     100       77                    78                   26

So from the above records there are total 3 variants run on 2 types of operation i.e TestOperation and TestOperationDifference and below are the 3 variants for specific Test 100:

Variants1(This run in TestOperation)
Variants2(This run in TestOperation)
Variants3(This run in TestOperationDifference)

This above 3 parent variants will come as because all this parent child variants are being used in 2 tables i.e TestOperation and TestOperationDifference.

So for finding total parent variants I need to figure out from both the tables (TestOperation and TestOperationDifference) corresponding child variants are used and based on that I need to count total parent variants.

This is my class:

public class Test
        {
            public int Id { get; set; }
            public string Version { get; set; }
            public virtual ICollection<TestOperation> TestOperation { get; set; }
            public virtual ICollection<TestOperationDifference> TestOperationDifference { 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 int  variation { get; set; }
            public virtual SubVariants SubVariants { get; set; }
            public virtual SubVariants SubVariants1 { get; set; }
            public virtual Test Test { get; set; }

        }

        public class TestOperationDifference
        {
            public int Id { get; set; }
            public Nullable<int> TestId { get; set; }
            public int SourceSubVariantId { get; set; }
            public int TargetSubVariantId { get; set; }

            public int unmatch { get; set; }

            public virtual SubVariants SubVariants { get; set; }
            public virtual SubVariants SubVariants1 { get; set; }
            public virtual Test Test { get; set; }
        }

        public class Variants
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string Type { get; set; }
            public int CategoryId { get; set; }

            public virtual ICollection<SubVariants> SubVariants { get; set; }

            public virtual Category Category { 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<TestOperationDifference> TestOperationDifference { get; set; }

            public virtual ICollection<TestOperationDifference> TestOperationDifference1 { get; set; }
            public virtual ICollection<TestOperation> TestOperation { get; set; }
            public virtual ICollection<TestOperation> TestOperation1 { get; set; }
        }

My query:

var data =(from mk in context.Test
                  select new 
                  {

                      TotalVariants = (mk.TestOperation.Select(t => t.SubVariants).Count()
                                   +
                                      mk.TestOperationDifference.Select(t => t.SubVariants).Count())
                    }).ToList();

Output :8

Expected output:3

You should *really* work on reducing the size of the question. Do you really need 5 tables in order to demonstrate the issue you're facing? You should simplify the situation until it's *just* the relevant parts (but still complete) — Jon Skeet, Jun 30 '16 at 13:26
@JonSkeet:Actually i have put this tables for easy understanding of this problems and without this table it would be little hard to know exactly what output is needed but i agree that size of question is big — I Love Stackoverflow, Jun 30 '16 at 13:42
Are you saying you think it's impossible to simplify this further? That you really need that many tables and that many properties? That seems unlikely to me. — Jon Skeet, Jun 30 '16 at 13:59
I've added an Init method to make more easily verifiable the question — Ciro Corvino, Jun 30 '16 at 14:40
oops.. the update will be visible only after peer revision. I post the static method in my answer below.. — Ciro Corvino, Jun 30 '16 at 14:55
@CiroCorvino your edit is on its way to getting rejected and as one of the rejection voters I thought you should know why: the question is already a major wall of text. Adding your code into it will only harm it further. We need less details in this question not more. — Maverik, Jun 30 '16 at 16:13
@Maverik oops.. pardon, you're right.. I understand that the comment of Jon was intended to simplify and therefore reduce the complessive size and also code present in the question.. I thought to be of aid at community adding a method for initialize the classes and find more easily a solution, but evidentelly I contributed to increase the complexity of the question.. I do delete the "edit" also from my answer post — Ciro Corvino, Jun 30 '16 at 16:40

Ciro Corvino · Accepted Answer · 2016-06-30T20:13:10.030

2

Update

Ok Learning, here it is a solution..

var tot_variants_for_test =
    (from v_name in 
       (from t_op in test 
        select new { v_name = t_op.TestOperation.Select(sv => sv.SubVariants.Variants.Name) }
       ).First().v_name 
     select v_name)
    .Union(
    (from v_name in 
      (from t_opdf in test 
       select new { v_name = t_opdf.TestOperationDifference.Select(sv => sv.SubVariants.Variants.Name) }
     ).First().v_name
     select v_name))
   .Count();

edited Jun 30 '16 at 20:13

answered Jun 30 '16 at 12:48

Ciro Corvino

2,038
5
20
33

But i have done the same thing and getting wrong output which i have mention in he question.This both query will give me output 8 but that isnt the expected output.please see the expected output – I Love Stackoverflow Jun 30 '16 at 12:51
Thank you so much for the answer – I Love Stackoverflow Jul 02 '16 at 06:00

score 2 · Answer 2 · edited May 23 '17 at 11:51

2

From top of my head, assuming I understood your confusing description. I think you need to union variants from both TestOperation and TestOperationDifference, then distinct and count them. Not sure if this will work in EF.

let toQuery = context.Test.SelectMany(mk=>TestOperation.Select(t=>t.SubVariants.Variants));
let todQuery = context.Test.SelectMany(mk=>TestOperationDifference.Select(t=>t.SubVariants.Variants));

let total = toQuery.Concat(todQuery).Disctinct().Count;

Also, your naming is confusing. You are using plural (s) for single-item references and you have SourceControlDetailId in your model that is not in table and have SubVariants and SubVariants1 instead of SourceSubVariant and TargetSubVariant. I would recommend to fix this first.

edited May 23 '17 at 11:51

Community

1
1

answered Jun 30 '16 at 13:00

Euphoric

12,645
1
30
44

Thank you so much for the answer but can you tell me why you have used selectmany instead of just select?? – I Love Stackoverflow Jul 02 '16 at 05:59
@Learning It is obvious if you know the difference between them. – Euphoric Jul 02 '16 at 08:04

score 1 · Answer 3 · answered Jun 30 '16 at 14:37

1

from test in Tests
where version == 0
let opsVariants = test.TestOperations
  .SelectMany(x => x.SourceSubVariant.Variant).Distinct()
let diffsVariants = test.TestOperationDifferences
  .SelectMany(x => x.SourceSubVariant.Variant).Distinct()
let variants = opsVariants.Union(diffsVariants)
select variants.Count();

answered Jun 30 '16 at 14:37

Amy B

108,202
21
135
185

Thank you so much for the answer – I Love Stackoverflow Jul 02 '16 at 05:58

How to group by on 2 child entities and get total of both this child entities?

3 Answers3

Linked