Getting inappropriate output with left join

问题:

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:

回答1:

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:

1. created a new console app project

2. 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.

3. Since you want all SubVariants for each Varian, I created a separate method to handle the rules which you had for creating the CustomSubVariantList. 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()
            };

回答2:

This is a "variant" of your query.. it extracts all SubVariants per Variant(s) ordered as they are in the table SubVariants (by their autoseed Sql Server Identity Id), irrespective of TestOperation involved and avoid duplicates as your expected outputs.

Unfortunately it isn't possible to have always all subvariants designated as source and tartget subvariant, because this information depends on testoperation, in fact testop decides if a subvar is "source" or "target". So, if some subvariants isn't included into testoperations records/entity then they cannot appear as source or target.. a workaround could be to attach, next to "Name" property of anonymous type returned, also "Id SubVar"

I added 2 DTOs because entity framework doesn't allow using distinct().ToList() method to preserve order and apply custom distinct rule, so I added a step more to pass from Link To EF at Link To Object through dto objects and in this way make possible the applying the distinct custom rule..

    public class CustomerVariantDTO
    {
        public String Type;
        public String ParentVariant;
        public IEnumerable<CustomerSubVariantDTO> CustomSubvariantList;
    }

    public class CustomerSubVariantDTO
    {
        public int? VariantId;
        public int? SubVariantId;
        public int? SourceId;
        public int? TargetId;
        public String Name;
        public Decimal? DiffPerc;
    }

    sealed class CustomerSubVariantDTOComparer : IEqualityComparer<CustomerSubVariantDTO>
    {
        public int GetHashCode(CustomerSubVariantDTO obj)
        {
            return 0;
        }

        public bool Equals(CustomerSubVariantDTO x, CustomerSubVariantDTO y)
        {
            return x.SourceId == y.SourceId &&
                   x.TargetId == y.TargetId &&
                   x.Name == y.Name &&
                   x.DiffPerc == y.DiffPerc;

        }
    }


    public void DoTestOfCustomerVariantDTO()
    {
        int testId = 1;
        using (var context = new VariantsEntities())
        {
            var query =
              (from v in context.Variants
               where v.Type == "Add"
               select new CustomerVariantDTO
               {
                   ParentVariant = v.Name,
                   Type = v.Type,
                   CustomSubvariantList =
                   (
                     from sv in context.SubVariants.Select(sv => sv)
                     join x in
                     (
                         from svparent in v.SubVariants
                         from to in svparent.TestOperation
                         where to.TestId == testId
                         select new
                         {
                             svparent.Name,
                             to.DiffPerc,
                             SourceId = (int?)to.SubVariants.Id ?? 0,
                             TargetId = (int?)to.SubVariants1.Id ?? 0
                         }
                     )
                     on sv.Name equals x.Name into g
                     from x in g.DefaultIfEmpty()
                     select new CustomerSubVariantDTO{
                         VariantId = sv.VariantId,
                         SubVariantId = sv.Id,
                         SourceId = (int?)x.SourceId ?? sv.TestOperation.Select(to => to.SourceSubVariantId).FirstOrDefault(),
                         TargetId = (int?)x.TargetId ?? sv.TestOperation.Select(to => to.TargetSubVariantId).FirstOrDefault(),
                         Name = sv.Name,
                         DiffPerc = x.DiffPerc
                     }
                  ).OrderBy(csv => new { csv.VariantId, csv.SubVariantId })
               }).ToList();

            var listCustomVariants =
                query.Select(v => new{
                    Type = v.Type,
                    ParentVariant = v.ParentVariant,
                    CustomSubvariantList = v.CustomSubvariantList.Distinct(new CustomerSubVariantDTOComparer()).ToList()
                });

            foreach (var item in listCustomVariants){
                Console.WriteLine($"Type: {item.Type} - ParentVariant: {item.ParentVariant} ");
                foreach (var csubvar in item.CustomSubvariantList)
                    Console.WriteLine($"SourceId: {csubvar.SourceId} - TargetiId: {csubvar.TargetId} - NAme: {csubvar.Name} - DiffPerc: {csubvar.DiffPerc} ");
            }
        }
    }