1

I am new to LinQ and have some issues while executing a Linq statement. Below is the summary of the same.

I have two class objects with same class type, eg: Report r1, Report r2 which has values like month, ID, values. values are different for both objects. eg:

List<Report> r1 = new List<Report>();
List<Report> r2 = new List<Report>();

        r1.Add(new Report { month = 1, ID = 1, a = 5, b = 12 });
        r1.Add(new Report { month = 1, ID = 2, a = 6, b = 13 });
        r1.Add(new Report { month = 2, ID = 1, a = 6, b = 14 });
        r1.Add(new Report { month = 2, ID = 2, a = 8, b = 15 });
        r1.Add(new Report { month = 3, ID = 2, a = 9, b = 16 });


        r2.Add(new Report { month = 1, ID = 1, a = 5, b = 22 });
        r2.Add(new Report { month = 1, ID = 2, a = 6, b = 23 });
        r2.Add(new Report { month = 2, ID = 1, a = 6, b = 24 });


        var delreport =
                            from rr1 in r1
                            join rr2 in r2 on new { rr1.month, rr1.ID }
                            equals new { rr2.month, rr2.ID } into result
                            select result;

However, I am getting blank result set. I am expecting to get r2 data (a, b values) matching with month and id of r1 along with r1 data (a, b values). hope, my question is clear, request your help.

Ramjee Gupta
  • 41
  • 1
  • 1
  • 6

3 Answers3

10

I believe this is what you are looking for

var innerJoin =
    from rr1 in r1
    join rr2 in r2 on new {rr1.Month, rr1.ID} equals new {rr2.Month, rr2.ID}
    select new { R1 = rr1, R2 = rr2 };

the new anonymous object can be any type and has access to any properties that are available for the two collections being joined.

For a left outer join this is how it would be done

var leftJoin = 
    from rr1 in r1
    join rr2 in r2 on new {rr1.Month, rr1.ID} equals new {rr2.Month, rr2.ID} into ps
    from rr2 in ps.DefaultIfEmpty()
    select new { R1 = rr1, R2 = rr2 };
Aaron Roberts
  • 1,342
  • 10
  • 21
  • Hi Aaron, Can you please help with left outer join for the same case. I wanted to see all rows of r1 dataset with r2 values for matching and 0 value for non-matching results. – Ramjee Gupta Sep 10 '17 at 04:46
  • please see this answer for a left outer join https://stackoverflow.com/questions/3404975/left-outer-join-in-linq – Aaron Roberts Sep 10 '17 at 04:49
3

This is the chain version of your query. 

var delreport= r1.Join(r2, l => l.ID, r => r.ID, (l, r) => new { lft = l, rght = r })
    .Where(x => x.lft.month == x.rght.month)
    .Select(_ => new{ _.rght.ID, _.rght.month}).ToList();

It is tested and works with your data.

0014
  • 893
  • 4
  • 13
  • 40
  • 3
    this is much less readable than the linq syntax he is using, why would he switch to this instead? – Aaron Roberts Sep 10 '17 at 04:05
  • @AaronRoberts I like doing everything in chains – 0014 Sep 10 '17 at 04:06
  • 2
    sure, but the OP is using a different syntax. I would suggest using the same syntax to answer the question since using one over the other in your case is opinion based. – Aaron Roberts Sep 10 '17 at 04:10
  • 1
    @AaronRoberts OP has **used** the syntax however did not **ask** for an answer with the same syntax. Since his is not working, he might want to use a working statement. Its up to him. – 0014 Sep 10 '17 at 04:14
  • @0014 - But this doesn't work. When I use the OP's data and your query I just get syntax errors. Did you test this? – Enigmativity Sep 10 '17 at 04:22
  • @Enigmativity OP has edited his question, please refer to the edit section then perhaps you will understand why I wrote my statement this way. There should not be any syntax errors other than the table names... – 0014 Sep 10 '17 at 04:29
  • @0014 - His variable names don't appear to have changed between edits. – Enigmativity Sep 10 '17 at 04:33
  • @0014 - Also your use of `Month` rather than `month` is causing an error. – Enigmativity Sep 10 '17 at 04:35
  • @0014 - Why have you got `.Select(_ => _)`? – Enigmativity Sep 10 '17 at 04:37
  • @Enigmativity I edited my question. Good catch with the syntax error on Month instead of month. The code was inefficient but working. Now I reduced the complexity. – 0014 Sep 10 '17 at 04:58
  • @0014 - It's now not returning any of the `a` and `b` values that the OP was after. Are you testing this before posting? – Enigmativity Sep 10 '17 at 07:10
  • @Enigmativity Yes it is tested, however you dont get it. The statement that was posted was based on the only query before the **edit** of OPs. Now click on edit and look at the template linq query OP has posted. It is returning **new { rr2.month, rr2.ID } into result** which means my answer is suppose to return what it is returning now. With that much of rep you have I cant understand how you cant think of these. If you want to know how to add a and b into my return statement just add them in the select. `new{ _.rght.ID, _.rght.month, _.rght.a, _.rght.b}` thats it. – 0014 Sep 10 '17 at 07:28
-1

If you want to join two table with some same of one column fkid is same then used following code two join both table and access to get desired result otherwise ignore.

following db is the database entities object.

Note: In your code you join different entities month and id are different in data that reason you get empty result used following code

var v = (from a in db.r1.ToList()
         join b in db.r2.ToList() on a.Month equals b.ID

         select new
          {
            Id = a.ID,
            month = b.Month // or a.Month as you want,
            values = a.values
          });

by using these you access both table value and get accepted result. thank if it is usable for you.

Rajkumar Dorkhande
  • 64
  • 6