Get data from both tables in LINQ

Question

I am trying to fetch an IP-address column which is present in both tables, A and B. Table A has many IP-adresses and a specific IP-address which is not present in table B. I want distinct IP-addresses from both the tables. That means I want all IP-adresses from table A which are not present in table B, plus all IP-adresses from table B which are not present in table A. I am trying the below code which is only giving me the IP-adresses which are not present in table B. Please help.

var ips= (from a in CS.A
 where !CS.B.Any(f => f.IPAddress == a.IPAddress)
    select a.IPAddress).Distinct();

Possible duplicate of [how to convert sql union to linq](http://stackoverflow.com/questions/2744205/how-to-convert-sql-union-to-linq) — Random Dev, Jan 29 '16 at 05:43
just use the `.Union` operator (see the link I gave with the close-vote) — Random Dev, Jan 29 '16 at 05:44

Viplock · Answer 1 · 2016-01-29T06:35:19.283

0

You can simply use this

 var ips= ((from a in CS.A
          where !CS.B.Any(f => f.IPAddress == a.IPAddress)
          select a.IPAddress).Distinct()).Union((from b in CS.B
          where !CS.A.Any(f => f.IPAddress == b.IPAddress)
          select b.IPAddress).Distinct());

edited Jan 29 '16 at 06:35

answered Jan 29 '16 at 05:45

Viplock

3,259
1
23
32

@Carsten can you please explain – Viplock Jan 29 '16 at 06:13

score 0 · Answer 2 · answered Jan 29 '16 at 08:10

You can just Concat both IP columns and group by the IPs. Then only select the groups which have a count of 1 (= IP only occurs in one table)

var res =
        (from ip in CS.A.Select(x => x.ip)
                        .Distinct()
                        .Concat(CS.B.Select(x => x.ip).Distinct())
         group ip by ip into grp
         where grp.Count() == 1
         select grp.Key).ToList();

You can test this with the following example:

var x1 = Enumerable.Range(0, 20);       
var x2 = Enumerable.Range(5, 20);

var res =
        (from ip in x1.Select(x => x).Distinct().Concat(x2.Select(x => x).Distinct())
         group ip by ip into grp
         where grp.Count() == 1
         select grp.Key).ToList();

res.ForEach(Console.WriteLine);

Devaffair · Answer 3 · 2016-01-29T06:01:43.117

-1

My first suggestion would be to use a store procedure to achieve that. Something along the lines of:

SELECT tblA.ip, tblB.ip FROM Table1 tblA
INNER JOIN Table2 tblB
ON 1=1
GROUP BY tblA.ip

the "ON" here will give you a condition that is always true.

Roughly the same query should work with linq as well. Experiment :-)

edit UNION is also an option, however most not as easily read by non-SQL developers.

Select distinct cmnTbl.ip from (SELECT tblA.ip from Table1 tblA UNION SELECT tblB.ip from Table1) cmnTbl

edited Jan 29 '16 at 06:01

answered Jan 29 '16 at 05:45

Devaffair

182
14

@Carsten You are correct. I usually prefer to use inner join and group by as it is more readable for the average Joe. But using Union and Distinct is a great "advanced" technique. – Devaffair Jan 29 '16 at 06:03
why would a simple `UNION` be harder to read than a `INNER JOIN` with a strange `ON 1=1`? - but do as you please - it just seemed really strange to me – Random Dev Jan 29 '16 at 06:31

Get data from both tables in LINQ

3 Answers3